SQL Syntax in VBA Problem with data type mismatch and Variant

[Phred]

Access 2007, This is a short chunk of code but I am getting a Run-time error 13 Type Mismatch. I think it is the "varJoinID As Variant" syntax in the SQL statement but I am not sure. Shouldn't there be a @ before the variable?

This is for a rough Prototype presentation on Tuesday. So I am forcing some of the variable content. You will see what I mean. I have never dimmed a Var and pulled it into SQL so I am not sure if I have the syntax right.

Private Sub PersonID_DblClick(Cancel As Integer)

Dim inPersonID As Integer
Dim stName As String
Dim stFieldName As String
Dim varJoinID As Variant
Dim stSQL As String

inPersonID = PersonID
stTableName = "Person_TBL"
stFieldName = "PersonID"
varJoinID = DMax("JoinID", "PropertyPeopleJoin_TBL")

stSQL = "UPDATE PropertyPeopleJoin_Tbl" & " SET PersonTable = '" & stTableName & "', PersonField = '" & stFieldName & "', PersonKey = " & inPersonID & "" & " WHERE JoinID = " & varJoinID & ";"
DoCmd.RunCommand stSQL

I appreciate your help.

Phred

[June7]

RunCommand is wrong method. Use:

DoCmd.SetWarnings False
DoCmd.RunSQL stSQL
DoCmd.SetWarnings True

or

CurrentDb.Execute

I don't see anything wrong with the syntax and I don't think the variant declaration should be an issue as long as JoinID is a number type field and the value returned by DMax is all digits. However, if there are no records in tables then that could cause error. Code should handle a fresh, empty startup database.

Suggest you test the UPDATE with all literal strings then change to concatenated variables one at a time and see which fails.

[Phred]

JoinID is a number and DMax is all digits. I tested one variable at a time.

I still get an error even with only one field in the SQL statement.

The table this is updating has 8 fields. The first 4 have data. This update query should be updating fields 5,6,7. I have truncated it to attempt just the first field. All the data types are correct but it still errors out on data type.

Could it be trying to write to field 1 instead of field 5? If this is possible then I need to account some way for fields 1,2,3,4 in the SQL statement so this update starts with Field 5. If this is the case I have no idea of the correct syntax to include the first four fields but not write anything to them. If this is the problem could you show me a sample of syntax to do that?

has data has data has data has data UPDATE UPDATE UPDATE
Field 1 Field 2 Field 3 Field 4 Field 5 Field 6 Field 7
JoinID PropertyTable PropertyField PropertyKey PersonTable PersonField PersonKey

Thanks

Phred

[Phred]

I just understood what you suggested in putting in the actual text and trying it instead of the variable.

I'll try that now.

Ignore the above.

Sorry

[Phred]

I have tried everything (except apparently the solution). I still get a Type Mismatch error 13. I reduced it to one field to update.

The JoinID field is an autonumber integer

My variable stTableName = "TableName" forces a string.

The destination field is PersonTable and I confirmed it is a text field 255 chars.

The watches show that the proper information is coming up in the variables.

I don't know what the syntax is to try to drop "TableName" in as literal text, instead of a variable, in the UPDATE statement. I looked and tried everything I could find. But nothing works.

I would take a suggestion here as to syntax necessary to force the literal text into the UPDATE statement to try it.

Private Sub PersonID_DblClick(Cancel As Integer)
Dim stSQL As String
Dim varJoinID As Variant
Dim stTableName As String
stTableName = "Person_TBL"
varJoinID = DMax("JoinID", "PropertyPeopleJoin_TBL")
stSQL = "UPDATE PropertyPeopleJoin_Tbl" & " SET PersonTable = '" & stTableName & "' & "" WHERE JoinID = " & varJoinID & ";"
DoCmd.RunCommand stSQL

Thanks, Phred

[orange]

Try


DoCmd.RunSQL stSQL instead of DoCmd.RunCommand stsql

[Phred]

Orange and June7:

You both suggested I try DoCmd.RunSQL stSQL and I figured it must be my syntax and not the DoCmd.RunCommand stSQL. How could it be that easy?

I finally tried it and wow, you were both right.

Thanks a million.

I will close this out.

Phred