Order entry form using data from a second table

[Terry]

I need to create a form that enters data into the "orders" table. In that
table there are several fields that I want to populate from a second table
"suppliers" (suppliername, address1, address2, etc...) The two tables will
have a common field [supplier_Number]. The relationship is one (Supplier) to
many (Orders).

The idea is that when the suppliernumber is entered in a combobox, the other
supplier fields will automatically fill in information from the suppliers
table. How do I do this? I assume I cannot use a subform since I do not want
to just display the data. Someone in this group had suggested that I base
the form on a query that combined the two tables, but because of the one to
many relationship, the query is not updateable.

I am sure this is a very basic thing to do, but I cannot seem to get it. Any
guidance would be appreciated.

Terry

 

[akphidelt]

Alright, maybe ill beat the bush a little bit more. But you really should use
a query to join the two tables. I don't understand your reasoning of not
using a query, and why would you need the address and suppliername repeated
over and over again in the orders table?

 

[Terry]

Don't mind you beating the bush - it may be the right way to do it, I just
have not been able to get it to work. As far as the redundant data, it may
not be critical, but we have always stored the a few of the items in the
orders table also (address fields and phone mostly) to keep a permenent
record of that information at the tiem of the order. We don't recreate the
entire contact record, just a few critical fields.

As for the query, how do I get around the fact that it is not updateable?

Thanks for the help.

Terry

Alright, maybe ill beat the bush a little bit more. But you really should
use
a query to join the two tables. I don't understand your reasoning of not
using a query, and why would you need the address and suppliername
repeated
over and over again in the orders table?

I need to create a form that enters data into the "orders" table. In that
table there are several fields that I want to populate from a second
table
"suppliers" (suppliername, address1, address2, etc...) The two tables
will
have a common field [supplier_Number]. The relationship is one (Supplier)
to
many (Orders).

The idea is that when the suppliernumber is entered in a combobox, the
other
supplier fields will automatically fill in information from the suppliers
table. How do I do this? I assume I cannot use a subform since I do not
want
to just display the data. Someone in this group had suggested that I base
the form on a query that combined the two tables, but because of the one
to
many relationship, the query is not updateable.

I am sure this is a very basic thing to do, but I cannot seem to get it.
Any
guidance would be appreciated.

Terry

 

[Dale Fye]

I disagree.

I would consider two ways to do what you want.

1. Make the SQL for the suppliers combo box look something like:

SELECT SupplierID, SupplierName, Address1, Address2, ...
FROM tbl_Suppliers

Then bind the combo box to the Supplier_Number field. In the AfterUpdate
event of the combo box, use code similar to the following to populate the
locked, unbound textboxes for the address.

Private Sub cbo_Supplier_AfterUpdate

me.txt_SupplierName = me.cbo_Suppliers.column(1)
me.txt_Supp_Address1 = me.cbo_Supplier.column(2)
me.txt_Supp_Address2 = me.cbo_Supplier.column(3)

End sub

But if you have a lot of suppliers, and are bringing this stuff back across
a network, you might want to try option #2

2. Include only the SupplierID and SupplierName in the combo box. Then, in
the combo boxes afterupdate event, retrieve the other fields you need, for
only the record selected. Something like:

Private Sub cbo_Supplier_afterUpdate

Dim strSQL as string
Dim rs as DAO.Recordset

me.txt_SupplierName = me.cbo_Suppliers.column(1)

strSQL = "SELECT Address1, Address2, .... " _
& "FROM tbl_Suppliers " _
& "WHERE Supplier_ID = " & me.cbo_Suppliers
set rs = currentdb.openrecordset strsql

if rs.eof then
msgbox "invalid ID"
Else
me.txt_Address1 = rs("Address1")
me.txt_Address2 = rs("address2")
...
End if

rs.close
set rs = nothing

End sub


--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Alright, maybe ill beat the bush a little bit more. But you really should use
a query to join the two tables. I don't understand your reasoning of not
using a query, and why would you need the address and suppliername repeated
over and over again in the orders table?

I need to create a form that enters data into the "orders" table. In that
table there are several fields that I want to populate from a second table
"suppliers" (suppliername, address1, address2, etc...) The two tables will
have a common field [supplier_Number]. The relationship is one (Supplier) to
many (Orders).

The idea is that when the suppliernumber is entered in a combobox, the other
supplier fields will automatically fill in information from the suppliers
table. How do I do this? I assume I cannot use a subform since I do not want
to just display the data. Someone in this group had suggested that I base
the form on a query that combined the two tables, but because of the one to
many relationship, the query is not updateable.

I am sure this is a very basic thing to do, but I cannot seem to get it. Any
guidance would be appreciated.

Terry

 

[Terry]

Have not had a chance to fully implement and test it, but first swing (using
option 1) looks like a hit - thanks.
Terry

I disagree.

I would consider two ways to do what you want.

1. Make the SQL for the suppliers combo box look something like:

