How to get the value from this query onto a form?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have this query:
SELECT Info.ResId, Info.CurrentVisit
FROM Info
WHERE (((Info.ResId)=[whichId]));

Which gets the ResId and current visit number for a specific ResId

How do I get the CurrentVisit to show up on the form? The ResId is coming
from a text box on the form ResId.
 
As you want the data to show on the same form as the ResID value in a text
box you don't need a query; you can use the DLookup function. Make the
ControlSource of a text box on the form something like this:

=DLookup("CurrentVisit", "Info", "ResID = " & [whichID])

where whichID is the name of the text box containing the ResID. This assumes
ResID is a number data type; if it’s a text data type wrap the value in
quotes:

=DLookup("CurrentVisit", "Info", "ResID = " & Chr(34) & [whichID] & Chr(34))
 
Thanks for the help that should do it.

Can you also do this with code? Like something similar to this
Set qdf1a = CurrentDb.QueryDefs("Formula1a")
qdf1a.Parameters("whichId") = Me.ResId
Set rs1a = qdf1a.OpenRecordset

By acutally calling the query and then using the result and then pulling
what you need from the result from the query with code?

Ken Sheridan said:
As you want the data to show on the same form as the ResID value in a text
box you don't need a query; you can use the DLookup function. Make the
ControlSource of a text box on the form something like this:

=DLookup("CurrentVisit", "Info", "ResID = " & [whichID])

where whichID is the name of the text box containing the ResID. This assumes
ResID is a number data type; if it’s a text data type wrap the value in
quotes:

=DLookup("CurrentVisit", "Info", "ResID = " & Chr(34) & [whichID] & Chr(34))

pokdbz said:
I have this query:
SELECT Info.ResId, Info.CurrentVisit
FROM Info
WHERE (((Info.ResId)=[whichId]));

Which gets the ResId and current visit number for a specific ResId

How do I get the CurrentVisit to show up on the form? The ResId is coming
from a text box on the form ResId.
Click to expand...
Click to expand...
 
You can, though to get a single value the DLookup function would be faster.
To do it via a recordset a function along these lines (using DAO) would work:

Function GetValFromQuery(strQuery As String, strField As String)

Dim dbs As DAO.Database, qdf As DAO.QueryDef
Dim prm As DAO.Parameter, rst As DAO.Recordset

Set dbs = CurrentDb
Set qdf = dbs.QueryDefs(strQuery)

For Each prm In qdf.Parameters
prm = Eval(prm.Name)
Next prm

Set rst = qdf.OpenRecordset

Set rst = qdf.OpenRecordset

GetValFromQuery = rst.Fields(strField)

End Function

You'd call it like so:

Me.MyTextox = GetValFromQuery("MyQuery", "MyField")
 
Back
Top