Enter Data in Table Field to Update Others

[karl009]

Hi,
My problem is that I am trying to get a number of fields to update with data by only entering data into one field.
Database Test Layout
SQ:Table
ID
SQNo PK
Name
VTDate

SQL:Table
ID PK
SQNo
Item
Dec
YN

Items:Table
Item PK
Dec
YN

Relationships
SQ.SQNo àSQL.SQNo 1 to M
SQL.Item àItems.Item 1 to M

So in the SQL:Table when an Item is entered into the field the data from the Dec and YN comes from the Items:Table

How do you achieve this?
Thanks
Karl

 

[SOS]

You use a main form and have a subform with the Master/Child links set to the appropriate field(s).

 

[karl009]

Hi,

Thanks for the quick response.

Could you give some examples of this or some websites?

Thanks
Karl

 

[SOS]

Well, here's an example (from Bob Larson's website):
http://downloads.btabdevelopment.com/Samples/misc/SAMPLE-StoreDiffData.zip

Hopefully that will help.

 

[karl009]

Hi,

Seem to have some of this worked out, I have found an example of what I was looking for and that is using a DlookUp.

But when I try and do this with my database it dose not work.

Can any one provide simple example databases using this?

Many Thanks

 

[SOS]

What is the code you are using for your DLookup which doesn't work?

 

[karl009]

Hi,

This is the code that I am using;

DLookUp("ItemDetail","Sales_Items","ItemNo = " & [ItemNo])

To make it easy I have uploaded an test database that I am playing with on a small scale.

In the Sales_Q form when the Item Number is selected I would like the ItemDetail to be retrieved from the Sales_Items table and be placed in ItemDetail field.
Thanks
Karl

 

[SOS]

You needed quotes.

 

[karl009]

Hi SOS,

Thanks for the response.

I have made the changes, put the DlookUp in the control source for the field and placed the quotes in, I am now getting #Error which is more then I have had.

This is the code that I have used, is that correct, also I have a macro for the COMBO BOX is that needed if placing it in the control source.

=DLookUp("ItemDetail","Sales_Items","ItemNo = "" & [ItemNo] &")

If you could, could you upload the Test database with the changes so I am able to see what’s going on?

Many Thanks
Karl

 

[karl009]

Hi,

Ok after looking closely I have worked out the quote marks and its all working now.

But I have tried to apply a calculation though a query then sub form and it does not update the total but when entering data direct it updates the total.

Is there any way for the data from the Dlookup to be recognised by the calculation or to enter the data in the table?

I have seen this done with a example database from Microsoft using the Dlookup in the Macros and the action set as SetValue.

Thanks
Karl

 

[SOS]

You should not be storing the Item Detail in the second table. You already have Item Number which is what should be stored and then in any queries you can pull the description together. You do not store that data twice in the database (normalization rules).