Accessing a new record

[Guest]

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

 

[Guest]

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

I'm guessing that the prefix "aqry" means that it is an Append query and
"dqry" is a Delete query.

In looking at your code, it looks like you open a recordset based on table
"tblCheck_If_DHL", then you run an append query "aqryCheck_If_DHL" which
changes the table but doesn't change the recordset. Next you do a FindFirst
on the recordset and can't /don't find the record.

There are two ways to fix this:

1) change the order - do the append, then open the recordset

or

2) add rstCurrent.ReQuery after the append and before the findfirst.


I noticed a couple of other things...

Since you created a databaseobject and a recordset object, you should close
the record set and dereference the two objects before you exit the sub.
There are three places you should add these three lines before the first two
Exit Sub 's and before the first Delete query.

rstCurrent.Close
Set rstCurrnet = Nothing
Set dbCurrent = Nothing


The other thing is instead if the many IF statements, you could use a case
construct:

'** snip **

Select Case strShipMethod
Case "H", "U", "T", "8", "J", "K", ""
' do nothing
Case Else
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 Select

' *** snip **

HTH