problem in the code

[kanthi]

I have the follwoing code where am trying to display all the
columns(attributes) of a paticular table "TEST" for a particular
vehicle record.

on the user form i have 3 combo boxes wher the user has to select the
OEM ,vehicle model,model year and enable the Checkbox "ALL" and press
the search button.
for some reason i cant figure out what the problem is with the
following code...i am new to access.............can somebosy help me on
the same.

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 = strSQL = "SELECT * FROM VehicleTable,TestTable WHERE
TestTable.DoorTrimPanelID = VehicleTable.DoorTrimPanelID AND
TestTable.OEM= Me.Combo160.Value AND
TestTable.VehicleModel=Me.Combo162.Value AND
TestTable.ModelYear=Me.Combo10.Value "

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

 

[TC]

You say the code has "a problem", but you don't say what the problem
/is/.

You need to say what you /expect/ to happen, and what /actually/
happens, before someone can help.

Here are some tips on how to ask your questions effectively:
http://tc2.atspace.com/0012-UsingUsenet.htm#PostSubject

 

[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

 

[TC]

When i execute the code it says syntax error in from clause

OK, this means that there is a syntax error in the FROM part of your
SQL statement.

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 & ""

First, you need a space before the ON keyword. Second, you need quotes
around some or all of the three combo box values.

I can fix the statement for you, if you tell me what are the data types
of the OEM field, the VehicleModel field, and the ModelYear field, in
the table?

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

 

[bindu]

OEM,vehicle model is of text type and model year is a number....In the
first place is my query correct according to what am planning to
do....i have given a detailed explaination of what i want...cud u plz
look into it.

 

[TC]

That was quick!

I can fix the SQL statement for you, but I can't review the whole thing
for you. It's 1 a.m. in the morning here, and I am going to bed in a
moment!

This should fix the SQL statement. Be sure to cut & paste exactly. I
have added some necessary extra spaces, and quote-marks:

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

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

 

[bindu]

Hey thnx...

may be u cud review the rest mor..am not in a hurry...will try with
this code for now....

thnx a lot...gud nite