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
