rs.FindFirst

[oli82]

Hi,

I have used the control wizard to create a combo to find a record in the list and return the entry. I currently have 150000 records in the database, less that half are displayed on the drop down list and I am unable to jump to records past the list.

Eg. I can find all records beinging A - H but anything after H doesnt work. My row source query displays all the records however

Thanks in advance for your help. Is there a better way to to this?

Cheers,

Oliver

 

[KenHigg]

150k records in a combo box is bad juju. I would suggest using something like cascading combo boxes to narrow the search. You can search the forum on the topic.

 

[dkinley]

I know there are limits to a combo box. You can either try to filter out some of those records prior to the user selecting or do some rewriting.

Here is a method posted by Allen Browne.

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


-dK

 

[oli82]

once I have narrowed down the search do I then use rs.findfirst to take me to the record?

Cheers

 

[Rabbie]

I am a little puzzled by "I currently have 150000 records in the database". Are these records all in 1 table. If so you probably need to look up "Data Normalisation". Most relational databases should have more than 1 table with relationships set up between them but since you give no details of your DB it is impossible to say if you have a good design or not.

 

[oli82]

The database has about 25 relationships and links with lookups and other tables. There are 150000 records in a particular table - sorry for the confusion. The data has been normalised.

 

[dkinley]

Without knowing specifics I would assume tht using rs.FindFirst would be what you use. Something like ...

rs.FindFirst "someID = " & txtThatID

Oh ... I am thinking you are looking for rs.MoveFirst to get into the first record of the recordset.


-dK

 

[oli82]

many thanks for all your help I appear to be getting there now. narrowing down the combo box selection before running the find first.

Good stuff, makes me happy on a rainy day.

 

[dkinley]

Oh ... I am thinking you are looking for rs.MoveFirst to get into the first record of the recordset.

I made an edit while you were posting after I reread.

-dK