dLookup field

pancho13

Registered User.
Local time
Today, 22:21
Joined
Jun 2, 2003
Messages
10
Hello Everybody

Just wonder if you wonderful people could help me with this...

I would like to create a field in a form that contains a dlookup statement that looks a particular field on table "A" based, obviously, in another field from that table....no dramas here.

but, The problem that i have is that I want to capture the data displayed in that dlookup field into another table, table "B", once the form in question is closed.

Any ideas????
 
What table is the form based on? Are tables A and B related? If so, why do you need to put a value in table B from table A? If they are not related then why do you need to put the value from table A to table B anyway? Can you give a specific example of what it is you want to do?

The more information you can give the better the chances that someone here will be able to answer your question....

Jack
 
OOOppsss!!

More Info....Here we go, then!!

The form is based in table "B", there is a common field in the 2 tables, field "1", but I need to minimised the typing on the form for the users, so that why I need to do the dlookup field so that gives the user the option of selecting the value on the field "1" and display the dlookup field on the form. and then I need to capture the data from the dlookup field into table "B"....

In other words... How do I trasfer data from table "A" to Table "B" which is displayed using a dlookup field in a entry form, once the field "1" is populated.??

Cheers...........
 
How are the tables related? Is it a One to Many relationship or a One to One (rare)? If it is a one to Many you should not have to copy data from one table to the other as the data already exists in the other table. Same for a One to One relationship. If the tables are One to Many you should have a Form/Subform with the One table being the Record Source for the Main Form and the Many table the Record Source for the Subform.

Is it possible that Table A is a 'Lookup table' of values that you want to use in Table B? If so, make the control on the form a combo box based on the lookup table and bind it to the field where you want to store the Primary key of the lookup table.

If I am still not following you please bear with me and tell me the purpose of Table A, types of data in the table and what type of data you are storing in Table B.

hth,
Jack
 
Again Jack, Thank you for your Knowledgeable input.

Yes, you are right, The table "B" (- called "ACTION"), (which is the form for), is collecting data from the users, and the form is pretty much based in different tables, eg: "People", "Location", "Equipments", etc.


You are right, again, instead a dlookup field, I could make the a combo field from which the data is pick selected from table "A", of course, ..........but I'm trying minimise the entry for the users, in that form.

the idea is this..... if I'm selecting "Equipment 1", which is a combo field from a lookup list from table "Equipment", this "Equipment 1" has assigned a particular "Location X", so.... everytime the user select "Equipment 1" from the pick list in the form, the dlookup field displays "Location X", but I need this info to be entered automaticaly into a "Location" field, for the "Action" table. eventually, Outbound this data into the Location field in table "Action".....Make sense????

Ta.........Cheers
 
I think I understand what you want....hopefully!

In the combo box you will have the Equipment name (or whatever) and the Location. When the user selects a piece of equipment code like this in the After Update event of the combo box will fill in the Location control on the form:

Me.Location = Me.ComboBoxName.Column(x)

In the code above x is the actual physical column number minus 1. So if the the Location is in the 3 column of the combo box then replace x with 2 (combo boxes are zero based).

Truth be told you should NOT store the location in Table B because the data already exists in the Equipment table. If your tables are properly related then the ONLY thing you want to store in the Equipment field in Table B is the Primary Key for the piece of equipment in the Equipment table. You should not even have a Location field in Table B. The exception might be if you move the Equipment from place to place, but then why would you have the Location stored in the Equipment table?

Since I cannot see your database I will just say to be sure that your tables are properly normalized or as your database grows you will find it harder and harder to query or otherwise work with your data.

hth,
Jack
 
Thanks Jack

The Problem with not capturing the "Location" data in table "B" is that I have a report on based on that Location fields, so I could have a look all the "equipments" for that "location" with problems, in order to quantify and do some analysis/follow ups.
Keeping in mind that 1 location could contains many equipments

Appreciate your help, mate!
 
Hmmm. If one Location can have many equiment then the Equipment table should be related to the Location table in a One (location) to Many (equipment) relationship.

I don't want to beat a dead horse because I really don't know what you have and you do so I will just say continued success with your project!

Jack
 
Rather than using DLookup()s to "lookup" data, it is more efficient to use a query that joins the two tables. So, if for example you want to show CustomerName on your Order form and Invoice, base them on a query that joins the Order table to the Customer table. That way columns from both tables will be available in the recordsource and you can simply choose them from the field list.
 

Users who are viewing this thread

Back
Top Bottom