Recordset Navigation problem - MoveNext do not execute

[EvaH]

I would like to access recordset object in the entire module. I have made the recordset global, but the MoveNext is not executing. Please help! Any ideas as to why.

Thank you in advance!

[Stewart Ross]

Could you post the code for what you are trying to achieve? I'd be certain that MoveNext is working fine - it is how you are trying to apply your recordset that is the problem. If you are using a form and create a recordset using recordsetclone, the two recordsets are entirely independent. Movenext in your cloned recordset will not cause any change in the Form's recordset.

-Stewart

[EvaH]

Thank you for your help! Your help is truly appreciated!

My code follows:

 
Option Compare Database
Public varBookmark As Variant
Private rs1 As ADODB.Recordset
Public lngTotalRec As Long
 
 
 '--WHEN THE USER CLICK THE VIEW BUTTON, BASED ON THE '--SELECTION CRITERIA THE QUERY IS BUILT.
 
Private Sub ViewCmd_Click()
 
Debug.Print Form.Name
 
DoCmd.SetWarnings False
 
If IsNull(Me.clientid) Then
   MsgBox "Please enter or select a CLIENT NAME"
   Exit Sub
End If
 
Dim Conn As ADODB.Connection
Set Conn = CurrentProject.Connection
'Dim rs1 As New ADODB.Recordset
'rs1.ActiveConnection = Conn
 
Set rs1 = New ADODB.Recordset
rs1.ActiveConnection = Conn
 
 
Dim strSQL As String
Dim blnCalcTotalRecs As Boolean
blnCalcTotalRecs = True
 
Debug.Print Me.clientid
Debug.Print Me.JobNumber
Debug.Print Me.CampaignType
Debug.Print Me.MailerStatus
 
Debug.Print Forms!frmclientmailschedule!clientid
Debug.Print Forms!frmclientmailschedule!CampaignType
Debug.Print Forms!frmclientmailschedule!MailerStatus
 
 
If Not IsNull(Me.JobNumber) Or _
   Not IsNull(Me.CampaignType) Or _
   Not IsNull(Me.MailerStatus) Then
   If Not IsNull(Me.JobNumber) Then
      strSQL = "select *" & _
                " from tblClientMailSchedule" & _
                " where clientid = " & Me.clientid & _
                " and jobnumber = '" & Me.JobNumber & "';"
 
   Else
       If Not IsNull(Me.CampaignType) And Not IsNull(Me.MailerStatus) Then
          strSQL = "select *" & _
                " from tblClientMailSchedule" & _
                " where clientid = " & Me.clientid & _
                " and campaigntype = '" & Me.CampaignType & "'" & _
                " and mailerstatus = '" & Me.MailerStatus & "'" & _
                " order by startdate;"
       Else
          If Not IsNull(Me.CampaignType) Then
             strSQL = "select *" & _
                " from tblClientMailSchedule" & _
                 " where clientid = " & Me.clientid.Value & _
                " and campaigntype = '" & Me.CampaignType.Value & "'" & _
                " order by startdate;"
          Else
             If Not IsNull(Me.MailerStatus) Then
                strSQL = "select *" & _
                   " from tblClientMailSchedule" & _
                   " where clientid = " & Me.clientid.Value & _
                   " and mailerstatus = '" & Me.MailerStatus.Value & "'" & _
                   " order by startdate;"
             End If
          End If
       End If
   End If
Else
   strSQL = "select * " & _
            "from tblClientMailSchedule" & _
            " where clientid = " & Me.clientid.Value & _
            " order by startdate ; "
End If
 
Debug.Print strSQL
 
rs1.Open strSQL, Conn, adOpenKeyset, adLockOptimistic
 
If rs1.BOF Or rs1.EOF Then
   MsgBox "NO SCHEDULE EXIST FOR CLIENT - Please verify your selection criteria"
   Exit Sub
End If
 
Call Move_Record_To_Form
 
 
End Sub
 
 
 '-- THE MOVE_RECORD_TO_FORM, MOVES THE DATA OF THE '--RECORDSET TO THE FORM
 
Public Sub Move_Record_To_Form()
 
'Public Sub Move_Record_To_Form(ByRef rs1 As ADODB.Recordset)
 
clientid.Value = rs1![clientid]
JobNumber.Value = rs1![JobNumber]
CampaignType.Value = rs1![CampaignType]
StartDate.Value = rs1![StartDate]
EndDate.Value = rs1![EndDate]
MailerStatus.Value = rs1![MailerStatus]
MailingName.Value = rs1![MailingName]
TriggerSegment.Value = rs1![TriggerSegment]
TSName.Value = rs1![TSName]
MailingFrequency.Value = rs1![MailingFrequency]
NbrMailingDay.Value = rs1![NbrMailingDay]
DataSource.Value = rs1![DataSource]
RemoteAccess.Value = rs1![RemoteAccess]
reportid.Value = rs1![reportid]
NbrDLDay.Value = rs1![NbrDLDay]
Comments.Value = rs1![Comments]
'ScheduleID.Value = rs1![ScheduleID]
 
 
Dim position
Dim SearchString As String
 
