Getting error 3022 trying to add record

G

Guest

I am adding records to a table (tblMSRUsed) that records Medical Service
Record (MSR) numbers that have been used. To prevent using an MSR more than
once, I made the MSR column the "Primary Key". When adding a new record, I
trap error 3022. I believe the error should only occur if the MSR is already
in the table when I add a record. Instead I always get error 3022, even if
the new MSR is not already in tblMSRUsed, and can prevent the error only by
completely emptying tblMSRUsed before adding a record. Here is part of my
code. Perhaps someone can see my error please:

Set rsMSR = db.OpenRecordset("tblMSRUsed")

With rsMSR
.AddNew
!MSR = MSRNumber
!PatUniqueID = PatientID$
!ApptDate = AppointmentDate$
!Resource = Provider$
.Update
End With

An Error Handler displays Case Err.Number = 3022

Thank you, Richard
 
M

Marshall Barton

richardb said:
I am adding records to a table (tblMSRUsed) that records Medical Service
Record (MSR) numbers that have been used. To prevent using an MSR more than
once, I made the MSR column the "Primary Key". When adding a new record, I
trap error 3022. I believe the error should only occur if the MSR is already
in the table when I add a record. Instead I always get error 3022, even if
the new MSR is not already in tblMSRUsed, and can prevent the error only by
completely emptying tblMSRUsed before adding a record. Here is part of my
code. Perhaps someone can see my error please:

Set rsMSR = db.OpenRecordset("tblMSRUsed")

With rsMSR
.AddNew
!MSR = MSRNumber
!PatUniqueID = PatientID$
!ApptDate = AppointmentDate$
!Resource = Provider$
.Update
End With

An Error Handler displays Case Err.Number = 3022
Click to expand...


You should post the rest of the procedure.

It sounds like you forget the Exit Sub before the start of
the error handler code.
 
G

Guest

Marshall,

I did not post the entire subroutine, because the sub is long and does a lot
of other things, but here it is if it helps.:

Public Sub ReadInput()
On Error GoTo ErrorHandler
Dim db As Database, rsInput As Recordset, rst As Recordset, tbl As
TableDef, rsMSR As Recordset
Dim strSQLText As String, PatientID$, MSRNumber, AppointmentDate$,
ImportCount%
Dim Diag1$, Diag2$, Diag3$, Provider$, Units(8) As String, i%, CPTRow,
Msg$, Resident$
Set db = CurrentDb
strSQLText = "SELECT F0001, Dx3, CustomDx1, CustomDx2, Agent01, Agent02,
Agent03, " & _
"Agent04, Agent05, Agent06, Agent07, Agent08, PatUniqueID,
Resource, MSR, " & _
"ApptDate, Dx2, Dx1, Resident " & _
"FROM ScannedDataImaging ORDER BY MSR"

If Not DeleteTable("tblTransactions") Then GoTo ReadInput_End
Set rst = db.OpenRecordset("tblTransactions")
Set rsMSR = db.OpenRecordset("tblMSRImportLog")
Set rsInput = db.OpenRecordset(strSQLText)
With rsInput
While Not .EOF
PatientID$ = !PatUniqueID: MSRNumber = !MSR
AppointmentDate$ = Format$(!ApptDate, "mmddyyyy")
Provider$ = varLookup("PROVIDERCODE", "dbo_Resources",
"RESOURCEIDDESC = '" & !Resource & "'")
Diag1$ = DiagnosisLookup(!Dx1, !CustomDx1, !CustomDx2)
Diag2$ = DiagnosisLookup(!Dx2, !CustomDx1, !CustomDx2)
Diag3$ = DiagnosisLookup(!Dx3, !CustomDx1, !CustomDx2)
Units(1) = !Agent01: Units(2) = !Agent02: Units(3) = !Agent03:
Units(4) = !Agent04
Units(5) = !Agent05: Units(6) = !Agent06: Units(7) = !Agent07:
Units(8) = !Agent08
CPTRow = !F0001: Resident$ = !Resident

With rsMSR
.AddNew
!MSR = MSRNumber
!PatUniqueID = PatientID$
!ApptDate = AppointmentDate$
!Resource = Provider$
.Update
End With

