Sleeper: Get a record number based on a field value

[Jmoreau]

I'm trying to open a form to a certain record based on the value of a field in the form.
I can't figure out how to get that certain record. The field is 'mth' so I need to have the record number where me.mth.value = strmonthyear
Can anyone help with this seemingly easy task?
Below is what I have so far:



Private Sub Form_Load()
Dim strMonthNum As Integer 'Month number (1-12)
Dim strMonth As String 'Month name (January through December)
Dim strYear As Integer 'Year number (2012 etc)
Dim strMonthYear As String 'Concatenated month and year (January2012 etc)
Dim strFormName As String
Dim recordID As String
strFormName = "frmByMonth"
strMonthNum = Month(Date)
If strMonthNum = 1 Then
strMonth = "January"
ElseIf strMonthNum = 2 Then strMonth = "February"
ElseIf strMonthNum = 3 Then strMonth = "March"
ElseIf strMonthNum = 4 Then strMonth = "April"
ElseIf strMonthNum = 5 Then strMonth = "May"
ElseIf strMonthNum = 6 Then strMonth = "June"
ElseIf strMonthNum = 7 Then strMonth = "July"
ElseIf strMonthNum = 8 Then strMonth = "August"
ElseIf strMonthNum = 9 Then strMonth = "September"
ElseIf strMonthNum = 10 Then strMonth = "October"
ElseIf strMonthNum = 11 Then strMonth = "November"
ElseIf strMonthNum = 12 Then strMonth = "December"
End If
strYear = Year(Date)
strMonthYear = strMonth & strYear
recordID = Me.Mth
'strMonthNum = strMonthNum + 23 'I keep having to add 12 to this number, but don't want to use that anymore (started with February2012)
DoCmd.GoToRecord acForm, "frmByMonth", acGoTo, strMonthNum
End Sub

[Jmoreau]

Anyone? It kinda jumbled up the code, so if I need to clarify anything, no problem.

[jonh]

Is there a reason you can't use a query? or a filter?

from memory a filter should be something like

me.filter = "[my field]='some value'"
me.filteron = true

to remove the filter

me.filteron = false

[Jmoreau]

Thanks for your response!
I'm not familiar with filters. I have the form opening based on a query, and then a subform inside of that. What I'm trying to accomplish is to basically get the record number where mth.value = January2016, and then assign it to strmonthnum, so I can pass it that value in the open forms parameter. I'm wondering if a For loop might retrieve the correct record number?

[jonh]

Well I would just set the recordsource in the open event.


Me.RecordSource = "select * from tableX where [mth]='" & MonthName(Month(Date)) & Year(Date) & "'"

you can find records in the form's underlying recordset


Dim f As Form
DoCmd.OpenForm "form1"
Set f = Forms("form1")


f.Recordset.FindFirst "[mth]='" & MonthName(Month(Date)) & Year(Date) & "'"


and you can supply a where clause when opening the form from other code


DoCmd.OpenForm "form1", , , "[mth]='" & MonthName(Month(Date)) & Year(Date) & "'"