Basic query to vba translations

[Lewis]

Hi guys,

I don't have a lot of experience with vba but enough to make sense of the code. I'm trying to find simple examples illustrating the vba equivalent of the various query types. For example, the following performs an append to a table based on text box's on a form...

Dim db As database
Dim rec As Recordset

Set db = CurrentDb
Set rec = db.OpenRecordset("Select * from tblTable")

rec.AddNew
rec("tblField1") = Me.[Text1]
rec("tblField2") = Me.[Text2]
rec update

Set rec = Nothing
Set db = Nothing

So, it would seem that "update" = Append in this code, a strange translation, but ok, I can handle it.

What if I want to use one of the text boxes as the criteria for a record in the table and use the other text box as an update value of another field in the table? Insert? Seems I've seen that before somewhere, but could be wrong. Still, a basic example would be helpful. Same for Select and Delete types if possible....or a resource/example?

The old dog's learning new tricks, so any help is appreciated.



Bos

[June7]

Concatenate variables. Reference to control on form is a variable.

"SELECT * FROM tblTable WHERE ID=" & Me.Text1

"DELETE FROM tblTABLE WHERE ID=" & Me.Text1

"UPDATE tblTABLE SET Field1=" & Me.Text1 & " WHERE ID=" & Me.ID

"INSERT INTO tblTable(Field1, Field2, Field3) VALUES(" & Me.Text1 & ", '" & Me.Text2 & "', #" & Me.Text3 & "#")

Can use Execute method in VBA for SQL actions:

CurrentDb.Execute "INSERT INTO tblTable(Field1, Field2, Field3) VALUES(" & Me.Text1 & ", '" & Me.Text2 & "', #" & Me.Text3 & "#")

[ranman256]

Use queries, not VB.