By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,591 Members | 650 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,591 IT Pros & Developers. It's quick & easy.

Populate 3 fields in a subform based on a combo box on main form

P: 1
Hello Friends:
I am trying to populate a subform that has three fields that would pull view only data from a child table. The criteria for this data is to select the three fields where one of them is the value of column 1 of a combo box on the main form. This field is also the master child link between the two tables.

The combo field is SSN. So when I populate SSN in the main form, I would like the SSN to be used as a select statement like this:
SELECT VISITS.VISITDATE, VISITS.NOTES FROM VISITS WHERE VISITS.SSN = MAINFORM.COMBOBOXSSN.COLUMN(1).

Can anyone show me how the above pseudo code be translated into the real code? I realize that something needs to be done in the afterupdate event of the COMBOBOX on the main form but I am getting lost after that....
Please help.
Regards
Pankaj
Oct 19 '06 #1
Share this Question
Share on Google+
2 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
The record source of your subform can be the following query:

SELECT VISITS.VISITDATE, VISITS.NOTES
FROM VISITS
WHERE VISITS.SSN = [Forms]![MainFormName]![ComboBoxSSN];

In the after update event of the combobox put the following code

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub ComboBoxSSN_AfterUpdate()
  3.  
  4.    Me.SubformName.Requery
  5.  
  6. End Sub
  7.  
  8.  

Hello Friends:
I am trying to populate a subform that has three fields that would pull view only data from a child table. The criteria for this data is to select the three fields where one of them is the value of column 1 of a combo box on the main form. This field is also the master child link between the two tables.

The combo field is SSN. So when I populate SSN in the main form, I would like the SSN to be used as a select statement like this:
SELECT VISITS.VISITDATE, VISITS.NOTES FROM VISITS WHERE VISITS.SSN = MAINFORM.COMBOBOXSSN.COLUMN(1).

Can anyone show me how the above pseudo code be translated into the real code? I realize that something needs to be done in the afterupdate event of the COMBOBOX on the main form but I am getting lost after that....
Please help.
Regards
Pankaj
Oct 20 '06 #2

P: 9
I have a similar situation, and have done, I think, exactly what you suggest, but it doesn't seem to want to work. The source data for the combo box in the main form is:

SELECT [Current Employees].LastName, [Current Employees].FirstName, [Current Employees].ID FROM EmployeeIDs INNER JOIN [Current Employees] ON EmployeeIDs.ID=[Current Employees].ID ORDER BY [Current Employees].LastName;

The code, which is just what you have, looks like this:
Expand|Select|Wrap|Line Numbers
  1. Private Sub EmployeeList_AfterUpdate()
  2.  
  3.    Me.PayRateCodes_Subform.Requery
  4.  
  5. End Sub
  6.  
Where EmployeeList is the name of the combo box in the main form and PayRateCodes_Subform ought to be the name of the subform.

The source for the subform is the following:

SELECT PayRateCodes.UID, PayRateCodes.EmployeeID, PayRateCodes.RateCode, PayRateCodes.BeginDate, PayRateCodes.EndDate FROM PayRateCodes WHERE PayRateCodes.EmployeeID=Forms!NewPayRateCodes!Empl oyeeList ORDER BY PayRateCodes.UID;

One strange thing is that the PayRateCodes Subform seems to be listed as its own form in the main screen for the DB, but only shows up as a subform in the VBA environment.
-Angwe

The record source of your subform can be the following query:

SELECT VISITS.VISITDATE, VISITS.NOTES
FROM VISITS
WHERE VISITS.SSN = [Forms]![MainFormName]![ComboBoxSSN];

In the after update event of the combobox put the following code

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub ComboBoxSSN_AfterUpdate()
  3.  
  4.    Me.SubformName.Requery
  5.  
  6. End Sub
  7.  
  8.  
Jan 3 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.