ON VB, How to call the result/value of a query (1 Viewer)

[K0k1man]

When I try to prepopulate a text field with the result of a query, I get the query string and not the result of the question. How to load the answer in a text box?


********CODE BELOW*************

Private Sub DestinationList_AfterUpdate()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As Recordset
Dim strSQL As String
Set db = CurrentDb
Set qdf = db.QueryDefs("Temp")


If ((Me.CubicMeters.Value) > 10) Then
strSQL = "SELECT G10 FROM AllCosts WHERE Port ='" & Me.PortDropDown.Value & "' AND Destination = '" & Me.DestinationList.Value & "';"
Else
strSQL = "SELECT L10 FROM AllCosts WHERE Port ='" & Me.PortDropDown.Value & "' AND Destination = '" & Me.DestinationList.Value & "';"
End If

qdf.SQL = strSQL


Set qdf = Nothing
Set db = Nothing

Me.OceanFreightCost.SetFocus
Me.OceanFreightCost.Requery
Me.OceanFreightCost2.Requery


End Sub

 

[The_Doc_Man]

Usually you have to do a DoCmd.RunSQL to run SQL queries - but the issue is still how do you get back the answer?

Take a look at the DLookup function, which seems to me to be the closest thing to what you might want to do.

Instead of writing out the full query, you would do a

Variable = DLookup( "[G10]", "AllCosts", ..... your complex SQL WHERE clause goes here" )

Or, of course, the other let of the IF/THEN .... DLookup( "[L10]", .... )

 

[K0k1man]

Advanced DLookup and Conditionals in VBA

Once you guys helped so much, here is the final working code with a good example of VB/Access syntax and If/Else statements.


***********************

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Set db = CurrentDb
Set qdf = db.QueryDefs("Temp")


If ((Me.CubicMeters.Value) > 10) Then
strSQL = "SELECT G10 FROM AllCosts WHERE Port ='" & Me.PortDropDown.Value & "' AND Destination = '" & Me.DestinationList.Value & "';"

Dim varX As String
varX = DLookup("[G10]", "[AllCosts]", "[Port]='" & Me.PortDropDown & "' AND [Destination]='" & Me.DestinationList & "'")
Me.DCL_Rate = varX


Else
strSQL = "SELECT L10 FROM AllCosts WHERE Port ='" & Me.PortDropDown.Value & "' AND Destination = '" & Me.DestinationList.Value & "';"

Dim varX2 As String
varX2 = DLookup("[L10]", "[AllCosts]", "[Port]='" & Me.PortDropDown & "' AND [Destination]='" & Me.DestinationList & "'")
Me.DCL_Rate = varX2
End If


qdf.SQL = strSQL


Set qdf = Nothing
Set db = Nothing

 

[The_Doc_Man]

Ah, I see that you have been advised of one of those "special" rules (qualifies as an "Old Programmer's Rule" - if you open it, close it. If you take it out, put it back. I refer, of course, to the "Set xxx = nothing" statements. Good habit, don't drop it.

I'm not sure you needed quite everything you had there, but it's your problem and you said it is working. Which in the final analysis is the important part.

 

[K0k1man]

Thanks Doc. Yes you were right, during all my attemps I ended up creating redundant code. I just finished cleaning it up and I now just have the DLookUp instead of the whole SQL String... The big breakthrough was getting the correct VBA/Access syntax for the Dlookup with multiple fields and multiple criteria... Believe it or not it took me some browsing to locate a syntax that would work.

**********************

Dim varX As String
varX = DLookup("[G10]", "[AllCosts]", "[Port]='" & Me.PortDropDown & "' AND [Destination]='" & Me.DestinationList & "'")
Me.DCL_Rate = varX

**********************


Thanks guys,

Koki

 

[The_Doc_Man]

Believe it or not it took me some browsing to locate a syntax that would work.


Trust me, those of us who have worked more than superficially with Access believe you.