Database

odin1177 · Jun 30, 2011

I am almost done with this database, but I need help with a macro that will help me auto-populate 5 fields after a certain field is populated. I have I have part number data in a separate table tblPartNumbers and I have a form frmRipForm. There are 6 fields alike between these two unbound objects, as the rip form is bound to tblRipForm and the Part Numbers table is bound to nothing, and needs to stay that way. I want to push data based on a record when the the Part number field is populated, I know it will be an after update macro, but I am lost after that. The fields are description, tool number, cavities, cycle time, and customer. I have this data all stored on tblPartNumbers, and want to push these to the form, which has 6 identical fields, plus many more for data entry. Any help would be appreciated, as I am new to Access, and this is my first database.

Thank you for your time, and have a great day!

Montez659 · Jul 1, 2011

odin1177 said:
When a Part Number is inputted I want the form to recognize based on the row of data that contains the corresponding data, like in my examples above, and auto populate those other fields in the form.

And it is pulling Part Number from tblPartNumbers, correct? Is Part Number a unique value or are there multiple records in tblPartNumbers with the same Part Number?

odin1177 said:
None of the code modules work, they all give the same error, I don't know what to do to make them work.

Yes, but I need to see the code

odin1177 · Jul 1, 2011

Here is what I can figure to be the code to use

Me.txtCustomer.Value = DLookup("[Customer]", "tblPartNumbers", "[Customer]= '" & [Forms]![frmRipForm]![Customer].Value & "'")

Me.txtDescription.Value = DLookup("[Description]", "tblPartNumbers", "[Description]= '" & [Forms]![frmRipForm]![description].Value & "'")

Me.txtToolNumber.Value = DLookup("[Tool Number]", "tblPartNumbers", "[Tool Number]= '" & [Forms]![frmRipForm]![Tool Number].Value & "'")

Me.txtCycleTime.Value = DLookup("[Cycle Time]", "tblPartNumbers", "[Cycle Time]= '" & [Forms]![frmRipForm]![Cycle Time].Value & "'")

Me.txtCavities.Value = DLookup("[Cavities]", "tblPartNumbers", "[Cavities]= '" & [Forms]![frmRipForm]![Cavities].Value & "'")

odin1177 · Jul 1, 2011

Montez659 said:
And it is pulling Part Number from tblPartNumbers, correct? Is Part Number a unique value or are there multiple records in tblPartNumbers with the same Part Number?

Yes, but I need to see the code

It pulls part numbers because I put in a combo box linked to the table, but when a part number is finally entered I want the other 5 fields to populate based on the corresponding row!

Thanks so much for your time and continued patience with this rookie!

Montez659 · Jul 1, 2011

Ok, here is what I can see that is not right (oh, and btw, use code tags. Makes it easier to read. [ code] and [/ code] without the spaces).

Code:

Me.txtCustomer.Value = DLookup("[Customer]", "tblPartNumbers", "[Customer]= '" & [Forms]![frmRipForm]![Customer].Value & "'")


Me.txtDescription.Value = DLookup("[Description]", "tblPartNumbers", "[Description]= '" & [Forms]![frmRipForm]![description].Value & "'")


Me.txtToolNumber.Value = DLookup("[Tool Number]", "tblPartNumbers", "[Tool Number]= '" & [Forms]![frmRipForm]![Tool Number].Value & "'")


Me.txtCycleTime.Value = DLookup("[Cycle Time]", "tblPartNumbers", "[Cycle Time]= '" & [Forms]![frmRipForm]![Cycle Time].Value & "'")


Me.txtCavities.Value = DLookup("[Cavities]", "tblPartNumbers", "[Cavities]= '" & [Forms]![frmRipForm]![Cavities].Value & "'")

For each of these Dlookup's you want to reference the same combobox where the part number is located. The expression looks like this: DLookup("FieldName" , "TableName" , "Criteria= 'string'")

You have the field name and table name correct (as far as I can see) but your criteria is incorrect. You want to reference the part number in your criteria for each lookup, because it is essentially looking at the same record, different field.

So

Code:

Me.txtCycleTime.Value = DLookup("[Cycle Time]", "tblPartNumbers", "[partNumber]= '" & [Forms]![frmRipForm]![cmbPartNumber].Value & "'")
Me.txtCavities.Value = DLookup("[Cavities]", "tblPartNumbers", "[partNumber]= '" & [Forms]![frmRipForm]![cmbPartNumber].Value & "'")

is what you need.
However, not sure that this corresponds to the error that you were receiving so one more question. In your table, design view, is Part Number a number or Text field?

odin1177 · Jul 1, 2011

Montez659 said:
Ok, here is what I can see that is not right (oh, and btw, use code tags. Makes it easier to read. [ code] and [/ code] without the spaces).

Code:

