We help IT Professionals succeed at work.

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

Medium Priority
77 Views
Last Modified: 2019-06-26
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
Comment
Watch Question

Paul Cook-GilesSenior Application Developer
CERTIFIED EXPERT
Commented:
dim strSQL as string
DoCmd.SetWarnings False

DoCmd.RunSQL   "Update tblQualitybase set StartDate = #" & me.txt.StartDate & "#, EndDate = #" & me.txt.EndDate & "# where ID = " & me.txt.ID

DoCmd.SetWarnings True

Open in new window


This code can be directly behind a button on frmQualityCheck, or you can create a stand-alone sub in the form module, and call it from a button.  Note that I used your field names ("txt.StartDate", etc) as you gave them;  if they're different on the form, you'll need to tweak the SQL to match.  Note also that this assumes that ID is a numeric field, and that StartDate and EndDate are date fields.
Owner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010
Commented:
You would normally do this in the click event of a button or in the before or afterupdate event of the form.  In a button, it would look something like:

Private sub cmdUpdate_Click

    Dim strSQL as string
    strSQL = "UPDATE tblQualityBase Set StartDate = #" & me.txtStartDate &"#, EndDate = #" & me.txtEndDate & "# " _
           & "WHERE ID = " & me.txtID
     currentdb.execute strsql, dbfailonerror

End Sub

Open in new window

Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:
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.
Dale JamesTherapist

Author

Commented:
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
Paul Cook-GilesSenior Application Developer
CERTIFIED EXPERT

Commented:
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.
John TsioumprisSoftware & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
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
Paul Cook-GilesSenior Application Developer
CERTIFIED EXPERT

Commented:
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.
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:
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
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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.
Dale JamesTherapist

Author

Commented:
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.