not able to navigate recordset

[beebelbrox]

Hi, I am new VB programming in Access and I am requesting help with the following code.

This code is attached to a form that will display a specific recordset based in information passed to the form from another form. The problem I am having is that for each of the case statements the proper records (values and Number of records) are being returned but the movement commands do not work, only the first record in the set is displayed. I can use the troubleshooting code to advance the recordset to any point in the file at which the display is blank until I click the cmdMoveFirst button. I’m missing something. I don’t think it is the code for the nav buttons thats' pretty simple stuff. From what research I’ve done I think/suspect, but mostly guess it’s the cursor type and location. Any help would be greatly appreciated, especially tips on trouble shooting this type of problem.

TIA

Option Compare Database
 
Private Sub Form_Load()
 
Dim SQL As String
Dim cmdFormPop As ADODB.Command
Dim cnn As ADODB.Connection
Dim fldUserAssignment As ADODB.Field
'Dim prmValue1 As ADODB.Parameter
Dim S1 As Integer
 
 
    Set cmdFormPop = New Command
    Set cmdFormPop.ActiveConnection = CurrentProject.Connection
 
 
    S1 = gUserAssignment  'pass user ID to parameter value
 
    Debug.Print gFormState
 
    With gRS
        .CursorType = adOpenDynamic
        .CursorLocation = adUseServer
        .LockType = adLockBatchOptimistic
    End With
 
    Select Case gFormState
 
    Case "Unworked"
 
 
        cmdFormPop.CommandType = adCmdText
        cmdFormPop.CommandText = "SELECT CLAMNO,VENDNM,Question1 FROM PPmaster " & _
            "WHERE ((PPmaster.UserAssignment) = ? And (PPmaster.Question1) = 1);"
 
        Set prm0 = cmdFormPop.CreateParameter("prmUserAssignment", adVarChar, adParamInput, 15)
        prm0.Value = S1
 
        cmdFormPop.Parameters.Append prm0
 
        Set gRS = cmdFormPop.Execute
 
 
    Case "Worked"
 
        cmdFormPop.CommandType = adCmdText
        cmdFormPop.CommandText = "SELECT CLAMNO,VENDNM,Question1 FROM PPmaster " & _
            "WHERE ((PPmaster.UserAssignment) = ? And (PPmaster.Question1) <> 1);"
 
        Set prm0 = cmdFormPop.CreateParameter("prmUserAssignment", adVarChar, adParamInput, 15)
        prm0.Value = S1
 
        cmdFormPop.Parameters.Append prm0
 
        Set gRS = cmdFormPop.Execute
 
 
    Case "All"
 
        cmdFormPop.CommandType = adCmdText
        cmdFormPop.CommandText = "SELECT CLAMNO,VENDNM,Question1 FROM PPmaster " & _
            "WHERE (PPmaster.UserAssignment) = ?;"
 
        Set prm0 = cmdFormPop.CreateParameter("prmUserAssignment", adVarChar, adParamInput, 15)
        prm0.Value = S1
 
        cmdFormPop.Parameters.Append prm0
 
        Set gRS = cmdFormPop.Execute
 
    End Select
 
 
    'troulbe shooting code to validatecorrect records are returned
 
    Do Until gRS.EOF
        Debug.Print gRS.Fields(0)
        Debug.Print gRS.Fields(1)
        Debug.Print gRS.Fields(2)
                gRS.MoveNext
    Loop
 
            Debug.Print "record count: "; gRS.RecordCount
 
 
    gRS.MoveFirst
 
    'MasterDisplay is a seperate module that will dispaly all of
    'the fields in a single instance instead of under each move control
 
    'MasterDisplay
 
 
End Sub
Public Sub cmdMoveFirst_Click()
 
    On Error GoTo DbError
 
    'Move to the first record in the result set.
    gRS.MoveFirst
 
        Forms!frmMasterDisplayA!txtCLAMNO = gRS.Fields(0)
        Forms!frmMasterDisplayA!txtQuestion1 = gRS.Fields(1)
        Forms!frmMasterDisplayA!txtVENDNM = gRS.Fields(2)
 
 
        'MasterDisplay is a seperate module that will dispaly all of
            'the fields in a single instance instead of under each move control
 
        'MasterDisplay
 
    Exit Sub
 
DbError:
 
    MsgBox "There was an error retrieving information " & _
        "from the database." _
        & Err.Number & ", " & Err.Description
 
End Sub
 
Public Sub cmdMoveLast_Click()
 
    On Error GoTo DbError
 
    'Move to the last record in the result set.
    gRS.MoveLast
 
        Forms!frmMasterDisplayA!txtCLAMNO = gRS.Fields(0)
        Forms!frmMasterDisplayA!txtQuestion1 = gRS.Fields(1)
        Forms!frmMasterDisplayA!txtVENDNM = gRS.Fields(2)
 
 
        'MasterDisplay is a seperate module that will dispaly all of
            'the fields in a single instance instead of under each move control
 
        'MasterDisplay
 
    Exit Sub
 
DbError:
 
    MsgBox "There was an error retrieving information " & _
        "from the database." _
        & Err.Number & ", " & Err.Description
 
End Sub
 
Public Sub cmdMoveNext_Click()
 
    On Error GoTo DbError
    'Move to the next record in the result set if the cursor is not
    'already at the last record.
    If gRS.AbsolutePosition < _
        gRS.RecordCount Then
 
        gRS.MoveNext
 
        Forms!frmMasterDisplayA!txtCLAMNO = gRS.Fields(0)
        Forms!frmMasterDisplayA!txtQuestion1 = gRS.Fields(1)
        Forms!frmMasterDisplayA!txtVENDNM = gRS.Fields(2)
 
 
        'MasterDisplay is a seperate module that will dispaly all of
            'the fields in a single instance instead of under each move control
 
        'MasterDisplay
 
 
    End If
 
    Exit Sub
 
DbError:
 
    MsgBox "There was an error retrieving information " & _
        "from the database." _
        & Err.Number & ", " & Err.Description
 
End Sub
 
Public Sub cmdMovePrevious_Click()
 
    On Error GoTo DbError
 
    'Move to the previous record in the result set, if the
    'current record is not the first record.
    If gRS.AbsolutePosition > 1 Then
 
        gRS.MovePrevious
 
        Forms!frmMasterDisplayA!txtCLAMNO = gRS.Fields(0)
        Forms!frmMasterDisplayA!txtQuestion1 = gRS.Fields(1)
        Forms!frmMasterDisplayA!txtVENDNM = gRS.Fields(2)
 
 
        'MasterDisplay is a seperate module that will dispaly all of
            'the fields in a single instance instead of under each move control
 
        'MasterDisplay
 
    End If
    Exit Sub
 
DbError:
 
    MsgBox "There was an error retrieving information " & _
        "from the database." _
        & Err.Number & ", " & Err.Description
 
End Sub