List Box populate from Combo Box selection & save results.

[FOE2272]

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.

[MMcCarthy]

Have a look at this tutorial and come back with any questions.

Cascading Combo/List Boxes

Mary

[FOE2272]

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.

[MMcCarthy]

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 ...

Dim strSQL As String
 
   strSQL = "INSERT INTO Results (Company, Employee) " & _
      " VALUES ('" & Me!Company & "', '" & Me!Employee & "')"
   DoCmd.RunSQL strSQL
 

Mary

[FOE2272]

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

 
Private Sub Company_AfterUpdate()
  With Me![Employee Name]
    If IsNull(Me!Company) Then
      .RowSource = ""
    Else
      .RowSource = "SELECT [EmpName] " & _
                   "FROM TblEmployees " & _
                   "WHERE [ID]=" & Me!Company
    End If
    Call .Requery
  End With
End Sub

[MMcCarthy]

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.