Form not writing field value back to table

[Sarah Kingswell]

I am sure this is simple but I haven't used Access for a long time and I
have forgotten how much of this works.

I have a form which allowes me to enter data into a new quote.header table.
The quote.header table has a relationship to the customer table. When I
enter a new record using the form and select the customer name using a combo
box it updates the quotes table with the customer name. (great that bit
works!) In the form I have a field which displays the customer number field
from the customer table. I also want to write this back to the customer
number field on the quote. header table.

So my question is, how do I get access to populate the customer number field
on the quote header table with the customer number field from the customer
table. At the moment it is just displaying it on the form and not writing
it back.

Any help gratefully apreciated.

--
Solent Carpentry Services Ltd
3 Capel Ley
Purbrook
Hampshire
PO7 5PU

Tel: 02392 782875
Mobile: 07721 529512

Privileged/Confidential Information may be contained in this message. If
you are not the address indicated in this message (or responsible for
delivery of the message to such person), you may not copy or deliver this
message to anyone. In such case you should destroy this message and kindly
notify the sender by reply email. Please advise immediately if you or your
employer does not consent to Internet email messages of this kind.
Opinions, conclusions and other information in this message that do not
relate to the official business of my company shall be understood as neither
given nor endorsed by it.

Registered Office: The Old Surgery, 18 Mengham Lane, Hayling Island,
Hampshire, PO11 9JT. Company Reg. No. 05667859 Registered in England and
Wales

 

[BruceM]

Store only the Customer Number. Bind the combo box to the CustomerNumber
field in the QuoteHeader table. The combo box Row Source is based on the
Customer table. Its first column is CustomerNumber, and its second column
is CustomerName. On the combo box Property sheet set the Bound Column to 1,
the Column Count to 2, and the Column Widths to something like 0";1.5".
If you need to display the CustomerNumber, add the Customer table to the
form's Record Source query. Add the CustomerNumber from tblCustomer to the
query, and bind a text box to this field.
Another way is to add an unbound text box to the form. Set its Control
Source to:
=[ComboBoxName].Column(0)
The advantage of storing only the number is that if the CustomerName changes
(and they do, in my experience) you do not need to update any records other
than that customer's record in the Customer table.

 

[Sarah Kingswell]

Thanks Bruce

I have tried both of your suggestions but I still cannot get the customer no
field stored on the quote header table. The form is designed to allow the
user to select a customer name from the combo box. Another field stores the
customer no which the user doesn't need to see. It does display the correct
customer no but I want it written back to the quote table and it won't do
this. User error I am sure! but I have been looking at this for hours
today and cannot figure out why I can't get it working.

Do you have any other suggestions?

Store only the Customer Number. Bind the combo box to the CustomerNumber
field in the QuoteHeader table. The combo box Row Source is based on the
Customer table. Its first column is CustomerNumber, and its second column
is CustomerName. On the combo box Property sheet set the Bound Column to
1, the Column Count to 2, and the Column Widths to something like 0";1.5".
If you need to display the CustomerNumber, add the Customer table to the
form's Record Source query. Add the CustomerNumber from tblCustomer to
the query, and bind a text box to this field.
Another way is to add an unbound text box to the form. Set its Control
Source to:
=[ComboBoxName].Column(0)
The advantage of storing only the number is that if the CustomerName
changes (and they do, in my experience) you do not need to update any
records other than that customer's record in the Customer table.

I am sure this is simple but I haven't used Access for a long time and I
have forgotten how much of this works.

I have a form which allowes me to enter data into a new quote.header
table. The quote.header table has a relationship to the customer table.
When I enter a new record using the form and select the customer name
using a combo box it updates the quotes table with the customer name.
(great that bit works!) In the form I have a field which displays the
customer number field from the customer table. I also want to write this
back to the customer number field on the quote. header table.

So my question is, how do I get access to populate the customer number
field on the quote header table with the customer number field from the
customer table. At the moment it is just displaying it on the form and
not writing it back.

Any help gratefully apreciated.

--
Solent Carpentry Services Ltd
3 Capel Ley
Purbrook
Hampshire
PO7 5PU

Tel: 02392 782875
Mobile: 07721 529512

Privileged/Confidential Information may be contained in this message. If
you are not the address indicated in this message (or responsible for
delivery of the message to such person), you may not copy or deliver this
message to anyone. In such case you should destroy this message and
kindly notify the sender by reply email. Please advise immediately if
you or your employer does not consent to Internet email messages of this
kind. Opinions, conclusions and other information in this message that do
not relate to the official business of my company shall be understood as
neither given nor endorsed by it.

Registered Office: The Old Surgery, 18 Mengham Lane, Hayling Island,
Hampshire, PO11 9JT. Company Reg. No. 05667859 Registered in England and
Wales

 

[BruceM]

Store only the customer number. Bind the combo box to the CustomerNumber
field. Set the combo box Row Source so that it includes the CustomerNumber
and the CustomerName. Hide the CustomerNumber column in the combo box by
setting its width to 0. The combo box will display the name, but will store
the number.
If this is not clear, please describe what you are using for the combo box
Row Source.

Thanks Bruce

I have tried both of your suggestions but I still cannot get the customer
no field stored on the quote header table. The form is designed to allow
the user to select a customer name from the combo box. Another field
stores the customer no which the user doesn't need to see. It does
display the correct customer no but I want it written back to the quote
table and it won't do this. User error I am sure! but I have been
looking at this for hours today and cannot figure out why I can't get it
working.

Do you have any other suggestions?

Store only the Customer Number. Bind the combo box to the CustomerNumber
field in the QuoteHeader table. The combo box Row Source is based on the
Customer table. Its first column is CustomerNumber, and its second
column is CustomerName. On the combo box Property sheet set the Bound
Column to 1, the Column Count to 2, and the Column Widths to something
like 0";1.5".
If you need to display the CustomerNumber, add the Customer table to the
form's Record Source query. Add the CustomerNumber from tblCustomer to
the query, and bind a text box to this field.
Another way is to add an unbound text box to the form. Set its Control
Source to:
=[ComboBoxName].Column(0)
The advantage of storing only the number is that if the CustomerName
changes (and they do, in my experience) you do not need to update any
records other than that customer's record in the Customer table.

I am sure this is simple but I haven't used Access for a long time and I
have forgotten how much of this works.

I have a form which allowes me to enter data into a new quote.header
table. The quote.header table has a relationship to the customer table.
When I enter a new record using the form and select the customer name
using a combo box it updates the quotes table with the customer name.
(great that bit works!) In the form I have a field which displays the
customer number field from the customer table. I also want to write
this back to the customer number field on the quote. header table.

So my question is, how do I get access to populate the customer number
field on the quote header table with the customer number field from the
customer table. At the moment it is just displaying it on the form and
not writing it back.

Any help gratefully apreciated.

--
Solent Carpentry Services Ltd
3 Capel Ley
Purbrook
Hampshire
PO7 5PU

Tel: 02392 782875
Mobile: 07721 529512

Privileged/Confidential Information may be contained in this message.
If you are not the address indicated in this message (or responsible for
delivery of the message to such person), you may not copy or deliver
this message to anyone. In such case you should destroy this message
and kindly notify the sender by reply email. Please advise immediately
if you or your employer does not consent to Internet email messages of
this kind. Opinions, conclusions and other information in this message
that do not relate to the official business of my company shall be
understood as neither given nor endorsed by it.

Registered Office: The Old Surgery, 18 Mengham Lane, Hayling Island,
Hampshire, PO11 9JT. Company Reg. No. 05667859 Registered in England
and Wales