You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
Today’s guest author is Justin Farrell, author of the Access 2007 tutorial http://www.dealing-with-data.net.
This is a useful trick which gives the Access Developer additional flexibility when working with forms and data. It uses Access VBA and DAO.
Once the user enters data into the unbound textbox (txtBox) and clicks the Send Data To Table button, Access opens a recordset based on the tblTest table. The txtBox data is then stored in a variable called varTextData and then added to the rst recordset. The recordset is updated and the txtBox control is reset ready for new data to be entered. The result is an unbound form that interacts with a database table.
Private Sub cmdSend_Click() Dim db As DAO.Database Dim rst As DAO.Recordset Dim varTextData As String varTextData = txtBox Set db = CurrentDb Set rst = db.OpenRecordset("tblTest", dbOpenDynaset) rst.AddNew rst!fldTest = varTextData rst.Update rst.Close db.Close Me!txtBox = "" End Sub
Justin’s supplied a sample database that illustrates this technique.
Comments: (6) Collapse
Excellent tip. Perfect example of how knowing just a little bit of VBA can really empower your Access databases.
Hi Justin, There's really no need to declare the database variable, since you are only using it one time. You could simply use: Set rst = CurrentDB.OpenRecordset("tblTest", dbOpenDynaset) However, a few points:
1.) You should not close things you do not open. You didn't open CurrentDB in code, so you should remove the line of code that attempts to close the db variable: db.Close. Change this to: Set db = Nothing 2.) If you are going to use the db variable, you should set it equal to Nothing at the end. 3.) You should always close and destroy DAO recordset variables, to help prevent database bloat. More information here: How to prevent database bloat after you use Data Access Objects (DAO) support.microsoft.com/.../289562 Here are my suggested changes: Private Sub cmdSend_Click()
On Error GoTo ProcError Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strTextData As String strTextData = txtBox Set db = CurrentDb Set rst = db.OpenRecordset("tblTest", dbOpenDynaset) rst.AddNew rst!fldTest = varTextData rst.Update Me!txtBox = "" ExitProc: 'Cleanup If Not rs Is Nothing Then rs.Close: Set rs = Nothing End If Set db = Nothing Exit Sub
ProcError: MsgBox "Error " & Err.Number & ": " & Err.Description, _ vbCritical, "Error in procedure cmdSend_Click..." Resume ExitProc
End Sub Tom Wickerath
Microsoft Access MVP
PS. I did not double space the code that I posted, so just close up the extra blank lines.
Just a note that this procedure will work with previous versions of Access as well, at least back to Access 97.
I would be inclined to trap for a null value and I would also probably validate that the data entry is the appropriate data type i.e. IsNumeric(Me.txtBox).
Why bother with a variable if you are direct driving from a form. If Not IsNull(Me!txtBox) then
.
rst!fldTest = Me!txtBox
End If
Hi guys How would you do this if you wanted to update two fields in a table from two unbound form controls? With thanks for your help.
Comments: (loading) Collapse