Opening Form at specific record using multiple criteria

[Dan9684]

Hi,

I actually posted on this a while ago but got nowhere, had to drop it, but now I still can't fix it.

I have a database, on which there is a menu, [frmMenu]. I have linked a table [tblMenuSearch] to this menu, it has fields [GIOSearch] and [MonthSearch] on it only.

The two fields appear on the [frmMenu] as well as a button. I need to be able to fill in the two fields e.g. [GIOSearch] with a seven digit number i.e. 1234567 and the [MonthSearch] with i.e. 01/12/2018

When these two are completed, pressing the button needs to bring up a record in another form [frmMain] (filled with data from [tblMain]) which along with those two fields, will bring a lot of associated data.

Fields in [tblMenuSearch] - [GIOSearch] links to [GIOCode] on [tblMain] and [MonthSearch] links to [MonthEnding] on [tblMain]

The current code on the button is:

Private Sub Command5_Click()
On Error GoTo Err_Command5_Click


Dim stDocName As String
Dim stLinkCriteria As String




stDocName = "frmMain"

stLinkCriteria = "[GIOCode]=" & Me![GIOSearch] & " And [MonthEnding]=#" & Me![MonthSearch] & "#"
DoCmd.OpenForm stDocName, , , stLinkCriteria


Exit_Command5_Click:
Exit Sub


Err_Command5_Click:
MsgBox Err.Description
Resume Exit_Command5_Click

End Sub

Can anyone see where I'm going wrong please?

Thanks in advance.

[Dan9684]

Attached database if that helps.

[Minty]

It's not working because in your frmMain on open event you are forcing the form to go to a new record.

You also need to format the date correctly in VBA, change the line below. Read up here for why http://allenbrowne.com/ser-36.html

    stLinkCriteria = "[GIOCode]=" & Me.GIOSearch & " And [MonthEnding]=#" & Format(Me.MonthSearch, "yyyy-mm-dd") & "#"

Also note the use of Me. not Me! as well, this allows intellisense to kick in, and ensures you are referring to the forms objects.
Finally add

Option Compare Database
Option Explicit

To the top of ALL your code modules, it will help with variable typos etc.

[mike60smart]

Hi

I changed the Search Controls to be Combobox's Looking for the values stored in tblMain.
I modified the Code on the Search button to use the following :-

Private Sub Search_Click()


    On Error GoTo Search_Click_Error
Dim strCrit As String
If Len(Me.txtGIO & vbNullString) > 0 Then
    strCrit = strCrit & "([GIOCode] = " & Me.txtGIO & ") AND "
  End If


If IsDate(Me.txtMonth) Then
  strCrit = strCrit & "([MonthEnding] >= #" & Format(Me.txtMonth, "dd/mm/yyyy") & "#) AND "
End If
If strCrit > "" Then
  strCrit = Left(strCrit, Len(strCrit) - 5)
  
DoCmd.OpenForm "frmSearch", acNormal, , strCrit




End If
    
    On Error GoTo 0
    Exit Sub


Search_Click_Error:


    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Search_Click, line " & Erl & "."


End Sub

Your tblMain has Multiple Repeating Groups which should I believe be normalised.

[Minty]

@Mike - that date format in the where clause will fail to filter correctly, you have to use either mm/dd/yyyy or preferably yyyy-mm-dd (It's so much easier to determine exactly what the date is that way around visually.)

[mike60smart]

Hi Minty

Yes you are right. I overlooked the format of the Date but it should as you say follow the USA Format for Dates as I have been told many times by the Guru's.