Using a Select Query to Return the Result to a Variable

[ellidavd]

I have a form with three textboxes:
txtAisle
txtSect
txtBox

I have a table called Hoods with columns that correspond to those three
textboxes:
Hoods.Aisle
Hoods.Sect
Hoods.Box

I would like to run a query to get the maximum value from the Hoods.Box
column based on the input from txtAisle and txtSect and put that value
into txtBox.

I have done this before using VB.Net and C#, but for some reason I am
not able to get it in Access.

I know how to run the query in code. The following code works:

Private Sub btnBox_Click()
Dim strSQL As String
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim AisleHold As String
Dim SectHold As String

Set db = CurrentDb

strSQL = "SELECT Max([Hoods].[Box]) AS [Max Of Box]FROM Hoods WHERE
Aisle=" & txtAisle & "And Sect=" & txtSect & ";"
db.QueryDefs.Delete "qryMyQuery"
Set qdf = db.CreateQueryDef("qryMyQuery", strSQL)
DoCmd.OpenQuery "qryMyQuery", acNormal, acEdit
End Sub

I would like to place the result of the query in a variable to display
in txtBox rather than having a datasheet appear with the result.

Thanks for any help.

 

[Alex Dybenko]

Hi,
if Aisle and/or Sect has string value - you have to put it in quotes:

strSQL = "SELECT Max([Hoods].[Box]) AS [Max Of Box]FROM Hoods WHERE Aisle='"
& txtAisle & "'And Sect='" & txtSect & "'"

if no - then what error do you get?

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[ellidavd]

I think I'll have to create a recordset. I've added the following to
the above code.

Dim rst As DAO.Recordset

strSQL = "SELECT Max([Hoods].[Box]) AS [Max Of Box]FROM Hoods WHERE
Aisle=" & AisleHold & "And Sect=" & SectHold & ";"

Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot, dbReadOnly)


How do I get the result of the query into a variable? It should only be
one value.

Thanks

 

[chris.nebinger]

You need to open a recordset. The OpenQuery will actually open the
query to view, but not give you the variables


Private Sub btnBox_Click()
Dim strSQL As String
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim AisleHold As String
Dim SectHold As String

Set db = CurrentDb

strSQL = "SELECT Max([Hoods].[Box]) AS [Max Of Box]FROM Hoods WHERE
Aisle=" & txtAisle & "And Sect=" & txtSect & ";"
Set rst =dbs.OpenRecordset(strSQL)
txtbox = rst("Max Of Box")
rst.close
set rst =nothing




Chris Nebinger

End Sub

 

[Dirk Goldgar]

I have a form with three textboxes:
txtAisle
txtSect
txtBox

I have a table called Hoods with columns that correspond to those
three textboxes:
Hoods.Aisle
Hoods.Sect
Hoods.Box

I would like to run a query to get the maximum value from the
Hoods.Box column based on the input from txtAisle and txtSect and put
that value into txtBox.

I have done this before using VB.Net and C#, but for some reason I am
not able to get it in Access.

I know how to run the query in code. The following code works:

Private Sub btnBox_Click()
Dim strSQL As String
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim AisleHold As String
Dim SectHold As String

Set db = CurrentDb

strSQL = "SELECT Max([Hoods].[Box]) AS [Max Of Box]FROM Hoods WHERE
Aisle=" & txtAisle & "And Sect=" & txtSect & ";"
db.QueryDefs.Delete "qryMyQuery"
Set qdf = db.CreateQueryDef("qryMyQuery", strSQL)
DoCmd.OpenQuery "qryMyQuery", acNormal, acEdit
End Sub

I would like to place the result of the query in a variable to display
in txtBox rather than having a datasheet appear with the result.

Thanks for any help.

You're doing more work than you need to. You could do it this way:

'----- start of code example 1 -----
Private Sub btnBox_Click()

Dim strSQL As String
Dim rs As DAO.Recordset

strSQL = _
"SELECT Max([Box]) AS MaxBox FROM Hoods " & _
"WHERE Aisle=" & Me!txtAisle & " And Sect=" & Me!txtSect

Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
With rs
If .EOF Then
Me!txtBox = Null
Else
Me!txtBox = .Fields(0)
End If
.Close
End With
Set rs = Nothing

End Sub
'----- end of code example 1 -----

Or you could do let the DMax() function handle the whole business for
you, like this:

'----- start of code example 2 -----
Private Sub btnBox_Click()

Me!txtBox = _
DMax("Box", "Hoods", _
"Aisle=" & Me!txtAisle & " And Sect=" & Me!txtSect)

End Sub
'----- end of code example 2 -----

 

[ellidavd]

Chris and Dirk,
All of your solutions work. I knew I couldn't use DoCmd.OpenQuery. I
just wanted to make sure my query works.

Alex,
I wasn't getting an error. Right now those fields are formatted as
numbers. I might make them text, and then I will have to use quotes.

Thanks to all of you.

 

[Tim Ferguson]

(e-mail address removed) wrote in @i42g2000cwa.googlegroups.com:

strSQL = "SELECT Max([Hoods].[Box]) AS [Max Of Box]FROM Hoods" & _
" WHERE Aisle=" & txtAisle & "And Sect=" & txtSect & ";"

Don't forget you need a space after [Max Of Box] and before the FROM.


Anyway, why not do a DMax() and have done with all the recordset stuff

jetWhere = "Aisle = " & txtAisle & " AND Sect = " & txtSect
txtBox.Value = DMax("Box", "Hoods", jetWhere)


Hope that helps


Tim F