"Randy" wrote:
> 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
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
|
Similar Threads
