VBA for edit record not working

[sacacompany]

please help me locate issue in following code.....

Dim Records As DAO.Recordset
Dim Records1 As DAO.Recordset

Set Records1 = CurrentDb.OpenRecordset("Select ID, Paid From RentInvoice where ID = " & Me.Text6, dbOpenDynaset, dbAppendOnly)
Debug.Print ID, paid
With Records1
Records1.Edit
Records1!paid = True
Records1.Update
Set Records1 = Nothing

End With

 

[June7]

What is the issue - error message, wrong result, nothing happens?

Why do you need to use code to edit record? Not using a bound form?

Do you really need a yes/no check field? Don't you have DatePaid and maybe CheckNum fields?

 

[isladogs]

You can't use dbAppendOnly to update an existing record.
Why not just use an update query instead of a recordset?

 

[sacacompany]

thank u for quick reply...i ll try update query...thks

 

[Pat Hartman]

Welcome to the forum You will find that we have more success with helping you to solve problems when we know what the problem you are having is.

1. If you are appending a record, you would use the same code you have but with .AddNew rather than .Edit
2. Why are you not using a bound form?
3. Using control names like Text6 is very poor practice. When you manually create a control on the form, the very next thing you need to do is to modify the Name property of the control to give it a meaningful name. Next week, you won't have a clue what Text6 refers to.

 

[sacacompany]

thanks for the input Pat
your advice is well taken
regarding bound form ...i don't have good experience with them as i keep my tables on the local server in separate access files and the front end distributed to all staff. unbound forms are working fine and fast for me so far. if you have any advice i would love to have it

 

[Pat Hartman]

The whole point of using a RAD (Rapid Application Development) tool is to use its features. Bound forms are the primary feature of Access. It is what makes Access, Access. When you elect to bypass the features provided by the RAD tool, there is absolutely no reason to be using the RAD tool. Use some other platform that has a smaller footprint and gives you the features you want.

You seem to know little about VBA and DAO so I really don't understand the point of not using bound forms. Usually this mistake is made by experienced developers who think they can do "Access" better than Access can.

Your setup is correct. The BE belongs on the server and each user needs his own personal copy of the FE but this has nothing whatsoever to do with whether or not you use bound forms.

Bound forms automagically handle all data fetching/updating tasks. You add code in various form events to validate data so you can stop invalid records from being saved or show/hide controls or control other aspects of how the form looks. Use the Form's BeforeUpdate event for validation. It is the LAST event that runs before Access saves the dirty record and it can NEVER be bypassed. That means you have total control over whether any given record gets saved.

 

[sacacompany]

Well, your above reply is theoretically correct ... I have been developing applications on Access since 1997. it's a long journey from bound to unbound forms. You are right in saying that I bypass RAD. But this is done to deal with less educated users and slow networks in enterprises. the ultimate blame is given to the application instead of fixing the network. So I purposely switched to unbound forms although these require more of my development time and effort but save me from user usual complaints that the Access app runs sometimes and crashed other times!! But having said all this your point is very well taken. I now will use bound forms to try to work around and stop fetching all records on loading of form. Can you have any suggestions..thanks in advance

 

[June7]

Not the first experienced developer I've seen express preference for unbound forms. You still get to take some advantage of RAD, at least in building objects, just not the data handling part of forms. Access query designer is a convenient asset as well.

 

[ebs17]

i keep my tables on the local server in separate access files

Why different files as backends?
Because of the mass of data (2GB limit), because the data have nothing to do with each other, or because of the overview?

After 20 years you should be familiar with the DAO constants to use.
Likewise, I would argue that a Recordset1.Close is missing. Unless one fully trusts how garbage collection works, closing the recordset is more important than removing the reference.