Simple question - Select/Edit Record with vba

[systemx]

Hi all,

I have tried to find the solution for this on the site...not sure if I am searching for the right terms though as I cannot seem to locate what I need. I was hoping someone could help me out.

I am trying to select a record where the the field 'ID' equals 'Me.txtans.Value' and update other fields in that record.

Here's what I have -

Private Sub cmdSubmit_Click()

Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset
rs.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source= G:\...\complaints.mdb;"
rs.Open "tblEnquiries", , adOpenKeyset, adLockOptimistic, adCmdTable

With rs
If .Fields("ID") = Me.txtAns.Value
.Fields("Date_Received") = Date
.Fields("Time_Received") = Time
.Fields("Type") = Me.cboType.Value
.Fields("Category") = Me.cboCategory.Value
.Fields("Description") = Me.txtDescr.Value
.Fields("Customer_Outcome") = Me.txtCusOut.Value
.Update
End If
End With

rs.Close
Set rs = Nothing

End Sub

I am absolutely certain my code to select the record is incorrect...but I'm really not sure what I should be using. I tried to use a SQL 'SELECT' statement - but it did not like my syntax - I kept getting a 'Case' error.

Could anyone advise what the correct way to select a record from a table is please?

Thank you

Rob

 

[systemx]

Got it! I am using

Dim strSQL As String

strSQL = "UPDATE tblEnquiries SET Category =" & Chr(34) & Me.cboCategory.Value & Chr(34)
strSQL = strSQL & " WHERE ID = " & Me.txtAns.Value & ";"
CurrentDb.Execute strSQL

strSQL = "UPDATE tblEnquiries SET Type =" & Chr(34) & Me.cboType.Value & Chr(34)
strSQL = strSQL & " WHERE ID = " & Me.txtAns.Value & ";"
CurrentDb.Execute strSQL

strSQL = "UPDATE tblEnquiries SET Description =" & Chr(34) & Me.txtDescr.Value & Chr(34)
strSQL = strSQL & " WHERE ID = " & Me.txtAns.Value & ";"
CurrentDb.Execute strSQL

strSQL = "UPDATE tblEnquiries SET Customer_Outcome =" & Chr(34) & Me.txtCusOut.Value & Chr(34)
strSQL = strSQL & " WHERE ID = " & Me.txtAns.Value & ";"
CurrentDb.Execute strSQL

strSQL = "UPDATE tblEnquiries SET Date_Received =" & Chr(34) & Date & Chr(34)
strSQL = strSQL & " WHERE ID = " & Me.txtAns.Value & ";"
CurrentDb.Execute strSQL

strSQL = "UPDATE tblEnquiries SET Time_Received =" & Chr(34) & Time & Chr(34)
strSQL = strSQL & " WHERE ID = " & Me.txtAns.Value & ";"
CurrentDb.Execute strSQL

strSQL = "UPDATE tblEnquiries SET Method_of_Response =" & Chr(34) & Me.cboTypeRes.Value & Chr(34)
strSQL = strSQL & " WHERE ID = " & Me.txtAns.Value & ";"
CurrentDb.Execute strSQL

strSQL = "UPDATE tblEnquiries SET Response_Required =" & Chr(34) & Me.chkResReq.Value & Chr(34)
strSQL = strSQL & " WHERE ID = " & Me.txtAns.Value & ";"
CurrentDb.Execute strSQL

MsgBox "Thankyou. Your complaint was succesfully submitted"

Cheers

Rob

 

[RuralGuy]

I would think one update would work as well:

strSQL = "UPDATE tblEnquiries SET " & _
"Category =" & Chr(34) & Me.cboCategory & Chr(34) & " ," & _
"Type =" & Chr(34) & Me.cboType & Chr(34) & " ," & _
"Description =" & Chr(34) & Me.txtDescr & Chr(34) & " ," & _
"Customer_Outcome =" & Chr(34) & Me.txtCusOut & Chr(34) & " ," & _
"Date_Received =" & Chr(34) & Date & Chr(34) & " ," & _
"Time_Received =" & Chr(34) & Time & Chr(34) & "," & _
"Method_of_Response =" & Chr(34) & Me.cboTypeRes & Chr(34) & " ," & _
"Response_Required =" & Chr(34) & Me.chkResReq & Chr(34) & _
" WHERE ID = " & Me.txtAns & ";"
CurrentDb.Execute strSQL

If you replace Date() with Now() you can record Date and Time in the same field. The .Value property is the default property of most controls and as such need not be specified.