Storing values selected from a combo

[Guest]

I have created an invoice form that (unsurprisingly) starts with the debtor
name, account no, and several address fields.
I have a combo that looks up the debtor table and displays several columns
to show the names, account nos and full addresses and enable the correct one
to be selected. (Hence my earlier post about the alignment of the dropdown
list, which you may have seen a little while ago).
I can populate the various fields on the form by using
=comboboxname.Column(#) as appropriate for each of them, so that the full
details of the debtor are then displayed on the "invoice".
However, this is only a skin-deep "display in the form" situation and the
data is not stored in the fields of the underlying Invoices table (except for
the DebtorName which of course I had selected directly from the combo and
coded to store in the Invoices.DebtorName field.
I do need all the other bits of data to be stored so that (a) the merge
process has something to look for when I print the invoice and (b) so that
various queries and reports will find the full Debtor details, not just the
name.
So, the question: how do I get the various "dependent" bits of data, i.e.
those that followed my selection of the Debtor name, to store in the Invoices
table?
Maybe I am going about this completely the wrong way - perhaps I should be
making better/different use of relationships and queries... all suggestions
are welcome!!
Many thanks
CW

 

[Guest]

It sounds like your form is not a bound form or the controls are not bound to
a field in the form's record source.
I'm thinking perhaps it is the controls that are not bound because you say
the debtor field does update.

 

[Steve]

<<Maybe I am going about this completely the wrong way >>
Yes, you are!

Your tables should look like:
TblDebtor
DebtorID
...."dependent" bits of data...

TblProductID
ProductID
ProductName

TblInvoice
InvoiceID
InvoiceDate
DebtorID

TblInvoiceDetail
InvoiceDetailID
InvoiceID
ProductID
Quantity
Price

To record an invoice you need a form/subform. The main form is based on
TblInvoice and the subform is based on TblInvoiceDetail. You use your
combobox in the main form to record DebtorID. In similar fashion you use a
combobox in the subform to record ProductID.

To print an invoice, you need a Report/Subreport. The main report needs to
be based on a query that includes TblInvoice and TblDebtor. Using a query
that includes TblDebtor, you are able to get all the "dependent" bits of
data into the report from TblDebtor. The subreport needs to be based on a
query that includes TblInvoiceDetail and TblProduct. Using a query that
includes TblProduct, you are able to get the product name into the subreport
from TblProduct.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)