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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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.
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
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.
ASKER
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.
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.
1. When you use:
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:
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 warning3. 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.