Solved Replace combo box with text box to display query result (1 Viewer)

Dag Solder

New member
Local time
Today, 10:40
Joined
May 8, 2020
Messages
19
Hi All,

I wonder if someone could help please?

I have a simple DB with one table, one form and 2 queries. (attached). The form has a pair of cascading combo boxes. The second query returns the highest number to the second combo box.

Instead of displaying / choosing this maximum value in a combo box, I would like to display the result in a text box as the user should not be able to change this value.

I have tried various methods including setting the form's record source to the query (this made the form controls disappear and left me with a blank form!). I admit, this has me stumped!

I eventually want to pass this information to another form so that the user will only have access to the record selected.

Any ideas would be gratefully received.

p.s. I am a real beginner

Cheers Dag
 

Attachments

  • Database1.accdb
    1.3 MB · Views: 109

cheekybuddha

AWF VIP
Local time
Today, 10:40
Joined
Jul 21, 2014
Messages
2,237
You could use the DMax() function as the ControlSource of a textbox:
=DMax([ValueField], [TableName], "MatchField = " & [NameOfFirstCombo])

hth,

d
 

Dag Solder

New member
Local time
Today, 10:40
Joined
May 8, 2020
Messages
19
Hi @cheekybuddha, thank you for your message, to be honest, this is far above my knowledge level / skill set.

I have located the Record source for the text box and tried various permutations of the code you kindly suggested, but other than syntax errors, my text box returns the same: #Name? The output from the first combobox determines the set of options for the second and by using the max in the totals box of the query, the user is presented with just one option for the second, hence why I want to replace with a text box.

For the project I have the following:

Table = tbl_Manuals ( fields within Table: ID, Manual Type, Details of Change, Issue Number )

Form = Form1 with ( 1st Combo Box = ComboType, 2nd Combo Box = ComboIssue, Text Box = IssNo )

qry_ManType runs from ComboType =

Code:
SELECT DISTINCT tbl_Manuals.[Manual Type]
FROM tbl_Manuals;

qry_IssNumber runs from ComboIssue =

Code:
SELECT Max(tbl_Manuals.[Issue Number]) AS [MaxOfIssue Number], tbl_Manuals.[Manual Type]
FROM tbl_Manuals
GROUP BY tbl_Manuals.[Manual Type]
HAVING (((tbl_Manuals.[Manual Type])=[Forms]![Form1]![ComboType]));

SO I guess what I want to display in the text box "IssNo" is the value for MaxOfIssue Number
 

cheekybuddha

AWF VIP
Local time
Today, 10:40
Joined
Jul 21, 2014
Messages
2,237
Hi,

Try:
=DMax("[Issue Number]", "tbl_Manuals", "[Manual Type] = " & [ComboType])
This assumes that [ComboType] is a numeric value.

If it is text then use:
=DMax("[Issue Number]", "tbl_Manuals", "[Manual Type] = '" & [ComboType] & "'")

Remember this goes as the ControlSource of textbox IssNo.

hth,

d
 

bob fitz

AWF VIP
Local time
Today, 10:40
Joined
May 23, 2011
Messages
4,717
Take a look at the attached db:
 

Attachments

  • Database1BOB.accdb
    484 KB · Views: 102

Dag Solder

New member
Local time
Today, 10:40
Joined
May 8, 2020
Messages
19
@cheekybuddha & @bob fitz, thank you both for taking the time to look at this for me, I am in awe of how you guys navigate around code like you do!

The code works like a dream and removes yet another hurdle in my way. It was the [Manual Type] that was tripping me up, had tried just about everything else
 

bob fitz

AWF VIP
Local time
Today, 10:40
Joined
May 23, 2011
Messages
4,717
@cheekybuddha & @bob fitz, thank you both for taking the time to look at this for me, I am in awe of how you guys navigate around code like you do!

The code works like a dream and removes yet another hurdle in my way. It was the [Manual Type] that was tripping me up, had tried just about everything else
Thank you for your kind words. Just trying to help when I can :).
 

Users who are viewing this thread

Top Bottom