Display query result in a text box

[Jacinto]

I'd like to display the result of the query below
on a text box, named "bidNumber", after an update event.
I've put the "First" function so the result is only one.
But it does not seem to display. Any help will be very
appreciated!

Dim bidDisplay As String
Me.Refresh
If Bid = True Then

BidDisplay = "SELECT First(IIf([Opportunity].[bid],
[industryTypeId]+ Format([Opportunity].
[opportunityId],""000""),"""")) AS Bid_Reference FROM
Company INNER JOIN Opportunity ON Company.companyId =
Opportunity.companyId GROUP BY Opportunity.Description,
Opportunity.opportunityId, Opportunity.Bid HAVING
(((Opportunity.opportunityId)=[Forms]![frmCompany]!
[Child14].[Form]![opportunityId]) AND ((Opportunity.Bid)
=Yes))"

BidNumber = bidDisplay
Me.Refresh
End If

Thanks for your help and sorry for making this a new post
while its a continuation of an existing post.

Jacinto

 

[Duane Hookom]

One method would be to use a combo box and set the Row Source to the query.
You can also use code to set the value of an unbound text box.

 

[John Vinson]

BidDisplay = "SELECT First(IIf([Opportunity].[bid],
[industryTypeId]+ Format([Opportunity].
[opportunityId],""000""),"""")) AS Bid_Reference FROM
Company INNER JOIN Opportunity ON Company.companyId =
Opportunity.companyId GROUP BY Opportunity.Description,
Opportunity.opportunityId, Opportunity.Bid HAVING
(((Opportunity.opportunityId)=[Forms]![frmCompany]!
[Child14].[Form]![opportunityId]) AND ((Opportunity.Bid)
=Yes))"

BidNumber = bidDisplay
Me.Refresh
End If

This code will display the TEXT STRING "SELECT First(IIF... <etc>" in
the textbox bidDisplay, if it displays anything at all.

To display data from a Query on a form, either use the SQL of the
Query as the Form's Recordsource and put a textbox on the form bound
to a field in the query; or store the query in the Queries page, as a
saved parameter query, and use DLookUp to look up the value of a field
(or the field) in the Control Source of the textbox.

 

[Ken Snell]

Reply posted in original thread.

 

[Jacinto]

Thanks for all your positive and prompt replies!!!

Ive Tried Both Ken's method and worked well.
John's DLOOKUP method works well in VBA code but not in
control source, as Ken also noticed. Now I got a choice!

Thank you all!!

Jacinto

-----Original Message-----
Reply posted in original thread.

--
Ken Snell
<MS ACCESS MVP>

I'd like to display the result of the query below
on a text box, named "bidNumber", after an update event.
I've put the "First" function so the result is only one.
But it does not seem to display. Any help will be very
appreciated!

Dim bidDisplay As String
Me.Refresh
If Bid = True Then

BidDisplay = "SELECT First(IIf([Opportunity].[bid],
[industryTypeId]+ Format([Opportunity].
[opportunityId],""000""),"""")) AS Bid_Reference FROM
Company INNER JOIN Opportunity ON Company.companyId =
Opportunity.companyId GROUP BY Opportunity.Description,
Opportunity.opportunityId, Opportunity.Bid HAVING
(((Opportunity.opportunityId)=[Forms]![frmCompany]!
[Child14].[Form]![opportunityId]) AND ((Opportunity.Bid)
=Yes))"

BidNumber = bidDisplay
Me.Refresh
End If

Thanks for your help and sorry for making this a new post
while its a continuation of an existing post.

Jacinto

.