Update field on table, with value from 2nd table from form

[dkowitz]

I have two tables and a form:
Table 1
Id products pkg size customer addr city state zip

Table 2 (set up as query)
Id product pkg size

Form 1
Has same fields as table 1 and was originally built from that table.
However…I made some changes so the user can enter some things and allow some
fields to default based upon entry of other fields.
Product is a ComboBox that has dropdown of ‘products’ from table 2. When I
select the product it ‘automatically’ fills in the TextBox ‘pkg size’ on the
form. However…it does not update Table 1 like all the other fields.

My ComboBox ‘products’ on the form is set up:
Control Source Products
Row Source qryTable2
Row Source Type Table/Query

My TextBox ‘pkg size’ on the form is set up:
Control Source =[Products].[Column](2)

I can go back into my form, and the ‘pkg size’ is there, but not on Table 1…..
but everything else I enter on my form is there???

Why doesn’t the pkgsize update on Table1 ???

I’ve tried events…AfterUpdate…
Ie; DoCmd.RunSQL "UPDATE [Table1] Set[pkgsize] ='" [Products].[Column](2)
"'"

 

[John Spencer]

It doesn't update because the control is not bound to the field. It is using
an expression to show a value.

If you really need to store package size, you can use a bit of code in the
after update event of the combobox.

Private Sub Products_AfterUpdate()
Me.pkgsize = Me.Products.[Column](2)
End sub

You might now need to store package size since it is always available by
linking the products table. The only reason you should store it is if package
size will change for a product over time. Say the size goes from 16 oz to
13.5 oz. If you need to know that historically, you might store it in the
table 1.

Other ways to do this are more complex and are often more trouble than they
are worth.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[dkowitz]

Thanks for responding John....

Unfortunately I'm getting a runtime error:
"You can't assign a value to this object"

I must have some property or something set up wrong. Any suggestions what
that might be?

I've done some simple projects in Access and Exel...but am a Cobol mainframer
by trade....
thanks for any pointers you can provide!

daryl

It doesn't update because the control is not bound to the field. It is using
an expression to show a value.

If you really need to store package size, you can use a bit of code in the
after update event of the combobox.

Private Sub Products_AfterUpdate()
Me.pkgsize = Me.Products.[Column](2)
End sub

You might now need to store package size since it is always available by
linking the products table. The only reason you should store it is if package
size will change for a product over time. Say the size goes from 16 oz to
13.5 oz. If you need to know that historically, you might store it in the
table 1.

Other ways to do this are more complex and are often more trouble than they
are worth.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I have two tables and a form:
Table 1

[quoted text clipped - 27 lines]

Ie; DoCmd.RunSQL "UPDATE [Table1] Set[pkgsize] ='" [Products].[Column](2)
"'"

 

[John Spencer]

Did you change the control's source from the expression to the pkgsize
field?

You also will need to run an update query to fix the records where the
field did not get the value.

UPDATE [Table 1] INNER JOIN [Table 2]
ON [Table 1].Product = [Table 2].Product
SET [Table 1].[Pkg Size] = [Table 2].[Pkg Size]
WHERE [Table 1].[Pkg Size] is null

If you can only build queries in query design view.
== Add both tables
== drag from Product to Product to set up the join
== Add the Table 1 Pkg size field to the field list
== Set the criteria under the field to
Is Null
== Select Query: Update from the menu
== In the UPDATE To under this field enter
[Table 2].[Pkg size]
== Select Query: Run from the menu

That should update your table. Obviously replace the table and field
names with the real table and field names.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Thanks for responding John....

Unfortunately I'm getting a runtime error:
"You can't assign a value to this object"

I must have some property or something set up wrong. Any suggestions what
that might be?

I've done some simple projects in Access and Exel...but am a Cobol mainframer
by trade....
thanks for any pointers you can provide!

daryl

It doesn't update because the control is not bound to the field. It is using
an expression to show a value.

If you really need to store package size, you can use a bit of code in the
after update event of the combobox.

Private Sub Products_AfterUpdate()
Me.pkgsize = Me.Products.[Column](2)
End sub

You might now need to store package size since it is always available by
linking the products table. The only reason you should store it is if package
size will change for a product over time. Say the size goes from 16 oz to
13.5 oz. If you need to know that historically, you might store it in the
table 1.

Other ways to do this are more complex and are often more trouble than they
are worth.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I have two tables and a form:
Table 1

[quoted text clipped - 27 lines]

Ie; DoCmd.RunSQL "UPDATE [Table1] Set[pkgsize] ='" [Products].[Column](2)
"'"

 

[dkowitz via AccessMonster.com]

Solved!!
Thank you so much for your help John........
goes to show you, you can 'teach this old dog a few tricks'!!
Very much appreciated!!!

Did you change the control's source from the expression to the pkgsize
field?

You also will need to run an update query to fix the records where the
field did not get the value.

UPDATE [Table 1] INNER JOIN [Table 2]
ON [Table 1].Product = [Table 2].Product
SET [Table 1].[Pkg Size] = [Table 2].[Pkg Size]
WHERE [Table 1].[Pkg Size] is null

If you can only build queries in query design view.
== Add both tables
== drag from Product to Product to set up the join
== Add the Table 1 Pkg size field to the field list
== Set the criteria under the field to
Is Null
== Select Query: Update from the menu
== In the UPDATE To under this field enter
[Table 2].[Pkg size]
== Select Query: Run from the menu

That should update your table. Obviously replace the table and field
names with the real table and field names.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Thanks for responding John....

[quoted text clipped - 39 lines]

Ie; DoCmd.RunSQL "UPDATE [Table1] Set[pkgsize] ='" [Products].[Column](2)
"'"