R
Randy
Thank you in advance for your assistance. I have the following VB code
behind a form. What's happening is the record is appended to the table but
when I execute the rstCurrent.NoMatch I do not find a record unless I exit
the subroutine and re-execute the subroutine. Any advice is greatly
appreciated.
Private Sub cmdAddOrder_Click()
On Error GoTo Err_cmdAddOrder_Click
Dim dbCurrent As Database
Dim rstCurrent As Recordset
Dim strSearch As String
Dim strShipMethod As String
Dim strStatus As String
Set dbCurrent = CurrentDb()
Set rstCurrent = dbCurrent.OpenRecordset("tblCheck_If_DHL", dbOpenDynaset)
'
' It was faster getting the record into Access than doing the read via ODBC
'
DoCmd.SetWarnings False
DoCmd.OpenQuery "aqryCheck_If_DHL", acViewNormal
DoCmd.SetWarnings True
'
' Now check the record that was returned
'
' Dim Test As String
' Test = Forms![frmOrder_Entry]!txtOrderNumber
' Call MsgBox(Test, vbOKOnly)
strSearch = Forms![frmOrder_Entry]!txtOrderNumber
rstCurrent.FindFirst "WCOR40 = '" & strSearch & "'"
'
' If the order number is not in the local file, you will be at EOF
'
***Note** The next line of code is were I'm having a problem.
If rstCurrent.EOF = True Or rstCurrent.NoMatch = True Then
Call MsgBox("Invalid Order Number", vbOKOnly)
Exit Sub
End If
'
' The order is valid, check if it needs a commercial invoice
'
strShipMethod = rstCurrent.Fields("WDSM40")
If strShipMethod <> "H" And strShipMethod <> "U" And strShipMethod <>
"T" And strShipMethod <> "8" And strShipMethod <> "J" And strShipMethod <>
"K" And strShipMethod <> "" Then
Call MsgBox("Order is Not DHL Worldwide Priority, UPS Worldwide
Expedite, UPS International - Commercial Invoice Not Needed or Purolator",
vbOKOnly)
DoCmd.SetWarnings False
DoCmd.OpenQuery "dqryCheck_If_DHL", acViewNormal
DoCmd.SetWarnings True
Exit Sub
End If
'
' This order needs a commercial invoice. Clean up the check file and build
the commercial invoice file
'
DoCmd.SetWarnings False
DoCmd.OpenQuery "dqryCheck_If_DHL", acViewNormal
'
' Delete the order if it is there before the append to avoid duplicates if
there is a change.
'
DoCmd.OpenQuery "dqryDHL_Commercial_Invoice", acViewNormal
DoCmd.OpenQuery "aqryDHL_Commercial_Invoice", acViewNormal
DoCmd.SetWarnings True
Call MsgBox("Order Added to Commercial Invoice File", vbOKOnly)
txtOrderNumber.SetFocus
txtOrderNumber.Value = 0
Exit_cmdAddOrder_Click:
Exit Sub
Err_cmdAddOrder_Click:
MsgBox Err.Description
Resume Exit_cmdAddOrder_Click
End Sub
behind a form. What's happening is the record is appended to the table but
when I execute the rstCurrent.NoMatch I do not find a record unless I exit
the subroutine and re-execute the subroutine. Any advice is greatly
appreciated.
Private Sub cmdAddOrder_Click()
On Error GoTo Err_cmdAddOrder_Click
Dim dbCurrent As Database
Dim rstCurrent As Recordset
Dim strSearch As String
Dim strShipMethod As String
Dim strStatus As String
Set dbCurrent = CurrentDb()
Set rstCurrent = dbCurrent.OpenRecordset("tblCheck_If_DHL", dbOpenDynaset)
'
' It was faster getting the record into Access than doing the read via ODBC
'
DoCmd.SetWarnings False
DoCmd.OpenQuery "aqryCheck_If_DHL", acViewNormal
DoCmd.SetWarnings True
'
' Now check the record that was returned
'
' Dim Test As String
' Test = Forms![frmOrder_Entry]!txtOrderNumber
' Call MsgBox(Test, vbOKOnly)
strSearch = Forms![frmOrder_Entry]!txtOrderNumber
rstCurrent.FindFirst "WCOR40 = '" & strSearch & "'"
'
' If the order number is not in the local file, you will be at EOF
'
***Note** The next line of code is were I'm having a problem.
If rstCurrent.EOF = True Or rstCurrent.NoMatch = True Then
Call MsgBox("Invalid Order Number", vbOKOnly)
Exit Sub
End If
'
' The order is valid, check if it needs a commercial invoice
'
strShipMethod = rstCurrent.Fields("WDSM40")
If strShipMethod <> "H" And strShipMethod <> "U" And strShipMethod <>
"T" And strShipMethod <> "8" And strShipMethod <> "J" And strShipMethod <>
"K" And strShipMethod <> "" Then
Call MsgBox("Order is Not DHL Worldwide Priority, UPS Worldwide
Expedite, UPS International - Commercial Invoice Not Needed or Purolator",
vbOKOnly)
DoCmd.SetWarnings False
DoCmd.OpenQuery "dqryCheck_If_DHL", acViewNormal
DoCmd.SetWarnings True
Exit Sub
End If
'
' This order needs a commercial invoice. Clean up the check file and build
the commercial invoice file
'
DoCmd.SetWarnings False
DoCmd.OpenQuery "dqryCheck_If_DHL", acViewNormal
'
' Delete the order if it is there before the append to avoid duplicates if
there is a change.
'
DoCmd.OpenQuery "dqryDHL_Commercial_Invoice", acViewNormal
DoCmd.OpenQuery "aqryDHL_Commercial_Invoice", acViewNormal
DoCmd.SetWarnings True
Call MsgBox("Order Added to Commercial Invoice File", vbOKOnly)
txtOrderNumber.SetFocus
txtOrderNumber.Value = 0
Exit_cmdAddOrder_Click:
Exit Sub
Err_cmdAddOrder_Click:
MsgBox Err.Description
Resume Exit_cmdAddOrder_Click
End Sub