DLookup error

[Nadine67]

Hello and thank you in advance for any attention this post may receive.

So I want a text box to populate based on what is in a combo box. Both text and combo box values come from the same table.

Table = Fauna


Combo Box = "Animal"
Text Box = "AnimalGenre"

Combo Box RowSource =SELECT [ID], [Animal] FROM Fauna ORDER BY [Animal];
Text Box Control Source =DLookUp("Animal Genre","Fauna","ID=" & [Animal])

Combo Box is working fine. Text box contains #Name? error.

Cheers Nadine

[June7]

Don't use DLookup.

Include the Animal Genre field as another column in the combobox RowSource. Then expression in textbox can refer to that column by its index. Index begins with 0.

=[combobox name].[Column](2)

Advise no spaces or special characters/punctuation (underscore is exception) in naming convention. If used, enclose name in [].

[NoellaG]

Hi Nadine,

your combobox can work with the control source = DLookUp("[Animal Genre]","Fauna","ID=" & [Animal]) if [Animal] is a number field.
If [Animal] is a text field it should read: DLookUp("[Animal Genre]","Fauna","ID=""" & [Animal] & """")

greetings
Noëlla

[June7]

'Animal' is the name of combobox and its value is the ID which I presume is a number field.

Again, DLookup is not needed and performs slower than the suggested alternative.

[Nadine67]

Thank June and Noella for your advice

June I am not sure how to include the Animal Genre field as another column in the combobox RowSource.

Thank you for your help

Nadine

[June7]

RowSource: SELECT [ID], [Animal], [Animal Genre] FROM Fauna ORDER BY [Animal];

ColumnCount: 3

ColumnWidths: 0;0.5;0.5

[Nadine67]

Hello june

Thank you for your time. So I did what you suggested and it is working a treat in the table only for the 'Animal' column only.

I am not sure what is going on with the form now because it is only giving me a number selection (ID).

TABLE - Fauna
Column - Animal
Data Type - Number
Display Control - Combo Box
Row Source Type - Table/Query
Row Source - SELECT [ID], [Animal], [Animal Genre] FROM Fauna ORDER BY [Animal];
Bound - 0
Column - 3
Width - 0;2.5;0
Limit to List - Yes
Multiple Values - No
List Edits - No
Show only Row Source - No

FORM - Capture
Name - Animal
Selection Type - Combo Box
Control Source - Animal
Row Source - SELECT [ID], [Animal], [Animal Genre] FROM Fauna ORDER BY [Animal];
Row Source Type - Table/Query
Bound Column - 0

So if the parameters for the table and form field 'Animal' are the same I should only be able to select the text name from the list and not the IDNumber right?

The auto-populating 'AnimalGenre' field based on the 'Animal' selection is not working either.

TABLE - Fauna
Column - AnimalGenre
DataType - Text
Display Control - Text Box


FORM - Capture
Name - AnimalGenre
Selection Type - Text Box
Control Source - =[Animal].[Column](2)
Text Format - Plain Text

June what have I done wrong?

Thank you again for your time and help.

regards Nadine

[Nadine67]

Hello june

Please excuse my incompetence......I know why the ID was all I could select on the form.......because I told it that is what I wanted to select by saying SELECT [ID],

All else is still not working for me.

[June7]

Why save Genre to table? That is duplicating data. The Genre can always be retrieved in a query that joins tables.

Saving the Genre into record will require code (macro or VBA) in some event.

[Nadine67]

OMG June that is so right......and so obvious now that it has slapped me in the face.

Thank you June.

Have a great day!

[Nadine67]

Hello June

So now I have built a new access db and i have used your above formula in much the same way only this time i want the text box ([combobox name].[column](2) value to appear in the table 'ImprovementIdeas' when I save it because this data will be exported for analysis. The lookup table is 'ProductUnit'

TABLE - ImprovementIdeas
Field - Product (Number)
Field - Unit (Text)

TABLE - ProductUnit
Field - ProductType (Number)
Field - UnitType (text)

FORM - CaptureData
Field - txtProduct (ComboBox)
ControlSource - Product
RowSource - SELECT [ProductType], [UnitType] FROM ProductUnit ORDER BY [ProductType];
Field - txtUnit (text)
ControlSource - =[txtProductType].[Column](2)

Not sure how to do this using your formulas. i had thought of using IIF in along winded way but I know there has to be a smarter way.

Cheers....Nadine

[June7]

There is no Column(2) in that RowSource. Column index begins with 0. So the first column is index 0 and the second is index 1.

Why do you need to save UnitType into ImprovementIdeas? Aren't you saving the ProductType number? To export data, build a query that includes both tables. This will make all related data available.

[Nadine67]

Thank you June.

I built the query and it is doing exactly what i want it to.

Thank you once agan.

Nadine