Solved How to display text from 2 Combo Boxes selections? (1 Viewer)

[bnefit]

Hi

I am new to Access and to the forum. I am trying to build a database and currently have a problem in Forms that require assistance.

I have created the following in Access:
Query: _Test & Tag

In Forms, I have created 2 Combo Boxes which I have labelled as Location (Combo33) and Description (Combo37). I have also created a Text Box labelled as Current Test Date (Text40).

For Combo33 (Location), I want to have a dropdown list of the various locations so I have set the Row Source as:
SELECT DISTINCT [_Test & Tag].Location FROM [_Test & Tag] ORDER BY [_Test & Tag].[Location];

For Combo37 (Description), the list of items will depends on the location so I have set the Row Source as:
SELECT [_Test & Tag].Description FROM [_Test & Tag] WHERE Combo33=[_Test & Tag]!Location ORDER BY [_Test & Tag].[Description];

For Text40 (Current Test Date), I would like to display the Test Date from _Test & Tag depending on the selection of Combo33 and Combo37. However, I am having trouble trying to get this to work.

I have tried to set the Control Source to DLookup(“[Test Date]”,”[_Test & Tag]”,”[Combo33]=” & Forms!Test & TagUpdate2!Location, “[Combo37]=” & Forms!Test & TagUpdate2!Description) but it didn't work.

Thanks for reading and any ideas/help would be greatly appreciated.

 

[June7]

Try this.

Combo37 properties:

RowSource: SELECT [Description], [Test Date] FROM [_Test & Tag] WHERE [Location] = [Combo33] ORDER BY [Description];
ColumnCount: 2
ColumnWidths: 2";1"

Then expression in Text40 ControlSource: =Combo37.Column(1)

Otherwise, correct DLookup:

=DLookup("[Test Date]", "[_Test & Tag]", "[Location]='" & Combo33 & "' AND [Description]='" & Combo37 & "'")

Description is a reserved word and should not use reserved words as names. Also shouldn't use spaces and punctuation/special characters in naming convention.

 

[arnelgp]

some of the records in the query has same Location + description, but different Tag No.

 

[bnefit]

Try this.

Otherwise, correct DLookup:

=DLookup("[Test Date]", "[_Test & Tag]", "[Location]='" & Combo33 & "' AND [Description]='" & Combo37 & "'")

Description is a reserved word and should not use reserved words as names. Also shouldn't use spaces and punctuation/special characters in naming convention.

Hi June7

I've tried the corrected DLookup code and it worked! Thank you very much!

I will rename "Description" to something else and also remove all spacing and punctuation/special characters as you suggested.

 

[Gasman]

Why not give your controls some meaningful names? 6 months down the line, you are not going to remember what Combo33 is?

cboLocation
cboDescription
txtTestDate

Even for this question it would have already saved you some typing.

 

[bob fitz]

Cross Posted: https://www.accessforums.net/showthread.php?t=81308&p=459371#post459371

Please read: https://www.excelguru.ca/content.php?184

 

[bnefit]

Hi Bob, apologies once again and thanks for the reminder.

 

[June7]

Glad you have a working solution. However domain aggregate would be my last choice as they can slow performance. I would recommend other suggested option of including field in combobox RowSource and expression in textbox references combobox column.

 

[bnefit]

Hi June7

Thanks for the additional info. I just tried the other option and the performance is indeed faster.