Error trap if no records in recordset 1

[telephoto]

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

 

[MajP]

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

 

[telephoto]

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

 

[MajP]

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/ff821459.aspx

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.

 

[telephoto]

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

 

[MajP]

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.

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