vba courses london - Finding and updating records in a form

The UK's most regular instructor-led training courses.
Training information: vba courses london · Excel london

Resolved · Low Priority · Version Standard

Finding and updating records in a form

Louise has attended:
Access VBA course

by - delegate Louise [4 posts] (2007 Apr 27 Fri, 16:07) Reply

I want to be able to search a field in a form and if a question has not been answered correctly (currently reads "tbc") then go to that record so that the user can amend it.

I have tried using recordsets and it will successfully locate the record but when I change the record to say "No" and then press my button and the error appears again so the user needs to change it again I get error 3020.

I can see why I'm getting this error because I am not writing my inital change to the table but I'm not sure where to put the .update.

This is the code:

Me.RecordsetClone.MoveFirst
Me.Bookmark = Me.RecordsetClone.Bookmark
Me.RecordsetClone.FindFirst "ActionTakenNY = 'tbc'"
If Me.RecordsetClone.NoMatch = True Then
MsgBox "Everything ok"
Else
Me.Bookmark = Me.RecordsetClone.Bookmark
End If
Me.RecordsetClone.Close
End Sub

RE: Finding and updating records in a form

by - trainer David gold contributer [428 posts] (2007 Jun 1 Fri, 09:57) Reply

Hi Louise

Sorry for the delay in getting back to you, but we have been very busy.

If I was trying to solve this problem waht I would do is

- use what you have done so far, so that you go to the record that with tbc.

-I would include a button on the form that says update, so when you have change tbc to no, you would click the update button, this is where you would write the change to the database using the .update. You could take this a step further and have a message box that says "are you sure that you want to update this record.

Hope this helps and again sorry for the delay.

David

RE: Finding and updating records in a form

by - delegate Louise [4 posts] (2007 Jun 1 Fri, 09:59) Reply

Hi David,

Thank you for your reply I will give it a go!!

Louise

· Company Roles in Visual Basic Training
· What Is Microsoft Excel VBA and How Can It Help You Work Smarter?

Vba tip:

Good coding practice

It is important that you always comment the VBA code that you write. By doing this it will make the code much easier to read, as both yourself and other people will be able to see what the code is doing or meant to be doing. This is very important if there is more than one person who writes code in the company as you may have different styles of coding and may not really understand what the other person is trying to do. Also if you write some code and then do not touch it for a year or so you may actually forget what it is meant to be doing. so it is very important to include comments throughout your code and try and make sure that they are as useful as possible

View all VBA hints and tips