Writing to a table from an unbound form.

[rodrunner79]

Hey guys, it's me again.

Question, if I had a form that has no record source (a table/query bound to it) and one of the controls (textbox) has a value I want to write to a table in the database, how do I do this?

For example:
A user open's a form (frmForm1), the form has a control called txtBox1, and a command button called Save. Now the user inputted "Hello" on txtBox1 and clicked Save. What I want the command button to do is when clicked I want it to write the value of the txtBox to a table (tblHello).

Note: The form's record source is not bound to tblHello.

 

[PHV]

You may either execute an update query (DoCmd.RunSQL or CurrentDb.Execute methods) or play with a Recordset object (either DAO or ADODB).

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rodrunner79]

Hey thanks, I'm trying to prevent myself from accomplishing the task by using a query or using DoCmd.RunSQL ("UPDATE table1 SET table1.field = this WHERE (this and that and this);"). So I'll look into more about recordsets. Thanks for the tip.

 

[lespaul]

I'm trying to prevent myself from accomplishing the task by using a query or using DoCmd.RunSQL ("UPDATE table1 SET table1.field = this WHERE (this and that and this);")

Why would you prevent yourself from using these techniques?


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[fneily]

In the following: Private Sub and End Sub will already be there when you enter the On Click Event for the Command button.

Private Sub CommandButtonInternalName_OnClick()
Dim DB As Database
Dim RS As Recordset
Dim strWhere As String

Set DB = CurrentDb()
Set RS = DB.OpenRecordset("tblHello", dbOpenDynaset)
strWhere = "[TableFieldName] = " & Chr(34) & Me![txtBox1] & Chr(34)
RS.FindFirst strWhere
If RS.NoMatch Then
MsgBox "No match found"
Else
RS.Edit
RS![TableFieldName] = Me![txtBox1]
(any other field addtions)
RS.Update
End If
RS.Close
DB.Close
Set RS = Nothing
Set DB = Nothing
End Sub

If txtBox1 is a numeric, change the strWhere to
strWhere = "[TableFieldName] = " Me![txtBox1]

You must also reference the DAO library, in VBA click Tools - References and check the appropriate library such as Microsoft DAO Object Library 3.0 or whatever. Place this reference near the top. Make it like the fourth reference.

 

[rodrunner79]

Why would you prevent yourself from using these techniques?

Because if I use the DoCmd.RunSQL "Insert ....", then I would have to add theline "SendKeys "y", False) above it to say yes to the prompt since it is an action query. There's nothing wrong with it except that when user looses focus of the program (Access) then the SendKeys is invalidated. Which by the way brings me to another question, besides the SendKey Action, is there another way to anticipate and say yes to a prompt unattended?

Also, thanks for your help fneily... Much appreciated.

 

[fneily]

You could create a macro that runs the SQL. So your macro may look something like this:
Echo bottom parameter set to NO
Setwarnings bottom parameter set to NO
RunSql bottom parameter set to SQL statement
Echo Yes
Setwarnings Yes

The Setwarnings dictates whether the system prompt is shown or not. I use this method for my action queries in a macro.

 

[PHV]

Have a look at the DoCmd.SetWarnings method.
Furthermore, the CurrentDb.Execute method doesn't prompt the user (unless it is called with dbFailOnError).

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Ed2020]

PHV,

The CurrentDB.Execute method doesn't prompt the user even if you use dbFailOnError. The user will only see an error message if an unhandled error occurs whilst executing the query.

Ed Metcalfe.

Please do not feed the trolls.....