Yet another auto fill question

[Yensid]

I have a table (tblAD) that stores "DocNumber" and "Description" information (i.e. AD-1001-001 = Widget).


I have a form (frmECO).
Two fields on this form are the focus of this question.
The first field is "DocNumber", the second field is "Description".
I want the "Description" field in the form to be automatically filled in with the description of the "DocNumber" based on the information from the table.
For example: I want the user to enter AD-1001-001 in the "DocNumber" field on the form (frmECO) and I want the "description" field automatically filled in with Widget.

I found the following code and have been trying to get it to work...unsuccessfully. Can anyone help?

=DLookUp("[Description]","tblAD","[DocumentNumber] = " & [Forms]![DocumentNumber])

Another thing that I am going to have trouble with is I have about 15 or so tables such as the "tblAD" that have "DocNumber" and "Description" information that I want to autofill based on the "DocNumber" entered on the form.
So, a user could type in AD-1001-001 on the form and the description would automatically fill in with Widget (as explained before), but I also want that same form to allow the user to type in say CD-2224-003 - from a table called "tblCD", and autofill with that particular numbers description. I hope all of this makes sense.

Thanks for your time!

[June7]

Why unsuccessful - error message, wrong results, nothing happens? DocNumber is a text field so need apostrophe delimiters for the criteria and the form name:

=DLookUp("[Description]","tblAD","[DocumentNumber] = '" & [Forms]![formnamehere]![DocumentNumber] & "'")

DLookup is really an inefficient method to retrieve related date on a form. I prefer:

1. use multi-column combobox and then textboxes refer to combobox columns to display info

2. include the reference table in form RecordSource joined on the ID fields, jointype "Include all records from PRIMARY table...", then textbox bound to fields of the reference table, Locked property Yes, TablStop property No.

[Yensid]

I got it to work ONCE with =DLookUp("[Description]","[tblAD]","[DocNumber] = '" & [Forms]![frmECO]![DocumentNumber] & "'") BUT ONLY ONCE. Now nothing will happen.

I will have a go with your suggestion June7, but I don't know that I understand everything you wrote (my lack of knowledge)

[June7]

Maybe this will help for the combobox option http://datapigtechnologies.com/flash...combobox3.html

[Yensid]

I don't think a combobox is what I want. Couple reasons

1) I don't want to have the user scroll through 1000's of part numbers (the user will have the part number right in front of them) - they should be able to type the part number in one box, and have the description of that part number auto fill in another box.
2) So far all the solutions I have seen would work for 1 table. I have about 20 tables that have different types of parts. For example, I have a tblCD for components, I have a tblAD for assemblies, tblFT for fixtures, tblOP for Standard Operating Procedure documents and so on.

How can I have one box that a user will enter in any of those different kinds of document numbers, and there will be a box next to it that will autofill with the description of that particular document number?

[June7]

1. Don't have to scroll through combobox list. Type the value in the combobox, AutoExpand will match item in the list. Make combobox multi-column, one column for the part number and one for the description. The description can also be displayed in a textbox by expression referencing column of combobox. The tutorial demonstrates this. Yes, this is a 1 table solution.

2. Would require VBA code that searches each table until value is either found and returned or not found and message to that effect returned. This could be a 'If Then ElseIf Else End If' structure. Review http://office.microsoft.com/en-us/ac...010341717.aspx OR build a UNION query that contains all the part numbers and descriptions from all 20 tables and use that query as data source for item 1 concept. A UNION query essentially presents the data in a normalized structure as it should be.

[Yensid]

Do you think it would make sense to put all item numbers (regardless of type) in the same table? Or do you think separating them out as I have (Assembly document, Operating documents, etc) into different tables is the best way to go? If I put them all in the same table, I wouldn't have to make a query...correct?

[June7]

Are the fields identical across the 20 tables?

Either structure can be made to work. The multi-table version requires code or UNION. You have to decide which will work best for you. Seems one table is most optimized but I would have to know more about your data.

[Yensid]

I'll create the database so all document numbers and descriptions are in one table (tblDocuments). I will assign a document type designation (tblDocTypes) for each document in order to differentiate them. I'm sure I'll have more questions...

[June7]

The sounds like valid normalization. Good Luck.