Auto-fill for Customer to Invoice form

[Guest]

I have a similar DB to Northwinds for a small electrical company.
The Customer Table and Invoice Table are linked one-to many and there is a
combo box that assigns the customerid to the invoice.

Because the job site may/may not be the customer's billing info I want the
job location info (name,address,phone etc which is part of the Invoice table)
to fill from the Customer Table (which is part of the underlying querry) and
allow me to change the info when necessary (same customer different job
location such as property managers).

Northwinds has the same occurance on the Orders from: Drop down box for
customer and auto fills in "ship to" but I can not find how it is
accomplished. Any help would be greatly appreciated. Thanks

Patty

 

[John Vinson]

I have a similar DB to Northwinds for a small electrical company.
The Customer Table and Invoice Table are linked one-to many and there is a
combo box that assigns the customerid to the invoice.

Because the job site may/may not be the customer's billing info I want the
job location info (name,address,phone etc which is part of the Invoice table)
to fill from the Customer Table (which is part of the underlying querry) and
allow me to change the info when necessary (same customer different job
location such as property managers).

What you can do in this case is to "push" the information from the
combo box into bound textboxes on the form, using the combo box's
AfterUpdate event. For example:

Private Sub cboCustomerID_AfterUpdate()
If IsNull(Me!txtCustomerName) Then
Me!txtCustomerName = cboCustomerID.Column(1)
End If
If IsNull(Me!txtCustomerAddress) Then
Me!txtCustomerAddress = cboCustomerID.Column(2)
End If
<etc. etc.>

This checks to see if information has already been filled in, and
leaves it alone if so; if the field is null, it uses the *zero based*
Column property of the combo to update the other field.

John W. Vinson[MVP]

 

[Rick Brandt]

I have a similar DB to Northwinds for a small electrical company.
The Customer Table and Invoice Table are linked one-to many and there
is a combo box that assigns the customerid to the invoice.

Because the job site may/may not be the customer's billing info I
want the job location info (name,address,phone etc which is part of
the Invoice table) to fill from the Customer Table (which is part of
the underlying querry) and allow me to change the info when necessary
(same customer different job location such as property managers).

Northwinds has the same occurance on the Orders from: Drop down box
for customer and auto fills in "ship to" but I can not find how it is
accomplished. Any help would be greatly appreciated. Thanks

Patty

One easy way is to include all of the required data in the ComboBox for
CustomerID. Make the additional columns hidden by setting there column
width setting to zero. Then in the AfterUpdate event of the ComboBox have
code...

Me.CustomerName = Me.CustomerID.Column(1)
Me.CustomerAddress = Me.CustomerID.Column(2)
etc..

When you make the selection all of the other TextBoxes will be populated,
but you will still be able to change them as required.