Search a large recordset

[Opal]

Initially, I was using the following for users to search records on a
form:

Private Sub cboMoveTo_AfterUpdate()
Dim rs As DAO.Recordset

If Not IsNull(Me.cboMoveTo) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[CassetteID] = " & Me.cboMoveTo
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
Me.Refresh
End Sub

But I had a problem in that there are 29 cassettes and for each of the
29 cassettes there are 99 cassette IDs so I added a second combo
box and attached the same code above to search ID numbers but the
combo boxes work separately from one another and the user does not
get the desired results from their search. If I use just one combo box
I will have to show 2871 records which is not a good idea.

I have been looking at Allen Browne's "Combos with Tens of Thousands
of Records":

http://allenbrowne.com/ser-32.html

But his record source is one table and I am pulling from two. Should
I
combine into one table or is there another suggestion?

 

[Opal]

(e-mail address removed)
m:

Initially, I was using the following for users to search records
on a form:

Private Sub cboMoveTo_AfterUpdate()
Dim rs As DAO.Recordset

    If Not IsNull(Me.cboMoveTo) Then
        'Save before move.
        If Me.Dirty Then
            Me.Dirty = False
        End If
        'Search in the clone set.
        Set rs = Me.RecordsetClone
        rs.FindFirst "[CassetteID] = " & Me.cboMoveTo
        If rs.NoMatch Then
            MsgBox "Not found: filtered?"
        Else
            'Display the found record in the form.
            Me.Bookmark = rs.Bookmark
        End If
        Set rs = Nothing
    End If
    Me.Refresh
End Sub

But I had a problem in that there are 29 cassettes and for each of
the 29 cassettes there are 99 cassette IDs so I added a second
combo box and attached the same code above to search ID numbers
but the combo boxes work separately from one another and the user
does not get the desired results from their search. If I use just
one combo box I will have to show 2871 records which is not a good
idea.

I have been looking at Allen Browne's "Combos with Tens of
Thousands of Records":

But his record source is one table and I am pulling from two.
Should I
combine into one table or is there another suggestion?

