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!

odin1177 · Jul 1, 2011

Montez659 said:
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.

you put Me.txtCycleTime.Value

I changed them to this: Me.CycleTime.Value removing the txt part. hopefully it works I am crossing my fingers. Thanks for your help and all of your efforts and time.

odin1177 · Jul 1, 2011

Montez659 said:
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.

So far no go I changed the code to this, it seems to run as i get no errors, but I also get no data.

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

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

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

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

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

Montez659 · Jul 1, 2011

On your form, the textbox where you want you Cycle Time to populate, go to properties. tell me what exactly what is written in the Name property.

odin1177 · Jul 1, 2011

Montez659 said:
On your form, the textbox where you want you Cycle Time to populate, go to properties. tell me what exactly what is written in the Name property.

Cycle Time

Copied and pasted it myself and I had to space the PartNumber to Part Number or got an error.

odin1177 · Jul 1, 2011

Montez659 said:
On your form, the textbox where you want you Cycle Time to populate, go to properties. tell me what exactly what is written in the Name property.

Here is the EXACT code I have:

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

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

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

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

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

Montez659 · Jul 1, 2011

Ok, from what it looks like, a big part of the problem is that your field names have spaces in them. Remove the space so that Cycle Time is either CycleTime or put an underscore in the actual name of the field.

Access really hates spaces, and I never use them even in naming a report or form. I just change the caption.

odin1177 · Jul 2, 2011

Montez659 said:
Ok, from what it looks like, a big part of the problem is that your field names have spaces in them. Remove the space so that Cycle Time is either CycleTime or put an underscore in the actual name of the field.

Access really hates spaces, and I never use them even in naming a report or form. I just change the caption.

I changed all of them, and deleted all of the spaces. The code runs without displaying errors, but does nothing!

Private Sub Customer_LostFocus()
Me.Customer.Value = DLookup("[Customer]", "tblPartNumbers", "[PartNumber]= '" & [Forms]![frmRipForm]![Customer].Value & "'")
End Sub

Private Sub Description_LostFocus()
Me.Description.Value = DLookup("[Description]", "tblPartNumbers", "[PartNumber]= '" & [Forms]![frmRipForm]![Description].Value & "'")
End Sub

Private Sub Tool_Number_LostFocus()
Me.Tool_Number.Value = DLookup("[ToolNumber]", "tblPartNumbers", "[PartNumber]= '" & [Forms]![frmRipForm]![ToolNumber].Value & "'")
End Sub

Private Sub Cavities_LostFocus()
Me.Cavities.Value = DLookup("[Cavities]", "tblPartNumbers", "[PartNumber]= '" & [Forms]![frmRipForm]![Cavities].Value & "'")
End Sub

Private Sub Cycle_Time_LostFocus()
Me.Cycle_Time.Value = DLookup("[CycleTime]", "tblPartNumbers", "[PartNumber]= '" & [Forms]![frmRipForm]![CycleTime].Value & "'")
End Sub

odin1177 · Jul 2, 2011

odin1177 said:
I changed all of them, and deleted all of the spaces. The code runs without displaying errors, but does nothing!

Private Sub Customer_LostFocus()
Me.Customer.Value = DLookup("[Customer]", "tblPartNumbers", "[PartNumber]= '" & [Forms]![frmRipForm]![Customer].Value & "'")
End Sub

Private Sub Description_LostFocus()
Me.Description.Value = DLookup("[Description]", "tblPartNumbers", "[PartNumber]= '" & [Forms]![frmRipForm]![Description].Value & "'")
End Sub

Private Sub Tool_Number_LostFocus()
Me.Tool_Number.Value = DLookup("[ToolNumber]", "tblPartNumbers", "[PartNumber]= '" & [Forms]![frmRipForm]![ToolNumber].Value & "'")
End Sub

Private Sub Cavities_LostFocus()
Me.Cavities.Value = DLookup("[Cavities]", "tblPartNumbers", "[PartNumber]= '" & [Forms]![frmRipForm]![Cavities].Value & "'")
End Sub

Private Sub Cycle_Time_LostFocus()
Me.Cycle_Time.Value = DLookup("[CycleTime]", "tblPartNumbers", "[PartNumber]= '" & [Forms]![frmRipForm]![CycleTime].Value & "'")
End Sub

I also changed the control source on the frmRipForm to link it to the exact copy of itself in tblPartNumbers, and it populates #Name in the fields, but brings up errors in the VBA code that you have helped me with. Can we alter the code to work with the new control source that I have assigned???

Montez659 · Jul 2, 2011

Well, before we do that, let me show you something else that is wrong. Each of those DLookups needs to be in the Lost Focus property of the combobox that is holding the value of the part number. You have them each assigned to their own Lost Focus, and that is not correct because you are never "tabbing" or "focusing" on those fields to be able to lose the focus.

It should be something like

Code:

Private Sub partNumber_LostFocus ()
Me.Customer.Value = DLookup("[Customer]", "tblPartNumbers", "[PartNumber]= '" & [Forms]![frmRipForm]![Customer].Value & "'")
Me.Description.Value = DLookup("[Description]", "tblPartNumbers", "[PartNumber]= '" & [Forms]![frmRipForm]![Description].Value & "'")
Me.Tool_Number.Value = DLookup("[ToolNumber]", "tblPartNumbers", "[PartNumber]= '" & [Forms]![frmRipForm]![ToolNumber].Value & "'")
Me.Cavities.Value = DLookup("[Cavities]", "tblPartNumbers", "[PartNumber]= '" & [Forms]![frmRipForm]![Cavities].Value & "'")
Me.Cycle_Time.Value = DLookup("[CycleTime]", "tblPartNumbers", "[PartNumber]= '" & [Forms]![frmRipForm]![CycleTime].Value & "'")
End Sub

Go back to the original control source and see if this code works. And remember to change Private Sub partNumber_LostFocus() to whatever that combobox is. And make sure that the Lost Focus property on the combobox where the Lost Focus is is set to Event Procedure.

xenou · Jul 2, 2011

Maybe because there are multiple names the same, based on part number,
as this customer has over 500 of the 721 parts, How would I fix this error????
Customer            Part Number   Description
Inteva Products     516300000001  LH Panel   5163-000 34.00 1
Inteva Products     516300000101  LH Panel   5163-000 34.00 1
Inteva Products     516300000301  LH Panel   5163-000 34.00 1
Inteva Products     516300000701  LH Panel   5163-000 34.00 1
Inteva Products     516300100001  LH Housing 5163-001 30.00 1

1) why are customers associated with Part Numbers? Can't more than one customer buy a part? (or if parts are purchased, more than one supplier supply a part?)

2) You're question isn't clear. I don't see "multiple names the same based on part number". Each part number in your example is shown once only, so only one name would go with it.

Database

odin1177

New Member

odin1177

New Member

Excel Facts

odin1177

New Member

Montez659

Well-known Member

odin1177

New Member

odin1177

New Member

Montez659

Well-known Member

odin1177

New Member

odin1177

New Member

Montez659

Well-known Member

xenou

MrExcel MVP

Similar threads

Forum statistics

Share this page

Database

New Member

New Member

Excel Facts

New Member

Well-known Member

New Member

New Member

Well-known Member

New Member

New Member

Well-known Member

MrExcel MVP

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