4 Different CRITERIA used to open a FORM 2

[ZaZa]

Hi everyone,

Can anyone show me how to use multiple criteria from 3 or 4 different combo boxes as a filter when openinig a form?

I want the user to select from the following comboBoxes on a form:
------------
System (text)
Discipline (text)
Status (integer)
Employee (integer)
---------
After he has made his selection I want hime to hit a command buttion to open up a form that displays only the records that meets the four criteria above.

I keep getting a "data mismatch error" or a "cannot find the field specified ..." But from looking at previous posts this seems the way to do it.

Here is my [pathetic] code thus far:
-----------------------------
On Error GoTo Err_OpenForm_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "PunchItemsComments"

stLinkCriteria = "[SystemID]= "& Me![System]and [DisciplineID] = " & Me![Discipline] and [StatusID] = " & Me![Status] and [PersonAssigned]= " & Me!Employee
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_OpenForm_Click:

Exit Sub

Err_OpenForm_Click:
MsgBox Err.Description
Resume Exit_OpenForm_Click

-------------------------

Any help will be greatly appreciated. I think that maybe the problem lies with teh fact that some of the fields are integer while others are text ???!!?? If so how do I rewrite the strCriteria so accomodate the tow different data types????


Anxiuosly awaiting any help,
ZaZa

 

[ZaZa]

Can someone PLEEEEEAAASSEEE HELP??!!

Zaza

 

[Maquis]

You have the basic idea correct. Your spacing and quotes are incorrect but that may just be typos from pasting to this forum.

You said the 1st two fields are text fields. This is causing your type mismatches. If a field is a text field then you need quotes around it. For example:

select * from table1 where FirstName = Anna; won't work, but select * from table1 where FirstName = "Anna"; will work.

Sooo, that said you need something like this in your statement:

stLinkCriteria = "[SystemID]= '" & Me![System] & "' And [DisciplineID] = '" & Me![Discipline] & "'

Another point to consider which may or may not be an issue for you... Will your users always pick something from all 4 comboboxes? If so your statement is fine as it is. If not then you need to take into account the possibility of no selections in the combo boxes.

stLinkCriteria = ""
If Not(IsNull(me![System])) then
stLinkCriteria = "[SystemID] = '" & me!System & '"'
end if

If Not(IsNull(me!Discipline)) then
if stLinkCriteria = "" then
stLinkCriteria = "[DisciplineID] = '" & me!Discipline & "'"
else
stLinkCriteria = stLinkCriteria & " and "[DisciplineID] = '" & me!Discipline & "'"
end if
end if

repeat for next 2 entries.


Maq B-)
<insert witty signature here>

 

[tlbroadbent]

Add quotes around the string or character data types. You are also missing some required quotes for the query string. Try something like the following.

stLinkCriteria = &quot;[SystemID]= &quot; & Me![System] & _
&quot; And [DisciplineID] = &quot; & Me![Discipline] & _
&quot; And [StatusID] = &quot; & Me![Status] & _
&quot; And [PersonAssigned]= &quot;&quot;&quot; & Me!Employee & &quot;&quot;&quot;&quot; Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[ZaZa]

Marquis, Terry, Thanks for responding,

I hadn't condsidered the possibility of some of the fields being blank so thanks for the tip.

I have tried following Marquis suggestion but now I keep getting a paramater prompt for the the 2, 3rd amd 4th text boxes on the form ie[Discipline], [Status] and [Employee]

I think the trouble lies with the word &quot;AND&quot; when trying join the new criteria to the old criteria. When I used marquis 's code:

stLinkCriteria = stLinkCriteria & &quot; and &quot;[DisciplineID] = '&quot; & me!Discipline & &quot;'&quot;

