V
venus as a boy
Hello,
I am creating a database using data imported from Excel.
Everything was going fine until last week, record 4429 was mistakenly erased. (Something like at the end of the day we started typing in the info but stopped, so the auto number was assigned but the record was not kept)
It's very helpful for us to have the record number (on the bottom of the screen which says Record: 2234 of 4491) so this is why I was trying to correct it.
My fix: Copy records 1 - 4428 to a new table, enter a new 4429, and then copy records 4430 through 4459 to my new table.
New Problem: I have a VBA code which I was using with a form to prevent duplicates by SSN. It stopped working when I got a new table. Here it is:
Private Sub SSN_BeforeUpdate(Cancel As Integer)
Dim strMsg As String
Dim iAns As Integer
Dim rs As DAO.Recordset ' reserve a variable
Set rs = Me.RecordsetClone ' set it to this form's recordsource
rs.FindFirst "[SSN] = '" & Me!SSN & "'" ' is this a dup?
If Not rs.NoMatch Then ' yes it is
Cancel = True ' don't enter the SSN in the table
strMsg = "This SSN already exists!" & _
vbCrLf & "ID: " & rs![New ID] & vbCrLf & _
"Name: " & rs![Last] & ", " & rs![First] & _
vbCrLf & "SSN: " & rs![SSN] & vbCrLf & "DOB: " & rs![DOB]
iAns = MsgBox(strMsg, vbYesCancel)
If iAns = vbYes Then
Me.Undo ' erase the data on the form
Me.Bookmark = rs.Bookmark ' move to the record
Else
Me.SSN.Undo ' just erase the SSN
End If
End If
End Sub
1: Please simplify this code. I think this is unnecessary:
rs.FindFirst "[SSN] = '" & Me!SSN & "'" ' is this a dup?
If Not rs.NoMatch Then ' yes it is
Cancel = True ' don't enter the SSN in the table
Since all the code does is when I enter the SSN it says either nothing at all or if it already exists it says 'This SSN already exists...[record info]' I don't think the above lines are necessary. (I originally had different plans but this one worked out well which is why unnecessary code is there)
2: The Code is not working at all with the new table!! I renamed it after the old table, still not working. What I get is "Module Not Found" and then MS Visual Basic displays the code with 'Private Sub SSN_BeforeUpdate(Cancel As Integer)' highlighted.
I am creating a database using data imported from Excel.
Everything was going fine until last week, record 4429 was mistakenly erased. (Something like at the end of the day we started typing in the info but stopped, so the auto number was assigned but the record was not kept)
It's very helpful for us to have the record number (on the bottom of the screen which says Record: 2234 of 4491) so this is why I was trying to correct it.
My fix: Copy records 1 - 4428 to a new table, enter a new 4429, and then copy records 4430 through 4459 to my new table.
New Problem: I have a VBA code which I was using with a form to prevent duplicates by SSN. It stopped working when I got a new table. Here it is:
Private Sub SSN_BeforeUpdate(Cancel As Integer)
Dim strMsg As String
Dim iAns As Integer
Dim rs As DAO.Recordset ' reserve a variable
Set rs = Me.RecordsetClone ' set it to this form's recordsource
rs.FindFirst "[SSN] = '" & Me!SSN & "'" ' is this a dup?
If Not rs.NoMatch Then ' yes it is
Cancel = True ' don't enter the SSN in the table
strMsg = "This SSN already exists!" & _
vbCrLf & "ID: " & rs![New ID] & vbCrLf & _
"Name: " & rs![Last] & ", " & rs![First] & _
vbCrLf & "SSN: " & rs![SSN] & vbCrLf & "DOB: " & rs![DOB]
iAns = MsgBox(strMsg, vbYesCancel)
If iAns = vbYes Then
Me.Undo ' erase the data on the form
Me.Bookmark = rs.Bookmark ' move to the record
Else
Me.SSN.Undo ' just erase the SSN
End If
End If
End Sub
1: Please simplify this code. I think this is unnecessary:
rs.FindFirst "[SSN] = '" & Me!SSN & "'" ' is this a dup?
If Not rs.NoMatch Then ' yes it is
Cancel = True ' don't enter the SSN in the table
Since all the code does is when I enter the SSN it says either nothing at all or if it already exists it says 'This SSN already exists...[record info]' I don't think the above lines are necessary. (I originally had different plans but this one worked out well which is why unnecessary code is there)
2: The Code is not working at all with the new table!! I renamed it after the old table, still not working. What I get is "Module Not Found" and then MS Visual Basic displays the code with 'Private Sub SSN_BeforeUpdate(Cancel As Integer)' highlighted.