Link to home
Start Free TrialLog in
Avatar of Dale James
Dale James

asked on

MS ACCESS: Update Access Table with contents from text boxes within form

Hello Team

Using VBA (SQL) within an Access Module and referencing the following elements, I would like to execute an Update action by transferring the contents of two text boxes within a form to an Access table where  text box txt.ID = "ID" column in target table.

Form Name: frmQualityCheck
Text Boxes:  txt.StartDate, contents to be transferred to StartDate field within tblQualityBase where txt.ID = ID field within tblQualityBase
                       txt.EndDate   contents to be transferred to EndDate field within tblQualityBase where txt.ID = ID field within tblQualityBase
                       txt.ID

Table Name:  tblQualitybase
Table Fields:  StartDate, EndDate, ID

Thank you in advance for any assistance provided.

Sincerely

Dale
SOLUTION
Avatar of Paul Cook-Giles
Paul Cook-Giles
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Note that I chose to use the execute method to perform this update.  I prefer this method for two reasons (3)

1.  When you use:
Docmd.SetWarnings False

Open in new window

you suspend any message from the system, so if the query does not work, you don't get any feedback.
2.  When you use:
doCmd.SetWarnings

Open in new window

if any of the code between the "False" and "True" settings generates an error, you may branch out of your code without the docmd.SetWarnings True command ever firing.  This can cause serious problems when you return to development mode and you run a query that deletes or updates a bunch of records that you shouldn't; you will not get an error message or warning
3.  the Execute method provides a way to trap errors which occur in your code.  I generally will write my SQL statement after assessing the values of all the parameters that I will be using in the query.  In my previous example, if you failed to enter txtEndDate, an error would be generated which you could assess in your error handler, but using docmd.SetWarnings you would receive no such error and would not know that the update statement failed.
Avatar of Dale James
Dale James

ASKER

Hello Paul and Dale

Thanks you for such a speedy response.

Just working on the your suggestions at the moment and I will feedback soon.

Thanks again

Dale
I typed this out in my test database, and got a "Compile error:  method or data member not found" on the field names that have txt. as a prefix.  If you get the same error, rename the fields on your form without the dot.
A variation on the answers provided using the After_Update event..
As soon as you type legit dates (start/end) these are reflected on your fields
Database2.accdb
Dale, your points are well made;  I should have included error-trapping in that sample code.
I've run into issues using CurrentDb.Execute against linked SQL tables, particularly those that have Identity columns.
Well, Paul, my example didn't include error handling either, but I've totally stopped using SetWarnings in favor of the Execute method, did this years ago.

Although a lot of my apps use SQL Server as the BE, I don't recall having issues with the Execute method with those.  I always ensure the every SQL table contains primary key (usually my identity column) and a RowVersion (TimeStamp) column.  With those two items, I don't ever recall having problems with action queries against a Linked SQL Server table, but I do write a lot of SPs and pass parameters to the SP, and sometimes simply open the recordset and write the values to the fields that way (best way to avoid SQL Injection).

Dale
Is there some reason that you are not using a bound form?   Most of my apps use SQL Server BEs and ALL of them use bound forms for updating.  Occasionally search forms might be bound to pass-through queries or stored procedures but the bulk of the application always makes use of Access' RAD features.
Hello Team

I have just realised that my original response and thanks posting did not log so just posting again in order to close question.

Just want to thank all for their contribution and advise..as always. very much appreciated.

Dale, thank you for your extended advise and provision of code.  This worked perfectly.

Paul, thank you for you postings and code, You were correct about the compile error due to incorrect naming of text box.  Once amended, your code also work as intended.

Pat, the form being used is actually a mixture of bound and unbound controls as a result of the initial build provided.  As an external backend table has been joined to the  original database, in order to ensure that no values are return via a bound control to the external backend table, the backend details are pulled for them only to be passed to an internal table.

Once again, thank you everyone for all the advise given.

Sincerely

Mark

John, very much appreciated your additional coding to expand the functionality of the original requirement.  This is something which I will definitely incorporate at some point.