Bookmarks and the UNION SELECT

[sahaitu]

Greetings:

I have a form that I would like to filter based on a selection in one combo box. I used the "Filter the Form Based on My Selection" wizard.

I have the following code in the combo box:

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

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

I then have the following code in the query behind the combo box:

SELECT tblState.idnStateID, tblState.StateLong FROM tblState UNION SELECT "*", "All" FROM tblState;

When I select "All" from the combo box, I get the following error:

"Run-time Error '13':

Type Mismatch"

Can anyone assist? Thank you in advance for your assistance.

Sahaitu

 

[PHV]

You may try either this:
SELECT idnStateID & '' AS stateID, StateLong FROM tblState UNION SELECT "*", "All" FROM tblState;
in conjunction with:
If Nz(Me!Combo4, "*") = "*" Then
rs.MoveFirst
Else
rs.FindFirst "idnStateID=" & Me!Combo4
End If

Or this:
SELECT idnStateID, StateLong FROM tblState UNION SELECT 0, "All" FROM tblState;
in conjunction with:
rs.FindFirst "idnStateID>=" & Nz(Me!Combo4, 0)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[sahaitu]

Thank you so much. I do have one problem though:

The code has gotten rid of the Run-Time Error. Though when I select "ALL" from the drop down list, I only get cases listed for "Alabama", which is the first state in my list.

I have the following code for the combo box:

SELECT tblState.idnStateID, tblState.StateLong FROM tblState UNION SELECT 0, "<All>" FROM tblState
ORDER BY tblState.StateLong;

And the following code for the event procedure:

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

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

I appreciate your help on this. Getting this to work is such a relief, sweee

http://wwww...

Sahaitu

 

[PHV]

Seems you haven't read carefully my previous post.
Replace this:
rs.FindFirst "[idnStateID] = " & Str(Nz(Me![Combo9], 0))
By this:
rs.FindFirst "idnStateID[highlight]>[/highlight]=" & Nz(Me!Combo9, 0)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[sahaitu]

Okay, I see. Thank you. One more quick question though. I have changed the code to reflect what you have provided.

The "<ALL>" selection works only if the case has a state ID of 1, (Alabama), so, essentially, I still have the same issue.

"<ALL>" is only pulling states with a state ID of "1".

There is obviously something I'm doing wrong here. My code is still the same, minus the change you requested above.

I do thank you for your time on this.

 

[PHV]

You wanted a filter ?
So no Recordset nor Bookmark are necessary:
Private Sub Combo9_AfterUpdate()
If Nz(Me!Combo9, 0) = 0 Then
Me.FilterOn = False
Else
Me.Filter = "idnStateID=" & Me!Combo9
Me.FilterOn = True
End If
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886