Find name in subform using cbox and go to that record # in main fo

  • Thread starter Thread starter Steve in MN
  • Start date Start date
S

Steve in MN

I have a customer entry form with linked sub form, one main customer name
with multiple pets names (many) in the sub form. I realize you cant use the
find button to search in the sub form. I have tried to use Allen's code to
use the sub form table in a combo box but couldn't get it to work.
I want be be able to choose the pet name using the combo box and then in the
after update event, have the main form go to that record #.
The [customer #] is the same in the main table and the sub table and that is
what links them...[Cust #] in main, [Customer #] in sub table.

How do I get the main form to go to that record selected in the combo box?

Thanks for any and all help.

Steve
 
Please be sure to backup before trying this. I have tested it but i made
assumptions on what your table and field names are so be carefull when
implementing it

i would also stray away from using # as part of a field name instead of
using CustID and CustomerID if they mean the same thing use the same thing..

Assuming you have a pet names table Tbl_Pets
with
PETID as a primary key
PetName and
CustomerID

and a customers table Tbl_customers
with CustomerID as primary key and
CustFName and
CustLName for the names

Create a unbound combo on your customers form called SelectByPetName_Combo
dont put anything in its control source (this is what makes it Unbound) set
number of colums to 2, and bound column to 1, set column widths to 0"; 2";
Because there are a million fidos' you might want the owner name in the
combo too so set its row source to a query like this (You can just paste it
into the row source if all names are the same)

SELECT tbl_Pets.CustomerID, tbl_Pets.petName & " Owner: " &
Tbl_customers.CustFName & " " & Tbl_customers.CustLName FROM Tbl_customers
INNER JOIN tbl_Pets ON Tbl_customers.CustomerID=tbl_Pets.CustomerID ORDER BY
tbl_Pets.petName & " Owner: " & Tbl_customers.CustFName & " " &
Tbl_customers.CustLName;

add this code to the form current property
Private Sub Form_Current()
' This makes the petnamecombo blank when you navigate to a different customer
Me.SelectByPetName_combo = ""
End Sub

and add this to the selectbypetname_combos after update event
Private Sub SelectByPetName_combo_AfterUpdate()
' Find the record that matches the control.
' using Dlookup to find the customer number by the pet name
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[CustomerID] = " & Str(Nz(DLookup("[CustomerID]",
"TBL_Pets", "PetID = " & Me![SelectByPetName_combo]), 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

hope this gets you there
 
Steve
im sorry when i first looked at your issue i thought it needed a dlookup in
the combos after update although it works it doesnt need it.
you can do most of this with the Wizard

heres the after update code without it

Private Sub SelectByPetName_combo_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[CustomerID] = " & Str(Nz(Me![SelectByPetName_combo], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

If you would like i could e-mail my test DB

Sorry
Barry
Bspyres@[no Spammers]aol.com
Barry A&P said:
Please be sure to backup before trying this. I have tested it but i made
assumptions on what your table and field names are so be carefull when
implementing it

i would also stray away from using # as part of a field name instead of
using CustID and CustomerID if they mean the same thing use the same thing..

Assuming you have a pet names table Tbl_Pets
with
PETID as a primary key
PetName and
CustomerID

and a customers table Tbl_customers
with CustomerID as primary key and
CustFName and
CustLName for the names

Create a unbound combo on your customers form called SelectByPetName_Combo
dont put anything in its control source (this is what makes it Unbound) set
number of colums to 2, and bound column to 1, set column widths to 0"; 2";
Because there are a million fidos' you might want the owner name in the
combo too so set its row source to a query like this (You can just paste it
into the row source if all names are the same)

SELECT tbl_Pets.CustomerID, tbl_Pets.petName & " Owner: " &
Tbl_customers.CustFName & " " & Tbl_customers.CustLName FROM Tbl_customers
INNER JOIN tbl_Pets ON Tbl_customers.CustomerID=tbl_Pets.CustomerID ORDER BY
tbl_Pets.petName & " Owner: " & Tbl_customers.CustFName & " " &
Tbl_customers.CustLName;

add this code to the form current property
Private Sub Form_Current()
' This makes the petnamecombo blank when you navigate to a different customer
Me.SelectByPetName_combo = ""
End Sub

and add this to the selectbypetname_combos after update event
Private Sub SelectByPetName_combo_AfterUpdate()
' Find the record that matches the control.
' using Dlookup to find the customer number by the pet name
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[CustomerID] = " & Str(Nz(DLookup("[CustomerID]",
"TBL_Pets", "PetID = " & Me![SelectByPetName_combo]), 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

hope this gets you there

Steve in MN said:
I have a customer entry form with linked sub form, one main customer name
with multiple pets names (many) in the sub form. I realize you cant use the
find button to search in the sub form. I have tried to use Allen's code to
use the sub form table in a combo box but couldn't get it to work.
I want be be able to choose the pet name using the combo box and then in the
after update event, have the main form go to that record #.
The [customer #] is the same in the main table and the sub table and that is
what links them...[Cust #] in main, [Customer #] in sub table.

How do I get the main form to go to that record selected in the combo box?

Thanks for any and all help.

Steve
Click to expand...
Click to expand...
 
Back
Top