MS Access Module help please

Jazsriel · #1 12-11-2024, 04:43 PM

I have a Access form. On it I have the exact same code 4 times. I do not know how to write modules and I would like to learn how to using this code.

Code:

Dim i As Integer

If IsNull(Me.NumTrucks) Then
    GoTo One
Else
For i = 1 To 17
    Me("A" & i).Enabled = Me.NumTrucks >= 1
    Me("B" & i).Enabled = Me.NumTrucks >= 2
    Me("C" & i).Enabled = Me.NumTrucks >= 3
    Me("D" & i).Enabled = Me.NumTrucks >= 4
    Me("E" & i).Enabled = Me.NumTrucks >= 5
    Me("F" & i).Enabled = Me.NumTrucks >= 6
    Me("G" & i).Enabled = Me.NumTrucks >= 7
    Me("H" & i).Enabled = Me.NumTrucks >= 8
    Me("J" & i).Enabled = Me.NumTrucks >= 9
Next

One:
End If

And this code works in the 4 instances I mentioned. So how it works is each section "A", "B", etc... has 17 fields in each one, and yes before anyone asks or says anything, all 17 for each one are needed. When you change, exit, click previous button, or next button this code will enable the fields associated with the number input in the "NumTrucks" field. So for instance if if I put 3 in the "NumTrucks" field, then fields for "A", "B", and "C" will become enabled and allow me to input data there.

I have tried to simply copy and paste the code to a module, make it a public Function and go that route, but as everyone that is way above my level on this can see, I ran into problems. Guidance please.

macropod · #2 12-17-2024, 08:01 PM

Instead of repeating that block of code each time, you create a sub like:

Code:

Sub Trucks(t As Long)
Dim i As Long, j As Long
For i = 1 To 17
  For j = 1 To 10
    Me(Chr(64 + j) & i).Enabled = t >= j
  Next
Next
End Sub

and assuming Me.NumTrucks returns 0 when empty, then call the Trucks sub with code like:

Code:

Call Trucks(Me.NumTrucks)

Alternatively, if Me.NumTrucks doesn't return 0 when empty, you might call the Trucks sub with code like:

Code:

Call Trucks(CLng(Me.NumTrucks))

or, failing that:

Code:

Dim t As Long
If IsNull(Me.NumTrucks) Then
  t = 0
Else
  t = Me.NumTrucks
End If
Call Trucks(t)

PS: For Access related discussions, you should visit AccessForum.net: Microsoft Access Forums

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Standard Module, Class Module and UserForm	jpl	Word VBA	0	03-14-2022 07:20 AM
VBA module to Calculate from two tables	shah0101	Excel Programming	2	01-30-2022 03:10 PM
Userform variables in another module	Justice	Word VBA	1	08-26-2021 12:56 AM
Compile error in Module	ksigcajun	Word VBA	2	04-08-2015 06:44 AM
AZWizard Module - ?hidden module	pcaldwell	Word	1	08-22-2012 01:19 PM