Full Version: Record selector via a combo box
UtterAccess Forums > Microsoft® Access > Access Forms
Squirrel70
I have a subform that I use to list all my records in a table. I'm using a continuous form so I can scroll through them all. I want to be able to create a combo box on my main form that I can use to skip down to a specific record and still have the ability to scroll through all my records. Basically it would be like a "goto" combo box and it would allow my users to skip down to a record and then if they need they can still scroll through all the records. There is a field in the table on my subform that I want to use as the selector in my combo box. There are duplicate entries in this field so I would want it to go to the first record with that choice. Any idea how to write all this code up?
HiTechCoach
You can use a "find" on a clone of the erecordset of the sub form. Then set the bookmark of the sub fom to match the bookmark found with the "find" on the cloned recordset.
Squirrel70
Do you think you can walk me through this? I'm a little new with this type of thing.
HiTechCoach
Here is code for a find combo box on the same form:
Private Sub ComboName_AfterUpdate()
Dim MyRecSet As Object
Set MyRecSet = Me.Recordset.Clone
MyRecSet.FindFirst "[FieldNameTosearch] = " & Me.ComboName ' numeric search
If Not MyRecSet.EOF Then
Me.Bookmark = MyRecSet.Bookmark
end if
End Sub
If your combo box is returning a string then use:
MyRecSet.FindFirst "[FieldNameTosearch] = " & Chr(34) & Me.ComboName & Chr(34) ' string search
You will need to substitute the names for your field, objects, and forms.
Squirrel70
What if the combo box is on the main form and the table is on the subform? How would the code be different?
What do you mean if my combo box is returning a string?
HiTechCoach
What is the data type of the field is your combo box is bound to? Is the data type text or numbers?
Squirrel70
It is bound to text
HiTechCoach
Then you will need to use:

MyRecSet.FindFirst "[FieldNameTosearch] = " & Chr(34) & Me.ComboName & Chr(34) ' string search

You will need to substitute the Me, which references the form where the code is, to the name of e the sub form.

Me.Recordset.Clone

would be something like:

Me.SubFormName.form.Recordset.Clone

or

Me.SubFormName.Recordset.Clone


Edited by: HiTechCoach on Sun Jan 15 18:56:00 EST 2006.

Edited by: HiTechCoach on Sun Jan 15 18:59:15 EST 2006.
Squirrel70
Ok I did all that but now I'm getting an error message that says "not a valid bookmark".
HiTechCoach
It would help to see your code and forms.
Can you post a sample of your database with the forms?
Be sure to compact and zip the MDB before attaching.
Squirrel70
I've tried to post my db on here before and it hasn't worked. Any chance I can email it to you?
HiTechCoach
First run the compact and repair.
When ZIP the MDB.
The ZIP must be 500k or less.
If the ZIP is still to big,
1) make a copy of your MDB. Then in the copy. delete out any reports, etc that are not needed
Or
2) you can create a NEW blank MDB and import just the objects that are needed.
Then try the steps again on the copy/NEW MDB.
Squirrel70
Nope, won't work. The size only 267kb and it is zipped but it won't work.
HiTechCoach
I attach ZIps to post a lot and have not ever had a problem.
You should be able to attach your ZIP since it is not to large.
Are you able to select the file you want to attach when to click the browse button?
Squirrel70
yes I can browse it and select it but when I choose post it said it can't find the website.
HiTechCoach
Are you clicking the "submit" button just below the file path text box?
Squirrel70
Yes and then it says can't find website.
HiTechCoach
I am changing locations and will be able to receive email.
You can send the file to
accesscoach@hitechcoach.com
HiTechCoach
I got your database. I will get back with your shortly.
HiTechCoach
Your were very close. I have to make one chanage at it worked:
!--c1-->
CODE
Private Sub RawDiameter1_AfterUpdate()
Dim MyRecSet As Object
Set MyRecSet = Me.tblClassB_subform2.Form.RecordsetClone
MyRecSet.FindFirst "[RawDiameter]=" & Chr(34) & Me.RawDiameter1 & Chr(34) 'string search
If Not MyRecSet.EOF Then
    Me.tblClassB_subform2.Form.Bookmark = MyRecSet.Bookmark   ' <<<<< changed this line
End If
End Sub
Squirrel70
Thank you for your help but I have one question. It seems to work ok but how come when I select a specific "RawDiameter" that is already shown in the table it won't put that one at the top of list? When I choose one that you can't see it brings it up and puts it in the top record but when it's already on there but not at the top it doesn't move it. Is this normal or can we modify the code to have it bring any selection to the top?
.g. Select any material type and then choose .12500 from the RawDiameter1 field. Notice it won't move but when you select one you can't see it moves that one.
HiTechCoach
When I was testing, I turned back on the record selector for the sub form. This way you get visual clue to which record you selected. You might try this and see if it will solve your issue.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.