DAO QueryDef Object already exists

[meadows43]

Hi,

When the following function is executed, I get an error telling me my QueryDef object already exists. The error occurs on the line where I try to set the QueryDef using the DAO database object's CreateQueryDef method. Here's the code

'*************************************************
Public Function ConvertUnits(FromUnit As Integer, ToUnit As Integer, InQuantity As String) As String


'Record operations
Dim dbs As DAO.Database
Dim rs As DAO.Recordset


Set dbs = CurrentDb
Set rs = dbs.OpenRecordset("MeasurementConversions", dbOpenDynaset)

'need to seek for FromUnit and ToUnit in Recordset and get ConversionFactor
Dim strSQL As String
strSQL = "SELECT ConversionFactor FROM MeasurementConversions WHERE FromMeasurement = " & FromUnit & " AND ToMeasurement = " & ToUnit

Dim qdf As DAO.QueryDef
Set qdf = dbs.CreateQueryDef("qryConvFactor", strSQL)
Set rs = qdf.OpenRecordset
DoCmd.OpenQuery "qryConvFactor", acViewNormal, acReadOnly
cf = rs![ConversionFactor]
dbs.QueryDefs.Delete "qryConvFactor"


Dim whole1 As Integer
Dim whole2 As Integer
Dim numerator1 As Integer
Dim numerator2 As Integer
Dim denom1 As Integer
Dim denom2 As Integer
Dim tempString As String
Dim tempNum As Integer
Dim tempDenom As Integer
Dim convF As String
Dim resultNum As String
Dim resultDenom As String

'Step 1 - load variables with whole num, numerator, and denom for both inStrings
tempString = sGetToken(InQuantity, 1)
If InStr(tempString, "/") <> 0 Then 'no whole num
whole1 = 0
numerator1 = sGetToken(tempString, 1, "/")
denom1 = sGetToken(tempString, 2, "/")
Else
'both whole num and fraction
If InStr(InQuantity, "/") <> 0 Then
tempString = sGetToken(InQuantity, 1)
whole1 = tempString
tempString = sGetToken(InQuantity, 2)
numerator1 = sGetToken(tempString, 1, "/") 'right parameter order?
denom1 = sGetToken(tempString, 2, "/")
numerator1 = numerator1 + (whole1 * denom1)
Else 'no fraction
whole1 = val(InQuantity)
numerator1 = 0
denom1 = 1
End If
End If

'Step 1 continued - load variables with whole, num, and denom of conv factor
tempString = sGetToken(cf, 1)
If InStr(convF, "/") <> 0 Then 'no whole num
whole2 = 0
numerator2 = sGetToken(convF, 1, "/")
denom2 = sGetToken(tempString, 2, "/")
Else
'both whole num and fraction
If InStr(convF, "/") <> 0 Then
tempString = sGetToken(convF, 1)
whole2 = tempString
tempString = sGetToken(convF, 2)
numerator2 = sGetToken(tempString, 1, "/")
denom2 = sGetToken(tempString, 2, "/")
numerator1 = numerator1 + (whole1 * denom1)
Else 'no fraction
whole2 = val(convF)
numerator2 = 0
denom2 = 1
End If
End If



'Step 2 - multiply fraction by conversion factor fraction
If denom1 <> 0 Then
'decimalValue1 = numerator1 / denom1
resultNum = numerator1 * numerator2
resultDenom = denom1 * denom2
End If


ConvertUnits = resultNum & "/" & resultDenom

End Function

 

[WayneRyan]

meadows,

Shouldn't your dbs.QueryDefs.Delete "qryConvFactor" be
before the dbs.CreateQueryDef?

Wayne

 

[meadows43]

Wayne,

Thanks, but now I get an error on the line

Set rs = qdf.OpenRecordset

"Data type mismatch in criteria expression"

 

[dcx693]

The problem probably relates to this line:
strSQL = "SELECT ConversionFactor FROM MeasurementConversions WHERE FromMeasurement = " & FromUnit & " AND ToMeasurement = " & ToUnit

The syntax of the expression is set up to accept numeric values for the FromMeasurement and ToMeasurement variables. I'm guessing you need to treat those like string values. If so, try this:
strSQL = "SELECT ConversionFactor FROM MeasurementConversions WHERE FromMeasurement = '" & FromUnit & "' AND ToMeasurement = '" & ToUnit & "'"

 

[meadows43]

You are right. Thanks! That solves this problem.