Code from Working Form used for another that doesn't work!!

[jdegeorge]

Hi

I have a form that works perfectly based on the following code in the ON LOAD property:

Private Sub Form_Load()

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

    Me.msgContact.Visible = False
    Me.msgOwner.Visible = False
    
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim sql As String

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

    Set db = CurrentDb()
    Set rs = db.OpenRecordset(sql, dbOpenDynaset)
        
    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
    End If

End Sub

I needed to create a similar form for another table in that database, so I took this code and changed the table references:

Private Sub Form_Load()

    Me.RecordSource = "SELECT tblMilestones.* FROM tblMilestones WHERE tblMilestones.DateClosed Is Not Null " _
        & "And tblMilestones.Conversion) = [Forms]![frmSignOn]![ctlConversion] ORDER BY tblMilestones.[Milestone#];"

    Me.msgContact.Visible = False
    Me.msgOwner.Visible = False
    
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim sql As String

    sql = "SELECT Count(tblMilestones.MilestoneID) AS vCount FROM tblMilestones "
    sql = sql & "WHERE tblMilestones.Milestone_Status = 2 AND tblMilestones.Conversion = [Forms]![frmSignOn]![ctlConversion];"
    
    Set db = CurrentDb()
    Set rs = db.OpenRecordset(sql, dbOpenDynaset)
    
    MsgBox (rs!vcount)
    
    If rs!vcount = 0 Then
        DoCmd.Close acForm, Me.NAME, acSaveYes
        [Forms]![frmMilestoneMenu].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
    End If

End Sub

When I open the 2nd form, all the fields show "#NAME?" as the value.

I have done this code-copying to create other forms sucessfully, but I can't get this one to work for the life of me.

Any ideas? I'm probably missing something VERY obvious.

Thanks!!

Jim DeGeorge

 

[jdegeorge]

Hi

Thanks for anyone who tried to look at this. It was something very obvious!

    sql = "SELECT Count(tblMilestones.MilestoneID) AS vCount FROM tblMilestones "
    sql = sql & "WHERE tblMilestones.Milestone_Status = 2 AND tblMilestones.Conversion = [Forms]![frmSignOn]![ctlConversion];"

should have been

    sql = "SELECT Count(tblMilestones.MilestoneID) AS vCount FROM tblMilestones "
    sql = sql & "WHERE tblMilestones.Milestone_Status = 2 AND tblMilestones.Conversion = [red]'" & [/red][Forms]![frmSignOn]![ctlConversion][red] & "'[/red];"

It was staring me right in the face!!!


Jim DeGeorge

 

[dbelsey]

Hi, jdegeorge,

You might want to stick in a couple lines:

Set rs = Nothing
Set db = Nothing

before you leave your sub just to keep your memory from leaking all over your desk.


Dave

 

[jdegeorge]

dbelsey

Thanks! Wouldn't want to clean up that mess!

Jim DeGeorge