FindFirst Not Working with Decimal Data Type

[JonMulder]

I've got a bunch of tables that I'm linking to that have Primary fields as a Decimal Data Type. Actually, ALL of the number fields are of Decimal Data Type. I want to do a "FindFirst" to locate the first (and only) record that has, for example, "MASTER_SITE_ID = 49033". Prior to the FindFirst, I have some code to get the user to select a MASTER_SITE_ID from a listbox, then define a Variant variable for the Decimal Data Type. Here's a snippet of my code:



Private Sub CmdAddGridWell_Click()
Dim booWellNotInGridWellTable As Boolean
booWellNotInGridWellTable = True
Do While booWellNotInGridWellTable
DoCmd.OpenForm "GetWellLocation_CASGEMWells", , , , , acDialog
If pubvarMASTER_SITE_ID = "" Then
Exit Sub
End If
'Check that CASGEM Well Record is not already linked to a GridWell Record.
Dim dbs As Database
Dim rstGridWells As Recordset
Set dbs = CurrentDb()
Set rstGridWells = dbs.OpenRecordset("Query--GridWells", dbOpenDynaset)
rstGridWells.FindFirst "MASTER_SITE_ID = " & CDec(pubvarMASTER_SITE_ID)
''' The following code did not work.''' rstGridWells.FindFirst "MASTER_SITE_ID = '" & pubvarMASTER_SITE_ID & "'"
If rstGridWells.NoMatch Then
Exit Do
Else
Dim intResponse As Integer
Call PlaySound("OhOh.wav")
intResponse = MsgBox("This well is already linked to a Grid Well." & vbNewLine & "Do you want to view well data?", vbYesNo, "Well Already Linked to a Grid Well")
If intResponse = 6 Then
DoCmd.OpenForm "Form--ViewWellData"
End If
Exit Sub
End If
Loop
DoCmd.OpenForm "Form--EditGridWellData", , , , acFormAdd, acDialog
End Sub

I've tried using the CDec function, but that doesn't seem to work. I've read a lot about how Decimal Data Types are not very "VBA-friendly", and I would prefer not to have to deal with them. However, unfortunately, I am LINKED to these tables and have to deal with them as they are.

Any suggestions on what I can do, or what I'm doing wrong? Thanks in advance for any help!

Jon Mulder
Engineering Geologist, California Department of Water Resources, Red Bluff CA

[June7]

Decimal is not actually a Data Type. It is parameter of FieldSize property of Number Data Type

I tried to replicate the issue and can't. FindFirst works fine even if field is set for Decimal FieldSize.

[JonMulder]

Then maybe my syntax is wrong for the pubvarMASTER_SITE_ID. I've "dimmed" it as a variant.

I had seen a lot of chatter about the Decimal Data Type not being for "friendly" in VBA; thought that might be my problem.

[June7]

Variant declaration makes no difference in my code.

I've never set number types as Decimal but my simple procedure is not having issue. I am using Access 2010 but hopefully that doesn't matter.

[Missinglinq]

First off I'd place all of your Dim statements at the beginning of you Sub; I've seen cases where having Dim statements in the body of the Sub rather than at the top of the Sub caused problems. Then replace

Dim rstGridWells As Recordset

with

Dim rstGridWells As DAO.Recordset

and see what happens. I'd leave the above in place, as stated, even if it doesn't resolve the current problem. It should be in place in this manner, regardless.

It this doesn't work, try replacing

rstGridWells.FindFirst "MASTER_SITE_ID = " & CDec(pubvarMASTER_SITE_ID)

with

rstGridWells.FindFirst "MASTER_SITE_ID = """ & CDec(pubvarMASTER_SITE_ID) & """"

and see what happens.

Linq ;0)>

[June7]

If Master_Site_ID is a number field then the double quote (or apostrophe) delimiters will cause error. Did you try simply:

rstGridWells.FindFirst "MASTER_SITE_ID = " & pubvarMASTER_SITE_ID


If it is a text data type then the CDec conversion should not be necessary.