SELECT SupplierID, SupplierName, Address1, Address2, ...
FROM tbl_Suppliers

Then bind the combo box to the Supplier_Number field. In the AfterUpdate
event of the combo box, use code similar to the following to populate the
locked, unbound textboxes for the address.

Private Sub cbo_Supplier_AfterUpdate

me.txt_SupplierName = me.cbo_Suppliers.column(1)
me.txt_Supp_Address1 = me.cbo_Supplier.column(2)
me.txt_Supp_Address2 = me.cbo_Supplier.column(3)

End sub

But if you have a lot of suppliers, and are bringing this stuff back
across
a network, you might want to try option #2

2. Include only the SupplierID and SupplierName in the combo box. Then,
in
the combo boxes afterupdate event, retrieve the other fields you need, for
only the record selected. Something like:

Private Sub cbo_Supplier_afterUpdate

Dim strSQL as string
Dim rs as DAO.Recordset

me.txt_SupplierName = me.cbo_Suppliers.column(1)

strSQL = "SELECT Address1, Address2, .... " _
& "FROM tbl_Suppliers " _
& "WHERE Supplier_ID = " & me.cbo_Suppliers
set rs = currentdb.openrecordset strsql

if rs.eof then
msgbox "invalid ID"
Else
me.txt_Address1 = rs("Address1")
me.txt_Address2 = rs("address2")
...
End if

rs.close
set rs = nothing

End sub


--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Alright, maybe ill beat the bush a little bit more. But you really should
use
a query to join the two tables. I don't understand your reasoning of not
using a query, and why would you need the address and suppliername
repeated
over and over again in the orders table?

I need to create a form that enters data into the "orders" table. In
that
table there are several fields that I want to populate from a second
table
"suppliers" (suppliername, address1, address2, etc...) The two tables
will
have a common field [supplier_Number]. The relationship is one
(Supplier) to
many (Orders).

The idea is that when the suppliernumber is entered in a combobox, the
other
supplier fields will automatically fill in information from the
suppliers
table. How do I do this? I assume I cannot use a subform since I do not
want
to just display the data. Someone in this group had suggested that I
base
the form on a query that combined the two tables, but because of the
one to
many relationship, the query is not updateable.

I am sure this is a very basic thing to do, but I cannot seem to get
it. Any
guidance would be appreciated.

Terry

 

[Terry]

Have now set it up for all 10 fields I needed - works great (Option 1) Had a
problem at first because you need to be sure to set the "Column Count"
Property for the combo box to the number of feilds you want. After that it
works like a charm - thanks for the help!!

Terry

I disagree.

I would consider two ways to do what you want.

1. Make the SQL for the suppliers combo box look something like:

SELECT SupplierID, SupplierName, Address1, Address2, ...
FROM tbl_Suppliers

Then bind the combo box to the Supplier_Number field. In the AfterUpdate
event of the combo box, use code similar to the following to populate the
locked, unbound textboxes for the address.

Private Sub cbo_Supplier_AfterUpdate

me.txt_SupplierName = me.cbo_Suppliers.column(1)
me.txt_Supp_Address1 = me.cbo_Supplier.column(2)
me.txt_Supp_Address2 = me.cbo_Supplier.column(3)

End sub

But if you have a lot of suppliers, and are bringing this stuff back
across
a network, you might want to try option #2

2. Include only the SupplierID and SupplierName in the combo box. Then,
in
the combo boxes afterupdate event, retrieve the other fields you need, for
only the record selected. Something like:

Private Sub cbo_Supplier_afterUpdate

Dim strSQL as string
Dim rs as DAO.Recordset

me.txt_SupplierName = me.cbo_Suppliers.column(1)

strSQL = "SELECT Address1, Address2, .... " _
& "FROM tbl_Suppliers " _
& "WHERE Supplier_ID = " & me.cbo_Suppliers
set rs = currentdb.openrecordset strsql

if rs.eof then
msgbox "invalid ID"
Else
me.txt_Address1 = rs("Address1")
me.txt_Address2 = rs("address2")
...
End if

rs.close
set rs = nothing

End sub


--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Alright, maybe ill beat the bush a little bit more. But you really should
use
a query to join the two tables. I don't understand your reasoning of not
using a query, and why would you need the address and suppliername
repeated
over and over again in the orders table?

I need to create a form that enters data into the "orders" table. In
that
table there are several fields that I want to populate from a second
table
"suppliers" (suppliername, address1, address2, etc...) The two tables
will
have a common field [supplier_Number]. The relationship is one
(Supplier) to
many (Orders).

The idea is that when the suppliernumber is entered in a combobox, the
other
supplier fields will automatically fill in information from the
suppliers
table. How do I do this? I assume I cannot use a subform since I do not
want
to just display the data. Someone in this group had suggested that I
base
the form on a query that combined the two tables, but because of the
one to
many relationship, the query is not updateable.

I am sure this is a very basic thing to do, but I cannot seem to get
it. Any
guidance would be appreciated.

Terry