Create search button for subform on main form

[SGrohola]

I'm sure that this has been answered, and if it has, please point me in the right direction. I am creating a form with a subform (datasheet). My user wants to be able to click on a button (or enter into a text box), on the main form, to be able to search on a VIN in the subform, and have it bring up the related record in the main form.

The button should be on the main form. Right now, there is no way to search on any of the fields in the subform, and the user needs to be able to.



Any help would be great!!

[orange]

??? What is the subject on the main form??
What is the subject of the subform??

Tell us how VIN fits in all of this.

[Gicu]

You will need to give us some more info on the tables that feed the main form and the subform. Usually in the subform you are presenting a subset of its recordset (because you link it to the main form on a certain field, a one-to-many relationship). So when searching the subform as it is loaded for the current record you will not find VINs associated with another "master" record. That being said the easiest way to do that kind of search is by using combo boxes. Add a combo box to the main forms header. Its rowsource should include the primary key of the main form recordsource (as the first column and hidden) and the VINs from the subform's table. In the AfterUpdate event of the combo box you will navigate to the record coresponding to the VIN selected.

Cheers,
Vlad

[SGrohola]

The main form is 3rd Party Carrier information in regards to an incident (vehicle accident, lot accident), and the subform are the cargo units that have been damaged. There could just be one cargo unit, or several associated with the incident. There is a one-to-many relationship, with the ClaimID.

I have search buttons to be able to search on a Carrier name, or on the Claim # (number associated with the incident - generated by another system). I would like either a search button or a text box that the user can enter a VIN # of a damaged cargo unit, and it will "go to" the main form record that the VIN is associated with.

I have added a screen shot of the main form and subform. There is no data other than just test mish-mash right now.

Thank you!

[Gicu]

I prefer using combo boxes because of their auto-complete feature. What I suggest is creating a query based on the Cargo table (subform) in which you include ClaimID as your first column, then VIN and some other descriptors if you wish ([Cargo Veh Make], [Cargo Veh Year], etc.) and set its sort order. Now add a combo box on the main form, set its row source to this new query, make the first column width 0 (for the ClaimID). In the after update event add code to find the ClaimID. You can use the recordset bookmark option (the one Access uses when you add a combo box and use the wizard to make it find a specific record) or easier something like this:

Private Sub cboVINFinder_AfterUpdate()On Error Resume Next
Me.txtClaimID.SetFocus
DoCmd.FindRecord Me.cboVINFinder
Me!cboVINFinder.SetFocus
Me!cboVINFinder= Null
End Sub

Cheers,
Vlad

[SGrohola]

I prefer using combo boxes because of their auto-complete feature. What I suggest is creating a query based on the Cargo table (subform) in which you include ClaimID as your first column, then VIN and some other descriptors if you wish ([Cargo Veh Make], [Cargo Veh Year], etc.) and set its sort order. Now add a combo box on the main form, set its row source to this new query, make the first column width 0 (for the ClaimID). In the after update event add code to find the ClaimID. You can use the recordset bookmark option (the one Access uses when you add a combo box and use the wizard to make it find a specific record) or easier something like this:

Private Sub cboVINFinder_AfterUpdate()On Error Resume Next
Me.txtClaimID.SetFocus
DoCmd.FindRecord Me.cboVINFinder
Me!cboVINFinder.SetFocus
Me!cboVINFinder= Null
End Sub

Cheers,
Vlad

I'm getting a run-time error '2110': - Microsoft Access can't move the focus to the control ClaimID. Does the combobox need to be in the main form, or should it be in the header? Also, when I added in the VB code, it went red when I added "On Error Resume Next" after the AfterUpdate().

[isladogs]

I haven't read the rest of this thread but the code has at least one error

On Error Resume Next should be on its own line
Also use Me. instead of Me!

Private Sub cboVINFinder_AfterUpdate()    
On Error Resume Next
    Me.txtClaimID.SetFocus
    DoCmd.FindRecord Me.cboVINFinder
    Me.cboVINFinder.SetFocus
    Me.cboVINFinder= Null
End Sub

Also, I'm not sure what the DoCmd.FindRecord line is meant to be doing without reading the whole thing ..
You've updated the combobox and now you need to find that record????
Surely it will be on that record after the update....

[SGrohola]

What I need the code to do, is to be able to search on a Cargo VIN (that is housed in a subdatasheet) and return the correct record in the Main Form. It is a one-to-many relationship, with ClaimID being the connector between the two.

[Gicu]

The combo box is unbound and can be anywhere, but preferable on the header. Sorry about the on error.. it got messed up when I copied it. You need to replace txtClaimID with the name of your control that is bound to the field ClaimID.

Cheers,
Vlad

[isladogs]

I think the on error is a forum glitch.
I copied it twice with the code corrected and it reverted to the same line each time.
At the third attempt...after a further edit, I've got it as I wanted it.

[isladogs]

You might find the sample database at this thread helpful to what you are trying to do.
https://www.access-programmers.co.uk...d.php?t=294421

It is an emulated split form but without the disadvantages that standard split forms have.
It also has a search button doing exactly what you want.
Although I contributed much to the thread, the final version which works well isn't mine.

Recommend you take a look at it

[SGrohola]

Thank you to everyone for all the help! I was able to get the code working, and it definitely made me smile, because this has been a thorn in my side for a while now!!

[Gicu]

Glad to hear you got it working. For consistency I would recommend replacing your two buttons for searching by claim number and carrier with two combo boxes, they work very fast and they auto-populate which helps users to avoid spelling mistakes.

Please mark the thread solved if you think you're done with it.

Good luck!
Vlad

[SGrohola]

Thanks for the suggestion! I'll make the change to them.