Set Up Public Object and ComboBox variable

G

Gwhit

Hi,

I've been building Access databases for some time now, using code within the
forms and command buttons, etc. to perform various functions.

I'm trying to expand into modules with the following as a case in point,
seeking to convert from hard coded forms references (see the If...Then
statement) to a public function that allows ANY open form containing the
"PickDate" combo box to have the same coded rowsource as the one displayed:

Function DateCalc()
Dim strRowSource As String
strRowSource = Chr(34) & Format(DateSerial(Year(Date), Month(Date) + 1, 0),
"MMMM" & " " & "YYYY") & Chr(34) & ";"
strRowSource = strRowSource & Chr(34) & Format(DateSerial(Year(Date),
Month(Date), 0), "MMMM" & " " & "YYYY") & Chr(34) & ";"
strRowSource = strRowSource & Chr(34) & Format(DateSerial(Year(Date),
Month(Date) - 1, 0), "MMMM" & " " & "YYYY") & Chr(34) & ";"
strRowSource = strRowSource & Chr(34) & Format(DateSerial(Year(Date),
Month(Date) - 2, 0), "MMMM" & " " & "YYYY") & Chr(34) & ";"
strRowSource = strRowSource & Chr(34) & Format(DateSerial(Year(Date),
Month(Date) - 3, 0), "MMMM" & " " & "YYYY") & Chr(34) & ";"
strRowSource = strRowSource & Chr(34) & Format(DateSerial(Year(Date),
Month(Date) - 4, 0), "MMMM" & " " & "YYYY") & Chr(34) & ";"
strRowSource = strRowSource & Chr(34) & Format(DateSerial(Year(Date),
Month(Date) - 5, 0), "MMMM" & " " & "YYYY") & Chr(34) & ";"
strRowSource = strRowSource & Chr(34) & Format(DateSerial(Year(Date),
Month(Date) - 6, 0), "MMMM" & " " & "YYYY") & Chr(34) & ";"
strRowSource = strRowSource & Chr(34) & Format(DateSerial(Year(Date),
Month(Date) - 7, 0), "MMMM" & " " & "YYYY") & Chr(34) & ";"
strRowSource = strRowSource & Chr(34) & Format(DateSerial(Year(Date),
Month(Date) - 8, 0), "MMMM" & " " & "YYYY") & Chr(34) & ";"
strRowSource = strRowSource & Chr(34) & Format(DateSerial(Year(Date),
Month(Date) - 9, 0), "MMMM" & " " & "YYYY") & Chr(34) & ";"
strRowSource = strRowSource & Chr(34) & Format(DateSerial(Year(Date),
Month(Date) - 10, 0), "MMMM" & " " & "YYYY") & Chr(34) & ";"
strRowSource = strRowSource & Chr(34) & Format(DateSerial(Year(Date),
Month(Date) - 11, 0), "MMMM" & " " & "YYYY") & Chr(34) & ";"
If CurrentProject.AllForms("sbfrmRBSM").IsLoaded = True Then
Forms!sbfrmRBSM![PickDate].RowSource = strRowSource
ElseIf CurrentProject.AllForms("frmActiveProjects").IsLoaded = True Then
Forms!sbfrmActiveProjects![PickDate].RowSource = strRowSource
End If
End Function

Thank you.
 
M

Marshall Barton

Gwhit said:
I've been building Access databases for some time now, using code within the
forms and command buttons, etc. to perform various functions.

I'm trying to expand into modules with the following as a case in point,
seeking to convert from hard coded forms references (see the If...Then
statement) to a public function that allows ANY open form containing the
"PickDate" combo box to have the same coded rowsource as the one displayed:

Function DateCalc()
Dim strRowSource As String
strRowSource = Chr(34) & Format(DateSerial(Year(Date), Month(Date) + 1, 0),
"MMMM" & " " & "YYYY") & Chr(34) & ";"
strRowSource = strRowSource & Chr(34) & Format(DateSerial(Year(Date),
Month(Date), 0), "MMMM" & " " & "YYYY") & Chr(34) & ";"
strRowSource = strRowSource & Chr(34) & Format(DateSerial(Year(Date),
Month(Date) - 1, 0), "MMMM" & " " & "YYYY") & Chr(34) & ";"
strRowSource = strRowSource & Chr(34) & Format(DateSerial(Year(Date),
Month(Date) - 2, 0), "MMMM" & " " & "YYYY") & Chr(34) & ";"
strRowSource = strRowSource & Chr(34) & Format(DateSerial(Year(Date),
Month(Date) - 3, 0), "MMMM" & " " & "YYYY") & Chr(34) & ";"
strRowSource = strRowSource & Chr(34) & Format(DateSerial(Year(Date),
Month(Date) - 4, 0), "MMMM" & " " & "YYYY") & Chr(34) & ";"
strRowSource = strRowSource & Chr(34) & Format(DateSerial(Year(Date),
Month(Date) - 5, 0), "MMMM" & " " & "YYYY") & Chr(34) & ";"
strRowSource = strRowSource & Chr(34) & Format(DateSerial(Year(Date),
Month(Date) - 6, 0), "MMMM" & " " & "YYYY") & Chr(34) & ";"
strRowSource = strRowSource & Chr(34) & Format(DateSerial(Year(Date),
Month(Date) - 7, 0), "MMMM" & " " & "YYYY") & Chr(34) & ";"
strRowSource = strRowSource & Chr(34) & Format(DateSerial(Year(Date),
Month(Date) - 8, 0), "MMMM" & " " & "YYYY") & Chr(34) & ";"
strRowSource = strRowSource & Chr(34) & Format(DateSerial(Year(Date),
Month(Date) - 9, 0), "MMMM" & " " & "YYYY") & Chr(34) & ";"
strRowSource = strRowSource & Chr(34) & Format(DateSerial(Year(Date),
Month(Date) - 10, 0), "MMMM" & " " & "YYYY") & Chr(34) & ";"
strRowSource = strRowSource & Chr(34) & Format(DateSerial(Year(Date),
Month(Date) - 11, 0), "MMMM" & " " & "YYYY") & Chr(34) & ";"
If CurrentProject.AllForms("sbfrmRBSM").IsLoaded = True Then
Forms!sbfrmRBSM![PickDate].RowSource = strRowSource
ElseIf CurrentProject.AllForms("frmActiveProjects").IsLoaded = True Then
Forms!sbfrmActiveProjects![PickDate].RowSource = strRowSource
End If
End Function
Click to expand...


