Update Recordset Based on User Input Box (1 Viewer)

[davidb88]

Hi All -

I have a form in my front end database that is supposed to allow a user to search for a record based on account number and then make changes to the that record and for it to save in the back end database. I have the search function working where it populates different input boxes on the form with what is stored in the back end database but I cannot get the update function to work. I have tried to assign each input box with a variable and then run an Update SQL function to update each of the fields but the updates are not storing. Can anyone provide any example of a successful update statement that uses VBA variables in it or a way to update a specific record via a recordset type function?

Thank you!

 

[Uncle Gizmo]

Are you using an SQL Server backend?

 

[davidb88]

I am using an Access Backend. It is a relatively simple database and system with not much data held in it.

 

[Uncle Gizmo]

So are you using what is normally termed a "front end back end" database with forms, reports and queries in the front end and the tables are in the back end? The other feature of this system is the wizard that manages this, the "linked table manager"?

 

[davidb88]

Yes, that's what I am using. When new records are inputted it is done on the front end and then the linked table manager stores it in the backend tables. On the front end side now I would like to have an update form where the user searches for a record, it populates the form with what currently is in the database, the user makes updates to different fields as needed and then they press an update button to save those changes. Each record has an autonumber record ID which is what I have tried to use in the update SQL statement but it does not save the changes.

 

[Uncle Gizmo]

But I don't understand why you are having trouble updating the record? If your form finds the record, and you update the record, then the updates are automatically added to the back end table.

 

[davidb88]

Sorry maybe I'm not explaining it properly. So the user first puts an account number into the form. They press search. The other fields on the form populate with what is in the backend. The user then makes updates to the different fields and then presses a "Save Updates" button. Those updates aren't saving though. What I have that's not working is assign each input box to a vba variable and then an update statement that is updating each field on the table to the variables where the record id matches. That part of it isn't working though.

 

[Uncle Gizmo]

>>>The form populates with what's in the back end<<<

So is this form based on a record source, like the backend table?

 

[davidb88]

Yes, the form is based on the backend table. It is populated through a lookup by the primary key ID field.

I actually was able to get it to work through using a filtered recordset where the ID on the form equaled the ID on the backend.

Thank you for your help!