combobox data limits workarounds? 2

[ThunderGeek]

I have a question about a combobox in Access 2002. My customer database is over 70,000 records, I have a combo/lookup box that looks up customers by last name on a form. My lookup is now not working properly and will not scroll past the alphabet letter W, nor will it look any customer up that begins with any letter after W. I conducted a test on a copy of my database by deleting about 20,000 records and the lookup works fine with 50,000 records. I assume that 70,000 is over the lookup box capacity. My question is how do I work around this limit, without having to delete all of these records?
Thanks for any help anyone can offer.
ThunderGeek

 

[Remou]

Perhaps you could limit the selection in the change event?

Private Sub cboCustName_Change()
Me.cboCustName.RowSource = "Select CustName From Table1 Where CustName Like '" & Me.cboCustName.Text & "*'"
End Sub

 

[ThunderGeek]

I am not very good with code, my lookup uses a select statement to select data for the combo box and a macro to find the record on the form, the select statement is as such:

 SELECT [Customers Alphabetized].[Last Name], [Customers Alphabetized].[First Name], [Customers Alphabetized].[State/Province], [Customers Alphabetized].[Customer ID] FROM [Customers Alphabetized];

I wouldnt know how to code the change event properly?

 

[Remou]

Create a change event for your combo box and add some code:

Me.[i]InsertNameOfComboHere[/i].RowSource = "SELECT ca.[Last Name], " _
& "ca.[First Name], ca.[State/Province], " _
& "ca.[Customer ID] FROM [Customers Alphabetized] ca " _
& "WHERE ca.[Last Name] Like '" _
& Me.[i]InsertNameOfComboHere[/i].Text & "*'"

I have used an alias 'ca' for [Customers Alphabetized] as it makes life easier.

It is not a good idea to have field names with spaces, it complicates things.

 

[ThunderGeek]

The select statement works well as far as pulling out the records.

 "SELECT [Customers Alphabetized].[Last Name], " & "[Customers Alphabetized].[First Name], [Customers Alphabetized].[State/Province], " & "[Customers Alphabetized].[Customer ID] FROM [Customers Alphabetized] [Customers Alphabetized] " & "WHERE [Customers Alphabetized].[Last Name] Like '" & Me.Lookup.Text & "*'"

I however cannot get the record it finds, to dislpay on my form. I have a macro that fires at the after update, It previously updated the form, it now acts like the lookup combo box is null? Does the select statement actually save the data in this combobox,or do Ineed additional code. The macro in question has these steps:

 GoToContol [Customer id]
FindRecord =[Lookup]
GoToContol [Last Name]
SetValue [Lookup]Null

Thanks Thundergeek

 

[Remou]

Your original SQL does not include the Customer ID field, which you would need. Are you using the additional columns from the lookup combo? If not, there is no need to include them.

Try this:

Private Sub Lookup_AfterUpdate()
'This replaces the macro
'Use the recordset of the form.
Set rs = Me.RecordsetClone
'If the combo has a value for Customer ID
If Not IsNull(Me.Lookup.Column(1)) Then
    'Find the record (combo column numbers start from zero)
    rs.FindFirst "[Customer ID]=" & Me.Lookup.Column(1)
    If rs.NoMatch Then
       'Not Found - you don't have to use this
       MsgBox "Not found"
    Else
       'Go to the found record
       Me.Bookmark = rs.Bookmark
    End if
End If
End Sub

Private Sub Lookup_Change()
Me.Lookup.RowSource = "Select [Last Name], " _
& "[Customer ID] FROM [Customers Alphabetized] " _
& "WHERE [Last Name] Like '" & Me.Lookup.Text & "*'"
End Sub

 

[TheAceMan1]

How are ya ThunderGeek . . .

Perhaps this reference will assist: Combos with Tens of Thousands of Records

If you try this, be sure to disable your other code or make a seperate combo for testing . . .

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[ThunderGeek]

Thank You all for your help and assistance.
The last code worked fine, I do need the last name field as I have hundred of customers with names like Adams and Smith etc. I hope I can figure out how to do that myself.
Thanks Remou and Thanks Aceman1 for the link.
ThunderGeek