Me.txtCustomer.Value = DLookup("[Customer]", "tblPartNumbers", "[Customer]= '" & [Forms]![frmRipForm]![Customer].Value & "'") Me.txtDescription.Value = DLookup("[Description]", "tblPartNumbers", "[Description]= '" & [Forms]![frmRipForm]![description].Value & "'") Me.txtToolNumber.Value = DLookup("[Tool Number]", "tblPartNumbers", "[Tool Number]= '" & [Forms]![frmRipForm]![Tool Number].Value & "'") Me.txtCycleTime.Value = DLookup("[Cycle Time]", "tblPartNumbers", "[Cycle Time]= '" & [Forms]![frmRipForm]![Cycle Time].Value & "'") Me.txtCavities.Value = DLookup("[Cavities]", "tblPartNumbers", "[Cavities]= '" & [Forms]![frmRipForm]![Cavities].Value & "'")

For each of these Dlookup's you want to reference the same combobox where the part number is located. The expression looks like this: DLookup("FieldName" , "TableName" , "Criteria= 'string'")

You have the field name and table name correct (as far as I can see) but your criteria is incorrect. You want to reference the part number in your criteria for each lookup, because it is essentially looking at the same record, different field.

So

Code:

Me.txtCycleTime.Value = DLookup("[Cycle Time]", "tblPartNumbers", "[partNumber]= '" & [Forms]![frmRipForm]![cmbPartNumber].Value & "'") Me.txtCavities.Value = DLookup("[Cavities]", "tblPartNumbers", "[partNumber]= '" & [Forms]![frmRipForm]![cmbPartNumber].Value & "'")

is what you need.
However, not sure that this corresponds to the error that you were receiving so one more question. In your table, design view, is Part Number a number or Text field?

I have them all as text because some might have letters in the part number, cavities and cycle time were number but I changed them to text to run this code.

Montez659 · Jul 1, 2011

odin1177 said:
I have them all as text because some might have letters in the part number, cavities and cycle time were number but I changed them to text to run this code.

You actually wouldn't need to change them from number to text, just change the quotes in the code. Btw, I only copied two fo the 5 Dlookups, but what I showed applies to all of them.

odin1177 · Jul 1, 2011

Montez659 said:
You actually wouldn't need to change them from number to text, just change the quotes in the code. Btw, I only copied two fo the 5 Dlookups, but what I showed applies to all of them.

Thank you so much for your time, and tech support. I am used to being the one people ask for help on computer and software issues.

Montez659 · Jul 1, 2011

Hey no problem. Let me know how it goes just to make sure that I didn't mess something up that I was telling you (it happens too much!).

If I don't hear from you, have a great 4th!

odin1177 · Jul 1, 2011

Montez659 said:
Hey no problem. Let me know how it goes just to make sure that I didn't mess something up that I was telling you (it happens too much!).

If I don't hear from you, have a great 4th!

You also have a great 4th

Still doesn't work. I am using Lost Focus like you advised me. Here is the code I just changed the fields do I need to enter an UnderScore example Part_Number Cycle_Time

 Me.txtCycleTime.Value = DLookup("[Cycle Time]", "tblPartNumbers", "[partNumber]= '" & [Forms]![frmRipForm]![cmbPartNumber].Value & "'")

Me.txtCavities.Value = DLookup("[Cavities]", "tblPartNumbers", "[partNumber]= '" & [Forms]![frmRipForm]![cmbPartNumber].Value & "'")

Me.txtCustomer.Value = DLookup("[Customer]", "tblPartNumbers", "[partNumber]= '" & [Forms]![frmRipForm]![cmbPartNumber].Value & "'")

Me.txtToolNumber.Value = DLookup("[Tool Number]", "tblPartNumbers", "[partNumber]= '" & [Forms]![frmRipForm]![cmbPartNumber].Value & "'")

Me.txtDescription.Value = DLookup("[Description]", "tblPartNumbers", "[partNumber]= '" & [Forms]![frmRipForm]![cmbPartNumber].Value & "'")

Montez659 · Jul 1, 2011

odin1177 said:
Here is the code I just changed the fields do I need to enter an UnderScore example Part_Number Cycle_Time

I am not sure what you mean by this. The part where it says Me.txtCycleTime.Value needs to be the name of the textbox control that you have that you want to populate. So as long as it is named txtCycleTime, there should be no problem there. Also, there should be no spaces in field names on the form. If there are spaces in names of fields in the table, then they need to be surrounded with brackets.

Try looking at this. I am about to leave work, so it will be a while before I can respond back.

Database

odin1177

New Member

Montez659

Well-known Member

Excel Facts

odin1177

New Member

odin1177

New Member

Montez659

Well-known Member

odin1177

New Member

Montez659

Well-known Member

odin1177

New Member

Montez659

Well-known Member

odin1177

New Member

Montez659

Well-known Member

Similar threads

Forum statistics

Share this page

Database

New Member

Well-known Member

Excel Facts

New Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock