The Microsoft Jet database engine does not recognize <name> 2

[jdegeorge]

Hi

I'm trying to open FORM2 pointing to a record that was selected in FORM1.

In FORM2's ON LOAD property, I have the following code

    Me.RecordSource = "SELECT tblGaps.* FROM tblGaps WHERE tblGaps.DateClosed Is Null " _
        & "AND tblGaps.Conversion = [Forms]![frmSignOn]![ctlConversion] " _
        & "ORDER BY tblGaps.[Gap#];"

    Me.msgContraContact.Visible = False
    Me.msgContact.Visible = False
    Me.msgOwner.Visible = False
    Me.msgNewOwner.Visible = False

    Dim db As dao.Database, rs As dao.Recordset, rsNUM As dao.Recordset, sql As String, frm As Form, rst As dao.Recordset

    sql = "SELECT Count(tblGaps.GapID) AS vCount FROM tblGaps "
    sql = sql & "WHERE tblGaps.Gap_Status = 1 And tblGaps.Conversion = '" & [Forms]![frmSignOn]![ctlConversion] & "';"

    Set db = CurrentDb()
    Set rs = db.OpenRecordset(sql, dbOpenDynaset)
    Set frm = Forms!frmGapEditSelection
    Set rst = Me.RecordsetClone
    
    If rs!vcount = 0 Then
        DoCmd.Close acForm, Me.NAME, acSaveYes
        [Forms]![frmGapMenu].Visible = True

        Dim msg1, DgDef1, Response1 As Variant
        Beep
        msg1 = "There is nothing to edit for this option." _
                & vbCrLf & "Click OK to return to the previous menu.  Thank you!"
        DgDef1 = MB_OK + MB_ICONINFORMATION + MB_DEFBUTTON2
        Response1 = MsgBox(msg1, DgDef1, Title)
    Else
        Me!lblClock.Caption = Format(Now, "dddd, mmmm d, yyyy, hh:mm:ss AMPM")
        Me.TimerInterval = 1000
        rst.FindFirst "[ctlGapNum] = " & frm![ctlGapNumSelect]
        Me.Bookmark = rst.Bookmark
    End If
    
Set db = Nothing
Set rs = Nothing
Set frm = Nothing
Set rst = Nothing

When I make a selection on FORM1, FORM1 hides, FORM2 opens, but I get an error message that "he Microsoft Jet database engine does not recognize 'ctlGapNum'."

Any suggestions/ideas would be appreciated.

Thanks!


Jim DeGeorge

 

[snyperx3]

Is the variable IN THE TABLE named ctlGapNum?

-Pete

 

[jdegeorge]

No. That's the TEXT BOX's name on the form. The field in the table is called "Gap#".

Jim DeGeorge

 

[snyperx3]

Try?

rst.FindFirst "[Gap#] = " & frm![ctlGapNumSelect]

-Pete

 

[jdegeorge]

Pete

Getting closer. I changed the one line to

        rst.FindFirst "[Gap#] = " & frm![ctlGapNumSelect]

but am now getting the same error message but with a different twist

The Microsoft Jet database engine does not recognize 'BKONE' as a valid field name or expression."

'BKONE' is the first 5 characters of the [Gap#] field. Am I experiencing a NUMBER vs. TEXT issue?


Jim DeGeorge

 

[jdegeorge]

I changed the line to

rst.FindFirst [Gap#] = frm![ctlGapNumSelect]

and FORM2 opens without error, but it doesn't find that record. It opens to the first record in "Me.Recordsource".

Question...in the code I DIM a string called SQL and set it to a number

    Dim db As dao.Database, rs As dao.Recordset, rsNUM As dao.Recordset, sql As String, frm As Form, rst As dao.Recordset

    sql = "SELECT Count(tblGaps.GapID) AS vCount FROM tblGaps "
    sql = sql & "WHERE tblGaps.Gap_Status = 1 And tblGaps.Conversion = '" & [Forms]![frmSignOn]![ctlConversion] & "';"

    Set db = CurrentDb()
    Set rs = db.OpenRecordset(sql, dbOpenDynaset)
    Set frm = Forms!frmGapEditSelection
    Set rst = Me.RecordsetClone

When RST = the recordset clone, is it cloning the original recordset established at the top of the code or is it cloning the record count from the SQL string?


Jim DeGeorge

 

[PHV]

You may try this:[tt]
rst.FindFirst "[Gap#]='" & frm![ctlGapNumSelect] & "'"[/tt]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[snyperx3]

the recordset clone is a clone of the FORM's recordset because you are using 'Me'.

Does Gap# store a string or a number? i took from the field name that it was a number, but if it contains "BKONE" you now have me thinking its a string, in which case you would need to use:

rst.FindFirst "[Gap#] = '" & frm![ctlGapNumSelect] & "'"

-Pete

 

[jdegeorge]

PHV and Pete

C'est magnifique! You both provided the same solution at just about the same time! Enjoy the stars!

I ALWAYS, let me stress that, A-L-W-A-Y-S, have trouble with strings and numbers.

Thanks!!!

Jim DeGeorge