acActiveDataObject from DoCmd.gotorecord value

Question

Hi MVP's 

Thank you for your ongoing assistance

I have 2 command buttons with vba code. statement

Me.tblAttachments.SetFocus

DoCmd.GotoRecord , , acNxt

Me.tblAttachments.SetFocus

DoCmd.GotoRecord , , acPrevious

I would like to read the Field values of that record that is Active

eg. Field1, Field2, etc..

Can you please assist

I have found a loop that reads all the values, but think it is too detailed

Set MyDB = CurrentDb()

Set MyRS = MyDB.OpenRecordset("tblAttachmentsBefor", dbOpenForwardOnly

Do While Not MyRS.EOF

     Debug.Print MyRS![Field1]

     MyRS.MoveNext

Loop

Kind Regards

Answer 1

Umm, Append, Update, Delete ARE Action queries that are SQL statements. So I think you read something incorrectly. I agree it is better to use a SQL Action query to modify data in bulk.

When you use DoCmd.OpenQuery to run an Action query, then Access will prompt you that you are modifying records. Using DoCmd.SetWarnings suppresses those messages. However, if you use CurrentDB.Execute as I did in my example, the warnings are suppressed automatically.

When you create a Relationship using the Relationships window you can set Referential Integrity. Part of RI is cascading deletes. So if you delete a parent, it deletes the child records for you.

So back to my point. the Syntax for a DELETE query is:

DELETE * FROM table WHERE somefield = some value

You don't need to specify a field list since it deletes the whole record so you just need the *. You DO need to specify a criteria to identify the record(s) you want to delete. If you are deleting a single record you want to set criteria to the primary key. If you want to delete children records, then you set the Foreign Key value. For example: DELETE * FROM OrderDetails WHERE OrderID = x

That will delete all the line items for the specified order.

Hope this helps you understand better.

Answer 2

Thank you Scott

I had read in a handy tip guide it is better to use Action Queries to APPEND and UPDATE, 

rather than SQL commands where we have to turn off warnings. then on.

This is why i went down this path

I will surely then try this approach you recommend above.

Thank you.

Answer 3

OK, this is unnecessary. First, all you need for a DELETE query is the primary key.

strSQL = "DELETE * FROM table WHERE PK = " & Me.PKcontrol

CurrentDB.Execute strSQL

But if you have Casacde deletes set into your relationship, then deleting the parent record will delete the child records.

Answer 4

Hi Scott, 

Thank you for your follow up.

I guess you can advise me if this would be the correct way.

Basically, I have a list of records in the form, which are retrieved by a query.

When i click on the DoCmd.GotoRecord, 

then the row is highlighted.  I wish to delete this record.

Now I have this record in 2 tables to achieve the many to many relationship.

I have been able from your and other feedback to populate the tables.  This was achieved by a APPEND Action Query.

My thought here was to now create a DELETE Action Query, to delete the rows of data from the 2 tables, 

after having determined what records to read, from the DoCmd.GotoRecord

The loop that I had was misleading... it was just a sample that proved that I can read the records from the table.

But rather, i just wish to read the field values of the record that is highlighted from the DoCmd.GotoRecord.

Thank you again in advance.

Answer 5

Can you explain what you are trying to accomplish here. Reading the Field values into what? What do you plan on doing with them?

Your question is like asking what does one do with a hammer. The answer is drive nails. But it would help to know what you want to build.

What you need to do is loop through all the fields in the Recordset:

For Each fld In MyRS.Fields

On Error Resume Next

tfield = "txt" & fld.Name & ".value"

ffield = "MyRS!" & fld.Name

tfield = ffield

Next fld

But this only gets you a listing. In other words it does nothing by itself.