Trying to set up search of field on Subform from Main Form

[SGrohola]

I have a main form [frm_Main1] which has owner/operator information. I have several buttons on the main form, that users can click on to bring up a subform. On one of the subforms [frm_Truck], it houses Truck #'s and information regarding the truck, and it ties to the Owner/Operator on the main form. I have set up search buttons on the main form to be able to search by last name, or search by a driver.

What I am attempting to do, is set up a combo-box (I would prefer a command button) that will search on the Truck # from the Main form, and then bring up the corresponding record that is associated it. Meaning, Click on button on Main form, type in Truck #, and it will bring up the record that is associated with that Truck # (meaning it will show the Owner/Operator that is associated).

I found some code that uses a combo-box (that's why I mentioned it), but I keep getting errors when I try to run it.

Private Sub Combo147_AfterUpdate()
    Dim CBx As ComboBox, sfrm As Form, Cri As String
   
   Set CBx = Me!Combo147
   Set sfrm = [frm_Truck].Form
   
   Cri = "[EmployeeID] = '" & CBx.Column(0) & "'"
   Me.Recordset.FindFirst Cri 'Goto Employee
   
   Cri = "[TruckNo] = '" & CBx.Column(1) & "'"
   sfrm.Recordset.FindFirst Cri 'Goto weapon SN
   
   Set sfrm = Nothing
   Set CBx = Nothing
End Sub

[EmployeeID] is the relationship between the 2 forms. (frm_Main1, frm_Truck). [TruckNo] is the field in the [frm_Truck] which I'm trying to search on from the main form to bring up the record it's associated with.

Can anyone help with this code, or help change the code to work with a command button?

Thanks!

[ranman256]

no, dont use FindFirst.
If you want to find all users of the Truck# ,youd open a query that looks at the combo box and show all users.
via docmd.openquery "qsUsersOfTruckNum"
select * from tTruck where [truck#] = forms!myForm!cboTruck
you can also open a form using this query

but
if you want trucks of the current record owner from the main form,
you would only filter the subform records:

sub cboBox_Afterupdate()

if isNull(me.cboBox) then
   subform.form.filterOn = false
else
   subform.form.filter = "[Truck#]='" & me.cboBox & "'"
   subform.form.filterOn = true
end if
end sub

[aytee111]

You could also put the search field on the header of the subform, then you won't have to worry about main vs subform. Change "subform.form" to "Me".

[SGrohola]

Let me make sure that I clarify this, so I have the right information.

I have a query right now, well, several queries, that are command buttons, and they are under a subform off the main, and when each query is run, it brings up a report that can be viewed by the used.

I want to be able to add a command button to my main form (frm_Main1). The main form record lists the Owner/Operator, and has basic info (what region and terminal he works out of, phone #, start date, etc). Then, I have 2 subforms, and each one opens with a command button.

The subform (frm_Trucks) is associated with the Owner/Operator. There is no problem with the parent/child relationships that are set up. I can search from the main form on last name, and if there is a driver for the owner.

What I need to do from the main form, is add another command button that when clicked, the user can put a truck # in, and the main form will then goto that record.

The code that I added, is not code that I created, but hoped that it would work. But, I keep getting errors with that code. I would rather be able to create a command button than a combo-box to be able to search. Trying to keep it as easy as possible for all my users.

Thanks.

[aytee111]

Firstly, a search text box or combobox is far simpler for the user, the command button is an extra click plus all the additional maintenance that goes with it. Everyone else does searches by the user entering a value in either a combobox or a textbox and the form filtering on that value in the AfterUpdate event. You do love command buttons, don't you? Subforms are static forms and are never "opened" - do you mean they become visible or are they not subforms? If they truly are subforms you could have them both displayed all the time by using a tab control.

Secondly, you start off by talking about subforms but then describe your issue as displaying data on the main form, this is confusing.

Is this your process? Somehow a record is displayed on the main form. It contains owner/operator. At this point the subform containing the list of trucks is requeried automatically by linking the subform to the owner. From this list of trucks, the user wants to search for one particular truck which they have to enter, either with combobox or input box or however. This is where I do not see how the data on the main form changes.

[SGrohola]

My apologies for being confusing.... when I started creating this database, it had been almost 7 years since I had done one, and I really wasn't knowledgeable of all the changes to Access.

My main form holds each record for an Owner/ Operator. Below the owner/operator information are several command buttons, that associate to that record. One for address, and one for Trucks. When the command button is clicked, it opens the other form (to me, it's a subform, but it's technically it's own form, but the parent/child relationship is there). When the Truck form opens, it shows the Truck (only 1 per owner/operator) that is related to the Owner/Operator on the main form.

What I want to do is set up a search on the main form (be it a combo-box or a command button) that when a truck # is entered to be searched, it will go to the appropriate record in the main form (the parent record). I have searches set up for Last Name, and for another field. These fields are already on the Main form, so there was no problem setting up the search.

[aytee111]

These are definitely NOT subforms, that has been our confusion. Subforms are linked to main forms, forms are NOT linked - another confusion! This seems a very convoluted process. (It still makes sense to me to use subforms instead so that the data is always displayed, without having to go thru all this clicking and opening.)

Regardless, change the truck form to be continuous (Default View property) and then it will show all the trucks for that owner. You could further search within that form for one particular truck, in whatever way you see fit. I can't see the benefit of having the truck search on the first form.

[SGrohola]

I agree with you on the subforms, but I set up per my boss's specs. That's how he wanted it, and he signs my paycheck.

I will work on changing the view to a continuous form, and see how that works.

Thanks!