how to store values into a table using DLOOKUP function in the access form

[Ruler9]

Hello to everyone,

I have a certain problem, for which I read many advice on the web but still cannot apply any of them to solve my problem. Possibly because I am a beginner using access and not very well familiarized with the access terminology.

So, I successfully managed to retrieve data to a form text box in an access form, but these data are not saved in the underlying table so as to continue building my database. How can I do that ?

Please give me simple explanation so as to understand what to do.

Can it be done without using VBA?


Thank you in advance

[June7]

Must use code (macro or VBA) to save calculated value. Usually a bad idea to save calculated value. Calculate when needed.

http://support.microsoft.com/kb/209172

Are you saving as foreign key the primary key that links to record with the value retrieved by DLookup? Why do you need to save both? Maybe what you really need to do is join tables to retrieve related information. Or use a multi-column combobox to show the associated data.

[Ruler9]

It is designed like this:

The 1st field in which the primary key is assigned is an autonumber data defined. So, each time it takes a new number in the form, there is another field in the form which is connected with this 1st field and takes a certain value using Dlookup function. But I need this value also in the table , not only in the form, because I need to use this value to do calculations with other fields of the table.

Hope I gave you all the necessary details to understand. Do you have a solution to my problem?

Thanks in advance

[June7]

Doesn't alter my comments. Either save the autonumber PK as FK and join tables or use multi-column combobox OR don't save the FK and instead set combobox to save the data.

If you must save the FK and the value (bad idea), requires code to save the result of calculation.

[Ruler9]

I appreciate your effort to help. The issue is that I am a beginner and not so well familiarised with the terms. What is the FK ? and how can I save the autonumber PK as a FK? How will I join the tables using multi-column combobox? Which tables to join? There is 1 table which I want to feed with the data (the autonumber which is automatically saved in the table from the form, and the other field which retrieves data from another table). Do you mean these 2 tables?

Also you said, "don't save the FK and instead set combobox to save the data". How can I do that?

Finally you said:
"If you must save the FK and the value (bad idea), requires code to save the result of calculation."

The autonumber is automatically inserted in the table. So it is saved for sure. The other field that takes value with DLOOKUP is the one that I want to save to the table so as to make calculations.

Could you simplify your solutions so as to apply them ?

Thank you again!

[June7]

PK = primary key
FK = foreign key

Without knowing your database structure, can't be specific.

Options for viewing related information

1. form or report RecordSource is a query that joins tables

2. multi-column combo or list box
http://www.datapigtechnologies.com/f...combobox3.html
Then a textbox ControlSource can reference column index of combo or list box. Index begins with 0 so column 2 would be index 1.
=[comboboxname].[Column].(x)

3. DLookup