Calling a Function on Open(Return Value to txtbox)

[George10988]

Hey guys, I am trying to call a function that I made when a form is opened. I then want the function return value to populate into a textbox on that form. I know this is very simple because I know C#; however, I am new to VBA and I just cant seem to get this to work.

Basically I am trying to autopopulate a "Month" text box on a report with the current month. I originally used Month(Now()) and got an integer back; however, I want to return a string with the actual name of the month. This is what I have so far

'This is in my general declarations of the report

Public Function GetMonth(MonthName) As String
'This function will convert the returned month int to a string with the name of the month
'-----------------------------
'---------
'Author- George Campos
'Date- 5/6/2011
'Company- Accutron Systems
'-----------------------------
'---------
Dim ReturnMonth As Integer
Static MonthName As String
ReturnMonth = Month(Now())
 
Select Case ReturnMonth
Case 1
MonthName = "January"
Case 2
MonthName = "February"
Case 3
MonthName = "March"
Case 4
MonthName = "April"
Case 5
MonthName = "May"
Case 6
MonthName = "June"
Case 7
MonthName = "July"
Case 8
MonthName = "August"
Case 9
MonthName = "September"
Case 10
MonthName = "October"
Case 11
MonthName = "November"
Case 12
MonthName = "December"
End Select
 
End Function

'Then I have this on the open event

Private Sub Report_Open(Cancel As Integer)
Call GetMonth
 
Me.txtRptReportMonth = MonthName
End Sub

Any help would be much appreciated

 

[DJkarl]

Try putting that into the Load event instead of the Open event.

also you could achieve the same result using this

format(month(now),"MMMM")

 

[George10988]

DJKarl:

Thanks for the quick response. Exactly where would I put your suggested code at? Would it be in the text box that I want to autopopulate?

Also I have tried putting my "On open" code in my "On Load" event and I am still getting the same errors. "The expression on open you entered as the event property setting produced the following error: Only comments may appear after End Sub, End Function, or End Property

"
This error occurs when an event has failed to run because the location of the logic for the event cannot be evaluated. For example, if the OnOpen property of a form is set to =[Field], this error occurs because a macro or event name is expected to run when the event occurs."

 

[DJkarl]

DJKarl:

Thanks for the quick response. Exactly where would I put your suggested code at? Would it be in the text box that I want to autopopulate?

Also I have tried putting my "On open" code in my "On Load" event and I am still getting the same errors. "The expression on open you entered as the event property setting produced the following error: Only comments may appear after End Sub, End Function, or End Property

"
This error occurs when an event has failed to run because the location of the logic for the event cannot be evaluated. For example, if the OnOpen property of a form is set to =[Field], this error occurs because a macro or event name is expected to run when the event occurs."

In the Forms Load event.

Private Sub Form_Load()
Me.YourTextBox = Format(Month(Now), "MMMM")
End Sub

FYI - If you are just typing in the code window Access will not automatically pick up the event, in the form properties make sure the OnLoad event is set to [Event Procedure].

 

[George10988]

Thanks again for the quick response I'm actually at work right now trying to get this running and I hate when I waste time through trial and error!

It works now except for the fact that it is filling in the month January when it is May. My system date is correct also because I can see it says 5/6/2011

 

[DJkarl]

My bad on that one, drop the Month() from the function.

Or use

Monthname(month(now))

 

[George10988]

Awesome! thanks.