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!

xenou · Jul 3, 2011

By a test database I mean just a table with some dummy data and a form. You could run the test on the table already in your database - you just want to remove some of the variables and get it down to a simplified case (just two textboxes and one table involved). This helps when learning new things - like how to use a DLookup Formula in a form. Anyway, you don't have to create one - I created one for you in my last post.

ξ

odin1177 · Jul 5, 2011

odin1177 said:
I do not know how to do that, make any testing databases, this is my first one, and outside of the auto population, I did pretty well, but this puts me in over my head.

That form is linked to the table, where mine is not, only pulling data from the table to like teaching the database that this part number has a cycle time of, and tool number. I had a form with them linked in the table, and the data would over write the record, and not save a new one, thus making reporting impossible.

odin1177 · Jul 5, 2011

odin1177 said:
That form is linked to the table, where mine is not, only pulling data from the table to like teaching the database that this part number has a cycle time of, and tool number. I had a form with them linked in the table, and the data would over write the record, and not save a new one, thus making reporting impossible.

How about using the control source of the 5 fields, then making some code to play off of that? Would this work?

odin1177 · Jul 5, 2011

How about some help with these 5 pieces of DLookup code.

 Customer = DLookup (“Customer” , “PartNumbersT” , “PartNumber=” & PartNumber)
Description = DLookup (“Description” , “PartNumbersT”, “PartNumber=” & PartNumber)
Cavities = DLookup (“Cavities” , “PartNumbersT”, “PartNumber=” & PartNumber)
CycleTime = DLookup (“CycleTime” , “PartNumbersT”, “PartNumber=” & PartNumber)
ToolNumber = DLookup (“ToolNumber” , “PartNumbersT”, “PartNumber=” & PartNumber)

Trying to get 5 fields to auto populate from a table that stores data based on the part number field, and what is entered, when I try to run this code I get
Run time error '3075':

Syntax error (missing operator) in query expression 'PartNumber='

xenou · Jul 5, 2011

That form is linked to the table, where mine is not, only pulling data from the table to like teaching the database that this part number has a cycle time of, and tool number. I had a form with them linked in the table, and the data would over write the record, and not save a new one, thus making reporting impossible.

I don't know what you mean here. My sample database had only one form which was not linked to a table. It did pull data from a table with two example of a DLookup formula - this appears to be exactly what you want to do so I suggest you study the example until you understand how it works.

As far as continuing to help you, please post the following information:

The names of your tables.
The names of the relevant fields in your table(s) and their datatypes.
The names of the controls on your form.
The actual code you have tried to use so far.
Ideally, a small amount of sample data - such as a part number you are looking up.

Again, I urge you to focus on solving one DLookup. Once one works you should have no problem with the rest - it is simpler if we deal with one formula rather than five all at the same time.

odin1177 · Jul 5, 2011

xenou said:
I don't know what you mean here. My sample database had only one form which was not linked to a table. It did pull data from a table with two example of a DLookup formula - this appears to be exactly what you want to do so I suggest you study the example until you understand how it works.

As far as continuing to help you, please post the following information:

The names of your tables.

The names of the relevant fields in your table(s) and their datatypes.

The names of the controls on your form.

The actual code you have tried to use so far.

Ideally, a small amount of sample data - such as a part number you are looking up.

Again, I urge you to focus on solving one DLookup. Once one works you should have no problem with the rest - it is simpler if we deal with one formula rather than five all at the same time.

tblRipform, which is tied to the frmRipForm to save the data and report thie data saved here. The table that I want the form to pull based on the part number entered into the like fields in the form, only the form has a lot more fields to collect more data.
 Customer Part Numbers Description Tool Number Cycle Times Cavities
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
Inteva Products 516300400001 LH Cover 5163-004 21.00 2
Inteva Products 516300400101 LH Cover 5163-004 21.00 2
Inteva Products 516300400301 LH Cover 5163-004 21.00 2
Inteva Products 516300400701 LH Cover 5163-004 21.00 2
Inteva Products 516400400001 RH Cover 5164-004 21.00 2
Inteva Products 516400400101 RH Cover 5164-004 21.00 2

