Dlookup issue

[brharrii]

I'm assuming this is the correct place for this, if it isn't I apologize, let me know and I can find another place to post it, thanks!

I'm working in access 2010, I have 2 tables:

tblProductinfo
- itemNumber (PK)


- JDEDescription
- Sauce (Cbox from tbl Sauce)

and

tblSauce
- SauceID
- Sauce (populates cbox options for sauce field in tblProductinfo also FK)
- Ingredients (Memo field 255+ characters)

Form looks like this:

ProductInfo
- ItemNumber
- JDEDescription
- Sauce (cbox populated with options from tblSauce.sauce recorded in tblProductinfo.sauce)
- SauceID (unbound textbox, using dlookup to find corresponding tblsauce.SauceID)
- SauceIng (unbound textbox, using dlookup to find corresponding tbl.ingredients)

For the ubTextbox: SauceIng, I am using the following dlookup:

=DLookUp("ingredients","tblSauce","Sauce = " & Nz([sauce],0))

The field keeps returning the same: #Error and not displaying the ingredients I have listed in tblsauce.Ingredients (this is a memo field, I don't know if that could be causing a problem?)

Does anyone have any suggestions on what I may have done wrong? Thanks!

[ssanfu]

The first thing I would suggest is fix your tables. Get rid of the look-up fields. See http://access.mvps.org/access/lookupfields.htm

Set up a relationship between the two tables. Then you can fix the controls. Why are you using unbound controls???

The text box is the only control that I know of that will display a memo field.

[brharrii]

ssanfu I could kiss you,

This has been a week of mass frustration over this issue and it was just resolved with the help of your advice.... Thank you.

[ssanfu]

(awwww shucks... )

Glad to be of help....