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

List Box populate from Combo Box selection & save results.

FOE2272
P: 20
I have tried every option that I can think of and most of them on this forum. The closest that I got was to bring up the field but it changed the other records based on what was chosen in one record.

Details:
DB with one main table and several list (reference) tables.

I want to populate several list boxes on one form based on a choice from a combo box.

Example:

Tables:
Main
List Architects

In the "Main" table I have the "Architect_Company" field
in the "List_Architects" table I have the following fields:
Arch_Company
Arch_contact
Arch_address
Arch_City
Arch_State
Arch_Zip
Arch_Country
Arch_Phone
Arch_Cell
Arch_Fax
Arch_Email

in the form I have a combo box which allows me to choose the "Arch_Company" from the "List_Architects" table and save the result in the main table in a "Arch_Company" field.

I want to display the other fields in the form based on the "Arch_Company" field, if Null to leave them blank and If additional data is typed in to save the data to the "List_Architects" table.

Or as much of this as can be done.
May 11 '07 #1
Share this Question
Share on Google+
5 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Have a look at this tutorial and come back with any questions.

Cascading Combo/List Boxes

Mary
May 12 '07 #2

FOE2272
P: 20
That works fine to see the results, tried it the other day, but how do I get the results to save to specific fileds in a third table?

Where the chosen company from the "Company" combo box saves to a "Company" field and the chosen Employee from the "Employee Name" combo box saves to a "Employee" field of a 3rd "Results" table.
May 14 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
That works fine to see the results, tried it the other day, but how do I get the results to save to specific fileds in a third table?

Where the chosen company from the "Company" combo box saves to a "Company" field and the chosen Employee from the "Employee Name" combo box saves to a "Employee" field of a 3rd "Results" table.
Try something like this ...
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2.  
  3.    strSQL = "INSERT INTO Results (Company, Employee) " & _
  4.       " VALUES ('" & Me!Company & "', '" & Me!Employee & "')"
  5.    DoCmd.RunSQL strSQL
  6.  
Mary
May 15 '07 #4

FOE2272
P: 20
Great, Where is the script below does it go? Obviously, I'm new at this.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub Company_AfterUpdate()
  3.   With Me![Employee Name]
  4.     If IsNull(Me!Company) Then
  5.       .RowSource = ""
  6.     Else
  7.       .RowSource = "SELECT [EmpName] " & _
  8.                    "FROM TblEmployees " & _
  9.                    "WHERE [ID]=" & Me!Company
  10.     End If
  11.     Call .Requery
  12.   End With
  13. End Sub
May 15 '07 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
It doesn't go in this code.

Put it behind a command button or in the on close and/or on change event of the form whichever is more suitable.
May 17 '07 #6

Post your reply

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