Populating a listbox to select the records

[cybertyk]

ok im using this code to select a record from a list box so a user can find it faster this works ok how ever after i run a ammend quary on over 1700 records none of them will load (the ammend ones the others are fine) i get a blank msg box, is this due to the code?

Private Sub lstCustomer_AfterUpdate()
Dim rs As DAO.Recordset
    If Not IsNull(Me.lstCustomer) Then
        'Save before move.
        If Me.Dirty Then
            Me.Dirty = False
        End If
        'Search in the clone set.
        Set rs = Me.RecordsetClone
        rs.FindFirst "[tblCustomer]![ID] = " & Me.lstCustomer
        If rs.NoMatch Then
           MsgBox (Err.Description)
        Else
            'Display the found record in the form.
            Me.Bookmark = rs.Bookmark
        End If
        Set rs = Nothing
    End If
 Me.lstCustomer.Requery
End Sub

any help would be great cus this has been dring me mad for weeks lol

 

[MStef]

I suggest to use a "Combo box" instead of a "List box".
Look at "DemoFindA2002.mdb" (attachment, zip).
Open frmForm1 and try.
Look at VBA. I think it is what you need.

 

[cybertyk]

will do right now ta for the fast responce

 

[cybertyk]

ive taken a look at the example you have provided it is good and what im looking for how ever there will be about 1500 records in this part of the database and thats a lot to search though even with the search critia enabled, is there no way a list box can safely be used?

 

[cybertyk]

ok im using this code
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[tblCustomer]![ID] = " & Str(Nz(Me![lstCustomer], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

how ever i still cant select 1725 of the records i did notice that in the navigation bar that it stats that its only listing 300 would that make the record source the probleam?

 

[vbaInet]

Why are you using the Str() function?

Try this:

rs.FindFirst "[tblCustomer]![ID] = '" & Nz(Me![lstCustomer], 0) & "'"

 

[cybertyk]

cus im a noob lol, cheers i will give this a go XD

 

[vbaInet]

Hang on a sec, is CustomerID a Number or a String?

 

[cybertyk]

its a number, not to worry i think i have sorted the probleam its was the relationship i set up i used the wrong join prop DOH, but it seams to be ok now

fingers crossed this database is giving me a headache

 

[vbaInet]

In that case all you need is this:

rs.FindFirst "[tblCustomer]![ID] = " & Nz(Me![lstCustomer], 0)

Glad you found the culprit.

 

[cybertyk]

CHeers that bugger was annoying me for days XD

 

[vbaInet]

It's because you were using the Str() function for a number field.