populate fields in a form

[Paul]

Hi,

I have a form [frmPayments] with the following fields CoName, Address1,
Address2, Phone and Amount. The Record Source for this form is
tblPaymentDetails. CoName is a combobox that looks up tblCoDetails for the
company name.

I want to autopopulate the Address and phone fields based on my selection in
the CoName combobox which work fine but my problem is how do I pass these
values back to tblPaymens.

I've created some Dlookup functions to populate the Address and Phone
fields in frmPayments which work fine but how would I pass these values back
to tblPaymentDetails?

Thanks

 

[Rick B]

You woudn't. That would be redundant. Store a foreign key so you will know
who the customer is (Do you have a customer number?) but that is all. Then
you can pull the related data from your customer table when you need it.

You should (almost) never store the same data in more than one place. An
exception would be if you need to store a value at a particular point in
time. For example, if you have a product table and the price will change
from time to time, then you would also want to store the price paid in your
invoice table.

 

[Graham R Seach]

Paul,

You should NOT store the address and phone data in tblPaymentDetails, as it
would constitute redundant, or unnecessarily duplicated, data. This breaks
one of the cardinal rules of relational database design. Instead, you should
store only the primary key (PK) of the relevant company; if that happens to
be the company name, then you should store only the company name (CoName) in
tblPaymentDetails. If you have a numeric or other primary key, then
(preferably) store that.

But to show you how to do it...

If you want to copy data from tblCoDetails to several form fields, based on
the value selected in the combo, you should include those fields in the
combo's RowSource. To do that, amend its RowSource property as follows:

SELECT CoName, Address1, Address2, Phone FROM tblCoDetails

Set the combo's ColumnCount property to 4, and its ColumnWidths property to
4cm;0cm;0cm;0cm. If you use a different measurement system, then change it
as appropriate, but ensure that only the first column is displayed by
setting its relevant width greater than zero. Any columns having a width of
zero, will not be displayed.

Next, add the following code to the combo's AfterUpdate event:
Private Sub CoName_AfterUpdate()
If Not IsNull(Me!coName) Then
'A vaid entry is selected, populate the other controls
Me!Address1 = Me!CoName.Column(2, Me!CoName.ListIndex)
Me!Address2 = Me!CoName.Column(3, Me!CoName.ListIndex)
Me!Phone = Me!CoName.Column(4, Me!CoName.ListIndex)
Else
'If nothing is selected, then clear the other controls
Me!Address1 = ""
Me!Address2 = ""
Me!Phone = ""
End If
End Sub

If the address and phone controls on the form are bound to the apropriate
fields in the underlying table, then the table fields will be updated when
the record is saved/updated.

But as I said, you really should have a PK in tblCoDetails; probably an
Autonumber field. When you have that, all you need do is include the PK in
the combo's RowSource:
SELECT CoNo, CoName, Address1, Address2, Phone FROM tblCoDetails

....and bind the combo to a Long Integer field in tblPaymentDetails, called
"CoNo".

ColumnCount = 2
ColumnWidths = 0cm;4cm
BoundColumn = 1

You could then use the combo's AfterUpdate code to copy the address and
phone details as before, but those form controls should NOT be bound to the
form's underlying table (so as to NOT save them).

Have I made it understandable to you?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
(Currently in Japan)
---------------------------

Hi,

I have a form [frmPayments] with the following fields CoName, Address1,
Address2, Phone and Amount. The Record Source for this form is
tblPaymentDetails. CoName is a combobox that looks up tblCoDetails for the
company name.

I want to autopopulate the Address and phone fields based on my selection
in the CoName combobox which work fine but my problem is how do I pass
these values back to tblPaymens.

I've created some Dlookup functions to populate the Address and Phone
fields in frmPayments which work fine but how would I pass these values
back to tblPaymentDetails?

Thanks