If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Update field on table, with value from 2nd table from form
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) "'" |
#2
|
|||
|
|||
Update field on table, with value from 2nd table from form
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 wrote: 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) "'" |
#3
|
|||
|
|||
Update field on table, with value from 2nd table from form
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 John Spencer wrote: 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) "'" |
Thread Tools | |
Display Modes | |
|
|