All data types are text, part numbers, there are some that have letters in them, the rest was just to make it easy. Field names that are alike in both the table and the form is:

PartNumber
CycleTime
ToolNumber
Description
Customer
Cavities

tblPartNumbers only has those fields, where the form has many others, as the hourly manufacturing by the part number and the date also i have formulas to calculate stuff based on some the information that is auto filled(cavities, and cycle time (cycle time is the number of parts that are made each second ie.. cycle time of 15 is a part is made every 15 seconds multiplied by the amount of cavities.))

 Customer = DLookup (“Customer” , “PartNumbersT” , “PartNumber=” & PartNumber)
Description = DLookup (“Description” , “PartNumbersT”, “PartNumber=” & PartNumber)
Cavities = DLookup (“Cavities” , “PartNumbersT”, “PartNumber=” & PartNumber)
CycleTime = DLookup (“CycleTime” , “PartNumbersT”, “PartNumber=” & PartNumber)
ToolNumber = DLookup (“ToolNumber” , “PartNumbersT”, “PartNumber=” & PartNumber)

I developed this code from watching a youtube video on making a DLookup

Montez659 · Jul 5, 2011

Odin,

I agree with Xen as far as working with his database. No offense, but I feel like we keep going around in circles. If you work with his sample db, a step through the code, you should get a better handle on how everything works.

And hang in there - I don't want you to be discouraged! The guys on this board have been working with Access years beyond what I have done. And it takes a while to learn stuff. I was there too when I first started working with VBA in Excel.

So just play around with the sample and study how it works.

odin1177 · Jul 5, 2011

I am trying to interpret your code, and am having one lousy time. I think I am just retarted. I get part of it, but fail to see the big picture. I look at it, try to see where I can change names, but I have 5 fields to choose from, and looking at your code I cannot see where I make the changes, and what do I change what to???????

xenou · Jul 5, 2011

There's two problems with this formula (and the others like it):
Customer = DLookup (“Customer” , “PartNumbersT” , “PartNumber=” & PartNumber)

1)
you said your table is named tblRipform. so the second argument should be "tblRipform", not "PartNumbersT")

2)
if the part number is text it must be enclosed in single quotes:
PartNumber = '12345678'
Which in the formula looks like:
"PartNumber = '" & [MyControl] & "'"

Your quotes also look funny and may be bad too - not sure what's up with that.

Here's a new sample database. I have spaces in the field names so they may not match yours. My "part number" control is also called Text0, which is not what your's is called. Make the necessary changes:
<a href="http://northernocean.net/etc/mrexcel/20110705_db.zip">Sample Database</a>
sha256sum (zip file): 299ecd56b51a8356a2a5d8637a7d67170b8c71be4e2d82db76e7a38678c723bc

No code was required to fill the text boxes - the lookup formulas can go right in them on the form.

odin1177 · Jul 5, 2011

Private Sub Combo627_AfterUpdate()
Me.Customer.Value = DLookup("[Customer]", "[PartNumbers]", "[PartNumber] = " & Me.Combo627.Value)
End Sub

When I try to run this one I get error:

Query must have at least one destination field.

I was am reaching here, and even created a query with relationships between tblRipForm and tblPartNumbers with arrows going from tblPartNumbers to the matching fields in tblRipForm.

I do not know what else to do. could you look at my Database, and tell me where I went wrong????? I am getting really desperate, and you have helped me understand some kind of code, but I still fail to grasp the concepts as a whole

Database

odin1177

New Member

xenou

MrExcel MVP

Excel Facts

odin1177

New Member

odin1177

New Member

odin1177

New Member

xenou

MrExcel MVP

odin1177

New Member

Montez659

Well-known Member

odin1177

New Member

xenou

MrExcel MVP

odin1177

New Member

Similar threads

Forum statistics

Share this page

Database

New Member

MrExcel MVP

Excel Facts

New Member

New Member

New Member

MrExcel MVP

New Member

Well-known Member

New Member

MrExcel MVP

New 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