Record from form to database?

[Tom]

I have a control "Text208" on my form "DispatchForm" that is unbound.
The control sourse of "Text208" is tied to "=[Combo206].Column(1)".

What I want to happen.

I would like the information that is recorded into "Text208" to be
input automatically into my database "DispatchTable" into field "TrAddress".

Help me please....
TOM

 

[tina]

you'd have to use code, or a macro, to set the value of the field to the
value contained in the unbound textbox. use a SetValue action in a macro, or
a simple statement

Me!TrAddress = Me!Combo206.Column(1)

in VBA code.

HOWEVER, you're breaking normalization rules if you save the same address in
two different tables. you should have a solid business or technical reason
to *need* to break that rule, before you decide to do it. if you're not
familiar with normalization principles, see
http://home.att.net/~california.db/tips.html#aTip1 for more information.

hth

 

[Ken Sheridan]

Tom:

Assuming the form is bound to the DispatchTable table an its the current
record you want to update with the value, in the combo box's AfterUpdate
event procedure assign the value to the field:

Me.TrAddress = Me. Combo206.Column(1)

However, as Tina has said, you need to consider whether or not this
constitutes redundancy. If the value in the other table on which the combo
box is drawing is variable over time, i.e. it can be changed, but you want
the value in the DispatchTable to remain constant at that when the record was
inserted into that table, even if the value has changed in the combo box's
table, then there is no redundancy as the TrAddress column is functionally
dependent on the key of the DispatchTable. If on the other hand the values
in both tables should always be the same, then the column is not functionally
dependent on the key and there is redundancy and you should not store the
value in the DispatchTable but map to it via a query whenever required.

There is a situation analogous to the first scenario (no redundancy) in the
sample Northwind database, where the Orders Subform looks up and stores
values from the Products table in the UnitPrice column of the Order Details
table. This is because while the unit price of a product might change you
want each order to reflect the price at the time the order was made.

BTW I'd suggest that when you add controls to a form you give then a
meaningful name immediately, cboSuppliers say, rather than sticking with the
default arbitrary names like Combo206 which Access assigns to them by
default. When you refer to the controls in code it then becomes mush me
readable and easy to understand what its doing.

Ken Sheridan
Stafford, England

 

[tina]

Where do I put "Me!TrAddress = Me!Combo206.Column(1) "?

add the code to the combo box's AfterUpdate event procedure. for illustrated
instructions on how to create an event procedure, go to
http://home.att.net/~california.db/instructions.html and scroll down to
"Create a VBA event procedure".

hth

Ken & Tina,
I am extremely new to this. However, I don't think there is
any redundancy. The "DispatchTable" is storing data as it
is entered into the form. Creating a "new" record. The address
will be different on occasion. The form is used to create record(s).

Where do I put "Me.TrAddress = Me. Combo206.Column(1) "?

Do I add "TrAddress" to the form" and put it into the After Update?l, or do
I put it into the "DispatchTable" under "TrAddress"? AND, where
do I put it?

I'm a dummie when it comes to this. I need a step by step
description. i.e. right click on control, click on properties, etc.....
Thank you both for your help!
Tom

Tom:

Assuming the form is bound to the DispatchTable table an its the current
record you want to update with the value, in the combo box's AfterUpdate
event procedure assign the value to the field:

Me.TrAddress = Me. Combo206.Column(1)

However, as Tina has said, you need to consider whether or not this
constitutes redundancy. If the value in the other table on which the combo
box is drawing is variable over time, i.e. it can be changed, but you want
the value in the DispatchTable to remain constant at that when the record was
inserted into that table, even if the value has changed in the combo box's
table, then there is no redundancy as the TrAddress column is functionally
dependent on the key of the DispatchTable. If on the other hand the values
in both tables should always be the same, then the column is not functionally
dependent on the key and there is redundancy and you should not store the
value in the DispatchTable but map to it via a query whenever required.

There is a situation analogous to the first scenario (no redundancy) in the
sample Northwind database, where the Orders Subform looks up and stores
values from the Products table in the UnitPrice column of the Order Details
table. This is because while the unit price of a product might change you
want each order to reflect the price at the time the order was made.

BTW I'd suggest that when you add controls to a form you give then a
meaningful name immediately, cboSuppliers say, rather than sticking with the
default arbitrary names like Combo206 which Access assigns to them by
default. When you refer to the controls in code it then becomes mush me
readable and easy to understand what its doing.

Ken Sheridan
Stafford, England

I have a control "Text208" on my form "DispatchForm" that is unbound.
The control sourse of "Text208" is tied to "=[Combo206].Column(1)".

What I want to happen.

I would like the information that is recorded into "Text208" to be
input automatically into my database "DispatchTable" into field "TrAddress".

Help me please....
TOM

 

[Tom]

That is not working.
Tom

