Using VBA with SQL - What's wrong here?

[scroce]

This code keeps generating the error message "Data type mismatch." Can you see anything blatently wrong here? I can't seem to get the SQL to work correctly. I have a feeling it's somewhere in the syntax, but I can't seem to find out exactly where.

Also can you set a string or int variable = to the value in a text box, like strMystring = Me!txtMytext.Value? - is this legal? Thanks in advance for help. See code below.

Private Sub cmdDeleteSelectedViolation_Click()

Dim frm As Form, ctl As Control
Dim varSelection As Variant
Dim intBuildingID As Integer
Dim strDeleteQuery As String


Set frm = Forms!frmViolations
Set ctl = frm!lstCurrentElectricalViolations


For Each varSelection In ctl.ItemsSelected

varSelection = ctl.Column(1)
intBuildingID = Me.txtBuildingID.Value

strDeleteQuery = "DELETE tblJunctionBuildingViolation.BuildingID, tblJunctionBuildingViolation.ViolationID FROM tblJunctionBuildingViolation WHERE (((tblJunctionBuildingViolation.BuildingID)='" & intBuildingID & "')AND ((tblJunctionBuildingViolation.ViolationID)='" & varSelection & "'));"

DoCmd.RunSQL (strDeleteQuery)
Next varSelection

Forms!frmViolations!.Refresh

End Sub

 

[TheInsider]

Try simply: strMystring = Me.txtMytext, also in your SQL string:

tblJunctionBuildingViolation.BuildingID)='" & intBuildingID & "'

You are enclosing your numeric value with ' '. This is only for enclosing text values:

'' text
## date
<nothing> numeric

Rob Marriott
rob@career-connections.net

 

[TheInsider]

So it should be:

strDeleteQuery = &quot;DELETE tblJunctionBuildingViolation.BuildingID, tblJunctionBuildingViolation.ViolationID FROM tblJunctionBuildingViolation WHERE (((tblJunctionBuildingViolation.BuildingID)=&quot; & intBuildingID & &quotAND ((tblJunctionBuildingViolation.ViolationID)=&quot; & varSelection & &quot)&quot;

And you don't need the &quot;Me&quot; in &quot;strMystring = Me.txtMytext&quot;, simply use strMystring = txtMytext

Rob Marriott
rob@career-connections.net

 

[scroce]

OK, tried that, now it gives me a syntax error. hmmmm.

comment - when i run the variable watch in the watch window, as far as i can tell everything is being passed through.

 

[TheInsider]

After closer inspection, this is probly your problem:

Forms!frmViolations!.Refresh

You can't have &quot;!.&quot;

should be:
Forms!frmViolations.Refresh

Understanding the different operators and their uses is quite important - the &quot;!&quot; and &quot;.&quot; operator are similar but not really interchangable.

Rob Marriott
rob@career-connections.net

 

[scroce]

thanks- yes i did correct that little glitch, however:

i am pretty sure that it is still getting hung up on the SQL - the syntax error i get is referring to the sql sequence. It's so maddening.

any other ideas?

 

[TheInsider]

Are you using the line continuation operator &quot;_&quot; like below, if you are breaking the statement up over many lines?:

&quot;DELETE * FROM tblJunctionBuildingViolation WHERE &quot;_
& &quot;((BuildingID = &quot; & intBuildingID & &quot AND &quot; _ & &quot;(ViolationID = &quot; & varSelection & &quot)&quot;

Also, is BuildingID really an integer or is it a string being used as an integer. Finally the problem most likely lies in the fact that you are assigning ViolationID a variant data type - try Val(varSelection), CInt(varSelection) or do away with the variant data type entirely.


Rob Marriott
rob@career-connections.net

 

[scroce]

no, i have not been using the continuing &quot;_&quot; operator - the expression is too long to fit in this editor window when i cut/paste, so it breaks it up

BuildingID is actually an integer, in fact it is a auto number that assigns a unique number to each new building entered into the database - 1,2,3.... etc. It is also a primary key field.

If i did away with the variant type, then what would I dim the variable as?

I will try the above - I appreciate your responses.

SC

 

[TheInsider]

Hi,
I should have pointed this out earlier, you don't want to reference varSelection itself, you want to do the following:

ListBoxName.ItemData(varSelection)

This will return the appropriate value.

Rob Marriott
rob@career-connections.net

 

[TheInsider]

Get rid of this line too:

varSelection = ctl.Column(1)
Rob Marriott
robert_a_marriott@yahoo.com

Hire me! Full-time, contract, whatever...shhh don't let my current employer know I said that.