Update a record 3

[FoxProProgrammer]

I am trying to do something that should be simple. The more Access Help screens that I read, the more confused I get! lol

Let's say that a database contains several tables and several forms. One of the forms has only unbound list boxes, combo boxes, and a few buttons. I set the RowSource for each combo box and list box in an event procedure. For example, when the user selects an item from a combo box, the On Click procedure sets the RowSource for the other controls based on the selection. That all works great. The form does not have a RecordSource, nor do I think it needs one to accomplish the purpose of the form.

Now for the problem...The user presses a button when they want to change the data in a record. The record that I need to find in the table is reflected by their selections in the unbound controls. In FoxPro, I would simply select the table to look in with a SELECT statement (not SQL select), SEEK or LOCATE the record and REPLACE the data in a field. I looked in the Access Help for something similar and found multiple Find methods, multiple ways to update records, etc. I tried RecordSetClone.FindFirst but get an error. Perhaps I am getting this error because the form doesn't have a RecordSource? I don't understand the difference between ADO and DAO, and maybe that's why I am having trouble figuring out how to do what should be easy tasks in Access. Do I have to define my application as DAO or ADO or can I use either types of syntax in the same application? That might sound like a dumb question, but I am new to Access.

In summary:

- I need to select a table in a database.
- Find a record.
- Check to see if the record was found.
- If found, change the value in one of the fields in that record.

Any help would be greatly appreciated.

Thanks!

dz

 

[BanditLV]

I've done this so many times, I can do it in the dark (as long as my fingers are on the homerow keyboard keys - hehehehe)...

Dim Dbs as Database
Dim Rst as Recordset

Set Dbs = CurrentDb
Set Rst = dbs.OpenRecordset("NameOfTable",dbOpenDynaset)
'*** NOTE - when opening a recordset, it's best to open it as a "dbSnapShot" unless you're going to modify it - as we're planning on doing here.

Rst.FindFirst "[FieldName] = '" & TextFieldSpecs & "'"
'*** NOTE - OR use the following line if not a text lookup:
Rst.FindFirst "[FieldName] = " & FieldSpecs
If not Rst.NoMatch then ' Record Found
Rst.Edit
Rst!Field1 = NewField1Data
Rst!Field2 = NewField2Data
Rst!Field3 = NewField3Data
Rst.Update
Else ' Record Not Found
Rst.AddNew
Rst!Field1 = NewField1Data
Rst!Field2 = NewField2Data
Rst!Field3 = NewField3Data
Rst.Update
End If
Rst.Close

That should do it for you. This can basically be copied and pasted into your coding and modified as needed.

Enjoy!

Roy
in Las Vegas
aka LVBandit

 

[FoxProProgrammer]

Thanks, Roy! Looks simple enough. I was all around it, but didn't bring home the bacon. lol I couldn't figure out how to specify the table within the database that I wanted to search in. Now I know! Have a great day, and thanks again for your help.

dz

 

[grnzbra]

During the first part (which you say is working correctly), save the rowsource that you select with the combo box. Then, use this in the code to update the same rowsources.

 

[FoxProProgrammer]

Well, I'm still having trouble with this. The Help in Access leaves a lot to be desired. Here's my code:

Dim DbsManuf as Database
Dim RstContact as Recordset

Set DbsManuf = CurrentDb
Set RstContact = dbsManuf.OpenRecordset("manufacturers",dbOpenDynaset)

I get error 13, Type Mismatch when I run the program. manufacturers is the name of a table in my database. The weird thing is that the same code in another application works fine. Do I need to set up something in the environment?

Thanks,

dz

 

[cmmrfrds]

If you are using Access 2000, then explicitly declare the objects
Dim DbsManuf as DAO.Database
Dim RstContact asDAO.Recordset

Make sure there is a reference to the DAO library set.

 

[FoxProProgrammer]

Thanks cmmrfrds. The DAO declaration took care of the problem. This raises a few questions though.

1. Why did the code without the DAO declaration work in another application that I am developing?

2. If I use the DAO declaration, will my code be compatible with users that have Access 97?

3. There are three versions of the DAO library in References. How do I know which one to use? I used the one with the latest version date, but I couldn't find a description of any of the libraries.

grnzbra: Thanks for your suggestion as well.

Thanks, everyone!

dz

 

[cmmrfrds]

Access 2000 defaults to ADO. So, if the recordset is defined as Dim rs as recordset it will be a ADODB recordset.
The recordset is an object in both the DAO and ADO library.

Access 97 defaults to DAO. So, in the above example the same recordset would be DAO. An explicit declaration should be just fine in Access 97.

Best to Explicitly define the data objects to eliminate any confusion. Since you can use both ADO and DAO in the same program.

I always use the latest DAO library 3.6

 

[FoxProProgrammer]

cmmrfrds,

Thanks for your reply. That helps!

dz