Issues in access

  • Thread starter Thread starter bindu
  • Start date Start date
B

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
 
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.
 
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
 
Back
Top