I think it would be cleaner to call this procedure from the
form containing the combo box. This way, the combo box
object can be passed to the procedure as an argument,
relieving the it of the responsibility on figuring out what
form to operate on.

Public Sub DateCalc(mycombo As ComboBox)
Dim strRowSource As String

For k = 1 To -11 Step -1
strRowSource = strRowSource & ";" & Chr(34) & _
Format(DateSerial(Year(Date), Month(Date) + k, 0), _
"MMMM" & " " & "YYYY") & Chr(34)
Next k
mycombo.RowSource = strRowSource
End Sub
 
G

Gwhit

Wow! Nice and Smooth. Glad I asked.

Marshall Barton said:
Gwhit said:
I've been building Access databases for some time now, using code within the
forms and command buttons, etc. to perform various functions.

I'm trying to expand into modules with the following as a case in point,
seeking to convert from hard coded forms references (see the If...Then
statement) to a public function that allows ANY open form containing the
"PickDate" combo box to have the same coded rowsource as the one displayed:

Function DateCalc()
Dim strRowSource As String
strRowSource = Chr(34) & Format(DateSerial(Year(Date), Month(Date) + 1, 0),
"MMMM" & " " & "YYYY") & Chr(34) & ";"
strRowSource = strRowSource & Chr(34) & Format(DateSerial(Year(Date),
Month(Date), 0), "MMMM" & " " & "YYYY") & Chr(34) & ";"
strRowSource = strRowSource & Chr(34) & Format(DateSerial(Year(Date),
Month(Date) - 1, 0), "MMMM" & " " & "YYYY") & Chr(34) & ";"
strRowSource = strRowSource & Chr(34) & Format(DateSerial(Year(Date),
Month(Date) - 2, 0), "MMMM" & " " & "YYYY") & Chr(34) & ";"
strRowSource = strRowSource & Chr(34) & Format(DateSerial(Year(Date),
Month(Date) - 3, 0), "MMMM" & " " & "YYYY") & Chr(34) & ";"
strRowSource = strRowSource & Chr(34) & Format(DateSerial(Year(Date),
Month(Date) - 4, 0), "MMMM" & " " & "YYYY") & Chr(34) & ";"
strRowSource = strRowSource & Chr(34) & Format(DateSerial(Year(Date),
Month(Date) - 5, 0), "MMMM" & " " & "YYYY") & Chr(34) & ";"
strRowSource = strRowSource & Chr(34) & Format(DateSerial(Year(Date),
Month(Date) - 6, 0), "MMMM" & " " & "YYYY") & Chr(34) & ";"
strRowSource = strRowSource & Chr(34) & Format(DateSerial(Year(Date),
Month(Date) - 7, 0), "MMMM" & " " & "YYYY") & Chr(34) & ";"
strRowSource = strRowSource & Chr(34) & Format(DateSerial(Year(Date),
Month(Date) - 8, 0), "MMMM" & " " & "YYYY") & Chr(34) & ";"
strRowSource = strRowSource & Chr(34) & Format(DateSerial(Year(Date),
Month(Date) - 9, 0), "MMMM" & " " & "YYYY") & Chr(34) & ";"
strRowSource = strRowSource & Chr(34) & Format(DateSerial(Year(Date),
Month(Date) - 10, 0), "MMMM" & " " & "YYYY") & Chr(34) & ";"
strRowSource = strRowSource & Chr(34) & Format(DateSerial(Year(Date),
Month(Date) - 11, 0), "MMMM" & " " & "YYYY") & Chr(34) & ";"
If CurrentProject.AllForms("sbfrmRBSM").IsLoaded = True Then
Forms!sbfrmRBSM![PickDate].RowSource = strRowSource
ElseIf CurrentProject.AllForms("frmActiveProjects").IsLoaded = True Then
Forms!sbfrmActiveProjects![PickDate].RowSource = strRowSource
End If
End Function
Click to expand...


I think it would be cleaner to call this procedure from the
form containing the combo box. This way, the combo box
object can be passed to the procedure as an argument,
relieving the it of the responsibility on figuring out what
form to operate on.

Public Sub DateCalc(mycombo As ComboBox)
Dim strRowSource As String

For k = 1 To -11 Step -1
strRowSource = strRowSource & ";" & Chr(34) & _
Format(DateSerial(Year(Date), Month(Date) + k, 0), _
"MMMM" & " " & "YYYY") & Chr(34)
Next k
mycombo.RowSource = strRowSource
End Sub
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Remove Quotes within Data Values 0
Remove Quotes within Data Values 1
Expected end of statement 12
Function for previous month and present year 5
Functiosn run too Slow 6
True or False for SNP or PDF format to email 12
VBA SendObject fails after the 1 VAR instance 20
Access 2007 - Need YTD AND Monthly Info From Query 0

Top