Filling multiple fields in a form from one combo box looking for values

[fstenoughsnoopy]

I have a customer order database and I need to pull a customers
information, ie first name, last name, address, city, state, zip,
phone, etc, into the oder table. i don't know how to go about making
this work...

[Jeff Smith]

<fs*************@yahoo.com> wrote in message
news:11*********************@g10g2000cwb.googlegro ups.com...

I have a customer order database and I need to pull a customers
information, ie first name, last name, address, city, state, zip,
phone, etc, into the oder table. i don't know how to go about making
this work...

You don't. Insert the primary key of the customer into the assigned foreign
key of the order table. Search the Internet on normalisation or the 3rd
normal form.

Jeff

[Sarah Schreffler]

Jeff Smith wrote:

<fs*************@yahoo.com> wrote in message
news:11*********************@g10g2000cwb.googlegro ups.com...

I have a customer order database and I need to pull a customers
information, ie first name, last name, address, city, state, zip,
phone, etc, into the oder table. i don't know how to go about making
this work...

You don't. Insert the primary key of the customer into the assigned foreign
key of the order table. Search the Internet on normalisation or the 3rd
normal form.

I can see a reason for doing this actually -- for orders, you want a
history of what address a particular order actually went to, even if
the person later changes their address.

http://www.blueclaw-db.com/comboboxl..._combo_box.htm
shows an example of how to fill other text boxes from columns in a
combo box.

Basically, set control source to use the column property of a combo box
followed by a number.

--Sarah Schreffler

[fstenoughsnoopy]

I did the blue claw thing... Thanks Sarah. How do I have it save it in
that field? Right now it just shows it on the form, however, I need it
to permanently save that information to the fields in the order table?
Sarah Schreffler wrote:

Jeff Smith wrote:

<fs*************@yahoo.com> wrote in message
news:11*********************@g10g2000cwb.googlegro ups.com...

I have a customer order database and I need to pull a customers
information, ie first name, last name, address, city, state, zip,
phone, etc, into the oder table. i don't know how to go about making
this work...

You don't. Insert the primary key of the customer into the assigned foreign
key of the order table. Search the Internet on normalisation or the 3rd
normal form.

I can see a reason for doing this actually -- for orders, you want a
history of what address a particular order actually went to, even if
the person later changes their address.

http://www.blueclaw-db.com/comboboxl..._combo_box.htm
shows an example of how to fill other text boxes from columns in a
combo box.

Basically, set control source to use the column property of a combo box
followed by a number.

--Sarah Schreffler

[w_a_n_n_a_l_l_ -]

Your point is correct, but setting controlsource to the column of the
listbox or combobox will not store the data in the order table. By
definition, if the controlsource is the column of a control it cannot be a
field in a table.

If you're adding your record in code it would be along this line:

with rstOrder
.addnew
.fields("CustomerName") = me.cbo.Customer.column(COL_NAME)
.fields("Address") = me.cbo.Customer.column(COL_ADDRESS)
.fields("Phone") = me.cbo.Customer.column(COL_PHONE)
.fields("Email") = me.cbo.Customer.column(COL_EMAIL)
end with
The arrangement here assumes that you define a series of constants
(COL_NAME, etc) that identifiy the columns in your combobox that you want
copied to fields in the new record.

A variation is that in an Order subform, within a Customer form, since the
link is CustomerID, you would get the CustomerID the moment you type
anything on the New Line row of the subform. You could use I believe the
BeforeInsert event of the subform to copy the fields similar to code above,
but into controls on the new order row. E.G.:

(code within the subform)

me.txtCustomerName = me.parent.txtCustomerName
me.txtAddress = me.parent.txtCustomerName
me.txtWhatever = me.parent.txtWhatever

The point about wanting to make a snapshot of the name, address, phone,
whatever, as they were when the order was logged, is a very good point.

Another solution is to have address in table separate from the customer, so
that you can have as many as you need, and just mark the current one. then
you store the foreign key to that address record and not the text.

There are advantages and disadvantages both ways. You have to decide what
will work for you within time and budget constraints.

[fstenoughsnoopy]

Ok. I have the contact list with first name, middle initial, Last name
address, city, state and zip. They need to be assigned to a buyer and
seller under the same fields but with B1, B1, S1 and S2 standing for
the buyers and sellers of the items. I need them stored because people
change addresses but the contact list will be kept current so we can
send mail to them at new addresses but still know where the item went.
Im not the best at code and can do basics. Thanks for everyones
help!@!!
w_a_n_n_a_l_l_-@-_s_b_c_g_l_o_b_a_l._n_e_t wrote:

Your point is correct, but setting controlsource to the column of the
listbox or combobox will not store the data in the order table. By
definition, if the controlsource is the column of a control it cannot be a
field in a table.

If you're adding your record in code it would be along this line:

with rstOrder
.addnew
.fields("CustomerName") = me.cbo.Customer.column(COL_NAME)
.fields("Address") = me.cbo.Customer.column(COL_ADDRESS)
.fields("Phone") = me.cbo.Customer.column(COL_PHONE)
.fields("Email") = me.cbo.Customer.column(COL_EMAIL)
end with
The arrangement here assumes that you define a series of constants
(COL_NAME, etc) that identifiy the columns in your combobox that you want
copied to fields in the new record.

A variation is that in an Order subform, within a Customer form, since the
link is CustomerID, you would get the CustomerID the moment you type
anything on the New Line row of the subform. You could use I believe the
BeforeInsert event of the subform to copy the fields similar to code above,
but into controls on the new order row. E.G.:

(code within the subform)

me.txtCustomerName = me.parent.txtCustomerName
me.txtAddress = me.parent.txtCustomerName
me.txtWhatever = me.parent.txtWhatever

The point about wanting to make a snapshot of the name, address, phone,
whatever, as they were when the order was logged, is a very good point.

Another solution is to have address in table separate from the customer, so
that you can have as many as you need, and just mark the current one. then
you store the foreign key to that address record and not the text.

There are advantages and disadvantages both ways. You have to decide what
will work for you within time and budget constraints.

[fstenoughsnoopy]

Still Have no idea how to save it to the query and table???