Update Query 1

[lars7]

Hi,
I'm trying to create an update query that will enter a code into a field called "AFCCode" next to a code in another field called "code" so wherever the code in "code" appears as 11 the code in "AFCCode should be AFC34. This will hopefully be done from two textboxes.
I've tried a few variations of the code below to send the data from the boxes but I have had no success so far. It is creating the query but it is a Select query and there is no table in it.
Hope I've explained this ok and any help would be appreciated.

This is the code i'm trying to use:

Private Sub cmd2_Click()
' Pointer to error handler
On Error GoTo cmd2_Click_err
' Declare variables
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strwhere As String
Dim strcode As String
Dim strafccode As String
Dim strSQL As String
' Identify the database and assign it to the variable
Set db = currentdb
' Check for the existence of the query, create it if not found,
' and assign it to the variable
If Not QueryExists("qryaddcode") Then
Set qdf = db.CreateQueryDef("qryaddcode")
Else
Set qdf = db.QueryDefs("qryaddcode")
End If
If Len(strwhere) Then strwhere = "WHERE" & Mid(strwhere, 4)
' Build the SQL string
strSQL = "UPDATE tbltruststaff SET tbltruststaff.AFCCode = forms!frmprintreportdialog1!txt2"
WHERE tbltruststaff.Code = [Forms]![frmprintreportdialog1]![Txt1]
strwhere
' Pass the SQL string to the query
qdf.SQL = strSQL
' Turn off screen updating
DoCmd.Echo False
' Check the state of the query and close it if it is open
If Application.SysCmd(acSysCmdGetObjectState, acQuery, "qryaddcode") = acObjStateOpen Then
DoCmd.Close acQuery, "qryaddcode"
End If
' Open the query
DoCmd.OpenQuery "qryaddcode"
cmd2_Click_exit:
' Turn on screen updating
' Clear the object variables
Set qdf = Nothing
Set db = Nothing
Exit Sub
cmd2_Click_err:
' Handle errors
MsgBox "An unexpected error has occurred." & _
vbCrLf & "Please note of the following details:" & _
vbCrLf & "Error Number: " & Err.Number & _
vbCrLf & "Description: " & Err.Description _
, vbCritical, "Error"
Resume cmd2_Click_exit
End Sub

 

[PHV]

Get rid of all references to strwhere and try something like this:
strSQL = "UPDATE tbltruststaff SET AFCCode='" & Me!txt2 & "' WHERE Code=" & Me!Txt1

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

 

[Remou]

Hello again.
Sorry, I am not quite getting you. Do you wish to enter code 11 in a textbox and code AFC34 in another textbox and then update a table using these codes? If so, you may wish to try changing all this:
[tt] If Len(strwhere) Then strwhere = "WHERE" & Mid(strwhere, 4)
' Build the SQL string
strSQL = "UPDATE tbltruststaff SET tbltruststaff.AFCCode = forms!frmprintreportdialog1!txt2"
WHERE tbltruststaff.Code = [Forms]![frmprintreportdialog1]![Txt1]
strwhere[/tt]
To:
[tt]strSQL="UPDATE tbltruststaff SET tbltruststaff.AFCCode = '" & Me.txt2 & "' WHERE tbltruststaff.Code = " & Me.[Txt1][/tt]
This is assuming you are running the code on a form called frmprintreportdialog1 and that AFCCode is text and Code is numeric.

 

[lars7]

Hi there,
I tried both and in each case the query ran but then stoped with and error box appearing saying:

Data type mismatch in criteria expression.

I think i've removed all the reference to strwhere.
Here is the new code:

Private Sub cmd2_Click()
' Pointer to error handler
On Error GoTo cmd2_Click_err
' Declare variables
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strcode As String
Dim strafccode As String
Dim strSQL As String
' Identify the database and assign it to the variable
Set db = currentdb
' Check for the existence of the query, create it if not found,
' and assign it to the variable
If Not QueryExists("qryaddcode") Then
Set qdf = db.CreateQueryDef("qryaddcode")
Else
Set qdf = db.QueryDefs("qryaddcode")
End If
' Build the SQL string
strSQL = "UPDATE tbltruststaff SET tbltruststaff.AFCCode = '" & Me.Txt2 & "' WHERE tbltruststaff.Code = " & Me.[Txt1]
' Pass the SQL string to the query
qdf.SQL = strSQL
' Turn off screen updating
DoCmd.Echo False
' Check the state of the query and close it if it is open
If Application.SysCmd(acSysCmdGetObjectState, acQuery, "qryaddcode") = acObjStateOpen Then
DoCmd.Close acQuery, "qryaddcode"
End If
' Open the query
DoCmd.OpenQuery "qryaddcode"
cmd2_Click_exit:
' Turn on screen updating
' Clear the object variables
Set qdf = Nothing
Set db = Nothing
Exit Sub
cmd2_Click_err:
' Handle errors
MsgBox "An unexpected error has occurred." & _
vbCrLf & "Please note of the following details:" & _
vbCrLf & "Error Number: " & Err.Number & _
vbCrLf & "Description: " & Err.Description _
, vbCritical, "Error"
Resume cmd2_Click_exit
End Sub

 

[PHV]

And this ?[tt]
strSQL = "UPDATE tbltruststaff SET AFCCode = '" & Me!Txt2 & "' WHERE Code = '" & Me!Txt1 & "'"[/tt]


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

 

[lars7]

Hi PHV,
That worked but have you any idea why my database keeps freezing after I do it.I should have mentioned this in my last reply but I didn't see the conection untill it happened again there. I needed to come out of it then go back in to see if the table had updated.

 

[PHV]

Why not simply this ?
Private Sub cmd2_Click()
CurrentDb.Execute "UPDATE tbltruststaff SET AFCCode = '" & Me!Txt2 & "' WHERE Code = '" & Me!Txt1 & "'"
End Sub

I guess your problem was here:
DoCmd.Echo False

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

 

[lars7]

Hi PHV,
You where right about the "DoCmd.Echo False" and I didn't realise that the update could be done as simply as that. Could something like that be done for an append query too. I would like to add the starters to my stafftable so it would involve all the fields in the table.

 

[PHV]

Sure. A starting point:
CurrentDb.Execute "INSERT INTO tbltruststaff (AFCCode,Code,someDateField,someNumField) VALUES " _
& "('" & Me!Txt2 & "'" _
& ",'" & Me!Txt1 & "'" _
& ",#" & Format(Me![someDateControl], "yyyy-mm-dd") & "#" _
& "," & Me![someNumControl] & ")"

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

 

[lars7]

Thanks PHV for the starting point and for all the help with the query.