Help needed with combo box

[kanthi]

I have this query where i want to display all the columns(attributes)
of the test table of a particluar vehicleOEM,model n year.



strSQL = "SELECT * FROM VehicleTable, TestTable WHERE
TestTable.DoorTrimPanelID = VehicleTable.DoorTrimPanelID" And
TestTable.OEM = (Value selected from the first combo box) And
TestTable.VehicleModel = (the value selected from the 2nd combo box)
And TestTable.ModelYear = (The value selected from the third combo box)

how do i place the value selected in the combo box into the query???can
anyone help me with the same.

 

[Sandra Daigle]

You simply concatenate the value into the SQL String. The ampersand (&) is
the concatenation operator. Below is how it would look - also using the line
continuation character to improve the display of the SQL string.

strSQL = "SELECT * FROM VehicleTable, TestTable WHERE " _
& "TestTable.DoorTrimPanelID = " & VehicleTable.DoorTrimPanelID _
& " AND TestTable.OEM = " & me.cbo1 _
& " AND TestTable.VehicleModel = me.cbo2 _
& " And TestTable.ModelYear = Me.cbo3box)

 

[kanthi]

It isnt displaying anything Sandra....It still says object required

strSQL = "SELECT * FROM VehicleTable, TestTable WHERE " _
& "TestTable.DoorTrimPanelID = " & VehicleTable.DoorTrimPanelID _
& " AND TestTable.OEM = " & Me.Combo160 _
& " AND TestTable.VehicleModel = Me.Combo162" _
& " And TestTable.ModelYear = Me.Combo10"

 

[Sandra Daigle]

You've got a quote in the wrong place -probably because I left one off in my
post and VBA plopped it in for you in the wrong place. I found several other
mistakes I left in as well -

strSQL = "SELECT * FROM VehicleTable, TestTable WHERE " _
& "TestTable.DoorTrimPanelID = " & VehicleTable.DoorTrimPanelID _
& " AND TestTable.OEM = " & Me.Combo160 _
& " AND TestTable.VehicleModel =" & Me.Combo162 _
& " And TestTable.ModelYear =" & Me.Combo10

One other thing I noticed is that you appear to be selecting data from two
tables but there is no join on the tables. This is going to create a
cartesian product of the two - is that what you really want?

 

[Klatuu]

Where is the error occuring? Is this code in the form module for the form
you are in?

 

[kanthi]

doesnt this statement mean
TestTable.DoorTrimPanelID = VehicleTable.DoorTrimPanelID am joining??
IS there anything else i need to do???

 

[Sandra Daigle]

No - it needs a join statement - Lets start back at the beginning with just
the SQL. You might want to create the query with dummy parameters in the
query designer and then view the SQL (View->SQL) to see exactly how the SQL
should look. But basically the select statement should look something like
this:

SELECT VehicleTable.*, TestTable .*
FROM VehicleTable INNER JOIN [TestTable]
ON VehicleTable.DoorTrimPanelID= TestTable.DoorTrimPanelID
Where TestTable.OEM = n
AND TestTable.VehicleModel = y
And TestTable.ModelYear = z;

Putting this into a VBA string with values concatenated in you would have:

StrSQL = "SELECT VehicleTable.*, TestTable .* " _
& "FROM VehicleTable INNER JOIN [TestTable] " _
& "ON VehicleTable.DoorTrimPanelID= TestTable.DoorTrimPanelID " _
& "Where TestTable.OEM = " & me.cbo1 _
& " AND TestTable.VehicleModel = " & me.cbo2 _
& " And TestTable.ModelYear = " & me.cbo3 & ";"

(The closing semi-colon is not strictly required)

You can debug.print strSQL and then copy the resulting string from the
Immediate window into SQL view of a new query to test it.

debug.print strSQL

 

[kanthi]

But i dont want to select the vehicle table attributes...

 

[kanthi]

This is the code which i was working on.....

Private Sub Command67_Click()
Dim strValueList$
Dim rs As DAO.Recordset
Dim strSQL, strTemp As String
Dim i As Integer
Dim count As Integer
i = 0

If Check101.Value = -1 Then

strSQL = "SELECT * FROM VehicleTable, TestTable WHERE " _
& "TestTable.DoorTrimPanelID = " & VehicleTable.DoorTrimPanelID _
& " AND TestTable.OEM = " & Me.Combo160 _
& " AND TestTable.VehicleModel =" & Me.Combo162 _
& " And TestTable.ModelYear =" & Me.Combo10 & ";"


Set rs = CurrentDb.OpenRecordset(strSQL)

If Not rs.EOF Then
rs.MoveFirst
Do Until rs.EOF

i = 0

count = rs.Fields.count - 1

For i = 0 To count


strTemp = strTemp & rs.Fields(i).Name & ": " & rs.Fields(i) & "
"


Next i






rs.MoveNext
Loop
Else
MsgBox "No Records"
End If


End If

 

[Sandra Daigle]

Ok - but why did you have it in the query anyway? Just curious.

 

[Sandra Daigle]

You need to make the SQL changes I suggested in my last post - you can take
out the reference to VehicleTable if you don't need it. Otherwise, are you
getting errors? What is happening? Again, you probably should use
debug.print to print the finshed SQL statement to the immediate window. From
there you can copy it into the SQL view of the query designer and attempt to
open the query. Often, this will help you debug the SQL string you are
attempting to build in code.

To check for an empty recordset you really need to check both the EOF and
BOF conditions. Both are true when the recordset is empty.

if not (rs.bof and rs.eof) then
'do your thing
endif