Opening Forms With A WHERE Clause

[MarkSimm]

Hi, hope someone can help,

I have a front-end form with a combo box (called ‘Combo_Team’), which also has a button on it. When the button is pressed I run the following piece of code on the click event…

Private Sub BTN_Forms_ToDoLists_Click()
Dim strTeam As String
Me!Combo_Team.SetFocus
strTeam = Me!Combo_Team.Text
MsgBox (strTeam)
DoCmd.OpenForm "FRM_Animations_Main", , , Creater = strTeam
End Sub

…you can see from this that I am trying to open another form but I am trying to use what is in the combo box as a ‘WHERE’ clause to restrict the entries in the form.

This is not working. The form I am trying to open has an entry called ‘Creater’ on it (it is a list of peoples names) I am trying to restrict the forms entries to that of one person. The message box tells me that I am getting the correct string from the combo box but when the form is opened all of the records are filtered even the ones with the persons name I have selected (from the combo) in the ‘Creater’ field.

Please help!

Mark Simmons

 

[D-Fresh]

You need quotes around the where clause... Try this...

DoCmd.OpenForm "FRM_Animations_Main", , , "Creater = '" & strTeam & "'"

Hope that helps....

Doug

 

[MarkSimm]

Thanks for that. You are an Access god!

)

I tried it and it worked. That is not the whole problem however...

I now want to extend the current WHERE statement to filter on another field, so currently we have...

"Creater = '" & strTeam & "'"

...I want to also filter the records where the field 'Status = Not started Or In Progress'. How do I add these fields? I have tried the following...


"Creater = '" & strTeam & "'" And "Status = '" & "Not Started" Or "In Progress"

...but the syntax is confusing me. Can you help? What significance is the ' in the "'"?

Thanks for you help

Mark Simmons

 

[D-Fresh]

Okay, when you're building a string, use ampersands(&) and closed quotes to concatenate a string with a variable... If you're using straight text, like 'Not Started', then you don't have to close the quote, just put the text in tick marks(')... Also, when you use AND and Or, you have to repeat the variable... You can't say status='In Progress' or 'Not Started' you have to compare each to status... Use the code below.. That should work for you. Sorry if this sounds confusing.. It's really not once you get the hang of it...

"Creater = '" & strTeam & "' And Status ='Not Started' Or Status='In Progress'"


Hope this helps..

Doug

 

[MarkSimm]

Works just fine, and I get it now. Thanks for the help.

Mark