filter form based on another forms combo boxes

[gwog]

I have a form which contains 3 combo boxes (cboProdDate, cboLocation, and cboItem) and a command button that opens a form frmupdate2.

Here is the on click event procedure:


Private Sub cmdupdate2_Click()
On Error GoTo Err_cmdupdate2_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmUpdate2"

stLinkCriteria = ("[Item]=" & "'" & Me![cboItem] & "'") & ("[Production Date]=" & "#" & Me![cboProdDate] & "#") & ("[Location]=" & "'" & Me![cboLocation] & "'")

DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdupdate2_Click:
Exit Sub

Err_cmdupdate2_Click:
MsgBox Err.Description
Resume Exit_cmdupdate2_Click

End Sub

The problem is when I click the button I get the following error message:

"Syntax error (missing operator) in query expression '[Item]='B00800800'[Production Date]=#3/23/2004#[Location]='WP".


Any idea what syntax error I have?

Thanks.

ps. I use Access 97.

 

[RoyVidar]

Hi!

The where condition of the openform method of the docmd object, is a valid SQL where statement without the keyword Where, so you need to use something like:

[tt]stLinkCriteria = "[Item]='" & Me![cboItem] & "' AND [Production Date]=#" & Me![cboProdDate] & "# AND [Location]='" & Me![cboLocation] & "'"[/tt]

(just put AND's there, might be OR's?)

Roy-Vidar

 

[gwog]

Thanks Roy-Vidar. I tried both and - or

Both give me a type mismatch error.

Any other suggestions?

 

[RoyVidar]

Date type mismatch does not refer to AND/OR but to data type mismatch between the values passed and the fields they are used as criteria for. There might of course be typos etc, but:

1 - check that the datatypes of the fields are correct in relation to the values you pass (item is text, production date is date/time, location is text)
2 - try incremental (only one criteria at the time)

Roy-Vidar

 

[gwog]

The field types are correct. When I use individual command buttons they each work fine (no type mismatch error). But of course they only filter the 2nd form by the 1 combo box.

Here is the event procedure criteria for each:

1) Production Date:
stLinkCriteria = "[Production Date]=" & "#" & Me![cboProdDate] & "#"

2) Location:
stLinkCriteria = "[Location]=" & "'" & Me![cboLocation] & "'"


3) Item:
stLinkCriteria = "[Item]=" & "'" & Me![cboItem] & "'"


I also tried the following:

stLinkCriteria = [Production Date] = Me![cboProdDate] And [Location] = Me![cboLocation] And [Item] = Me![cboItem]

This did not give me any errors - but it also did not give me any data - it opened the form but the form was blank.

thanks for your continued help.

 

[RoyVidar]

No - it needs to be like in my first reply.

Do a Debug.Print stLinkCriteria after you've assigned it.

Study it in the immidiate pane (ctrl+g), and if you don't figure out how to amend, post it back here together with the complete stLinkCriteria assigning statement you use.

Roy-Vidar