Adding a field value, based on another field

[Amanda Book Girl]

On my table I have 4 fields - book ref, book number, book name, contractor
the form that feeds to this field has a multiple column option box which
shows all 4 entries ( so people can pick one )
However, only the book ref saves onto the table - the other 3 values dont
I have 3 other text boxes on my form which updated based on the values
chosen in the book ref option box - but these values dont then feed to the
table.
How do i do this ?

 

[Klatuu]

First, lets get the terminology down.
Fields belong to tables.
Controls belong to forms.

Each control on a form has a property named control source.
If the control source of a control has the name of a field in the form's
record source in it, then it is a bound field and the value entered in the
control be will saved in the record. When you navigate to an existing
record, the value currently in the field will be displayed in the control.

So, the short answer is if you are entering values in controls on your form
and these values are not being saved in the table that is the record source
for your form, then the controls are unbound.

 

[Amanda Book Girl]

Thanks - the control source for the 3 additional text boxes show like this :
=[Forms]![Updated Record Card August 2008]![ChooseDel].[Column](1)
but i want that value to then be bound back to the field on the table
Is this possible ?

 

[Klatuu]

No. A control source can either be empty, in which case it does nothing. It
can be an expression used to display data (as you are doing), or it can be
bound to a field in the form's record source. Note that when you use a
control with an expression in the control source, you can't change the value
by typing in the control.

What you can do is use two controls. One as you are doing to display the
data and a hidden bound control to save the data. What you have to do then
is programmatically assign the value of the visible control to the hidden
control. You should be able to do that in the form's before update event.
--
Dave Hargis, Microsoft Access MVP

Thanks - the control source for the 3 additional text boxes show like this :
=[Forms]![Updated Record Card August 2008]![ChooseDel].[Column](1)
but i want that value to then be bound back to the field on the table
Is this possible ?

First, lets get the terminology down.
Fields belong to tables.
Controls belong to forms.

Each control on a form has a property named control source.
If the control source of a control has the name of a field in the form's
record source in it, then it is a bound field and the value entered in the
control be will saved in the record. When you navigate to an existing
record, the value currently in the field will be displayed in the control.

So, the short answer is if you are entering values in controls on your form
and these values are not being saved in the table that is the record source
for your form, then the controls are unbound.

 

[Amanda Book Girl]

ha ha - but this is the bit i dont know how to do !
The table is called Main Table and the fields I want to populate would be
called Contractor, Book Name and Book No - from the Form Updated Record Card
Form August 2008 - ComboBox ChooseDel - columns 2 to 4
Amanda

No. A control source can either be empty, in which case it does nothing. It
can be an expression used to display data (as you are doing), or it can be
bound to a field in the form's record source. Note that when you use a
control with an expression in the control source, you can't change the value
by typing in the control.

What you can do is use two controls. One as you are doing to display the
data and a hidden bound control to save the data. What you have to do then
is programmatically assign the value of the visible control to the hidden
control. You should be able to do that in the form's before update event.
--
Dave Hargis, Microsoft Access MVP

Thanks - the control source for the 3 additional text boxes show like this :
=[Forms]![Updated Record Card August 2008]![ChooseDel].[Column](1)
but i want that value to then be bound back to the field on the table
Is this possible ?

First, lets get the terminology down.
Fields belong to tables.
Controls belong to forms.

Each control on a form has a property named control source.
If the control source of a control has the name of a field in the form's
record source in it, then it is a bound field and the value entered in the
control be will saved in the record. When you navigate to an existing
record, the value currently in the field will be displayed in the control.

So, the short answer is if you are entering values in controls on your form
and these values are not being saved in the table that is the record source
for your form, then the controls are unbound.
--
Dave Hargis, Microsoft Access MVP


:

On my table I have 4 fields - book ref, book number, book name, contractor
the form that feeds to this field has a multiple column option box which
shows all 4 entries ( so people can pick one )
However, only the book ref saves onto the table - the other 3 values dont
I have 3 other text boxes on my form which updated based on the values
chosen in the book ref option box - but these values dont then feed to the
table.
How do i do this ?

 

[BruceM]

