Dlookup for finf stock Position (1 Viewer)

[sbaud2003]

I have a field Stock_id(auto-number: Foramt P00001, P00002 etc)
When I use Doolkup function to get the stock value using tjhe code
=DLookup("current_stock", "tblStock", "[Stock_Id" = [Forms]![SK Enterprice]![TblInvoiceDetail Subform].[Form]![ITEMID])
it returns nothing

How to resolve.

 

[arnelgp]

stock id is only Formatted for Display, the actual value is untouched:
if the textbox is being Padded to the left with 0, you can delete the padding:


=DLookup("current_stock", "tblStock", "[Stock_Id]" = CLng("0" & [Forms]![SK Enterprice]![TblInvoiceDetail Subform].[Form]![ITEMID]))

 

[sbaud2003]

stock id is only Formatted for Display, the actual value is untouched:
if the textbox is being Padded to the left with 0, you can delete the padding:


=DLookup("current_stock", "tblStock", "[Stock_Id]" = CLng("0" & [Forms]![SK Enterprice]![TblInvoiceDetail Subform].[Form]![ITEMID]))

thanks!
Can I put this in the After update event in the Quantity filed, I tried it is giving an error message type mismatch

 

[Isaac]

Just curious, does this work?

=DLookup("current_stock", "tblStock", "[Stock_Id] = " & clng(replace([Forms]![SK Enterprice]![TblInvoiceDetail Subform].[Form]![ITEMID],"P","")))

 

[Pat Hartman]

Since autonumbers are numeric, they cannot contain a letter. You would have been better off to create a custom unique identifier and left the autonumber alone.

The following might work

=DLookup("current_stock", "tblStock", "[Stock_Id" = "P" & Format([Forms]![SK Enterprice]![TblInvoiceDetail Subform].[Form]![ITEMID], "00000"))

Make sure I guessed the number of zeros correctly.

 

[sbaud2003]

Since autonumbers are numeric, they cannot contain a letter. You would have been better off to create a custom unique identifier and left the autonumber alone.

The following might work

=DLookup("current_stock", "tblStock", "[Stock_Id" = "P" & Format([Forms]![SK Enterprice]![TblInvoiceDetail Subform].[Form]![ITEMID], "00000"))

Make sure I guessed the number of zeros correctly.

Thanks ... but it does not display the stock position
I put the code in the after update event in thetext box ITEMID to get the stock position before entering the Quanity ...

 

[sbaud2003]

Just curious, does this work?

=DLookup("current_stock", "tblStock", "[Stock_Id] = " & clng(replace([Forms]![SK Enterprice]![TblInvoiceDetail Subform].[Form]![ITEMID],"P","")))

No
I put the code in the after update event in thetext box ITEMID to get the stock position before entering the Quanity ...

 

[sbaud2003]

Thanks ... but it does not display the stock position
I put the code in the after update event in thetext box ITEMID to get the stock position before entering the Quanity ...

its not working object require error 424

 

[sbaud2003]

how to get the stock position before e

No
I put the code in the after update event in thetext box ITEMID to get the stock position before entering the Quanity ...

ntry in quanity

 

[arnelgp]

sample plz.

 

[sbaud2003]

TXTSTOCK.Value = DLookup("current_stock", "tblStock", "Stock_Id " = [Forms]![SK Enterprice]![TblInvoiceDetail Subform].[Form]![ITEMID])

sample plz.

 

[bob fitz]

Try:
TXTSTOCK.Value = DLookup("current_stock", "tblStock", "Stock_Id = " & [Forms]![SK Enterprice]![TblInvoiceDetail Subform].[Form]![ITEMID])

 

[sbaud2003]

Try:
TXTSTOCK.Value = DLookup("current_stock", "tblStock", "Stock_Id = " & [Forms]![SK Enterprice]![TblInvoiceDetail Subform].[Form]![ITEMID])

No. it is giving the same error.

 

[sbaud2003]

sample plz.

Actualluy I want to verify the stock position before preparing invoice.. can you help me

 

[bob fitz]

Actualluy I want to verify the stock position before preparing invoice.. can you help me

So what value are you trying to return with DLookup(). Quantities of stock should not be stored as data but calculated as and when needed.

 

[sbaud2003]

So what value are you trying to return with DLookup(). Quantities of stock should not be stored as data but calculated as and when needed.

In the details invoice section while entering records for invoicing it takes any value, thus after updation of the stock table it becomes negative. Howver, if stock value is 0 the item is not listed, so that time no problems. It the Stock held is 1 also it shows in the item list. I want the user to see if the quantity required is actually available or not

 

[bob fitz]

....Howver, if stock value is 0 ....

The question I was asking though was, "How are you getting this value." If you have it saved in a table, then IMHO your tables need to be restructured, for the reason I gave in my previous post.

 

[sbaud2003]

The question I was asking though was, "How are you getting this value." If you have it saved in a table, then IMHO your tables need to be restructured, for the reason I gave in my previous post.

Yes it is store in the Stock Table as field name 'current_stock'

 

[bob fitz]

Yes it is store in the Stock Table as field name 'current_stock'

I would recommend you to read this link: http://allenbrowne.com/AppInventory.html

 

[Pat Hartman]

I fell into your original syntax errors. Try this one:

=DLookup("current_stock", "tblStock", "Stock_Id = 'P'" & Format([Forms]![SK Enterprice]![TblInvoiceDetail Subform].[Form]![ITEMID], "00000") & "'")

If that doesn't work try these and let us know which works:

=DLookup("current_stock", "tblStock", "[Stock_Id] = 'P00001'")
=DLookup("current_stock", "tblStock", "[Stock_Id] = 1")