Solved Replace combo box with text box to display query result

[Dag Solder]

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

 

[cheekybuddha]

You could use the DMax() function as the ControlSource of a textbox:
=DMax([ValueField], [TableName], "MatchField = " & [NameOfFirstCombo])

hth,

d

 

[Dag Solder]

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 =

SELECT DISTINCT tbl_Manuals.[Manual Type]
FROM tbl_Manuals;

qry_IssNumber runs from ComboIssue =

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]

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]

Take a look at the attached db:

 

[Dag Solder]

@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

 

[cheekybuddha]

You're welcome!

 

[bob fitz]

@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 .