no need to change the table structure.
Build the two combo boxes, (I'll name thenm cboMove1 and cboMove2

rs.FindFirst "[CassetteID] = " & Me.cboMove1 _
    " AND [OtherID]" & me.cboMove2

You could put the code in both AfterUpdate events, or (better)
create a new sub in the form's module and just call that sub from
each combo's after update event.

You can get fancier and ask the user wether to search for one, both
or the other combo, and use a select case block based on hte result.

--
Bob Quintal

PA is y I've altered my email address.
- Hide quoted text -

- Show quoted text -

Thank you, Bob, just having trouble with the second half of that
statement

"AND [CassetteNoID] " & Me.cboMove2

I get a compile error - expected: Line number or label or statement
or end of statement

I have played around with the "line" but can't seem to get past this
error.....

 

[Opal]

(e-mail address removed)
m:

Initially, I was using the following for users to search records
on a form:
Private Sub cboMoveTo_AfterUpdate()
Dim rs As DAO.Recordset
    If Not IsNull(Me.cboMoveTo) Then
        'Save before move.
        If Me.Dirty Then
            Me.Dirty = False
        End If
        'Search in the clone set.
        Set rs = Me.RecordsetClone
        rs.FindFirst "[CassetteID] = " & Me.cboMoveTo
        If rs.NoMatch Then
            MsgBox "Not found: filtered?"
        Else
            'Display the found record in the form.
            Me.Bookmark = rs.Bookmark
        End If
        Set rs = Nothing
    End If
    Me.Refresh
End Sub
But I had a problem in that there are 29 cassettes and for each of
the 29 cassettes there are 99 cassette IDs so I added a second
combo box and attached the same code above to search ID numbers
but the combo boxes work separately from one another and the user
does not get the desired results from their search. If I use just
one combo box I will have to show 2871 records which is not a good
idea.
I have been looking at Allen Browne's "Combos with Tens of
Thousands of Records":
http://allenbrowne.com/ser-32.html
But his record source is one table and I am pulling from two.
Should I
combine into one table or is there another suggestion?

no need to change the table structure.
Build the two combo boxes, (I'll name thenm cboMove1 and cboMove2

rs.FindFirst "[CassetteID] = " & Me.cboMove1 _
    " AND [OtherID]" & me.cboMove2

You could put the code in both AfterUpdate events, or (better)
create a new sub in the form's module and just call that sub from
each combo's after update event.

You can get fancier and ask the user wether to search for one, both
or the other combo, and use a select case block based on hte result.

PA is y I've altered my email address.
-Hide quoted text -

- Show quoted text -

Thank you, Bob, just having trouble with the second half of that
statement

"AND [CassetteNoID] " & Me.cboMove2

I get a compile error - expected:  Line number or label or statement
or end of statement

I have played around with the "line" but can't seem to get past this
error.....- Hide quoted text -

- Show quoted text -

Bob,

I have continued to research this and since my second combo
box is a number field I changed the line to read:

rs.FindFirst "[CassetteID] = '" & Me![cboMove1] & _
"' AND [CassetteNoID] = " & Nz(Me![cboMove2], 0)

but I am getting hung up on the first combo box.... a type mismatch
error.... so I think that since I am using primary keys and autonumber
fields in the tables there is confusion between number fields and text
fields.

So the cboMove1 is also a numeric field...all these quotations have me
confused... I must need to change it also to Nz(Me![cboMove1], 0)
but what do I do with the ampersands and quotations.....I'm befuddled

 

[Opal]

(e-mail address removed)
m:
Initially, I was using the following for users to search records
on a form:
Private Sub cboMoveTo_AfterUpdate()
Dim rs As DAO.Recordset
    If Not IsNull(Me.cboMoveTo) Then
        'Save before move.
        If Me.Dirty Then
            Me.Dirty = False
        End If
        'Search in the clone set.
        Set rs = Me.RecordsetClone
        rs.FindFirst "[CassetteID] = " & Me.cboMoveTo
        If rs.NoMatch Then
            MsgBox "Not found: filtered?"
        Else
            'Display the found record in the form.
            Me.Bookmark = rs.Bookmark
        End If
        Set rs = Nothing
    End If
    Me.Refresh
End Sub
But I had a problem in that there are 29 cassettes and for each of
the 29 cassettes there are 99 cassette IDs so I added a second
combo box and attached the same code above to search ID numbers
but the combo boxes work separately from one another and the user
does not get the desired results from their search. If I use just
one combo box I will have to show 2871 records which is not a good
idea.
I have been looking at Allen Browne's "Combos with Tens of
Thousands of Records":
http://allenbrowne.com/ser-32.html
But his record source is one table and I am pulling from two.
Should I
combine into one table or is there another suggestion?
no need to change the table structure.
Build the two combo boxes, (I'll name thenm cboMove1 and cboMove2
rs.FindFirst "[CassetteID] = " & Me.cboMove1 _
    " AND [OtherID]" & me.cboMove2
You could put the code in both AfterUpdate events, or (better)
create a new sub in the form's module and just call that sub from
each combo's after update event.
You can get fancier and ask the user wether to search for one, both
or the other combo, and use a select case block based on hte result.
--
Bob Quintal
PA is y I've altered my email address.
-Hidequoted text -
- Show quoted text -

Thank you, Bob, just having trouble with the second half of that
statement

"AND [CassetteNoID] " & Me.cboMove2

I get a compile error - expected:  Line number or label or statement
or end of statement

I have played around with the "line" but can't seem to get past this
error.....- Hide quoted text -

- Show quoted text -

Bob,

I have continued to research this and since my second combo
box is a number field I changed the line to read:

rs.FindFirst "[CassetteID] = '" & Me![cboMove1] & _
                "' AND [CassetteNoID] = " & Nz(Me![cboMove2], 0)

but I am getting hung up on the first combo box.... a type mismatch
error.... so I think that since I am using primary keys and autonumber
fields in the tables there is confusion between number fields and text
fields.

So the cboMove1 is also a numeric field...all these quotations have me
confused... I must need to change it also to Nz(Me![cboMove1], 0)
but what do I do with the ampersands and quotations.....I'm befuddled- Hide quoted text -

- Show quoted text -

Okay so how's this?

rs.FindFirst "[CassetteID] = " & Nz(Me![cboMove1], 0) _
And "[CassetteNoID] = " & Nz(Me![cboMove2], 0)

but I still get a type mismatch on the first combo box.... ugh....

cboMove1

SELECT Cassette.CassetteID, Cassette.CassetteName
FROM Cassette;

Column Count = 2
Column Widths = 0",1"
Bound Column = 1

CassetteID

SELECT Cassette.CassetteID, Cassette.CassetteName
FROM Cassette;

Column Count = 2
Column Widths = 0",1"
Bound Column = 1

I have left this as a combo box on the form itself in case the user
needs
to make a change after creating the record.

In the Cassette Table: the CassetteID field is an autonumber field and
the
CassetteName is a text field.

In the source table for the form, the CassetteID field (the source
field
for the CassetteID combo box on the form) is a numeric field as a
foreign key.

What am I missing?

 

[John W. Vinson]

Okay so how's this?

rs.FindFirst "[CassetteID] = " & Nz(Me![cboMove1], 0) _
And "[CassetteNoID] = " & Nz(Me![cboMove2], 0)

but I still get a type mismatch on the first combo box.... ugh....

You need to move the word AND inside the quotes, with a blank in front of it.
The first argument to FindFirst needs to be a valid SQL WHERE clause as a
string:

rs.FindFirst "[CassetteID] = " & Nz(Me![cboMove1], 0) _
" And [CassetteNoID] = " & Nz(Me![cboMove2], 0)

This might evaluate to something like

[CasseteID] = 312 And [CassetteNoID] = 3