Auto populating data input form from other table

[mondeoman]

I have a data input form in which the user selects from combo boxes - for combo 1 - a client name and combo 2 - clients address (both these in a single separate table tbl_Clients. The user either selects an existing client/address or if not in list is asked if he/she want to add the new client/address. If the answer is yes a subform opens for them to add a new client which then is added to the tbl_Clients. When this sub form opens the user inputs 2 fields 1. the clients name and 2. the clients address. All this works fine. Now I could use a second combo box on the main form (basically an orders form) to select the address but what I would like to happen is that when the clients name is selected it automatically looks up the address and fills the clients address field in the main form (which is basically an order form). I want both the client and the address selected to be entered into the order table on which the main form is based. I know someone will say why do you want to do this if you have a table with client and address but please just accept for now that I do. Can someone tell me how I can achieve this or point me to a suitable tutorial. Thanks

 

[dhookom]

Can a client have more than one address? If not, add the address to the client combo box as a column (or more). Then use the after update event of the client combo box to populate the address text box:

    Me.txtAddress = Me.cboClient.Column(2)

The columns are numbered beginning with 0.

Duane
Hook'D on Access
MS Access MVP

 

[mondeoman]

Thnak you. Yes that is part of it but how do I then update the orders table with the clients address? I have tried putting a straight forward INSERT SQL statement after the above but this doesn't work

 

[dhookom]

Can you provide your significant table and field names as well as data types? What are the record sources of your forms?

Duane
Hook'D on Access
MS Access MVP

 

[mondeoman]

The significant table is called tbl_Interim_Certificate. The relevant fields are as follows:

Interim_Cert_ID - autonumber
Issued_By - Text
Issuer_Address - Text
Job_Reference
Employer_Code - Text
Employer- Text
Employer_Address - Text

There are other fields in the tble and form but either they will work in the same way as the above or the user inputs directly
The input form is called - frm_Interim_Input
The record sources are:

A combo box based on tblEmployers (fields Serial_Id, Employer, Employer_Address, Employer_Code - all text except the ID field which is autonumber). I use combo boxes to populate the various Employer, Address,Contractor etc fields. The overall form's record source is an SQL statement that has the following code:

SELECT tbl_Interim_Certificate.Interim_Cert_ID, tbl_Interim_Certificate.Issued_by, tbl_Interim_Certificate.Issuer_address, tbl_Interim_Certificate.Employer, tbl_Interim_Certificate.Employer_address, tbl_Interim_Certificate.Contractor, tbl_Interim_Certificate.Contractor_address, tbl_Interim_Certificate.Works, tbl_Interim_Certificate.Situated_At, tbl_Interim_Certificate.Serial_No, tbl_Interim_Certificate.Job_Reference, tbl_Interim_Certificate.Certificate_No, tbl_Interim_Certificate.Date_of_Issue, tbl_Interim_Certificate.Date_of_Valuation, tbl_Interim_Certificate.Final_Payment_Date, tbl_Interim_Certificate.Contract_dated, tbl_Interim_Certificate.Gross_Valuation, tbl_Interim_Certificate.Retention, tbl_Interim_Certificate.Advance_Payment, tbl_Interim_Certificate.Previously_Certified, tbl_Interim_Certificate.Amount_in_Words_1, tbl_Interim_Certificate.Amount_in_Words_2, tbl_Interim_Certificate.Amount_in_Words_3, tbl_Interim_Certificate.VAT_Rate, tbl_Interim_Certificate.[Amount_VAT_%]
FROM tbl_Interim_Certificate;

The user has to input the Issuer details, Employer Details and Contractor details. It would be so much better for the user if having selected the main piece of information - i.e. employers name or contractors name then the other fields (that are presently combos) could be changed to text fields that autopopulate and then update the underlying significant table.

Any help would be greatly appreciated.