Issues in access

[bindu]

hey...

see i have two tables


TestTable has - doortrimpanelID,field1,field2.....
VehicleTable has -
vehicleID,DoortrimpanelID(foreignkey),OEM,VehicleModel,Modelyear


now my form has


Combobox OEM which has entries populated from the vehicle table...
Combobox VehicleModel ''
Combobox Modelyear ''


i have a command button search and a check box "ALL"
and a texbox to display the result.


I was writing the code to display all the fields of the testtable when
a particular oem ,model,year is selected and the checkbox is
enabled....


When i execute the code it says syntax error in from clause...i dont
understand what to do...

Private Sub Command6_Click()


Dim strValueList$
Dim rs As DAO.Recordset
Dim strSQL As String, strTemp As String
Dim i As Integer
Dim count As Integer
i = 0

If Check11.Value = -1 Then

strSQL = "SELECT * FROM VehicleTable INNER JOIN TestTable" & _
"ON TestTable.DoorTrimPanelID =
VehicleTable.DoorTrimPanelID" & _
"WHERE TestTable.OEM = " & Me.Combo0 & "" & _
"AND TestTable.VehicleModel = " & Me.Combo2 & "" & _
"AND TestTable.ModelYear = " & Me.Combo4 & ""

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
Text143.Value = strTemp

End Sub

 

[Douglas J Steele]

You're missing some spaces. Look closely at how you're concatenating the
string: you're going to end up with

SELECT * FROM VehicleTable INNER JOIN TestTableON TestTable.DoorTrimPanelID
= VehicleTable.DoorTrimPanelIDWHERE etc.

Try:

strSQL = "SELECT * FROM VehicleTable INNER JOIN TestTable " & _
"ON TestTable.DoorTrimPanelID = VehicleTable.DoorTrimPanelID " & _
"WHERE TestTable.OEM = '" & Me.Combo0 & "' " _
"AND TestTable.VehicleModel = '" & Me.Combo2 & "' " & _
"AND TestTable.ModelYear = '" & Me.Combo4 & "'"

Note that this assumes that all three of OEM, VehicleModel and ModelYear are
text fields. Note, too, that I changed your quotes in the WHERE clause.
Exagerating for clarity, that's:

"WHERE TestTable.OEM = ' " & Me.Combo0 & " ' " _
"AND TestTable.VehicleModel = ' " & Me.Combo2 & " ' " & _
"AND TestTable.ModelYear = ' " & Me.Combo4 & " ' "
..
If any of the three fields are numeric, rather than text, remove the single
quotes on either side of the reference to the combo box.

 

[TC]

Bindu, please do not post the same question twice!

I've just helped you in the other thread. So now, either I - or Douglas
- or both of us - have wasted our time by answering your question
/twice/.

TC (MVP Access)
http://tc2.atspace.com