Opening Specific Records using a button

[Dan9684]

Hi All,



I have a form [frmMain] on which lots of information is entered, including [Date] and [Code]. There will only be one record with each combination of the two.

I have a menu on another form [frmMenu], where I have a [Date] and [Code] field, and a button.

How do I get the button to open the [frmMain] form on the record matching the values in the [Date] and [Code] fields on the menu?

Thanks in advance!

[Dan9684]

EDIT just to say I know I'm close

I'm at

stLinkCriteria = "[GIOCode]=" & Me![GIOSearch] And "[MonthEnding]=" & "#" & Me![MonthSearch] & "#"

it just says type mismatch?

[Homegrownandy]

date is a reserved word in access, you want to rename that field.

[davegri]

stLinkCriteria = "[GIOCode]=" & Me![GIOSearch] & " And [MonthEnding]=" & "#" & Me![MonthSearch] & "#"

AND should be included within quotes, and be sure to include the space before.

[Gicu]

And if the code is text you need to wrap it in single codes:

stLinkCriteria = "[GIOCode]='" & Me![GIOSearch] & "' And [MonthEnding]=#" & Me![MonthSearch] & "#"

Cheers,
Vlad

[Dan9684]

AND should be included within quotes, and be sure to include the space before.

homegrownandy, it's not actually date.

Dave, this doesn't return a mismatch but it also just returns a blank field? What would that mean?

[Dan9684]

And if the code is text you need to wrap it in single codes:

stLinkCriteria = "[GIOCode]='" & Me![GIOSearch] & "' And [MonthEnding]=#" & Me![MonthSearch] & "#"

Cheers,
Vlad

Thanks Vlad

'Data type mismatch in criteria expression'

Does it matter that the GIOCode is number format?

[Dan9684]

Just to sum up. I have code:

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

The two fields on the menu are [GIOSearch] and [MonthSearch] and the two fields to pick up from frmMain are [GIOCode] and [MonthEnding]

[Gicu]

if number you do not need the single quotes.

[Gicu]

Can you show us a sample value for MonthEnding?

[Dan9684]

I see in tblMain a record with GIOCode 6002538 and MonthEnding 01/12/2018 and input those into the fields in the menu which are the same format. Yet the button returns the form frmMain with no data

stLinkCriteria = "[GIOCode]=" & Me![GIOSearch] & " And [MonthEnding]=#" & Me![MonthSearch] & "#"

[Gicu]

Is the GioCode type set as Long or is it text - when you look at the value in the datasheet feeding the frmMain form is it left or right justified?

Put a break on the Docmd.OpenForm line and look at the value of your stLinkCriteria string in the immediate window just to confirm that everything is there.

Cheers,
Vlad

[Dan9684]

Is the GioCode type set as Long or is it text - when you look at the value in the datasheet feeding the frmMain form is it left or right justified?

Put a break on the Docmd.OpenForm line and look at the value of your stLinkCriteria string in the immediate window just to confirm that everything is there.

Cheers,
Vlad

It's right justified.

Not sure what the second part of your post means?

[Dan9684]

Hi just a thought on this

My code says stLinkCriteria = "[GIOCode]=" & Me![GIOSearch] & " And [MonthEnding]=#" & Me![MonthSearch] & "#"

MonthSearch and MonthEnding are both 01/12/2018 as in 1st December.

When I run a query from the database for that date and look at the SQL view it says:
WHERE (((tblMain.MonthEnding)=#12/1/2018#));

Could it be this format of the date I'm getting wrong? How can I change the format if so?

[Gicu]

Try:

stLinkCriteria = "[GIOCode]=" & Me![GIOSearch] & " And [MonthEnding]=#" &format(Me![MonthSearch],,"MM/DD/YYYY") & "#"

Cheers,
Vlad