Microsoft Access find button on main form that searches a subform 1

[kkalin]

I have a company main form and an account subform and I want to be able to create a find button on the main form that will search a field on the subform. The results will also need the main form data to reset relative to the found data on the subform. Can someone tell me what is the easiest way to do this in Access 2007? Thanks!

 

[TheAceMan1]

How are ya kkalin . . .

Not quite sure what your asking. Perhaps if you provide an example! . . .

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Countymnca]

TheAceMan1 - kkalin didnt reply, but I need the same thing. The example is below:

Firearms qualification database. The main form has the personnel information (name, address, etc) a subform has their weapon information (make, model ,serial number, etc).

I would like to be able to search for a serial number (sfrm field) and if found, it would set the parent form (personnel) to the matching person who has that weapon.

Forms are linked using an employee ID. Not sure it matters, but the weapon sfrm has its own sfrm (datasheet) that shows the qualification scores for that weapon. They are linked by Employee ID and Weapon SN.

Does that help? This would be great if it can be done.

Thanks

 

[kjv1611]

Can you use:

DoCmd.RunCommand acCmdFind

Or some variation thereof? There's FindNext, etc... all used to search the records on a form... or at least I think.. Not really used it much myself, yet..

Other option, probably best if it's a large data set would be to use a query or SQL for searching the underlying table.

--

"If to err is human, then I must be some kind of human!" -Me

 

[TheAceMan1]

How are ya Countymnca . . .

Can be done! 1st you need a combobox based on your [blue]weapons table[/blue] comprising two fields [blue]EmployeeID[/blue] and [blue]Weapon SN[/blue]. The [blue]EmployeeID[/blue] should be the 1st field and hidden in the combobox. Place the combo in the header or footer of the mainform. When your done post back the following:
[ol][li]Name of the subform. [/li]
[li]Name of the combobox.[/li]
[li]Name of EmployeeID (just to be sure) and its datatype (number or text).[/li]
[li]Name of Weapon SN (just to be sure) and its datatype (number or text).[/li]
[li]The RowSource property of the combobox.[/li][/ol]
[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Countymnca]

TheAceMan1 -
Here is the info you requested and thanks for the help!

1. WeaponSFRM
2. cboWPNsnSearch
3. EmpID Text
4. WSerialNumber Text
5. SELECT [WeaponTBL].[EmpID], [WeaponTBL].[WSerialNumber] FROM WeaponTBL;

 

[TheAceMan1]

OK Countymnca . . .

In the [blue]After Update[/blue] event of the combobox, copy/paste the following:

[blue]   Dim CBx As ComboBox, sfrm As Form, Cri As String
   
   Set CBx = Me!cboWPNsnSearch
   Set sfrm = [WeaponSFRM].Form
   
   Cri = "[EmpID] = '" & CBx.Column(0) & "'"
   Me.Recordset.FindFirst Cri [green]'Goto Employee[/green]
   
   Cri = "[WSerialNumber] = '" & CBx.Column(1) & "'"
   sfrm.Recordset.FindFirst Cri [green]'Goto weapon SN[/green]
   
   Set sfrm = Nothing
   Set CBx = Nothing[/blue]

Perform your testing!

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Countymnca]

Works Perfect...thanks!!! I will read up on what you did so I understand it. A star for the help.

 

[kkalin]

Thanks for all the info!

 

[Oblvion]

I am trying to double-click a Record Selector in my subform to populate the mainform with the same record. But I get Error Message 2465 'DATABASE can't find the field "|" referred to in your expression...

=======================
Option Compare Database
Option Explicit

Private Sub Form_DblClick(Cancel As Integer)

Dim TBH As TextBox, sfrm As Form, Cri As String

Set TBH = Me!MemberID
Set sfrm = [ChildrenSubform].Form '< ERROR MSG 2465 POPS UP HERE
Cri = "[MemberID] = '" & TBH & "'"
Me.Recordset.FindFirst Cri 'Goto MemberID of Mainform
Cri = "[MemberID] = '" & TBH & "'"
sfrm.Recordset.FindFirst Cri 'Goto MemberID of Subform
Set sfrm = Nothing
Set TBH = Nothing

End Sub
=======

 

[kjv1611]

Oblvion,

1. Start your own new thread.
2. For reference, see the links at the bottom of TheAceMan1's signature line.

If you really want to get help, that's how you need to proceed.

--

"If to err is human, then I must be some kind of human!" -Me