dlookup in unbound field

G

Guest

I have a subform that holds information regarding products being ordered.

The fields are:

LineNumber StockNumber Item etc...

The StockNumber field is a combo box and the ItemDescription field is an
unbound text box. I want the Item field to show the ItemDescription from the
Product Details Table automatically when a stocknumber is entered in the
StockNumber field.

Right now I have the unbound text box as follows:

=DLookup("[ItemDescription]", "Product Details", "[StockNumber]="
&[StockNumber])

This is producing an error. Can someone help?!
 
A

Allen Browne

In the Product Details, table, is StockNumber a Number type field, or a Text
type field?

If Text, you need extra quotes:
=DLookup("[ItemDescription]", "Product Details",
"[StockNumber]=""" & [StockNumber] & """")

If Number, the 3rd argument will be incomplete if the StockNumber is left
blank. Use Nz() to avoid the error:
=DLookup("[ItemDescription]", "Product Details",
"[StockNumber]=" & Nz([StockNumber],0))

It might be more efficient to create a query that includes the table for
your subform and also the Product Details table, and then use this query as
the RecordSource for your form. That way you can have the ItemDescription
field in your form without needing a DLookup().
 
G

Guest

Okay...that sort of worked.

It was indeed a Text field so it now reads:

=DLookup("[ItemDescription]", "Product Details",
"[StockNumber]=""" & [StockNumber] & """")

However when I choose a stocknumber from the combo box nothing happens in
the Dlookup box, it just remains blank.


Allen Browne said:
In the Product Details, table, is StockNumber a Number type field, or a Text
type field?

If Text, you need extra quotes:
=DLookup("[ItemDescription]", "Product Details",
"[StockNumber]=""" & [StockNumber] & """")

If Number, the 3rd argument will be incomplete if the StockNumber is left
blank. Use Nz() to avoid the error:
=DLookup("[ItemDescription]", "Product Details",
"[StockNumber]=" & Nz([StockNumber],0))

It might be more efficient to create a query that includes the table for
your subform and also the Product Details table, and then use this query as
the RecordSource for your form. That way you can have the ItemDescription
field in your form without needing a DLookup().

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

SeanK6876 said:
I have a subform that holds information regarding products being ordered.

The fields are:

LineNumber StockNumber Item etc...

The StockNumber field is a combo box and the ItemDescription field is an
unbound text box. I want the Item field to show the ItemDescription from
the
Product Details Table automatically when a stocknumber is entered in the
StockNumber field.

Right now I have the unbound text box as follows:

=DLookup("[ItemDescription]", "Product Details", "[StockNumber]="
&[StockNumber])

This is producing an error. Can someone help?!
Click to expand...
Click to expand...
 
A

Allen Browne

There could be a time delay before Access updates this calculated field. You
can try forcing the update with the Recalc method of the form if you wish.

To get it to work with your combo, presumably you have replaced the last
[StockNumber] only with the name of your combo. That should work, provided
the Bound Column of the combo does match the value in the StockNumber column
of the combo's RowSource.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

SeanK6876 said:
Okay...that sort of worked.

It was indeed a Text field so it now reads:

=DLookup("[ItemDescription]", "Product Details",
"[StockNumber]=""" & [StockNumber] & """")

However when I choose a stocknumber from the combo box nothing happens in
the Dlookup box, it just remains blank.


Allen Browne said:
In the Product Details, table, is StockNumber a Number type field, or a
Text
type field?

If Text, you need extra quotes:
=DLookup("[ItemDescription]", "Product Details",
"[StockNumber]=""" & [StockNumber] & """")

If Number, the 3rd argument will be incomplete if the StockNumber is left
blank. Use Nz() to avoid the error:
=DLookup("[ItemDescription]", "Product Details",
"[StockNumber]=" & Nz([StockNumber],0))

It might be more efficient to create a query that includes the table for
your subform and also the Product Details table, and then use this query
as
the RecordSource for your form. That way you can have the ItemDescription
field in your form without needing a DLookup().


SeanK6876 said:
I have a subform that holds information regarding products being
ordered.

The fields are:

LineNumber StockNumber Item etc...

The StockNumber field is a combo box and the ItemDescription field is
an
unbound text box. I want the Item field to show the ItemDescription
from
the
Product Details Table automatically when a stocknumber is entered in
the
StockNumber field.

Right now I have the unbound text box as follows:

=DLookup("[ItemDescription]", "Product Details", "[StockNumber]="
&[StockNumber])

This is producing an error. Can someone help?!
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Perfect...the bound column was not set right...thanks for the help!

Allen Browne said:
There could be a time delay before Access updates this calculated field. You
can try forcing the update with the Recalc method of the form if you wish.

To get it to work with your combo, presumably you have replaced the last
[StockNumber] only with the name of your combo. That should work, provided
the Bound Column of the combo does match the value in the StockNumber column
of the combo's RowSource.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

SeanK6876 said:
Okay...that sort of worked.

It was indeed a Text field so it now reads:

=DLookup("[ItemDescription]", "Product Details",
"[StockNumber]=""" & [StockNumber] & """")

However when I choose a stocknumber from the combo box nothing happens in
the Dlookup box, it just remains blank.


Allen Browne said:
In the Product Details, table, is StockNumber a Number type field, or a
Text
type field?

If Text, you need extra quotes:
=DLookup("[ItemDescription]", "Product Details",
"[StockNumber]=""" & [StockNumber] & """")

If Number, the 3rd argument will be incomplete if the StockNumber is left
blank. Use Nz() to avoid the error:
=DLookup("[ItemDescription]", "Product Details",
"[StockNumber]=" & Nz([StockNumber],0))

It might be more efficient to create a query that includes the table for
your subform and also the Product Details table, and then use this query
as
the RecordSource for your form. That way you can have the ItemDescription
field in your form without needing a DLookup().


I have a subform that holds information regarding products being
ordered.

The fields are:

LineNumber StockNumber Item etc...

The StockNumber field is a combo box and the ItemDescription field is
an
unbound text box. I want the Item field to show the ItemDescription
from
the
Product Details Table automatically when a stocknumber is entered in
the
StockNumber field.

Right now I have the unbound text box as follows:

=DLookup("[ItemDescription]", "Product Details", "[StockNumber]="
&[StockNumber])

This is producing an error. Can someone help?!
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Getting sums from continuous forms 3
Autofill and modify data in Access 3
How to stop Access highlight the text in a combo box after an event 1
Unbound text box returns nothing 1
DLOOKUP 2
Filter subform from combo unbound text box 3
Query Results in unbound form field 3
Bound a DLookUp Value 2

Top