How to make this query

tarek_ta2ta2

Tarek
Local time
Yesterday, 16:12
Joined
Jun 8, 2008
Messages
47
hi all

i make a database for cars so i will give u a brief a bout what i did

i create number of combo box so the user can select values form it
Ex:
Car make - model - transmission - year and so on

i learn how to make every combo box available after the selection of the previous combo

i restructure my database as u advice me
so put all the makes in one table so the models

i made a query so when select the car make and model the sub model will appear according to the model

so my final touch in this is to load the price field according to the slections in the combos

so again according to the advice from u i made a table called tblvehicle
contain all the basic data so i will make a query to test the data in my combos according to the data in this table

can anyone help me in this
 

Attachments

can anyone help me in this

Code:
Dim strSQL As String
Dim intCount As String
Dim lngPrice As Long
Dim myRS As Recordset
strSQL = "SELECT a.VehicleID, a.MakeID, a.ModelID, a.GradeId, a.TransmissionID, a.BasePrice "
strSQL = strSQL & "FROM tblVehicles AS A "
strSQL = strSQL & "WHERE (((a.MakeID)= '" & Me.cboMake & "') AND ((a.ModelID)= '" & Me.cboModel & "') "
strSQL = strSQL & "AND ((a.GradeId)='" & Me.cboGrade & "') AND ((a.TransmissionID)= '" & Me.cboTrans & "'));"
Debug.Print strSQL
Set myRS = CurrentDb.OpenRecordset(strSQL)
If myRS.RecordCount > 0 Then
    'found record
    lngPrice = myRS("BasePrice")
    MsgBox "Price found for selection = " & lngPrice
Else
    MsgBox "No data available"
End If
myRS.Close
Set myRS = Nothing

Replace you Value button code with this. It worked for me. BUT i note you have set the ID's as text data types, you might want to change them to numerical...warning change this code by taking the 's from the strSQL area as the criteria is looking for text, removing them and the query looks for numbers.

Hope this helps.
 
thanks for ur great support and i'll give it a try and tell u

but there's one thing which id 's u talk about i review all my id's and it's all numerical so can u please tell me what u mean
 
dear sim

it was great really what u did , but now i face this

when i try to make adjustment in the code like ad more columns to the tblvehicle

like the yesrs

it create error here is the code i highlight what i add to ur original code

Private Sub Price_Click()
Dim strSQL As String
Dim intCount As String
Dim lngPrice As Long
Dim myRS As Recordset
strSQL = "SELECT a.VehicleID, a.MakeID, a.ModelID, a.GradeId, a.YearsID, a.TransmissionID, a.BasePrice "
strSQL = strSQL & "FROM tblVehicles AS A "
strSQL = strSQL & "WHERE (((a.MakeID)= '" & Me.cboMake & "') AND ((a.ModelID)= '" & Me.cboModel & "') "
strSQL = strSQL & "AND ((a.GradeId)='" & Me.cboGrade & "') AND ((a.YearsID)= '" & Me.cboYear & "') AND ((a.TransmissionID)= '" & Me.cboTrans & "'));"
Debug.Print strSQL
Set myRS = CurrentDb.OpenRecordset(strSQL)
If myRS.RecordCount > 0 Then
'found record
lngPrice = myRS("BasePrice")
MsgBox "Your Car Estimated Price SAR " & lngPrice
Else
MsgBox "No data available"
End If
myRS.Close
Set myRS = Nothing
End Sub


so what i do wrong
 
thanks for ur great support and i'll give it a try and tell u

but there's one thing which id 's u talk about i review all my id's and it's all numerical so can u please tell me what u mean

Tarek..what i mean was when you built the vehicle table you set all of the ID fields to text, well the attached version anyway.

You may have set the NEW years field as Number and this would be cause the new error as there are ' (in blue below) included in the SQL statement

Code:
[COLOR=#ff0000]((a.YearsID)= [COLOR=blue]'[/COLOR]" & Me.cboYear & "[COLOR=blue]'[/COLOR])[/COLOR]

Hope this is clearer.
 
thanks man ... you are right i had set the NEW years field as Number

so i think i have to make all of the rest of the field numbers so i can u se it in calculation later

or what u think
 
thanks man ... you are right i had set the NEW years field as Number

so i think i have to make all of the rest of the field numbers so i can u se it in calculation later

or what u think

folks tell me..if it's a number store it as a number..else you have a cooking pot of trouble when you have to convert it from text etc...if people see a number they'll expect it to be stored as a number data type. It's worth the conversion now so you don't have hassle later.
 
when i change the fileds in the tblVehicles from text to number

this error appear - i change the strSQL to long


Private Sub Price_Click()
Dim strSQL As Long
Dim intCount As String
Dim lngPrice As Long
Dim myRS As Recordset
strSQL = "SELECT a.VehicleID, a.MakeID, a.ModelID, a.GradeId, a.YearsID, a.TransmissionID, a.BasePrice "
strSQL = strSQL & "FROM tblVehicles AS A "
strSQL = strSQL & "WHERE (((a.MakeID)= '" & Me.cboMake & "') AND ((a.ModelID)= '" & Me.cboModel & "') "
strSQL = strSQL & "AND ((a.GradeId)='" & Me.cboGrade & "') AND ((a.YearsID)= '" & Me.cboYear & "') AND ((a.TransmissionID)= '" & Me.cboTrans & "'));"
Debug.Print strSQL
Set myRS = CurrentDb.OpenRecordset(strSQL)
If myRS.RecordCount > 0 Then
'found record
lngPrice = myRS("BasePrice")
MsgBox "Your Car Estimated Price SAR " & lngPrice
Else
MsgBox "No data available"
End If
myRS.Close
Set myRS = Nothing
End Sub
 
Thanks sam u r great man

take this :)

as u know i have many combo boxs

so when i select value from one box and press the price button without complete the selection from the other combo the error in the attached picture appear

why ? i expect the message that said no data available appears

why this error window come up ?
 

Attachments

  • untitled.GIF
    untitled.GIF
    95.6 KB · Views: 119
I would also like to add my thanks to Call Me Sam, for I have been going nuts trying to get a coded 'WHERE' query to work. I could get it to work if selecting a Primary Key inputted from a forms text box. I could get it to work if writing the WHERE criteria into the code eg. SELECT * FROM tblWidgets WHERE tblWidgets.Country ='USA' , but no way could I get it to work if selecting anything other than the Primary Key from a text box, all I got were Parameters errors, it appears the answer was in the ' ' where its required to surround the form text box designation if selecting anything other than the Primary Key. My thanks again, my sanity is restored, but I have to ask why isn't this flagged up in the reference books, for I have been going backwards and forwards through them trying to find the problem, but no joy.
 

Users who are viewing this thread

Back
Top Bottom