INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Error trap if no records in recordset

Error trap if no records in recordset

(OP)
HI (again)
I have data to be inserted into record.
The record will be in one of two tables.
I have the vba to update the table by searching the table and editing the field
If the record is not in the second table Access stops at rst.movefirst with an error message. (because no record)
How can I error trap this event?

If n = 2 Then SqlStr = "SELECT tblMachine1.MachinePhone, tblMachine1.SerialNo FROM tblMachine1 WHERE (((tblMachine1.SerialNo)= '" & SearchStr & "'));"

Set rst = dbs.OpenRecordset(SqlStr)
rst.OpenRecordset
rst.MoveFirst
rst.Edit
rst.Fields(1) = txtMachinePhone
rst.Update

Many thanks (again)

Telephoto

RE: Error trap if no records in recordset

check if end of file
if not rst.eof then ...

RE: Error trap if no records in recordset

(OP)
Hello MajP
Tried it in various positions for some time, then the penny dropped.
The table will only ever have one entry, so it will always be rst.eof = true
Shame, because it seemed so obvious.

Do you have any other ideas please?

T

RE: Error trap if no records in recordset

Quote:

The table will only ever have one entry, so it will always be rst.eof = true
No, that is incorrect. Please read. You are doing something incorrect, but would have to see your code. I think in some rare cases depending on how you open the recordset you may need to check both .bof and .eof
If not(rst.Bof and rst.EOF) then ..
I do not think you really need to check both bof and eof because when you open a recordset from sql it should always be at bof. But it cannot hurt and it is commonly done, I do not know why.

https://msdn.microsoft.com/en-us/library/office/ff...

Quote (MS)

You can use the BOF and EOF properties to determine whether a Recordset object contains records or whether you've gone beyond the limits of a Recordset object when you move from record to record.

The location of the current record pointer determines the BOF and EOF return values.

If either the BOF or EOF property is True, there is no current record.

If you open a Recordset object containing no records, the BOF and EOF properties are set to True, and the Recordset object's RecordCount property setting is 0. When you open a Recordset object that contains at least one record, the first record is the current record and the BOF and EOF properties are False; they remain False until you move beyond the beginning or end of the Recordset object by using the MovePrevious or MoveNext method, respectively. When you move beyond the beginning or end of the Recordset, there is no current record or no record exists.

If you delete the last remaining record in the Recordset object, the BOF and EOF properties may remain False until you attempt to reposition the current record.

RE: Error trap if no records in recordset

(OP)
why should I have doubted you?
For anyone else's benefit the code (inside a for/next loop) now looks like this:

If Not rst.BOF Then
rst.MoveFirst
rst.Edit
rst.Fields(1) = txtMachinePhone
rst.Update
rst.Close
Else
rst.Close
End If

MajP many thanks

T

RE: Error trap if no records in recordset

The rst.movefirst will never do anything, wasted code. How could you be at .bof and not also at the first record? Alos closing the recordset is unneeded in 99.9% of code. That is also a hold-over, but makes people feel better. I still do not think you can ever not be at the first record when opening from SQL. I think if you clone a recordset like from a form you will be at the same current record and would have to move first.

CODE -->

If Not (rst.BOF and rst.eof) Then
  rst.Edit
  rst.Fields(1) = txtMachinePhone
  rst.Update
End I 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close