Help with DLookup function in form

[oZone]

I'm not familiar with the DLookup function, but am trying to accomplish a task that I know should use this function and need some help with the code.

My scenario:
I have a "Add or Change Customer Orders" form that displays the OrderNumber, OrderDate, CustomerNumber, and CustomerName. This form has a subform that displays details about the order such as PartNumber, QuantityOrdered, SalePrice, and PartDescription.

The form gets its values from an Orders table that has OrderNumber, OrderDate, and CustomerNumber fields. The subform gets its values from an OrderLine table that has OrderNumber, PartNumber, NumberOrdered, and QuotedPrice fields.

Listing the part description in the subform is what I am having trouble with. I need the part description to be displayed to the right of each part number in the subform, but the part description is in a seperate table called Parts.

I know a DLookup is the superior way to do it, but I'm clueless how to implement it. A collegue mentioned I could "add a Text box and use the DLookup function" but I have no idea how to do that.

I tried looking online for some examples but just dont know how to adapt them for my situation. One example I found was this:
=DLookup ("[ProductName]","Products", "[ProductID] =" & Forms![Order Details]!ProductID)

But I dont know how to adapt that to make my form work rite. I've tried several variants with no success...

I've also attached an example screenshot of what I'm trying to achieve. The "Customer Name" field in the form, and the "Description" field in the subform are what I am trying to get working (didnt mention the Customer Name field in the form because I think I can get that working).

Any help would be greatly appreciated.

 

[boblarson]

Check out this sample where you include the description in a query that the combo is based off of.
http://downloads.btabdevelopment.com/Samples/combos/Sample-FillInFields.zip

Then you don't need a DLookup (which by the way is NOT the best way to do this).

 

[oZone]

Thanks for the quick reply. I should've said that my "source" told me that a DLookup would be the best way to handle this situation. Apparently he may have been mistaken?

Either way, I'm new to MS Access and Forms and am trying to learn, so thanks for the file, I'm looking into it now and trying to decipher it and figure out how to adapt it to what I need.

If anyone could show me some more example code and how to use it, that'd be great.

 

[oZone]

Well I've tried to dissect the file you gave me to better understand what I'm doing but have only managed to accomplish a higher level of frustration lol.

Here's what I have so far (and it isnt working...):

I made a combo box (Combo29) in the subform that allows the user to select a part number to add it to the order. When a part is selected, the part description is sopposed to show to the right. In the properties for that combo box I put this in the Row Source field as a query:

SELECT tblOrderLine.PartNum, tblPart.Description FROM tblPart INNER JOIN tblOrderLine ON tblPart.PartNum = tblOrderLine.PartNum;

I then made a text box (txtDescription) to show the description (from the parts table) of whatever product is selected by the Part Number combo box. In this text box's properties I put the following in the After Update Event Proceedure field:

Private Sub Combo29_AfterUpdate()
    Me.txtDescription = Me.Combo29.Column(2)
End Sub

When I run the script, something Magical happens! NOTHING! lol.

I really am flying blind here, as I said I'm new to Access and just starting to learn how to create Forms now. If anyone could point me in the right direction it'd be most appreciated.

 

[boblarson]

How about uploading the database?
Here's how:
http://www.access-programmers.co.uk/forums/showthread.php?t=140587

 

[pbaldy]

You want column 1 rather than 2, as that property is zero based.

 

[boblarson]

You want column 1 rather than 2, as that property is zero based.

And make sure that the combo box's Number of Columns property is set to the correct number of columns (if the underlying query has two fields then make sure the number of columns property is set to 2).

 

[oZone]

Okay, didnt know I could do that, here you go.

My form is still messed up. It's not even close to final version. I wanted to tackle this unknown before I moved on to formatting so please excuse its awful state.

Also, one thing I couldnt figure out was how to make the sub form combo box select a different part number for each field. As it is now, if I have 3 fields in the subform and I select one part number from one combo box, that part number populates to all three fields? How do I make the subform select a seperate part for each field? I need to be able to add different part numbers to the subform fields so I can add say three different parts to one order.

Thanks for the help.

 

[oZone]

And make sure that the combo box's Number of Columns property is set to the correct number of columns.

That, and what pbaldy said "You want column 1 rather than 2, as that property is zero based."

Those worked! My subform is now showing the part descriptions! Thanks!

But still having a problem with it...

What I described in my last post, about the part number combo box. If a customer orders 3 items, those 3 different items will be (or should be) listed in the sub form, with their descriptions to the right. As of right now, if there are 3 order items, the part numbers are not showing up, only a combo box that lets me pick a part number. Then, when I pick it, all 3 fields show the same part number (not what I want). Each field should allow me to display the existing part number, or allow me to change that part number seperately.


I suspect it has something to do with the Control Source of the combo box???

If you look at my screenshot attachment in my earlier post, you'll see in that example the subform had different part numbers, all with a drop down box so you can select different ones if you wanted.

I've attached two screenshots here. One is of when I first double click the form, before I've touched anything. It should show the part numbers and descriptions for whatever order I choose (combo box in main form at left) but doesnt.

After I select a part number (second screenshot) it shows the description correclty now, but all subform fields are populated with this same part number. How do I get it so the part I select is only populated to that one field?

 

[pbaldy]

I haven't looked at the sample db, but it sounds like the part number control in the subform is not bound to the underlying table. It should be.