Handling Invalid Entries Myself

[ddevil]

When the user enters and invalid WR#, they get a runtime error 3021, no current record, which fails at CurrentDb.Execute strSQL. Is there a way to let the user know, via an input box and eliminate the system error from occurring? Part of the code is below.


strSQL = "INSERT INTO TempRefund_Customers ([ParentWRNumber], [CSSPremiseNumber], [Area], [CustomerName],[JobAddress], " & _
"[BillingAddressName], [BillingAddress],[BillingCity], [BillingState],[BillingZipCode], [OpUnit],[UType],[ProjectID],[Activity],[RefundableTotal],[CompletionDate]," & _
"[Status],[WRDesc],[TypeWR],[JobType]) Values ('" & intWRNumber & "','" & stPremise & "','" & stArea & "','" & stCustName & "'," & _
"'" & stJobAddress & "','" & stBillName & "','" & stBillAdd & "','" & stBillCity & "','" & stBillState & "', " & _
"'" & stBillZip & "','" & stOpUnit & "','" & stUType & "','" & stProjectID & "','" & stActivityID & "','" & intTotal & "','" & dtCompleteDate & "'," & _
"'" & stStatus & "','" & stWRDesc & "','" & stTypeWR & "','" & stJobType & "')"

CurrentDb.Execute strSQL

 

[Remou]

Why not validate the controls as they are entered?

 

[ddevil]

The values are entered through a query which is pulling information from oracle tables link in the database, but if the user types in the wrong # (fat finger) or an invalid #, then the error occurs.

 

[Remou]

Where is the user typing in this data in order for you to get, say, stTypeWR?

 

[ddevil]

The user types in just the WR. I'll post all of the code below instead of just a snipet. The CriteriaWr is what is passed from the form (where they enter the number that should equal the CD_WR number in the table.

Sub GetRefundData(CriteriaWR)

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim intEnterWR As Variant

intEnterWR = [CriteriaWR]

strSQL = "SELECT WRInquiry.* FROM WRInquiry Where CD_WR = " & intEnterWR & ";"

Dim stProjectID, stPremise, stArea, stCustName, stJobAddress, stBillName, stBillAdd, stBillCity, stBillState, stBillZip, stOpUnit, stUtilityType, stActivityID, stStatus, stWRDesc, stTypeWR, stJobType As String
Dim dtCompleteDate As Variant
Dim intWRNumber As Variant
Dim intTotal As Currency

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)


With rst

intWRNumber = rst![CD_WR]
stPremise = rst![ID_PREMISE]
stArea = rst![CD_AREA]
stCustName = rst![NM_CONTACT]
stJobAddress = rst![JobAddress]
stBillName = rst![NM_CONTACT]
stBillAdd = rst![Address]
stBillCity = rst![AD_TOWN]
stBillState = rst![CD_STATE]
stBillZip = rst![AD_POSTAL]
stOpUnit = rst![CD_CREWHQ_FIN]
stUType = rst![IND_UTIL]
stActivityID = rst![CD_WR]
stStatus = rst![CD_STATUS]
stWRDesc = rst![DS_WR]
If stWRDesc Like "*'*" Then
stWRDesc = Replace(stWRDesc, "'", "")
End If
stTypeWR = rst![TP_WR]
stJobType = rst![TP_JOB]
dtCompleteDate = rst![DT_COMPLETE]
intTotal = rst![QuoteAmount]
stProjectID = rst![CD_WO_INSTL]




strSQL = "INSERT INTO TempRefund_Customers ([ParentWRNumber], [CSSPremiseNumber], [Area], [CustomerName],[JobAddress], " & _
"[BillingAddressName], [BillingAddress],[BillingCity], [BillingState],[BillingZipCode], [OpUnit],[UType],[ProjectID],[Activity],[RefundableTotal],[CompletionDate]," & _
"[Status],[WRDesc],[TypeWR],[JobType]) Values ('" & intWRNumber & "','" & stPremise & "','" & stArea & "','" & stCustName & "'," & _
"'" & stJobAddress & "','" & stBillName & "','" & stBillAdd & "','" & stBillCity & "','" & stBillState & "', " & _
"'" & stBillZip & "','" & stOpUnit & "','" & stUType & "','" & stProjectID & "','" & stActivityID & "','" & intTotal & "','" & dtCompleteDate & "'," & _
"'" & stStatus & "','" & stWRDesc & "','" & stTypeWR & "','" & stJobType & "')"

CurrentDb.Execute strSQL


End With

DoCmd.OpenForm "RefundableForm", , , "ParentWRNumber = " & intEnterWR
DoCmd.Close acForm, "Updating Data"
DoCmd.Close acForm, "CriteriaForm"

Set fso = Nothing
Set fd = Nothing
Set ts = Nothing


End Sub

 

[Remou]

If you check at this point:

[tt]Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)[/tt]

To see if you have got a recordset:

[tt]If rs.EOF Then
'Incorrect CriteriaWR
Exit Sub
End If[/tt]

Would that suit?