I was getting an error so I removed the &quot; after the word AND {Don't ask me why) and the line wasn't red anymore.

Here is what I have written so far, following closely (I hope)on Marquis's outline:

-------------------------------------
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = &quot;frm02-PunchItemsComments&quot;

stLinkCriteria = &quot;&quot;
If Not (IsNull(Me![System])) Then
stLinkCriteria = &quot;[SystemID] = '&quot; & Me![System] &&quot;'&quot;
End If

If Not (IsNull(Me![Discipline])) Then
If stLinkCriteria = &quot;&quot; Then
stLinkCriteria = &quot;[DisciplineID]= ' &quot; & Me![Discipline]& &quot; ' &quot;
Else
stLinkCriteria = stLinkCriteria & &quot; and [DisciplineID] = ' &quot; & Me![Discipline] & &quot;'&quot;
End If
End If

If Not (IsNull(Me![StatusID])) Then
stLinkCriteria = stLinkCriteria & &quot; and [StatusID]= '&quot; & Me![StatusIDFilter] & &quot;'&quot;
End If

If Not (IsNull(Me!Employee)) Then
stLinkCriteria = stLinkCriteria & &quot; and [PersonAssigned]= ' &quot; & Me!Employee & &quot;'&quot;
End If

--------------------

Anxious,
Zaza

 

[Maquis]

Since you are getting parameter prompts it sounds like your quotes aren't lined up properly in the stLinkCriteria statement. The best way to fix this is to put a temporary msgbox statement right before your docmd.openform and display the stLinkCriteria variable. That way you can make sure it equals what you think it equals. If not, you'll know exactly where the quotes are wrong.

msgbox stLinkCriteria


Also, I noticed that you put single quotes around all your fields. I thought you said earlier that 2 of the fields were numbers. Numbers shouldn't have quotes. Just text fields. Maq B-)
<insert witty signature here>

 

[ZaZa]

Marquis, terry,

Again thanks for both you help.
I finally got it working after I sat down an drew a decisionn tree with all the possible outcomes ( 16 outcomes) and I made sure that my code catered for all the possibilities.

Marquis, that heads-up about the null values in the criteria was very important. Thanks.

If anyone is interested, here is my code:

---------------

On Error GoTo Err_Command11_Click

Dim stDocName As String
Dim stLinkCriteria As String
Dim intX As Integer

stDocName = &quot;frm02-PunchItemsComments&quot;

'Start with no criteria defined.
stLinkCriteria = &quot;&quot;
If Not (IsNull(Me![System]) Then
stLinkCriteria = &quot;[SystemID] = '&quot; & Me![System] & &quot; ' &quot;
End If


'Check the Dsicipline checkbox.
If Not (IsNull(Me![Discipline])) Then
If stLinkCriteria = &quot;&quot; Then
stLinkCriteria = &quot;[DisciplineID] = '&quot; & Me![Discipline] & &quot;'&quot;
Else
stLinkCriteria = stLinkCriteria & &quot;and [DisciplineID] = '&quot; & Me![Discipline] & &quot;'&quot;
End If
Else
stLinkCriteria = stLinkCriteria '& &quot; and [DisciplineID] = ' &quot; & Me![Discipline ] &quot;'&quot;
End If


'Check the Status combobox
If Not (IsNull(Me![Status])) Then
If stLinkCriteria = &quot;&quot; Then
stLinkCriteria = &quot; [StatusID]= &quot; & Me![Status] & &quot;&quot;
Else
stLinkCriteria = stLinkCriteria & &quot; and [StatusID]= &quot; & Me![Status] & &quot;&quot;
End If
Else
stLinkCriteria = stLinkCriteria
End If


'Check the Employee combobox
If Not (IsNull(Me!Employee))then
If stLinkCriteria = &quot;&quot; Then
stLinkCriteria = &quot;[PersonAssigned]= &quot; & Me!Employee & &quot;&quot;
Else
stLinkCriteria = stLinkCriteria & &quot; and [PersonAssigned]= &quot; & Me!Employee & &quot;&quot;
End If
Else
stLinkCriteria = stLinkCriteria
End If


'If there are no records then Dispaly message to user and return to dialogue form. Esle open filtered form
intX = DCount(&quot;PunchItemID&quot;, &quot;tbl01-PunchItems&quot;, stLinkCriteria)
If intX = 0 Then
MsgBox &quot;There are NO RECORDS THAT MEET YOUR CRITERIA. Please select another set of criteria. &quot;
Exit Sub
Else
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If


Exit_Command11_Click:

Exit Sub

Err_Command11_Click:
MsgBox Err.Description
Resume Exit_Command11_Click

End Sub
------------------

Thanks again for all your support.
ZaZa