insert another field again into same form from another issue but not same issue

[Stephanie53]

If i have another request out there regarding this same issue please disregard it as i cant find it to get to it. and if its there please just mark it solved when really it isnt so we dont get these 2 confused..thanks :-)



Here is the code as it stands now..

Private Sub Product_ID_AfterUpdate()
    'Initialize price and discount for each product change
    If Not IsNull(Me![Product ID]) Then
        Me![Quantity] = 0
        Me.Quantity.Locked = False
        Me!
[List Price] = DLookup(IIf(Forms![Order Details]!TypeID = 1, "
[List Price A] ", "
[List Price B]"), "Products", "ID=" & Me.Product_ID)
        Me![Status ID] = None_OrderItemStatus
        
        
    'Empty Product records mean user wants to delete line item
    Else
        eh.TryToRunCommand acCmdDeleteRecord
    End If
End Sub

This is what it looks like in Design View..


this is what it looks like in Form View..


Now my issue...

When selecting Product it needs to also brings up the Poduct Code too. Now if i am not providing the correct info at the above please let me know what you need. I am also attaching copy of my DB but i can not save it to an earlier version other than Access 2010 for anyone to view it.

Database54.zip

TIA

Stephanie

[rpeare]

If you can convert this to a version of access prior to 2010 I can take a look.

[Stephanie53]

rpeare as much as i would like to unfortuntely due to the macros and other stuff on DB I can not convert it to an earlier Version. That is why i posted that i couldnt and would need someone that has 2010 version to assist me...

but thanks for volunteering :-)

[rpeare]

I'm going to take a stab here after looking at your screen shot. If the product code is listed in your combo box but not showing (for instance the first column would be the PK of the product, the second column the name of the product (the only column showing) and the third column is the product code you can show that third column on your form instead of making it a bound value you can make it an unbound field with a control source of =ProductID.column(2)

So let's say you have these products in your product table

tblProducts
Product_ID  Product_Name  Product_Code
1           Item AAA      XX1
2           Item BBB      XX2

in the query driving your product information you want to include that product code as a column even if you do not show it then you can reference it's location using the .column method. Remember all list boxes/combo boxes start with column 0 so if your product code is in the third column visually that's only column 2 as far as access is concerned

alternately in the ON EXT property of your PRODUCTID field you could have Product_Code = productID.column(2) as well. But then you're storing data you don't need to.

[Stephanie53]

ok i will try that and see if that works, give me about 30 min as i am in middle of something else that requires my attention :-)

[Stephanie53]

ok this isnt working but i have to leave for the day..i will come back here tomorrow to see what i did wrong and we will go from there

TIA

Stephanie

[rzw0wr]

I downloaded your db.
If I understand what you want, you would like to see the Product code displayed in the combo box.
If this correct then on the format tab change the coulmn width form 0;2;1 to 1;1;1

If this not correct let use now.

Dale

[June7]

My suggestion:

Change the RowSource for Product combobox to:
SELECT Inventory.[Product ID], Inventory.[Product Name], Inventory.[Qty Available], [Product Code] FROM Inventory ORDER BY Inventory.[Product Name];
If you want the Product Code to show in the dropdown then set combobox to 4 columns and width 0";1";1";1"

Change Product Code to a textbox instead of listbox.

Set ControlSource to: =[Product ID].[column](3)

I agree with rpeare - no reason to save both ProductID and ProductCode to OrderDetails.

[Stephanie53]

ok i did everything you all said in the last post above..but the problem i am having is 2 things..

the column width are not allowing me to put in 0";1";1";1"

i changed it to text box as per instructed..but how can i get it to just auto fill when selecting Product - i dont want a combo drop box there.

TIA

Stephanie

[rpeare]

Where is your productID field, I don't see it on the form now

You need to have BOTH the productID combo box AND the field you have that has the reference to PRODUCTID.Column(3)

[Stephanie53]

here it is..its alway been there..where do i put PRODUCTID.Column(3) again on ProductID?

[rpeare]

Ok, so in your product ID combo box, is the third column the product code? does your combo box have 3 columns?

[Stephanie53]

it did until i added the other field i just changed it to 4

but still not working..i changed the column width to 0";1";1";1" too..not sure what i am still doing wrong

[Stephanie53]

here let me explain again...i am adding another field tht needs to populate

example

Customer calls in; they order the 4PB6000 - 1/4" BGPB 6000# (Product ID)- when the sales dept selects this then the other fields polulate; across the fields (Please see FORM VIEW )



as you can see under Product Code (which was the field added after form was created) is not populating across..it should say...

4PB6000 (Under Product) 1/4" BGPB 6000# (Product Code) 0 (Qty) $1.74 (List Price) ect ect

But as you can see nothing populates there..HELP :-)

I have a week to finish the LAST 2 issues this is having (the other issue is floating around somewhere...ughhh)

TIA

Stephanie

[rpeare]

do a screen shot of the query that is driving the combo box (the contents, and the design if you can) because it looks like you're concantenating the product and the product code into the same field which would be why you can't get column 3 out of the combo box but I'd need to see the design and contents of the query to determine it.