If Not IsNull(rs1!MailingDays) Then
   SearchString = rs1!MailingDays
 
   position = InStr(1, SearchString, "1", 1)
   If position > 0 Then
      ChkMailSun.Value = 1
   End If
 
   position = InStr(1, SearchString, "2", 1)
   If position > 0 Then
      ChkMailMon.Value = 1
   End If
 
   position = InStr(1, SearchString, "3", 1)
   If position > 0 Then
      ChkMailTue.Value = 1
   End If
 
   position = InStr(1, SearchString, "4", 1)
   If position > 0 Then
      ChkMailWed.Value = 1
   End If
 
   position = InStr(1, SearchString, "5", 1)
   If position > 0 Then
      ChkMailThu.Value = 1
   End If
 
   position = InStr(1, SearchString, "6", 1)
   If position > 0 Then
      ChkMailFri.Value = 1
   End If
 
   position = InStr(1, SearchString, "7", 1)
   If position > 0 Then
      ChkMailSat.Value = 1
   End If
End If
 
If Not IsNull(rs1!DownloadDays) Then
   SearchString = rs1!DownloadDays
 
   position = InStr(1, SearchString, "1", 1)
   If position > 0 Then
      ChkDLSun.Value = 1
   End If
 
   position = InStr(1, SearchString, "2", 1)
   If position > 0 Then
      ChkDLMon.Value = 1
   End If
 
   position = InStr(1, SearchString, "3", 1)
   If position > 0 Then
      ChkDLTue.Value = 1
   End If
 
   position = InStr(1, SearchString, "4", 1)
   If position > 0 Then
      ChkDLWed.Value = 1
   End If
 
   position = InStr(1, SearchString, "5", 1)
   If position > 0 Then
      ChkDLThu.Value = 1
   End If
 
   position = InStr(1, SearchString, "6", 1)
   If position > 0 Then
      ChkDLFri.Value = 1
   End If
 
   position = InStr(1, SearchString, "7", 1)
   If position > 0 Then
      ChkDLSat.Value = 1
   End If
End If
 
'Dim varBookmark As Variant
rs1.MoveLast
lngTotalRec = rs1.RecordCount
rs1.MoveFirst
varBookmark = rs1.Bookmark
Me.txtRecordNo = "Record " & Me.CurrentRecord & " of " & lngTotalRec
 
FirstRecBtn.Enabled = True
PrevRecBtn.Enabled = True
NextRecBtn.Enabled = True
LastRecBtn.Enabled = True
 
End Sub
 
 '--IF THE USER CLICK THE NEXT NAVIGATION BUTTON THE '--FOLLOWING CODE IS EXECUTED. THIS IS WHERE THE '--MOVENEXT COMMAND APPEAR TO NOT EXECUTE.  THE '--ME.CURRENTRECORD IS NOT UPDATING ON THE FORM.
 
Private Sub NextRecbtn_Click()
 
rs1.MoveNext
 
Me.txtRecordNo = "Record " & Me.CurrentRecord & " of " & lngTotalRec
 
Call Move_Record_To_Form()
 
MsgBox "Move To Next Record"
 
 
End Sub
 
 

[Stewart Ross]

Hi again. Can I check that the controls in your form are unbound - that is, that they are not associated with a specific recordsource table or query? You appear to be looking up records from a recordset then, in your Move_Record_To_Form function, setting each of your controls to the current recordset record. This will only work if the form is unbound - otherwise you will be setting values of the current record associated withyour form to values from a different record, with the likely outcome of creating duplicates which violate relational constraints.

Assuming it is an unbound form, when Movenext is called, you update the record count (line 227). Does this change as you would expect? If it does, then at least you know that the recordset has advanced by one.

What happens when you reach end of file? Your Movenext at present is followed by your Move_Record_To_Form without testing for rs1.eof, which must happen at some stage when you keep moving from record to record.

As an observation I would point out that the approach you are taking is hard for others to verify. In my opinion, it is simpler instead of using unbound controls to change the recordsource of a bound form so that the recordsource becomes your SQL statement, meaning that Access will take care of displaying records, moving from one to the next, dealing with EOF, and so on.

You can do this after you form your SQL string just by setting Me.Recordsource = strSQL. There are occasions when it may be essential to work on an unbound form, but it does make it complicated to do simple things like moving from one record to the next.

-Stewart

[EvaH]

Hi Stewart

Thanks so much for your help!

My form is bound, so do you think it is best to use an unbound form?

[MOCaseA]

I think what he is trying to say is that you need to verify that each source field is unbound... I may be wrong though...

[EvaH]

I am new to access...so please excuse my ignorance.

So should the form be a bound form, the form is currently bound. What do you mean by "source field unbound?

[Stewart Ross]

Hi. You are making life very complicated for yourself; you cannot use a bound form and then try to fill records from a code recordset. What you are actually doing is overwriting the currently-bound record each time you move a record from your recordset. There is no need at all to do any of this; as I mentioned, you can replace the form's recordsource with your SQL statement. However, these are all advanced techniques, and in my opinion you are trying to do things for which you have not got enough experience as yet.

Be aware, as I mentioned in my reply, that if you set the current record in your bound form to values already existing in your recordset you will create a duplicate record - you are NOT retrieving that specific record at all, you are creating a NEW record that is an exact DUPLICATE of the one you've moved.

This cannot work, as whatever the key fields are you have just set another record to the same key values, which Access will not allow (if it is set up with the correct relationships).

I am NOT recommending you use an unbound form, as it's so much work doing it like this - but what you are attempting to do will only work if your form IS unbound.

I am recommending a complete rethink of your approach; you can use the recordset's Clone facilities to generate a replica of the form's recordset, use the recordset's Find function to find any matching records, and its Bookmark properties to match the form back to the records found - just search MSDN for recordset clone and bookmark references. However, in my opinion you've a lot of backtracking to do before you will understand how to do it.

-Stewart