Where do I put "Me!TrAddress = Me!Combo206.Column(1) "?

add the code to the combo box's AfterUpdate event procedure. for illustrated
instructions on how to create an event procedure, go to
http://home.att.net/~california.db/instructions.html and scroll down to
"Create a VBA event procedure".

hth

Ken & Tina,
I am extremely new to this. However, I don't think there is
any redundancy. The "DispatchTable" is storing data as it
is entered into the form. Creating a "new" record. The address
will be different on occasion. The form is used to create record(s).

Where do I put "Me.TrAddress = Me. Combo206.Column(1) "?

Do I add "TrAddress" to the form" and put it into the After Update?l, or do
I put it into the "DispatchTable" under "TrAddress"? AND, where
do I put it?

I'm a dummie when it comes to this. I need a step by step
description. i.e. right click on control, click on properties, etc.....
Thank you both for your help!
Tom

Tom:

Assuming the form is bound to the DispatchTable table an its the current
record you want to update with the value, in the combo box's AfterUpdate
event procedure assign the value to the field:

Me.TrAddress = Me. Combo206.Column(1)

However, as Tina has said, you need to consider whether or not this
constitutes redundancy. If the value in the other table on which the combo
box is drawing is variable over time, i.e. it can be changed, but you want
the value in the DispatchTable to remain constant at that when the record was
inserted into that table, even if the value has changed in the combo box's
table, then there is no redundancy as the TrAddress column is functionally
dependent on the key of the DispatchTable. If on the other hand the values
in both tables should always be the same, then the column is not functionally
dependent on the key and there is redundancy and you should not store the
value in the DispatchTable but map to it via a query whenever required.

There is a situation analogous to the first scenario (no redundancy) in the
sample Northwind database, where the Orders Subform looks up and stores
values from the Products table in the UnitPrice column of the Order Details
table. This is because while the unit price of a product might change you
want each order to reflect the price at the time the order was made.

BTW I'd suggest that when you add controls to a form you give then a
meaningful name immediately, cboSuppliers say, rather than sticking with the
default arbitrary names like Combo206 which Access assigns to them by
default. When you refer to the controls in code it then becomes mush me
readable and easy to understand what its doing.

Ken Sheridan
Stafford, England

:

I have a control "Text208" on my form "DispatchForm" that is unbound.
The control sourse of "Text208" is tied to "=[Combo206].Column(1)".

What I want to happen.

I would like the information that is recorded into "Text208" to be
input automatically into my database "DispatchTable" into field "TrAddress".

Help me please....
TOM

 

[Tom]

Got it!!! Operator error.....
Tom

That is not working.
Tom

Where do I put "Me!TrAddress = Me!Combo206.Column(1) "?

add the code to the combo box's AfterUpdate event procedure. for illustrated
instructions on how to create an event procedure, go to
http://home.att.net/~california.db/instructions.html and scroll down to
"Create a VBA event procedure".

hth

Ken & Tina,
I am extremely new to this. However, I don't think there is
any redundancy. The "DispatchTable" is storing data as it
is entered into the form. Creating a "new" record. The address
will be different on occasion. The form is used to create record(s).

Where do I put "Me.TrAddress = Me. Combo206.Column(1) "?

Do I add "TrAddress" to the form" and put it into the After Update?l, or do
I put it into the "DispatchTable" under "TrAddress"? AND, where
do I put it?

I'm a dummie when it comes to this. I need a step by step
description. i.e. right click on control, click on properties, etc.....
Thank you both for your help!
Tom

:

Tom:

Assuming the form is bound to the DispatchTable table an its the current
record you want to update with the value, in the combo box's AfterUpdate
event procedure assign the value to the field:

Me.TrAddress = Me. Combo206.Column(1)

However, as Tina has said, you need to consider whether or not this
constitutes redundancy. If the value in the other table on which the combo
box is drawing is variable over time, i.e. it can be changed, but you want
the value in the DispatchTable to remain constant at that when the record was
inserted into that table, even if the value has changed in the combo box's
table, then there is no redundancy as the TrAddress column is functionally
dependent on the key of the DispatchTable. If on the other hand the values
in both tables should always be the same, then the column is not functionally
dependent on the key and there is redundancy and you should not store the
value in the DispatchTable but map to it via a query whenever required.

There is a situation analogous to the first scenario (no redundancy) in the
sample Northwind database, where the Orders Subform looks up and stores
values from the Products table in the UnitPrice column of the Order Details
table. This is because while the unit price of a product might change you
want each order to reflect the price at the time the order was made.

BTW I'd suggest that when you add controls to a form you give then a
meaningful name immediately, cboSuppliers say, rather than sticking with the
default arbitrary names like Combo206 which Access assigns to them by
default. When you refer to the controls in code it then becomes mush me
readable and easy to understand what its doing.

Ken Sheridan
Stafford, England

:

