Problem with forms/parameter query

[grantschneider]

I have a form that I use to run queries and I have 4 different
variables that can either be blank or not. I have tried to create
conditions where I am able to leave some or all blank or some or all
filled in and get the correct results returned. This is the code I am
using and it just doesn't seem to work:



WHERE ([Master Sales Forecast].SVP = [Forms]![ShareComm]![Combo12]
OR [Forms]![ShareComm]![Combo12] IS NULL)

AND ([Master Sales Forecast].VP = [Forms]![ShareComm]![VP]
OR [Forms]![ShareComm]![VP] IS NULL)

AND ([Master Sales Forecast].SC = [Forms]![ShareComm]![SC]
OR [Forms]![ShareComm]![SC] IS NULL);

 

[Guest]

Few points to check

1. The form need to be open

2. Check each field seperatly, mybe the problem is which one field only

3. Check the values return from the field in the form.
When the form is open, select values in all the fields, open the immidiate
window (Ctrl+g) and type
?[Forms]![ShareComm]![Combo12]

And press enter, see which value returned
Do the same for all fields

4. Check that the combo's are bound to the right field (stage 3 should
answer that)

5. If it's not all the above, please explain what do you mean that the query
doesn't work (error or no records returned)

 

[grantschneider]

Few points to check

1. The form need to be open

2. Check each field seperatly, mybe the problem is which one field only

3. Check the values return from the field in the form.
When the form is open, select values in all the fields, open the immidiate
window (Ctrl+g) and type
?[Forms]![ShareComm]![Combo12]

And press enter, see which value returned
Do the same for all fields

4. Check that the combo's are bound to the right field (stage 3 should
answer that)

5. If it's not all the above, please explain what do you mean that the query
doesn't work (error or no records returned)
--
Good Luck
BS"D

I have a form that I use to run queries and I have 4 different
variables that can either be blank or not. I have tried to create
conditions where I am able to leave some or all blank or some or all
filled in and get the correct results returned. This is the code I am
using and it just doesn't seem to work:

WHERE ([Master Sales Forecast].SVP = [Forms]![ShareComm]![Combo12]
OR [Forms]![ShareComm]![Combo12] IS NULL)

AND ([Master Sales Forecast].VP = [Forms]![ShareComm]![VP]
OR [Forms]![ShareComm]![VP] IS NULL)

AND ([Master Sales Forecast].SC = [Forms]![ShareComm]![SC]
OR [Forms]![ShareComm]![SC] IS NULL);- Hide quoted text -

- Show quoted text -

When I do step three, it gives me the primary key for that value
instead of the correct SVP. This must be the problem? Except I think
then it gets more complicated. I use the combo box to also look up a
record. When I switch the column that it is bound to, it sends me to
VBA and asks me to debug it.

Private Sub Combo12_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Nz(Me![Combo12], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub


it highlights this line: rs.FindFirst "[ID] = " & Str(Nz(Me![Combo12],
0))

as needing to be debugged when i change the bound column

what i mean by 'not working' is that it returns no results unless i
leave everything entirely blank.

grant