Open form based on two fields (1 Viewer)

[neiljw24]

Can someone please give some advise on a problem i have.

I have a form that is based on a table and i use this form to display search results.

I need to open the search results form based on two data fields on my criteria form.

I can open the form based on one of these fields but not the two.

Can anyone help please.

Thanks

 

[pbaldy]

What have you got so far? What data types are the 2 fields?

 

[neiljw24]

The two fields are Month and Year so a text field and a numerical field.

Using the access button wizard i can match up the year but not the month.

Also tried using a combo box but not experienced in usng them.

I want the user to type in the Month in one text box and the year in the next field, then hit the search button.

The results page has two fields also Month and Year.

I can't use the dlookup on the results page, i think, because it opens based on a few diffrent criteria forms.

I hope i explained it ok
Thanks

 

[pbaldy]

I was hoping you'd post the code you had so far, but this should work as a wherecondition:

"[Month] = '" & Me.[Month] & "' AND [Year] = " & Me.[Year]

Neither of those are good field names, since there are functions of the same names.

 

[neiljw24]

Sorry. My first day looking for help.

I see about the names. I've changed the field names on the invoice Table to InvoiceMonth and InvoiceYear.

I've also changed the form text boxes to criteriaMonth and criteriaYear.

This is the code i have. Bear in mind this is just the code behind the Access button wizard.

Private Sub SearchbyMonthYear_Click()
On Error GoTo Err_SearchbyMonthYear_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "SearchResults"

stLinkCriteria = "[InvoiceMonth]=" & "'" & Me![CriteriaMonth] & "'"

DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_SearchbyMonthYear_Click:
Exit Sub
Err_SearchbyMonthYear_Click:
MsgBox Err.Description
Resume Exit_SearchbyMonthYear_Click

End Sub
I just tried putting in the new where condition at the stLinkCriteria and i got a warning "Microsoft Access says can't find the field '|' referred to in your expression."

Sorry, self teaching myself hence the confusion i'm experiencing.

Thanks

 

[pbaldy]

No need to be sorry; you're doing fine. Try:

stLinkCriteria = "[InvoiceMonth]= '" & Me![CriteriaMonth] & "' AND InvoiceYear = " & Me.criteriaYear

 

[neiljw24]

Thanks Paul.

It worked great. Thanks for your help. I have a funny feeling that you'll see my name popping up here more often.

Thanks again
Neil

 

[Sharkiness]

Hi,

I have tried this but it doesnt seem to work for me. I want the button to show details based on the reference and suffix. Here is the code used

Private Sub ViewDetails_Click()
On Error GoTo Err_ViewDetails_Click
    Dim stDocName As String
    Dim stLinkCriteria As String
    stDocName = "FrmDetails"
    
    stLinkCriteria = "[FI REF]=" & "'" & Me![Reference] & "'" And Suffix = "Me.Suffix"
    DoCmd.OpenForm stDocName, acFormDS, , , stLinkCriteria
Exit_ViewDetails_Click:
    Exit Sub
Err_ViewDetails_Click:
    MsgBox Err.Description
    Resume Exit_ViewDetails_Click
    
End Sub

Hopefully you can help me also. Thanks

 

[Sharkiness]

BY the way I get a message stating 'Type Mismatch'

 

[neiljw24]

Hey Sharkiness. I'm not very good at VBA code.

The only fifference i can see between you code and the code that Paul sent me is:

Your cose seems to be missing an amber sign & at line

stLinkCriteria

Try this

stLinkCriteria = "[FI REF]= '" & Me![Reference] & "' AND Suffix = " & Me.Suffix

Hope this works.
Neil

 

[pbaldy]

Neil's correction to the code is correct, providing the first is text and the second is numeric. If they're both text:


stLinkCriteria = "[FI REF]= '" & Me![Reference] & "' AND Suffix = '" & Me.Suffix & "'"

 

[Sharkiness]

Hey Mate,

Can you still offer some help on this. I am working on a new form.

I have three sections where the form can be open based on certain criteria.

The first search can be a search on ID.

The second search can be on Surname and Postcode.

Up to now these two searches work. The same form opens for both these searches. This form's data source is a query where I have put the query criteria as follows.

Crtieria for ID field [Forms]![Search].[ID] OR
Criteria for surname field [Forms]![Search].[SURNAME] and Postcode Field Criteria [Forms]![Search].[POSTCODE]

When I try to add the search criteria for Surname and DOB I get no results even though I am searching for data that is definitely in my table. I have updated the query with similar to above in the next OR section but the form doesnt seem to like it.

Can you hekp at all?

Thanks

David

 

[pbaldy]

The criteria is in the query? What is the SQL of the query?