"DaveN" <da*****************@hotmail.co.uk> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...
Hi all,
I'm trying to update a record in a table with data from text boxes on a
form. As a background to this, I managed to add a new record to the
table in a similar manner with the following:
Set db = CurrentDb
Set rs = db.OpenRecordset("Project Table")
rs.AddNew
rs("Project_Number") = Me![Project_Number]
rs("Project_Manager") = Me![Project_Manager]
rs("Project_Name") = Me![Project_Name]
rs.Update
rs.Close
This works really well, but now I want to be able to change the data in
an existing record. Firstly I put the three bits of info onto the form
and then the user can change either the project manager or project
name. The project number is protected as this is a unique id for the
record, (although this is not the primary key in case you were
wondering!)
It seems to me that all I should do is something like this:
Set db = CurrentDb
Set rs = db.OpenRecordset("Project Table")
rs.Edit
SrchCrit = "[Project_Number] ='" & Me![Project_Number] & "'"
rs.FindFirst SrchCrit
rs("Project_Manager") = Me![Project_Manager]
rs("Project_Name") = Me![Project_Name]
rs.Update
rs.Close
However this just bombs out on the FindFirst method saying it's not
supported for this type of object. I know I have to do some sort of
search on the records because I need to ensure I'm changing the correct
one.
So someone please tell me what stupid amateur mistake I'm making here.
Cheers
Dave.
While looking at your code, select Tools>References look for something like:
Microsoft ActiveX Data Objects 2.1 Library
I expect you will be able to uncheck it with no problems, but after you do
make sure your code compiles.
What is happening is that there are two competing object libraries, both of
which have the recordset object. However, they are not identical and
although the DAO.Recordset object has a FindFirst method, the
ADODB.Recordset object does not. Since you have not specified which type of
recordset you want, Access assumes you are talking abot an ADO one and can't
find this method.
The morals of this episode are:
1. Remove any un-used references from your application
2. Always dimension your objects to show which library, e.g Dim dbs As
DAO.Database, Dim rst As DAO.Recordset
Now the next bit. If you did use the FindFirst method then you should check
to see whether you have found anything before you proceed. Check the
..NoMatch condition. However, in any case you should never load the whole
table into a recordset if you only intend to edit one record. All you need
is:
SELECT Project_Manager, Project_Name
FROM [Project Table]
WHERE Project_Number="ABC123"
So if this is a valid project number, your recordset has a single record in
it which you can edit.
As a final point, you can also avoid recordsets altogether by using
dbs.Execute strSQL where strSQL is an update query.
