Dlookup Lookup Value

[WhatnThe]

I have two tables & a data entry form in my access database so far.

Table: DataTracking
Table: LookUpValues
Form: DataEntry

My form looks like this

ID
Part Number
Date
Employee
Pieces
Default Speed
Actual Speed

The data entered in this form is added to the DataTracking table. I would like to have the “Default Speed” be automatically populated when I enter a part number. Is this possible to do this with a dlookup?

[RuralGuy]

Probably. What is the relationship between Default Speed and Part Number? Is the value in the "LookUpValues" table? If so then why not just use a ComboBox to select the PartNumber from that table and a Join in the RecordSource of the form to fill in the DefaultSpeed. Or maybe the AfterUpdate event of the ComboBox.

[WhatnThe]

Basically what I have here is a part tracking data base. The form that I have created is intended to add information to the Table: DataTracking. The look up values have every part that we run, and specific information to each part. The default speed data is located in the Table: LookUpValues. What I want to do is when a user types a part number in the form; I would like to have the default speed be automatically populated within the form.

These type of automatic look up values will be utilized within different forms. It is meant to be a convenience for the users adding data to the part tracking data base. I have been looking for a way to populate certain fields and have been unable to find a solution. I am fairly new to access.

If you would like an example of what I am looking for I can upload a sample of the database I am working on with dummy data.

[RuralGuy]

Since you will need to validate the PartNumber anyway, do you have a problem using a ComboBox so the user can select a valid number?

[WhatnThe]

Nope, I would not mind a combo box for the part number, but I am not sure how to go about doing this.

This is how my form would look then?:

ID
Part Number - Combo Box from LookUpValue Table
Date
Employee
Pieces
Default Speed - To be populated from Combo Box
Actual Speed

[RuralGuy]

Yes. Go ahead and post your sample db and I'll get you started.

[WhatnThe]

Ok, I have a dummy version of what I am working with attached. On the form I have labeled it with some text information. Feel free to add to or remove anything you need to, but please let me know what you did so I can learn the howto's Thanks in advanced for your assistance.

I did try to create the combo box but, I could not get it to auto populate the required speed & I could not get the part number to add to the data entry table from the combo box

[RuralGuy]

Here's how to do it with a join query as the RecordSource of the form.

[RuralGuy]

And here it is just using the AfterUpdate event of the ComboBox.

[WhatnThe]

Thanks for fixing the form, its doing what I wanted to do now but, I am still having the issue where the data recorded in not being added to the data entry table. I mean it is but its adding the LookUpId instead of the part.

Also if it not to much trouble would you explain how you did it?

[RuralGuy]

You probably want to look at the 2nd sample. The first one is all done with the query.

[WhatnThe]

Thanks a million for your second example. I have figured out how you did it! Your the man! The second example was much easier to figure out. Although if you could point me to an area to learn the first example I would be grateful.

[WhatnThe]

One last question about your sample three. If I wanted to have another populated cell using the code would I add the Me.SpecSPM = Me.PartNumber.Column(2)
or would I need to create a new code.

Lets say I want also display the output as well as the default speed. Would the code look like Me.Output = Me.SpecSPM = Me.PartNumber.Column(2) or would I need to create a new code?

[RuralGuy]

I believe the Output is the 4th column of the RowSource so the code would be:
Me.Output = Me.PartNumber.Column(3)

[WhatnThe]

Nevermind I figured it out. I was not placing the code in the correct position. I do thank you for your help. I am going to mark this issue as solved.

I will start new threads as I am sure to have a few more question in the next few days.

Thanks again!!!