I have a control "Text208" on my form "DispatchForm" that is unbound.
The control sourse of "Text208" is tied to "=[Combo206].Column(1)".

What I want to happen.

I would like the information that is recorded into "Text208" to be
input automatically into my database "DispatchTable" into field "TrAddress".

Help me please....
TOM

 

[Ken Sheridan]

Tom:

I think you are correct about there not being any redundancy here as it
sounds like the situation is analogous to the Northwind example I cited.

What you want to do is insert the value from the combo box's second column
into the TrAddress field of a new row in the DispatchTable table. I'm
assuming that the form's RecordSource is DispatchTable and you are simply
navigating to a new record and adding data. In which case you can have a
text box on the form bound to the TrAddress field or not as you wish. It all
depends whether you want to see the TrAddress or not on the form and whether
you want to be able to change it when the combo box inserts the value into
the field.

If you don't want to see it at all then all you need is the code in the
combo box's AfterUpdate event procedure. To do this select the combo box
while the form is open in design view. In its properties sheet select the
AfterUpdate event and click on the 'build' button; that's the one on the
right with 3 dots. In the next dialogue select Code Builder. When you exit
the dialogue the VBA editor will open at the combo box's AfterUpdate event
procedure. The first and last lines will be in place. In a new line between
them enter:

Me.TrAddress = Me. Combo206.Column(1)

That should assign the value from the combo box's second column (the Column
property is zero-based) to the field. You say its not working. So what is
happening? Is the form not bound to the Dispatchestable table? Leaving that
aside for the moment, read on:

If you want to see the TrAddress field but not be able to change the value
the combo box puts into it then add a text box to the form with the TrAddress
field as its ControlSource (its then 'bound' to the field). In the text
box's properties sheet set its Enabled property to False (No) and its Locked
property to True (Yes). When you select an item from the combo box the value
from the second column should now appear in the text box and be stored in the
TrAddress field.

If you want the users to be able to change the value after the combo box
inserts it in the text box just leave the Enabled and Locked properties of
the text box at the default settings of True and False respectively. If a
user changes the value from that inserted by the combo box the changes value
will now be stored in the field when the record is saved by moving to another
record, closing the form etc.

Ken Sheridan
Stafford, England

Got it!!! Operator error.....
Tom

That is not working.
Tom

Where do I put "Me!TrAddress = Me!Combo206.Column(1) "?

add the code to the combo box's AfterUpdate event procedure. for illustrated
instructions on how to create an event procedure, go to
http://home.att.net/~california.db/instructions.html and scroll down to
"Create a VBA event procedure".

hth


Ken & Tina,
I am extremely new to this. However, I don't think there is
any redundancy. The "DispatchTable" is storing data as it
is entered into the form. Creating a "new" record. The address
will be different on occasion. The form is used to create record(s).

Where do I put "Me.TrAddress = Me. Combo206.Column(1) "?

Do I add "TrAddress" to the form" and put it into the After Update?l, or
do
I put it into the "DispatchTable" under "TrAddress"? AND, where
do I put it?

I'm a dummie when it comes to this. I need a step by step
description. i.e. right click on control, click on properties, etc.....
Thank you both for your help!
Tom

:

Tom:

Assuming the form is bound to the DispatchTable table an its the current
record you want to update with the value, in the combo box's AfterUpdate
event procedure assign the value to the field:

Me.TrAddress = Me. Combo206.Column(1)

However, as Tina has said, you need to consider whether or not this
constitutes redundancy. If the value in the other table on which the
combo
box is drawing is variable over time, i.e. it can be changed, but you
want
the value in the DispatchTable to remain constant at that when the
record was
inserted into that table, even if the value has changed in the combo
box's
table, then there is no redundancy as the TrAddress column is
functionally
dependent on the key of the DispatchTable. If on the other hand the
values
in both tables should always be the same, then the column is not
functionally
dependent on the key and there is redundancy and you should not store
the
value in the DispatchTable but map to it via a query whenever required.

There is a situation analogous to the first scenario (no redundancy) in
the
sample Northwind database, where the Orders Subform looks up and stores
values from the Products table in the UnitPrice column of the Order
Details
table. This is because while the unit price of a product might change
you
want each order to reflect the price at the time the order was made.

BTW I'd suggest that when you add controls to a form you give then a
meaningful name immediately, cboSuppliers say, rather than sticking with
the
default arbitrary names like Combo206 which Access assigns to them by
default. When you refer to the controls in code it then becomes mush me
readable and easy to understand what its doing.

Ken Sheridan
Stafford, England

:

I have a control "Text208" on my form "DispatchForm" that is unbound.
The control sourse of "Text208" is tied to "=[Combo206].Column(1)".

What I want to happen.

I would like the information that is recorded into "Text208" to be
input automatically into my database "DispatchTable" into field
"TrAddress".

Help me please....
TOM