Dlookup

[jcaptchaos2]

Hello,

ItemNumber = DLookup("[itemnumber]", "[shoporderstatus]", "[shopordernumber]= " & [Forms]![shop_floor_data_entry]![shopordernumber])

I need help with the Dlookup function, here is my formula, I can't seem to get it to work but in the past have been able to get other Dlookup formula's to work with lots of messing around. I am hoping someone can tell me what I am missing. I am going to break this down as I understand it and then maybe someone could explain. "ItemNumber=" this is the text box that will be filled in with the reusult of this formula. "Dlookup("[itemnumber]","[Shoporderstatus]" this is the field name and the name of the table that I am pulling the data from. "[shopordernumber]= " & [Forms]![shop_floor_data_entry]![shopordernumber]) is (shopordernumber) field name in the [shoporderstatus] table and the & [Forms]![shop_floor_data_entry]![shopordernumber]) part is [shopordernumber] is the name of my combo box on form [shop_floor_data_entry] Does that make any sense at all? I have this in the after update event in the shopordernumber combo box.

 

[Swift_74d]

If [Forms]![shop_floor_data_entry]![shopordernumber] is a number the following should work...

ItemNumber = DLookup("[itemnumber]", "[shoporderstatus]", "[shoporderstatus]![shopordernumber]= " & [Forms]![shop_floor_data_entry]![shopordernumber])

If [Forms]![shop_floor_data_entry]![shopordernumber] is text the following should work...

ItemNumber = DLookup("[itemnumber]", "[shoporderstatus]", "[shoporderstatus]![shopordernumber]= '" & [Forms]![shop_floor_data_entry]![shopordernumber] & "'")

 

[jcaptchaos2]

Thanks for responding, I have this and is not working,

Private Sub Shop_Order_Number_AfterUpdate()
Item_Number = DLookup("[itemnumber]", "[shoporderstatus]", "[shoporderstatus]![shopordernumber]= " & [Forms]![shop_floor_data_entry]![ShopOrderNumber])
End Sub

The "Item_Number =" in the beginning - is "Item_Number" the name of my text box in the current form? so the Dlookup formula following fills in that text box with the answer?

 

[jcaptchaos2]

One more change but it still don't work,

Private Sub Shop_Order_Number_AfterUpdate()
Item_Number = DLookup("[itemnumber]", "[shoporderstatus]", "[shoporderstatus]![shopordernumber]= " & [Forms]![shop_floor_data_entry]![Shop_Order_Number])
End Sub

 

[jcaptchaos2]

I am getting this error, " Microsoft can not find referenced form 'shop_floor_data_entry'

 

[jcaptchaos2]

Ok this worked:I guess I didn't need the underscores. Thanks for your help.

Private Sub Shop_Order_Number_AfterUpdate()
Item_Number = DLookup("[itemnumber]", "[shoporderstatus]", "[shoporderstatus]![shopordernumber]= " & [Forms]![Shop Floor Data Entry]![Shop Order Number])
End Sub

 

[Montez659]

Spaces can be confusing at times. That is why it is best to avoid them whenever possible.

Generally, if the are contained within brackets they will display exactly like they are in Access, with spaces. If they are in code that doesn't use brackets, underscores will need to be in there. However, even with the underscores I have seen the code error out, so like I said, I usually avoid spaces altogether and just change the captions on my forms to something meaningful.

 

[boblarson]

I usually avoid spaces altogether

Amen to that - I use CamelCase for my naming. It is easily read and causes no ill effects and I rarely have to use square brackets for things so it simplifies my life immensely.

 

[jcaptchaos2]

I learned that unfortunately after this database was to far along to change back, but moving forward in anything I create I can guarantee there will be no spaces.

Thanks again to all you on this board for your help.