Updating Form Fields From Table

[Argi]

Hello Everyone.

I was wondergin if anyone knows how i can update my fomr fields when selecting a combobox value. For example i have a table that has say these fields

ItemNo
ItemWeight
ItemDescription

It is the primary table for ItemNo and has refernetial integrity checked as well as cascade values.

I have a secomd table with the fileds

ItemNo
NetWeights
GrossWeight
CasePerLayer

I created a from based on table 2. The form has a combp box were i selecet ItemNo from table one and it fills in the value under ItemNo in table 2. I want to display the otherfields in Table one in my form but cannot do this. So for example if a choose a aprticular ItemNo in the fomr i want to see the information for the other fileds in table one as well in the form, i also want them to update if i chose a new ItemNo before adding a record.

Any ideas?

 

[Isskint]

just place text boxes on your form and set the rowsource using DLOOKUP

EG Dlookup("[ItemWeight]","[Table1]","[ItemNo]=" & me.[ItemNo])

 

[JLCantara]

Hello Argi,

You have a very tipical problem; here is a general purpose solution:

1 - Create a form based on table #1; move the 3 fields in then form header.

2 - Create a second form based on table #2, selecting tabular format.

3 - Add the #2 form to #1 as a subform, use ItemNo to connect the subform.

That's It!!!

Good day,
JLCantara.

 

[Isskint]

Aye subforms are also a very good way to achieve this. If you have multiple records in table1 that would match your ItemNo, then a subform is the only way to go. However if there will only ever be 1 item the textboxes may surffice.

Good shout JLC

 

[JLCantara]

Hello Isskint.

If you have multiple records in table1 that would match your ItemNo, then a subform is the only way to go. However if there will only ever be 1 item the textboxes may surffice.

Since ItemNo is the primary key of the table, You can't have multiple records with it.

As I said, its a generic solution: one size fits all... Note that having ItemDescription enables the user to run Find on this field: this can be very usefull when the part number is not known.

Good day,
JLCantara.

 

[Argi]

Thank you for the helpful information Isskint and JL Cantara , i think the DLOOKUP may work better for me as i cannot get used to the look of the subform in the original form and i cant organize the fields our of the subform. I can;t seem to get the DLOOKUP to work , it is probably the syntax, i have incuded actual form and tbale names below

Table1: Item List

Fileds
ITEMNO
DESC
Case Gross Weight (KG)
Pack Size Net Weight (KG)

Table2: Zeea Product Spec DB

Some of the fileds in this table are
ItemNo
Case Gross Weight (KG)
Pack Size Net Weight (KG)

There is currently a form called Zeea Product Spec DB with all the fields in the table Zeea Product Spec DB in it

There is a 1 to 1 relationship on ITEMNO between these 2 tables.
There is currently a combo box in the form that provides a list of ITEMNO that i sellect from and once i select the ITEMNO it puts the vlaue in the ItemNo filed in the table Zeea Product Spec DB. The idea is once i select a particular ITEMNO to poupulate the fields in the table Zeea Product Spec DB or at least to show them so i can easily create a report from my form.

Can you please show me how the DLOOKUP will work in this case with these field, form and table names. I tried a few variations but am too new at Access to figure it our on my own. Thanks for the help.

 

[Isskint]

Dlookup's are not as frightening as they look. The thing to remember (which help does not make clear) is to enclose the parameters in speech marks "" and square brackets [ ] (especially if there are spaces in object name).
So generically DLOOKUP("[A field]","[On a table the field is on]","[Where another field on that table]=" & [another value]) The other value can be a form control, a set value, user provided etc.
The only thing to keep in mind is the data types of the WHERE should be the same (or convert one to match - Val(xyx), Str(123) etc) and text needs single quote marks around the value - "[Where another field on that table]='" & [another value] & "'")) - and dates need hash marks - "[Where another field on that table]=#" & [another value] & "#"))

Anyway enough of the boring teaching stuff:banghead:, its practical time

On the Zeea Product Spec DB form (or any form, report,query that uses Zeea Product Spec DB table as a data source) use these as rowsources in textboxes
Dlookup("[Case Gross Weight (KG)]","[Item List]","[ItemNo]=" & me.[ItemNo])
Dlookup("[Pack Size Net Weight (KG)]","[Item List]","[ItemNo]=" & me.[ItemNo])
Dlookup("[DESC]","[Item List]","[ItemNo]=" & me.[ItemNo])

If you wanted to show these on another object (form, report,query that DOES NOT use Zeea Product Spec DB table as a data source) when Zeea Product Spec DB form is open, then you would need:

Dlookup("[Case Gross Weight (KG)]","[Item List]","[ItemNo]=" & Forms!Zeea Product Spec DB![ItemNo])
Dlookup("[Pack Size Net Weight (KG)]","[Item List]","[ItemNo]=" & Forms!Zeea Product Spec DB![ItemNo])
Dlookup("[DESC]","[Item List]","[ItemNo]=" & Forms!Zeea Product Spec DB![ItemNo])

 

[Argi]

I get a #Name? showing up in the field. This si what i tried
=DLookUp("[Item List]![Case Gross Weight (Kg)]","[Item List]","[Item List]![ITEMNO]=" & [me].[ItemNo])

I also tried this first

=DLookUp("[Case Gross Weight (Kg)]","[Item List]","[ITEMNO]=" & [me].[ItemNo])

and get the same error, any ideas?

 

[Isskint]

The first one will not work because it is not formatted right (this is what you will get if you use query builder). The table name is not needed in the [Case Gross Weight (Kg)] or [ITEMNO]= parts of the function.

#Name often appears when the textbox you are using has the same name as one of the parts of the DLOOKUP. Can you confirm the name of the textbox you are using this code in?

 

[Argi]

I created a new text field and left the default name Text234 or something like that. I used the second versions of DLOOKUP namely

=DLookUp("[Case Gross Weight (Kg)]","[Item List]","[ITEMNO]=" & [me].[ItemNo])

and still got the same error. Any ideas?

 

[Isskint]

I have replicated this in a database and the problem appears to be the name of the textbox on the form holding the ItemNo value Me.ItemNo. Change the name of the textbox (say tbItemNo) and try again. This is one of the reasons more seasoned Access developers argue the case of not using field names as control names.

 

[Argi]

Isskint i have attached the BD, i feel it would take much less of your time looking at it instead of having me ask you several more questions. I counld not find a duplicate text box but there is a combo box and a filed with same data in them, combo just shows were field has written data from combo box query. I have since updated fields to eliminate spaces and replaced with underscore and the links are still not done, i am trying to figure out this post first. Thanks.

 

[Argi]

THis is update DB file.

 

[Argi]

This is update with relationships that i think may be right. Any opiniond is appreciated.

 

[Argi]

What happened to my support team . Did everyone lose interest already? Is there somethign wrong?

 

[Isskint]

Boo

Argi, see message on other thread.

 

[JLCantara]

I did not lost interest: i am just watching the parade...

If you can zip an Access 2007 version, I will show you how subforms could do the job. Right now, Access 2007 can't read your table...

JLCantara.

 

[Argi]

Yes i can but form will not work on import, please see attached. Also not sure fi relationships are done properly.

 

[Argi]

Here is some more, just in case cant open

 

[JLCantara]

Hi Argi,

Attached is a 'pro' (I started with the very first version of Access...) version of part of your your project. Examine very carefully the reorganisation of 'spec' tables and the 2 forms. I did not touched the field name (they gave me a nightmare!!!).

I'm afraid you will have to deal with suforms.

Isskint: give it a look, i would like to have comments from you.

JLCantara.