problems using DAO methods

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi guys,

I have attempted this procedure before, but I cannot figure out why it is
not working this time. I am trying to use DAO to find a record in a table
(not by primary key) then edit it. First I was getting "update without edit"
even though the edit was indeed there , or I get wrong type if I use the
wrong dbopen mode in OpenRecordset or I get EOF if I user findFirst even
though I know the record exists.

This what I have:

Dim invRS As DAO.Recordset
Set invRS = CurrentDb.OpenRecordset("INVOICE", dbOpenDynamic)
With invRS
If ....(New record functions)
Else
.FindFirst "RefNumber = '10129'"
If .EOF Then
MsgBox ("No Invoice by that ID.")
Exit Function
Else
.Edit
..Update


I have tried numerous ways, but cant figure out why nothing is working.

Any assistance would be great.

Cheers,
David
 
What is the Field Type of RefNumber? If it is a Number type (not a Text
type), drop the extra quotes, i.e.:
.FindFirst "RefNumber = 10129"

After a FindFirst, test NoMatch rather than EOF:
If .NoMatch Then
 
dp said:
Hi guys,

I have attempted this procedure before, but I cannot figure out why
it is not working this time. I am trying to use DAO to find a record
in a table (not by primary key) then edit it. First I was getting
"update without edit" even though the edit was indeed there , or I
get wrong type if I use the wrong dbopen mode in OpenRecordset or I
get EOF if I user findFirst even though I know the record exists.

This what I have:

Dim invRS As DAO.Recordset
Set invRS = CurrentDb.OpenRecordset("INVOICE", dbOpenDynamic)
With invRS
If ....(New record functions)
Else
.FindFirst "RefNumber = '10129'"
If .EOF Then
MsgBox ("No Invoice by that ID.")
Exit Function
Else
.Edit
.Update


I have tried numerous ways, but cant figure out why nothing is
working.

Any assistance would be great.

Cheers,
David
Click to expand...

There's one certain error and one probable error in the code you've
posted.

The Certain Error: With DAO recordsets, if you call the FindFirst method
and the record is not found, you are not located at EOF, and the EOF
property does not become True. Instead, the NoMatch property is set to
True. So your code should say

If .NoMatch Then
MsgBox ("No Invoice by that ID.")
Exit Function
Else
' ...
End If

The Probable Error: If RefNumber is a numeric field, not a text field,
you shouldn't enclose the value you are searching for in single-quotes.
Maybe it is a text field, in which case this ...
.FindFirst "RefNumber = '10129'"
Click to expand...

is correct. But if it's a number field of some sort, then you should
write this instead:

.FindFirst "RefNumber = 10129"

Beyond these, I can't address what other problem you may be having,
because you haven't posted all the code.
 
Hi,
From the incomplete code snippet the only thing I can think of is that
maybe RefNumber is not a string? In which case you shouldn't have the
single quote delimiters.

Also, you should use the .NoMatch property to see if a match has been found.
Help has some examples of this
 
Back
Top