To expand on the other response, the After Update event of the list box
could have something like this:

Me.BookNumber = Me.ChooseDel.Column(1)

This assumes ChooseDel is the name of your combo box, that you are in the
form Updated Record Card August 2008, that Column(1) is the Book Number, and
that BookNumber is the name of the Book Number field in the form's record
source.

I will use the name txtBookNumber for the text box bound to the BookNumber
field. You should be able to use BookNumber (the field name) as the Control
Source for txtBookNumber (the text box). After making a list box selection,
the BookNumber column will be written to the BookNumber field.

If you use the form's Before Update field you will probably want to leave
the control source for the text box set to:
[ChooseDel].Column(1)

A couple of concerns. Your form is numbered to reflect a specific month.
Do you intend to create a new form every month? Do you have separate tables
or queries for each month? If so, there is a problem with the design.

Also, it may not be necessary to store BookNumber, etc. If a BookNumber is
associated only with a specific BookRef you can continue to display the
information as you are doing now. If you can see it every time you look at
the record there is nothing to be gained by storing it again.

Thanks - the control source for the 3 additional text boxes show like this
:
=[Forms]![Updated Record Card August 2008]![ChooseDel].[Column](1)
but i want that value to then be bound back to the field on the table
Is this possible ?

First, lets get the terminology down.
Fields belong to tables.
Controls belong to forms.

Each control on a form has a property named control source.
If the control source of a control has the name of a field in the form's
record source in it, then it is a bound field and the value entered in
the
control be will saved in the record. When you navigate to an existing
record, the value currently in the field will be displayed in the
control.

So, the short answer is if you are entering values in controls on your
form
and these values are not being saved in the table that is the record
source
for your form, then the controls are unbound.

 

[Klatuu]

It doesn't matter what the table name is or the field names when you are
dealing with data in forms.

Here is how you can do it.
Create a macor
You will want use 3 SetValue actions, 1 for each control you need to update
In the Item box, put the name of the form and control you want to update
in the Expression box put the name of the control you are using to show the
data.
If you press F1 in each of the boxes, you should get some pretty good help
on how to set the macro up as you go.
Give the macro a name and save it.

Now open your form in design view, select the form, and open the properties
dialog for the form.
Select the Events tab. Select the Before Update event and type in the name
of the macro.
--
Dave Hargis, Microsoft Access MVP

ha ha - but this is the bit i dont know how to do !
The table is called Main Table and the fields I want to populate would be
called Contractor, Book Name and Book No - from the Form Updated Record Card
Form August 2008 - ComboBox ChooseDel - columns 2 to 4
Amanda

No. A control source can either be empty, in which case it does nothing. It
can be an expression used to display data (as you are doing), or it can be
bound to a field in the form's record source. Note that when you use a
control with an expression in the control source, you can't change the value
by typing in the control.

What you can do is use two controls. One as you are doing to display the
data and a hidden bound control to save the data. What you have to do then
is programmatically assign the value of the visible control to the hidden
control. You should be able to do that in the form's before update event.
--
Dave Hargis, Microsoft Access MVP

Thanks - the control source for the 3 additional text boxes show like this :
=[Forms]![Updated Record Card August 2008]![ChooseDel].[Column](1)
but i want that value to then be bound back to the field on the table
Is this possible ?



:

First, lets get the terminology down.
Fields belong to tables.
Controls belong to forms.

Each control on a form has a property named control source.
If the control source of a control has the name of a field in the form's
record source in it, then it is a bound field and the value entered in the
control be will saved in the record. When you navigate to an existing
record, the value currently in the field will be displayed in the control.

So, the short answer is if you are entering values in controls on your form
and these values are not being saved in the table that is the record source
for your form, then the controls are unbound.
--
Dave Hargis, Microsoft Access MVP


:

On my table I have 4 fields - book ref, book number, book name, contractor
the form that feeds to this field has a multiple column option box which
shows all 4 entries ( so people can pick one )
However, only the book ref saves onto the table - the other 3 values dont
I have 3 other text boxes on my form which updated based on the values
chosen in the book ref option box - but these values dont then feed to the
table.
How do i do this ?