AfterUpdate rs.FindFirst Question?

[66tigger40]

Hi

I have the following code which works great - but I know need to add to it so that if it doens't find the barcode then an error message is shown so that the user can't continue?

Thanks in advance

Private Sub txtBarCodeData_AfterUpdate()
Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[BarCodeData] = '" & txtBarCodeData & "'"
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
   
    Me.TimeCompleted.Value = Time()
    Me.DateCompleted.Value = Date
    Me.MinutesOnBreak.Value = 0
End Sub

 

[Eupher]

66Tigger40,

Put an Else clause in your If..Then statement:

If Not rs.EOF Then
    Me.Bookmark = rs.Bookmark
[blue]    Else
        MsgBox "Barcode not found.  Cannot continue."
        Exit Sub[/blue]
End If

HTH,

Ken S.

 

[66tigger40]

Hi Ken

Thanks for the quick reply - I now have the following code but it's telling me I have an Else without IF Statement?

Private Sub txtBarCodeData_AfterUpdate()
Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[BarCodeData] = '" & txtBarCodeData & "'"
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
        Else
        MsgBox "Barcode not found.  Cannot continue."
        Exit Sub
    End If
   
    Me.TimeCompleted.Value = Time()
    Me.DateCompleted.Value = Date
    Me.MinutesOnBreak.Value = 0
End Sub

Any ideas?

 

[PHV]

If Not rs.EOF Then
Me.Bookmark = rs.Bookmark
Else
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[66tigger40]

That's great - thank you

 

[TheAceMan1]

How are ya 66tigger40 . . .

You'll have to forgive me but there's a [blue]big problem[/blue] in your use of [blue]rs.EOF[/blue]!

[blue][purple]FindFirst has no effect on EOF[/purple]. Meaning for rs.FindFirst, rs.EOF will always be false, and [purple]Not rst.EOF will always be true![/purple] Wether rs.FindFirst succeeds or not!

The danger here is if a record matching the criteria isn't located, [blue]the current record pointer is unknown[/blue] and [purple]your attempt to set the bookmark will raise an error![/purple][/blue]

This easily fixed by using the [blue]NoMatch[/blue] property (the correct one to use) and along with a little cleanup of the code I show:

[blue]Private Sub txtBarCodeData_AfterUpdate()
   Dim rs As DAO.Recordset

   Set rs = Me.Recordset.Clone
   rs.FindFirst "[BarCodeData] = '" & txtBarCodeData & "'"
   
   If Not rs.[purple][b]NoMatch[/b][/purple] Then
      Me.Bookmark = rs.Bookmark
      Me.TimeCompleted.Value = Time()
      Me.DateCompleted.Value = Date
      Me.MinutesOnBreak.Value = 0
   Else
      MsgBox "Barcode not found.  Cannot continue."
   End If
   
End Sub[/blue]

See Ya! . . . . . .

 

[Eupher]

Hi, AceMan,

Your comments on FindFirst/EOF are noted. Seems OP has adapted the code created by the "Find a record on my form" wizard.

I'm curious... why does the wizard use this code? Is it to prevent an error being raised if there is no data in the form's recordset?

As always, thanks for your insight.

Ken S.

 

[TheAceMan1]

Howdy [blue]Eupher![/blue]

[blue]Seems OP has adapted the code created by the [purple]"Find a record on my form" wizard.[/purple][/blue]

No not quite . . . it typically looks like the following (just did it):

[blue]   [green]' Find the record that matches the control.[/green]
   Dim rs As Object

   Set rs = Me.Recordset.Clone
   rs.FindFirst "[ClientID] = " & Str(Me![Combo10])
   Me.Bookmark = rs.Bookmark[/blue]

I've never seen [blue]EOF[/blue] used in any wizard! . . . and doubt it exist in this way.

I could spell it all out, but if you find a little time, get your favorite beverage and in [blue]VBE help[/blue] have a look at the items. The [blue]help on these is well documented.[/blue]

[blue]You take care Ken . . . Ya Hear! . . .[/blue]

See Ya! . . . . . .

 

[66tigger40]

Hiya

Thanks for all the input and advice

I'll add the new code you have provided to my database - all help very much appreciated.

N

 

[Eupher]

AceMan,

Very interesting! Because I also just ran the wizard, and here is the code it produced:

Private Sub Combo18_AfterUpdate()
    [green]' Find the record that matches the control.[/green]
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[RecID] = " & Str(Nz(Me![Combo18], 0))
    [highlight]If Not rs.EOF Then[/highlight] Me.Bookmark = rs.Bookmark
End Sub

I'm running Access 2K3 SP2 (11.6566.8107); VBA v.6.4.9972.

What's up with that??

Ken S.

 

[TheAceMan1]

Eupher . . .

Yes . . . I aquired the same at work (2K3). Not only is this scary but it makes it [blue]version specific![/blue] If you've read the help I'm sure you can see the problem . . .

I e-mailed microsoft about this and waiting for any reply.

As far as this secaniro is concerned I'd switch to [blue]rs.NoMatch[/blue] which is [blue]not version specific![/blue]

See Ya! . . . . . .