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

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

 

[Guest]

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))

 

[Guest]

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?

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))

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.

 

[Guest]

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")