Update table with unbound combo box with sql string

  • Thread starter Thread starter Travis
  • Start date Start date
T

Travis

I am trying to update a table using a sql string. I am getting a runtime
error '3024'. Using Access 2000. This is my code:

Private Sub Combo22_AfterUpdate()
Dim strCombo22 As String
Dim numAcctNum As Double
Dim db As Database
Dim sql1 As String

strCombo22 = Me.Combo22
numAcctNum = Me.Account_Number
Set db = CurrentDb()

sql1 = "UPDATE [Loan Info].[Account Number] SET [ModificationOption] =" &
strCombo22 & " WHERE [Account Number]=" & numAcctNum & ";"

DoCmd.RunSQL sql1

End Sub
 
Hi Travis

Is [ModificationOption] a text field? If so then you must enclose the value
in quotes:

sql1 = "UPDATE [Loan Info].[Account Number] SET [ModificationOption] ='" &
strCombo22 & "' WHERE [Account Number]=" & numAcctNum & ";"

Note the single quotes after the "=" and before " WHERE".
 
First, at the beginning of your Update query, you are referring to a field
within
the table where you should be referring to the table name only. Basically, you
are telling Access to look for a table named [Loan Info].[Account Number],
which it can't find, hence the error 3024. It should be;

sql1 = "UPDATE [Loan Info] SET [ModificationOption] =""" &
strCombo22 & """ WHERE [Account Number]=" & numAcctNum & ";"

I also added additional quotes because, based on your post, Combo22
appears to be a string.

Second, you don't really need to declare variables to store the values
in your controls. You could just write;

sql1 = "UPDATE [Loan Info] SET [ModificationOption] =""" &
Me![Combo22] & """ WHERE [Account Number]=" & Me.[Account Number] & ";"

Last, it would seem a little unusual to use a Double data type variable to
store a value like "Account Number"
 
Thank you for the lesson in sql language. I've been trying to figure this
out all day. How do I disable the update warning? I don't want my users
seeing this message an update is going to happen. It might confuse them.

To answer your question about that Double data type variable, it was the
data type used when the database was passed to me. Rather than rebuild the
entire database I'm working with what was given to me.

Thanks again.

Beetle said:
First, at the beginning of your Update query, you are referring to a field
within
the table where you should be referring to the table name only. Basically, you
are telling Access to look for a table named [Loan Info].[Account Number],
which it can't find, hence the error 3024. It should be;

sql1 = "UPDATE [Loan Info] SET [ModificationOption] =""" &
strCombo22 & """ WHERE [Account Number]=" & numAcctNum & ";"

I also added additional quotes because, based on your post, Combo22
appears to be a string.

Second, you don't really need to declare variables to store the values
in your controls. You could just write;

sql1 = "UPDATE [Loan Info] SET [ModificationOption] =""" &
Me![Combo22] & """ WHERE [Account Number]=" & Me.[Account Number] & ";"

Last, it would seem a little unusual to use a Double data type variable to
store a value like "Account Number"

--
_________

Sean Bailey


Travis said:
I am trying to update a table using a sql string. I am getting a runtime
error '3024'. Using Access 2000. This is my code:

Private Sub Combo22_AfterUpdate()
Dim strCombo22 As String
Dim numAcctNum As Double
Dim db As Database
Dim sql1 As String

strCombo22 = Me.Combo22
numAcctNum = Me.Account_Number
Set db = CurrentDb()

sql1 = "UPDATE [Loan Info].[Account Number] SET [ModificationOption] =" &
strCombo22 & " WHERE [Account Number]=" & numAcctNum & ";"

DoCmd.RunSQL sql1

End Sub
Click to expand...
Click to expand...
 
Thank you for your help. Yes, [ModificationOption] is a text field.

Would I be able to accomplish the same update with a recordset? if so how
could you show me the code to do it?

Graham Mandeno said:
Hi Travis

Is [ModificationOption] a text field? If so then you must enclose the value
in quotes:

sql1 = "UPDATE [Loan Info].[Account Number] SET [ModificationOption] ='" &
strCombo22 & "' WHERE [Account Number]=" & numAcctNum & ";"

Note the single quotes after the "=" and before " WHERE".
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Travis said:
I am trying to update a table using a sql string. I am getting a runtime
error '3024'. Using Access 2000. This is my code:

Private Sub Combo22_AfterUpdate()
Dim strCombo22 As String
Dim numAcctNum As Double
Dim db As Database
Dim sql1 As String

strCombo22 = Me.Combo22
numAcctNum = Me.Account_Number
Set db = CurrentDb()

sql1 = "UPDATE [Loan Info].[Account Number] SET [ModificationOption] =" &
strCombo22 & " WHERE [Account Number]=" & numAcctNum & ";"

DoCmd.RunSQL sql1

End Sub
Click to expand...
Click to expand...
 
A couple of options.

You could turn off the warning temporarily, but it's important that you
turn it back on again. You could do this with;

DoCmd.SetWarnings (False)
DoCmd.RunSql sql1
DoCmd.SetWarnings (True)

The other option, which I prefer, would be to use the Connection method
to execute the query. To do this you will need to declare the connection
in the procedure, so your code would look like;

Private Sub Combo22_AfterUpdate()
Dim con As ADODB.Connection
Set con = CurrentProject.Connection
Dim strCombo22 As String
Dim numAcctNum As Double
Dim sql1 As String

strCombo22 = Me.Combo22
numAcctNum = Me.Account_Number

sql1 = "UPDATE [Loan Info] SET [ModificationOption] =""" &
strCombo22 & """ WHERE [Account Number]=" & numAcctNum & ";"

con.Execute sql1, dbFailOnError

Set con = Nothing

End Sub

This method will bypass the standard Access warning, but will still generate
an error message if something goes wrong.

--
_________

Sean Bailey


Travis said:
Thank you for the lesson in sql language. I've been trying to figure this
out all day. How do I disable the update warning? I don't want my users
seeing this message an update is going to happen. It might confuse them.

To answer your question about that Double data type variable, it was the
data type used when the database was passed to me. Rather than rebuild the
entire database I'm working with what was given to me.

Thanks again.

Beetle said:
First, at the beginning of your Update query, you are referring to a field
within
the table where you should be referring to the table name only. Basically, you
are telling Access to look for a table named [Loan Info].[Account Number],
which it can't find, hence the error 3024. It should be;

sql1 = "UPDATE [Loan Info] SET [ModificationOption] =""" &
strCombo22 & """ WHERE [Account Number]=" & numAcctNum & ";"

I also added additional quotes because, based on your post, Combo22
appears to be a string.

Second, you don't really need to declare variables to store the values
in your controls. You could just write;

sql1 = "UPDATE [Loan Info] SET [ModificationOption] =""" &
Me![Combo22] & """ WHERE [Account Number]=" & Me.[Account Number] & ";"

Last, it would seem a little unusual to use a Double data type variable to
store a value like "Account Number"

--
_________

Sean Bailey


Travis said:
I am trying to update a table using a sql string. I am getting a runtime
error '3024'. Using Access 2000. This is my code:

Private Sub Combo22_AfterUpdate()
Dim strCombo22 As String
Dim numAcctNum As Double
Dim db As Database
Dim sql1 As String

strCombo22 = Me.Combo22
numAcctNum = Me.Account_Number
Set db = CurrentDb()

sql1 = "UPDATE [Loan Info].[Account Number] SET [ModificationOption] =" &
strCombo22 & " WHERE [Account Number]=" & numAcctNum & ";"

DoCmd.RunSQL sql1

End Sub
Click to expand...
Click to expand...
Click to expand...
 
Hi Travis

Sorry, I missed the fact that you also had a field reference where you
should have just had the table.

sql1 = "UPDATE [Loan Info] SET [ModificationOption] ='" & strCombo22 _
& "' WHERE [Account Number]=" & numAcctNum & ";"

Is [Account Number] also a text field? If so then numAcctNum needs quotes
around it also:

sql1 = "UPDATE [Loan Info] SET [ModificationOption] ='" & strCombo22 _
& "' WHERE [Account Number]='" & numAcctNum & "';"

Instead of RunSQL you can use the Execute method on your database object:

CurrentDb.Execute sql1, dbFailOnError

This will avoid the annoying confirmation message that pops up.

I'm not sure what you mean by "accomplish the same update with a recordset".

You could open a recordset and edit the field, but I think executing a
single SQL command is a better option. Here's how you would do it:

Dim rs as Recordset
Dim sql1 as String

sql1 = "SELECT [ModificationOption] FROM [Loan Info] " _
& "WHERE [Account Number]=" & numAcctNum & ";"
Set rs = CurrentDb.OpenRecordset( sql1 )
If Not rs.EOF then
rs.Edit
rs![ModificationOption] = strCombo22
rs.Update
End If
rs.Close
Set rs = Nothing

This assumes that [Account Number] identifies a unique record in [Loan
Info].
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Travis said:
Thank you for your help. Yes, [ModificationOption] is a text field.

Would I be able to accomplish the same update with a recordset? if so how
could you show me the code to do it?

Graham Mandeno said:
Hi Travis

Is [ModificationOption] a text field? If so then you must enclose the
value
in quotes:

sql1 = "UPDATE [Loan Info].[Account Number] SET [ModificationOption] ='"
&
strCombo22 & "' WHERE [Account Number]=" & numAcctNum & ";"

Note the single quotes after the "=" and before " WHERE".
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Travis said:
I am trying to update a table using a sql string. I am getting a
runtime
error '3024'. Using Access 2000. This is my code:

Private Sub Combo22_AfterUpdate()
Dim strCombo22 As String
Dim numAcctNum As Double
Dim db As Database
Dim sql1 As String

strCombo22 = Me.Combo22
numAcctNum = Me.Account_Number
Set db = CurrentDb()

sql1 = "UPDATE [Loan Info].[Account Number] SET [ModificationOption] ="
&
strCombo22 & " WHERE [Account Number]=" & numAcctNum & ";"

DoCmd.RunSQL sql1

End Sub
Click to expand...
Click to expand...
Click to expand...
 
Back
Top