form sequence code not working

  • Thread starter Thread starter bbypookins
  • Start date Start date
B

bbypookins

I have the following code in my form (I included all of it just in case).
Under Division_AfterUpdate, the SeqNumber is supposed to automatically
increase by 1 each time a particular Division is selected, but it is only
going to 002 and then just keeps repeating 002 each time--it won't increase
after that. I am a beginner with this and was given this code on here, so I
have no idea what is wrong.

Option Compare Database

Private Sub cboFindRecord_AfterUpdate()
With Me.RecorsetClone
.FindFirst "[SeqNumber] = " & Me.SeqNumber & " AND [Division] = '" &
Me.Division & "'"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End Sub

Private Sub cmdRunSearch_Click()
On Error GoTo HandleError

If Nz(Me.cboFindRecord, "") = "" Then
MsgBox "Please enter a Division to search for"
Me.cboFindRecord.SetFocus
Exit Sub
End If

If Nz(Me.txtSequenceSearch, "") = "" Then
MsgBox "Please enter a Sequence Number to search for"
Me.txtSequenceSearch.SetFocus
Exit Sub
End If

With Me.RecordsetClone
.FindFirst "[Division]=""" & Me.cboFindRecord & """" & _
" AND [SeqNumber]=""" & Me.txtSequenceSearch & """"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
Else
MsgBox "No matching record found"
End If
End With

Exit_cmdRunSearch_Click:
Exit Sub

HandleError:
MsgBox Err.Description
Resume Exit_cmdRunSearch_Click

End Sub

Private Sub Division_AfterUpdate()
With Me
.SeqNumber = Format(Nz(DLookup("[SeqNumber]", "tblRPALog",
"[Division] = """ & .Division & """"), 0) + 1, "000")
End With
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)

End Sub

Private Sub Form_Current()
With Me
If .NewRecord Then
.Division.Enabled = True
.Division.Locked = False
Else
.Division.Enabled = False
.Division.Locked = True
End If

End With
End Sub

Private Sub PreviousRecord_Click()
On Error GoTo Err_PreviousRecord_Click


DoCmd.GoToRecord , , acPrevious

Exit_PreviousRecord_Click:
Exit Sub

Err_PreviousRecord_Click:
MsgBox Err.Description
Resume Exit_PreviousRecord_Click

End Sub
Private Sub Command72_Click()
On Error GoTo Err_Command72_Click


DoCmd.GoToRecord , , acNext

Exit_Command72_Click:
Exit Sub

Err_Command72_Click:
MsgBox Err.Description
Resume Exit_Command72_Click

End Sub
Private Sub NextRecord_Click()
On Error GoTo Err_NextRecord_Click


DoCmd.GoToRecord , , acNext

Exit_NextRecord_Click:
Exit Sub

Err_NextRecord_Click:
MsgBox Err.Description
Resume Exit_NextRecord_Click

End Sub
Private Sub NewRecord_Click()
On Error GoTo Err_NewRecord_Click


DoCmd.GoToRecord , , acNewRec

Exit_NewRecord_Click:
Exit Sub

Err_NewRecord_Click:
MsgBox Err.Description
Resume Exit_NewRecord_Click

End Sub
Private Sub QuitApplication_Click()
On Error GoTo Err_QuitApplication_Click


DoCmd.Quit

Exit_QuitApplication_Click:
Exit Sub

Err_QuitApplication_Click:
MsgBox Err.Description
Resume Exit_QuitApplication_Click

End Sub

Private Sub SeqNumber_AfterUpdate()
.Division.Enabled = True
.Division.Locked = False

With Me.RecorsetClone
.FindFirst "[SeqNumber] = " & Me.SeqNumber
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

End Sub
 
Try changing the DLookup to DMax.
--
Marsh
MVP [MS Access]

I have the following code in my form (I included all of it just in case).
Under Division_AfterUpdate, the SeqNumber is supposed to automatically
increase by 1 each time a particular Division is selected, but it is only
going to 002 and then just keeps repeating 002 each time--it won't increase
after that. I am a beginner with this and was given this code on here, so I
have no idea what is wrong.

Option Compare Database

Private Sub cboFindRecord_AfterUpdate()
With Me.RecorsetClone
.FindFirst "[SeqNumber] = " & Me.SeqNumber & " AND [Division] = '" &
Me.Division & "'"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End Sub

Private Sub cmdRunSearch_Click()
On Error GoTo HandleError

If Nz(Me.cboFindRecord, "") = "" Then
MsgBox "Please enter a Division to search for"
Me.cboFindRecord.SetFocus
Exit Sub
End If

If Nz(Me.txtSequenceSearch, "") = "" Then
MsgBox "Please enter a Sequence Number to search for"
Me.txtSequenceSearch.SetFocus
Exit Sub
End If

With Me.RecordsetClone
.FindFirst "[Division]=""" & Me.cboFindRecord & """" & _
" AND [SeqNumber]=""" & Me.txtSequenceSearch & """"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
Else
MsgBox "No matching record found"
End If
End With

Exit_cmdRunSearch_Click:
Exit Sub

HandleError:
MsgBox Err.Description
Resume Exit_cmdRunSearch_Click

End Sub

Private Sub Division_AfterUpdate()
With Me
.SeqNumber = Format(Nz(DLookup("[SeqNumber]", "tblRPALog",
"[Division] = """ & .Division & """"), 0) + 1, "000")
End With
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)

End Sub

Private Sub Form_Current()
With Me
If .NewRecord Then
.Division.Enabled = True
.Division.Locked = False
Else
.Division.Enabled = False
.Division.Locked = True
End If

End With
End Sub

Private Sub PreviousRecord_Click()
On Error GoTo Err_PreviousRecord_Click


DoCmd.GoToRecord , , acPrevious

Exit_PreviousRecord_Click:
Exit Sub

Err_PreviousRecord_Click:
MsgBox Err.Description
Resume Exit_PreviousRecord_Click

End Sub
Private Sub Command72_Click()
On Error GoTo Err_Command72_Click


DoCmd.GoToRecord , , acNext

Exit_Command72_Click:
Exit Sub

Err_Command72_Click:
MsgBox Err.Description
Resume Exit_Command72_Click

End Sub
Private Sub NextRecord_Click()
On Error GoTo Err_NextRecord_Click


DoCmd.GoToRecord , , acNext

Exit_NextRecord_Click:
Exit Sub

Err_NextRecord_Click:
MsgBox Err.Description
Resume Exit_NextRecord_Click

End Sub
Private Sub NewRecord_Click()
On Error GoTo Err_NewRecord_Click


DoCmd.GoToRecord , , acNewRec

Exit_NewRecord_Click:
Exit Sub

Err_NewRecord_Click:
MsgBox Err.Description
Resume Exit_NewRecord_Click

End Sub
Private Sub QuitApplication_Click()
On Error GoTo Err_QuitApplication_Click


DoCmd.Quit

Exit_QuitApplication_Click:
Exit Sub

Err_QuitApplication_Click:
MsgBox Err.Description
Resume Exit_QuitApplication_Click

End Sub

Private Sub SeqNumber_AfterUpdate()
.Division.Enabled = True
.Division.Locked = False

With Me.RecorsetClone
.FindFirst "[SeqNumber] = " & Me.SeqNumber
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

End Sub
Click to expand...
 
That worked. Thanks a million!

Marshall Barton said:
Try changing the DLookup to DMax.
--
Marsh
MVP [MS Access]

I have the following code in my form (I included all of it just in case).
Under Division_AfterUpdate, the SeqNumber is supposed to automatically
increase by 1 each time a particular Division is selected, but it is only
going to 002 and then just keeps repeating 002 each time--it won't increase
after that. I am a beginner with this and was given this code on here, so I
have no idea what is wrong.

Option Compare Database

Private Sub cboFindRecord_AfterUpdate()
With Me.RecorsetClone
.FindFirst "[SeqNumber] = " & Me.SeqNumber & " AND [Division] = '" &
Me.Division & "'"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End Sub

Private Sub cmdRunSearch_Click()
On Error GoTo HandleError

If Nz(Me.cboFindRecord, "") = "" Then
MsgBox "Please enter a Division to search for"
Me.cboFindRecord.SetFocus
Exit Sub
End If

If Nz(Me.txtSequenceSearch, "") = "" Then
MsgBox "Please enter a Sequence Number to search for"
Me.txtSequenceSearch.SetFocus
Exit Sub
End If

With Me.RecordsetClone
.FindFirst "[Division]=""" & Me.cboFindRecord & """" & _
" AND [SeqNumber]=""" & Me.txtSequenceSearch & """"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
Else
MsgBox "No matching record found"
End If
End With

Exit_cmdRunSearch_Click:
Exit Sub

HandleError:
MsgBox Err.Description
Resume Exit_cmdRunSearch_Click

End Sub

Private Sub Division_AfterUpdate()
With Me
.SeqNumber = Format(Nz(DLookup("[SeqNumber]", "tblRPALog",
"[Division] = """ & .Division & """"), 0) + 1, "000")
End With
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)

End Sub

Private Sub Form_Current()
With Me
If .NewRecord Then
.Division.Enabled = True
.Division.Locked = False
Else
.Division.Enabled = False
.Division.Locked = True
End If

End With
End Sub

Private Sub PreviousRecord_Click()
On Error GoTo Err_PreviousRecord_Click


DoCmd.GoToRecord , , acPrevious

Exit_PreviousRecord_Click:
Exit Sub

Err_PreviousRecord_Click:
MsgBox Err.Description
Resume Exit_PreviousRecord_Click

End Sub
Private Sub Command72_Click()
On Error GoTo Err_Command72_Click


DoCmd.GoToRecord , , acNext

Exit_Command72_Click:
Exit Sub

Err_Command72_Click:
MsgBox Err.Description
Resume Exit_Command72_Click

End Sub
Private Sub NextRecord_Click()
On Error GoTo Err_NextRecord_Click


DoCmd.GoToRecord , , acNext

Exit_NextRecord_Click:
Exit Sub

Err_NextRecord_Click:
MsgBox Err.Description
Resume Exit_NextRecord_Click

End Sub
Private Sub NewRecord_Click()
On Error GoTo Err_NewRecord_Click


DoCmd.GoToRecord , , acNewRec

Exit_NewRecord_Click:
Exit Sub

Err_NewRecord_Click:
MsgBox Err.Description
Resume Exit_NewRecord_Click

End Sub
Private Sub QuitApplication_Click()
On Error GoTo Err_QuitApplication_Click


DoCmd.Quit

Exit_QuitApplication_Click:
Exit Sub

Err_QuitApplication_Click:
MsgBox Err.Description
Resume Exit_QuitApplication_Click

End Sub

Private Sub SeqNumber_AfterUpdate()
.Division.Enabled = True
.Division.Locked = False

With Me.RecorsetClone
.FindFirst "[SeqNumber] = " & Me.SeqNumber
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

End Sub
Click to expand...
Click to expand...
 
Back
Top