With rst
For i% = 1 To 29
If Mid$(CPTRow, i%, 1) = 1 Then
.AddNew
'Record CPT Code
!MSR = MSRNumber
!PatUniqueID = PatientID$
!Procedure = varLookup("CPTCode", "tlkpProcedures",
"RowNumber = " & i%)
!Diag1 = Diag1$: !Diag2 = Diag2$: !Diag3 = Diag3$: !Diag4 = ""
!Units = 1
!DateOfService = AppointmentDate$
!Modifiers = ""
!Provider = Provider$
!Resident = Resident$
.Update
End If
Next i%
'Record Agents and Units
For i% = 1 To 8
If Units(i%) > 0 Then
.AddNew
!MSR = MSRNumber
!PatUniqueID = PatientID$
!Procedure = varLookup("Agent", "tlkpAgents", "RowNumber
= " & CStr(i%))
!Diag1 = Diag1$: !Diag2 = Diag2$: !Diag3 = Diag3$:
!Diag4 = ""
!Units = Units(i%)
!DateOfService = AppointmentDate$
!Modifiers = ""
!Provider = Provider$
!Resident = Resident$
.Update
End If
Next i%
End With
ImportCount% = ImportCount% + 1
ProcessNextForm:
.MoveNext
Wend
End With
rst.Close
Msg$ = ImportCount% & " encounter form" & IIf(ImportCount% = 1, " was ",
"s were ") & _
"interpreted and imported."
MsgBox Msg$, vbInformation, "Import of Scanned data"

ReadInput_End:
Set db = Nothing: Set rsInput = Nothing: Set rst = Nothing
strSQLText = vbNullString
Exit Sub

ErrorHandler:
If Err.Number = 3022 Then
Msg$ = "MSR #" & MSRNumber & " was previously scanned and will be
skipped"
If MsgBox(Msg$, vbInformation + vbOKCancel, "Duplicate Scan") = vbOK
Then
Resume ProcessNextForm
Else: Resume ReadInput_End
End If
Else
MsgBox Err.Number & " - " & Err.Description, vbCritical, "Error in
ReadInput"
Resume ReadInput_End
End If

End Sub
 
M

Marshall Barton

You were right, there is a lot going on in there. It's a
little hard to read with the archaic syntax, but I don't see
anything obviously wrong.

I will question the implicit typing of some variables as
Variant in the Dim statememts. Is it possible that you are
picking up an unexpected Null somewhere along the line?

The only other thought I have is that maybe the table has
another field with a unique index that's getting in the
way??

At this point, all I can suggest is that you single step
through the code and watch what happens.

Sorry I couldn't be more helpful.
 
G

Guest

Hi Marshall,

The table I open with rsMSR has MSR as the "key" field; otherwise nothing is
indexed. However, I will try to create the table again and let you know.

In the mean time, other than my fondness for declaring variables using "$"
or "%" (left over from Digital Research Basic), which I know makes "real"
programmers shudder, please comment if you see anything obvious in my syntax
that I could improve.

Richard

Marshall Barton said:
You were right, there is a lot going on in there. It's a
little hard to read with the archaic syntax, but I don't see
anything obviously wrong.

I will question the implicit typing of some variables as
Variant in the Dim statememts. Is it possible that you are
picking up an unexpected Null somewhere along the line?

The only other thought I have is that maybe the table has
another field with a unique index that's getting in the
way??

At this point, all I can suggest is that you single step
through the code and watch what happens.

Sorry I couldn't be more helpful.
--
Marsh
MVP [MS Access]

I did not post the entire subroutine, because the sub is long and does a lot
of other things, but here it is if it helps.:

Public Sub ReadInput()
On Error GoTo ErrorHandler
Dim db As Database, rsInput As Recordset, rst As Recordset, tbl As
TableDef, rsMSR As Recordset
Dim strSQLText As String, PatientID$, MSRNumber, AppointmentDate$,
ImportCount%
Dim Diag1$, Diag2$, Diag3$, Provider$, Units(8) As String, i%, CPTRow,
Msg$, Resident$
Set db = CurrentDb
strSQLText = "SELECT F0001, Dx3, CustomDx1, CustomDx2, Agent01, Agent02,
Agent03, " & _
"Agent04, Agent05, Agent06, Agent07, Agent08, PatUniqueID,
Resource, MSR, " & _
"ApptDate, Dx2, Dx1, Resident " & _
"FROM ScannedDataImaging ORDER BY MSR"

If Not DeleteTable("tblTransactions") Then GoTo ReadInput_End
Set rst = db.OpenRecordset("tblTransactions")
Set rsMSR = db.OpenRecordset("tblMSRImportLog")
Set rsInput = db.OpenRecordset(strSQLText)
With rsInput
While Not .EOF
PatientID$ = !PatUniqueID: MSRNumber = !MSR
AppointmentDate$ = Format$(!ApptDate, "mmddyyyy")
Provider$ = varLookup("PROVIDERCODE", "dbo_Resources",
"RESOURCEIDDESC = '" & !Resource & "'")
Diag1$ = DiagnosisLookup(!Dx1, !CustomDx1, !CustomDx2)
Diag2$ = DiagnosisLookup(!Dx2, !CustomDx1, !CustomDx2)
Diag3$ = DiagnosisLookup(!Dx3, !CustomDx1, !CustomDx2)
Units(1) = !Agent01: Units(2) = !Agent02: Units(3) = !Agent03:
Units(4) = !Agent04
Units(5) = !Agent05: Units(6) = !Agent06: Units(7) = !Agent07:
Units(8) = !Agent08
CPTRow = !F0001: Resident$ = !Resident

With rsMSR
.AddNew
!MSR = MSRNumber
!PatUniqueID = PatientID$
!ApptDate = AppointmentDate$
!Resource = Provider$
.Update
End With

With rst
For i% = 1 To 29
If Mid$(CPTRow, i%, 1) = 1 Then
.AddNew
'Record CPT Code
!MSR = MSRNumber
!PatUniqueID = PatientID$
!Procedure = varLookup("CPTCode", "tlkpProcedures",
"RowNumber = " & i%)
!Diag1 = Diag1$: !Diag2 = Diag2$: !Diag3 = Diag3$: !Diag4 = ""
!Units = 1
!DateOfService = AppointmentDate$
!Modifiers = ""
!Provider = Provider$
!Resident = Resident$
.Update
End If
Next i%
'Record Agents and Units
For i% = 1 To 8
If Units(i%) > 0 Then
.AddNew
!MSR = MSRNumber
!PatUniqueID = PatientID$
!Procedure = varLookup("Agent", "tlkpAgents", "RowNumber
= " & CStr(i%))
!Diag1 = Diag1$: !Diag2 = Diag2$: !Diag3 = Diag3$:
!Diag4 = ""
!Units = Units(i%)
!DateOfService = AppointmentDate$
!Modifiers = ""
!Provider = Provider$
!Resident = Resident$
.Update
End If
Next i%
End With
ImportCount% = ImportCount% + 1
ProcessNextForm:
.MoveNext
Wend
End With
rst.Close
Msg$ = ImportCount% & " encounter form" & IIf(ImportCount% = 1, " was ",
"s were ") & _
"interpreted and imported."
MsgBox Msg$, vbInformation, "Import of Scanned data"

ReadInput_End:
Set db = Nothing: Set rsInput = Nothing: Set rst = Nothing
strSQLText = vbNullString
Exit Sub

ErrorHandler:
If Err.Number = 3022 Then
Msg$ = "MSR #" & MSRNumber & " was previously scanned and will be
skipped"
If MsgBox(Msg$, vbInformation + vbOKCancel, "Duplicate Scan") = vbOK
Then
Resume ProcessNextForm
Else: Resume ReadInput_End
End If
Else
MsgBox Err.Number & " - " & Err.Description, vbCritical, "Error in
ReadInput"
Resume ReadInput_End
End If

End Sub
Click to expand...
Click to expand...
 
G

Guest

Marshall,

I renamed and then re-created the table referred to by rsMSR. The problem
went away, although not sure why. Thank you for your help. OK to ignore my
last question about your syntax comment.

Marshall Barton said:
You were right, there is a lot going on in there. It's a
little hard to read with the archaic syntax, but I don't see
anything obviously wrong.

I will question the implicit typing of some variables as
Variant in the Dim statememts. Is it possible that you are
picking up an unexpected Null somewhere along the line?

The only other thought I have is that maybe the table has
another field with a unique index that's getting in the
way??

At this point, all I can suggest is that you single step
through the code and watch what happens.

Sorry I couldn't be more helpful.
--
Marsh
MVP [MS Access]

I did not post the entire subroutine, because the sub is long and does a lot
of other things, but here it is if it helps.:

Public Sub ReadInput()
On Error GoTo ErrorHandler
Dim db As Database, rsInput As Recordset, rst As Recordset, tbl As
TableDef, rsMSR As Recordset
Dim strSQLText As String, PatientID$, MSRNumber, AppointmentDate$,
ImportCount%
Dim Diag1$, Diag2$, Diag3$, Provider$, Units(8) As String, i%, CPTRow,
Msg$, Resident$
Set db = CurrentDb
strSQLText = "SELECT F0001, Dx3, CustomDx1, CustomDx2, Agent01, Agent02,
Agent03, " & _
"Agent04, Agent05, Agent06, Agent07, Agent08, PatUniqueID,
Resource, MSR, " & _
"ApptDate, Dx2, Dx1, Resident " & _
"FROM ScannedDataImaging ORDER BY MSR"

If Not DeleteTable("tblTransactions") Then GoTo ReadInput_End
Set rst = db.OpenRecordset("tblTransactions")
Set rsMSR = db.OpenRecordset("tblMSRImportLog")
Set rsInput = db.OpenRecordset(strSQLText)
With rsInput
While Not .EOF
PatientID$ = !PatUniqueID: MSRNumber = !MSR
AppointmentDate$ = Format$(!ApptDate, "mmddyyyy")
Provider$ = varLookup("PROVIDERCODE", "dbo_Resources",
"RESOURCEIDDESC = '" & !Resource & "'")
Diag1$ = DiagnosisLookup(!Dx1, !CustomDx1, !CustomDx2)
Diag2$ = DiagnosisLookup(!Dx2, !CustomDx1, !CustomDx2)
Diag3$ = DiagnosisLookup(!Dx3, !CustomDx1, !CustomDx2)
Units(1) = !Agent01: Units(2) = !Agent02: Units(3) = !Agent03:
Units(4) = !Agent04
Units(5) = !Agent05: Units(6) = !Agent06: Units(7) = !Agent07:
Units(8) = !Agent08
CPTRow = !F0001: Resident$ = !Resident

With rsMSR
.AddNew
!MSR = MSRNumber
!PatUniqueID = PatientID$
!ApptDate = AppointmentDate$
!Resource = Provider$
.Update
End With

With rst
For i% = 1 To 29
If Mid$(CPTRow, i%, 1) = 1 Then
.AddNew
'Record CPT Code
!MSR = MSRNumber
!PatUniqueID = PatientID$
!Procedure = varLookup("CPTCode", "tlkpProcedures",
"RowNumber = " & i%)
!Diag1 = Diag1$: !Diag2 = Diag2$: !Diag3 = Diag3$: !Diag4 = ""
!Units = 1
!DateOfService = AppointmentDate$
!Modifiers = ""
!Provider = Provider$
!Resident = Resident$
.Update
End If
Next i%
'Record Agents and Units
For i% = 1 To 8
If Units(i%) > 0 Then
.AddNew
!MSR = MSRNumber
!PatUniqueID = PatientID$
!Procedure = varLookup("Agent", "tlkpAgents", "RowNumber
= " & CStr(i%))
!Diag1 = Diag1$: !Diag2 = Diag2$: !Diag3 = Diag3$:
!Diag4 = ""
!Units = Units(i%)
!DateOfService = AppointmentDate$
!Modifiers = ""
!Provider = Provider$
!Resident = Resident$
.Update
End If
Next i%
End With
ImportCount% = ImportCount% + 1
ProcessNextForm:
.MoveNext
Wend
End With
rst.Close
Msg$ = ImportCount% & " encounter form" & IIf(ImportCount% = 1, " was ",
"s were ") & _
"interpreted and imported."
MsgBox Msg$, vbInformation, "Import of Scanned data"

ReadInput_End:
Set db = Nothing: Set rsInput = Nothing: Set rst = Nothing
strSQLText = vbNullString
Exit Sub

ErrorHandler:
If Err.Number = 3022 Then
Msg$ = "MSR #" & MSRNumber & " was previously scanned and will be
skipped"
If MsgBox(Msg$, vbInformation + vbOKCancel, "Duplicate Scan") = vbOK
Then
Resume ProcessNextForm
Else: Resume ReadInput_End
End If
Else
MsgBox Err.Number & " - " & Err.Description, vbCritical, "Error in
ReadInput"
Resume ReadInput_End
End If

End Sub
Click to expand...
Click to expand...
 
M

Marshall Barton

Well, I think the %, $ type identifies are difficult to
read, but that's just a personal preference. Are you aware
that those are only needed in the Dim statement? They are
not needed in the body of a procedure.
Dim Diag1$, . . .
. . .
Diag1 = . . .

Most programmers prefer to be explicit in declaration
statements. For example, the statement:

Dim xxx, yyy As string
is the same as
Dim xxx As Variant, yyy As string

which makes xxx very loosely typed. With a Variant
variable, there may be all kinds of behind the scenes type
casting going on where ever the variable is used. Not only
that, but a Variant allows for a Null value, so your code
needs to be able to cope with this possibility.

Another (very minor) thing that I mentally stumbled over is:
While Not .EOF
. . .
Wend

The current (since '95) style is to use:
Do Until .EOF
. . .
Loop
or
Do While Not .EOF
. . .
Loop
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

3022 error while run update SQL 1
Run-Time Error 3022 4
Access MS Access VBA - 3022 error 3
runtime 3022 1
Add a record to an SQL Table 5
Duplicate the record in form and subform 23
run-time error 3022 2
Trapping error 3022 in order to automatically navigate to the pre-existing record 0

Top