Populate table after DLookup is performed

[OMGsh Y did I say Yes]

Happy Friday Everyone!

Wondering if someone can point me in the right direction. This question involves two tables and one form. One table contains static data (such as part number, description, cost, etc.). The other table is a more dynamic table that contains history about activities. The form builds the dynamic table from data in the static table.

I use a dlookup to populate the form, now I want to know how to put that data in my dynamic table.

Any help is greatly appreciated.

[RuralGuy]

If the two tables have a Relationship then there is no reason to put anything but a ForeignKey in the table. A query that joins the two would allow you to see and treat all of the fields of both tables as if they were in the same table.

[OMGsh Y did I say Yes]

It seems that works great when there is a one-to-one relationship, however in two of my fields there are multiple choices in the static table to choose from and the database errors because it doesn't know which one to pick. In the form I have it narrowed down to a single choice. Is there no way in the Control Source to say "do this DLookup and then put the data over in this other table"?

[RuralGuy]

As I stated earlier, there is no reason to use a DLookup() if the tables are related properly. You are not using Lookup Fields are you? http://www.mvps.org/access/lookupfields.htm

[OMGsh Y did I say Yes]

Starting to feel a little woozy here...thus the username.

A tutor taught me to create relationships to tables using the Lookup feature in Access. Is this the same Lookup you are talking about?

[RuralGuy]

Lookup Fields are in tables and not a good way to go. Most developers avoid them like the plague.

[OMGsh Y did I say Yes]

I understand that my tables may not be setup correctly, they are foundational. I have tried to avoid the DLookups without any success. Is there any way you can assist me where I am?

[RuralGuy]

It is all achievable but I need to know if you are using any Lookup Fields. If so then we need to eliminate them first. http://www.btabdevelopment.com/ts/removelookups

[OMGsh Y did I say Yes]

Hi RuralGuy,

Sorry for the delay in responding, this Southerner didn't take too kindly to Mother Nature the past few weeks. I am finally getting caught up and I have removed all of the lookups in my database. There were quite a few. Once removed I tested the database's functionality and all seems to be well.

Just a quick recap....

I am using a dlookup to populate a form and I want the item that was retrieved to go into another table (history gathering kinda thing).

Looking forward to hearing from you.

[RuralGuy]

I am using a dlookup to populate a form and I want the item that was retrieved to go into another table (history gathering kinda thing).

Looking forward to hearing from you.

I'm not sure how you are using this but it can be done with a DLookup() if that's what you need. Where did you put this DLookup()?

[OMGsh Y did I say Yes]

I have a table of reference data. Using a form I pull existing data using the dlookup and it shows up on the form. Beautiful. What I would like to do is take the data that I received from the dlookup and place it into a table (different table) that I use for tracking. Why? Because I am pulling up multiple combinations of data and would like to create a history per se.

[RuralGuy]

So you are fetching values from a table (not the RecordSource of the form) and you want to store those values in another table (not the RecordSource of the form)? It that is true then you will need to use a query to store the values.

[OMGsh Y did I say Yes]

In my attempts to create the form based on a query, I am afraid I have not been successful. This form seems too customized to fit into that category. I have heading data that is derived from three different tables (chosen by user) and the subform details are also derived from three different other tables with filtering for the items that have many variables available.

Here is the basic process of the form...

Header:
Creates a document id (automatic new record)
Database inputs date
User chooses Company (drop down)
Database filters Contacts for that Company
User chooses Contact (drop down)
User chooses Sales Rep (drop down)

Subform - Details
User chooses Part
Database inputs description (dlookup)
Database filters services for part chosen
User chooses service (dlookup)
Database inputs time (dlookup)
Database inputs cost (Dlookup)
User types in quantity
Database extends cost (calculation)
Database compares cost to new (calculation/dlookup combo)

[RuralGuy]

You are making this way more complicated than it need be. Can you make a query that returns the values you need from the tables involved in the DLookup's?

[OMGsh Y did I say Yes]

The query that I make returns too many values because of the many-to-one relationship.