Simple SQL statement in VB

[JFo]

Just trying to add the selection from a combo box into the dialog prompt/ error catcher message, however need help with my SQL command

Dim x As Integer
Dim NewData As String
Dim strSQL As String

NewData = Me.cmbEquipType.Value()
strSQL = "SELECT tblEquipmentType([EquipTypeName]) FROM tblEquipmentType" & _
" WHERE [tblEquipmentType].EquipTypeID = newData ;"


x = MsgBox("Do you want to add a" & Chr(34) & strSQL & _
Chr(34) & "EquipType to this inspection?", 4, "Exit?")

As you can see at the moment it actually prints to screen the SQL statement

I can get it to print to screen the combo value with

x = MsgBox("Do you want to add a" & Chr(34) & newData & _
Chr(34) & "EquipType to this inspection?", 4, "Exit?")

however because the bound column is the ID which I need for other things I simply need an SQL statement in vb to get the Name

I am fairly good with SQL in stand alone sense however I struggle using SQL statements in VB

Thanks

[orange]

For debugging, I'd recommend you use a number of Debug.Print variable_name in your code. This will provide info on the immediate window and allow you to visually check the contents of various variables before actually executing some sql.

eg Debug.print strSQL

I haven't tested this but my view is that

strSQL = "SELECT tblEquipmentType.EquipTypeName FROM tblEquipmentType" & _
" WHERE [tblEquipmentType].EquipTypeID = newData ;"

should be

strSQL = "SELECT tblEquipmentType([EquipTypeName]) FROM tblEquipmentType" & _
" WHERE [tblEquipmentType].EquipTypeID = " & newData & ";"

This would evaluate the value of newData and substitute it into the SQL string.

A Debug.print strsql would tell you for sure.

[JFo]

Thanks for your feed-back, not entirely sure how to use Debug.Print strSQL I put this in my code a number of times however it does nothing when I run normally or when I compile database.

I already know that newData is getting the correct EquipTypeID from the combo box I just want an SQL statement that converts the EquipTypeID to the EquipTypeName however I don't know how to do this in VB

This is correct SQL for my statement

SELECT tblEquipmentType.EquipTypeName FROM tblEquipmentType WHERE [tblEquipmentType].EquipTypeID =Forms!frmEquipment.EquipTypeID;

Also is there any application that can convert SQL to VB(Using SQL)? Or anywhere resources that could be recommended

Thanks

[pbaldy]

Presuming the other field is in the combo row source, simply:

MsgBox "Do you really want to add a " & Me.cmbEquipType.Column(1)

or whatever the appropriate column number is.

[JFo]

excellent that's it... no need to use sql an SQL statement if you extract the value like that, thanks

[pbaldy]

No problem; simpler is usually better.