If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Find name in subform using cbox and go to that record # in main fo
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 |
#2
|
|||
|
|||
Find name in subform using cbox and go to that record # in main fo
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" wrote: 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 |
#3
|
|||
|
|||
Find name in subform using cbox and go to that record # in mai
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" wrote: 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" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|