can anyone help me plzz

K

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
 
S

Steve Schapel

Kanthi,

I am not sure whether this is the only problem. But try changing the
strSQL to like this...

strSQL = "SELECT * FROM VehicleTable INNER JOIN TestTable" & _
" ON TestTable.DoorTrimPanelID =
VehicleTable.DoorTrimPanelID" & _
" WHERE TestTable.OEM = '" & Me.Combo160 & "'" & _
" AND TestTable.VehicleModel = '" & Me.Combo162 & "'" & _
" AND TestTable.ModelYear = '" & Me.Combo10 & "'"

(assumes that OEM and VehicleModel and ModelYear are all text data type
fileds)
 
G

Guest

Hi Kanthi,

In addition to Steve's comments, I'd like to offer a few of my own. This
line of code should be re-written:

From:
Dim strSQL, strTemp As String
To:
Dim strSQL As String, strTemp As String

The reason is that strSQL will be created as a variant variable if it is not
typecast. In all likelihood, it will be converted into a string variable once
the assignment is done (ie. strSQL = "SELECT * FROM VehicleTable.....).
However, it's better to start with a string variable if you know you will
need it as a string variable.

My other comment involves naming conventions. It's always better to take
some time up-front to rename your controls, before writing any VBA code, so
that your code is a lot more readable. Names like Command67, Check101,
Combo160, Combo162, Combo10 and Text143 do not make for very clear code. Some
suggestions for names might include:

cmdDisplayColumns <---> Command67
chkAllVehicles <---> Check101
cboOEM <---> Combo160
cboVehicleModel <---> Combo162
cboModelYear <---> Combo10

and so forth. Here are some links to pages that discuss naming conventions:

Commonly used naming conventions
http://www.mvps.org/access/general/gen0012.htm
http://www.xoc.net/standards/default.asp

If you are still having problems after trying the suggestion that Steve
posted, then try inserting the following statements:

debug.print strSQL
stop '<---Don't forget to remove this later on

just before the statement
Set rs = CurrentDb.OpenRecordset(strSQL)


Run your code. Open the Immediate window (Ctrl G). Select the entire SQL
statement and copy it (Ctrl C). Create a new query. Dismiss the Add Table
dialog without adding any tables. In query design view, click on View > SQL
View. You should see the word "SELECT" highlighted. Replace this with the
line of code that you copied, by using Ctrl V (paste). Try running the query.
This will often times give you an error message that is easier to track down
the cause. If the SQL statement is valid, the query should run without any
problems. You can also try switching back to the more familiar Design View,
using the View menu option.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 
S

Steve Schapel

Kanthi,

Can you please copy/paste what you've now got in your code, into your
reply? Thanks.
 
K

kanthi

here is the code...am getting fed up with this...its a simple thing
which is taking such a long time...sorry to bother u

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 INNER JOIN TestTable" & _
" ON TestTable.DoorTrimPanelID =
VehicleTable.DoorTrimPanelID" & _
" WHERE 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


Text143.Value = strTemp


End Sub
 
G

Guest

Interesting. I was able to get your code to work. I created two tables,
TestTable and VehicleTable. I added four fields in TestTable indicated in
your SQL statement:
DoorTrimPanel (I used Number / Long Integer), OEM (text), VehicleModel
(text) and ModelYear (text). The VehicleTable includes DoorTrimPanelID
(Number / Long Integer), TestField1, and TestField2. I added some test
records to both tables.

I created a form, using the exact names for the controls that you indicated.
It's working fine for me. So, try my earlier suggestion of using a
debug.print statement to print the SQL statement to the Immediate Window.
Then create a new query based on this SQL statement. Try running it. (You
might also try posting it, so that we can see the resulting SQL statement,
after the variables have been substituted in).


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 
K

kanthi

hey tom

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....
 
G

Guest

Hi Kanthi,

If you'd like to send me a compacted and zipped copy of your database, I
will take a quick look at it for you. You can replace any sensitive data with
dummy data. If you are interested, send me a private e-mail message with a
valid reply-to address. My e-mail address is available at the bottom of the
contributor's page indicated below.

Please do not post your e-mail address (or mine) to a newsgroup reply. Doing
so will only attract the unwanted attention of spammers.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 
B

bindu

Thanks a million tom....u dont know how much you have helped
me.............i really appreciate ur patience and help!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

problem in the code 6
Issues in access 2
Error ! 1
help in query! 4
Syntax error in query! 1
Help needed with combo box 10
Help needed with combo box 1
Help in Assigning value using recordset 2

Top