Cascading Combo Box Issues

[nika.duncan]

Hey,

I am trying to create cascading combo box where when I select the make of a vehicle only models for that make will appears. The code below is what was used. When I select the make on the form "frmVehicleDetails" and click on the dropdown for the model I receive the error "Syntax error in date in query expression 'tblVehicleModel.VehicleModel#". Attached is the file "FuelManagement1.zip" I am working with any help would be appreciated in resolving this issue.

Option Compare Database

Private Sub cboVehicleMakeName_AfterUpdate()


'Set the Vehicle Modelcombo box to be limited by the selecetd Vehicle Make.
Me.cboVehicleModel.RowSource = "SELECT tblVehicleModel.VehicleModel#, tblVehicleModel.VehicleModel FROM tblVehicleModel " & _
    " WHERE VehicleMake# = " & Nz(Me.cboVehicleMakeName) & _
    " ORDER  BY VehicleModelName"


Me.cboVehicleModel = Null


EnableControls




End Sub


Private Sub EnableControls()


'Clear the combo bozes
If IsNull(Me.cboVehicleMakeName) Then
    Me.cboVehicleModel = Null
End If
 
End Sub


Private Sub Combo6_AfterUpdate()
'Set the Vehicle Modelcombo box to be limited by the selecetd Vehicle Make.
Me.Combo8.RowSource = "SELECT tblVehicleModel.VehicleModel#, tblVehicleModel.VehicleModel FROM  tblVehicleModel " & _
    " WHERE VehicleMake# = " & Nz(Me.Combo8) & _
    " ORDER  BY VehicleModel"


Me.Combo8 = Null


EnableControls
End Sub


Private Sub Form_Load()


'When the form loads, enable/disable the combo boxes.  Combo boxes are only enabled if the preceeded combo box has a value.
EnableControls


End Sub

Thanks.
Nika

[Welshgasman]

Possibly the # which is used to surround dates?

[Gicu]

Any field names containing spaces and\or special characters need to be enclosed in square brackets:
SELECT [VehicleModel#], VehicleModel FROM tblVehicleModel.....

Cheers,

[Bob Fitz]

Try the attached db:
BTW I would advise against the use of special characters like # in the naming of any Access object.

[nika.duncan]

Thanks I will go through and edit the field names.

[nika.duncan]

Hey,

I need help in correcting the code behind the form. I corrected the issues with the field names but I am having another problem. When I select the make and try selecting the model I get a message " Enter Parameter Value" as shown below once I entered the number for the make the models are listed.



Please see the code inserted below also attached is the database

Private Sub cboKingdomID_AfterUpdate()

  ' Set the Phylum combo box to be limited by the selected Kingdom
  Me.cboPhylumID.RowSource = "SELECT tblPhylum.PhylumID, tblPhylum.PhylumName FROM tblPhylum " & _
     " WHERE KingdomID = " & Nz(Me.cboKingdomID) & _
     " ORDER BY PhylumName"
  Me.cboPhylumID = Null
  
  EnableControls
  FilterSpeciesList
  
End Sub

Any help will be appreciated.

ThanFuelManagement2.zipks
Nika
FuelManagement2.zip

[CJ_London]

according to your db the code behind cboVehicleMakeName_AfterUpdate is

'Set the Vehicle Modelcombo box to be limited by the selecetd Vehicle Make.
Me.cboVehicleModel.RowSource = "SELECT tblVehicleModel.VehicleModelNum, tblVehicleModel.VehicleModel FROM tblVehicleModel " & _
    " WHERE VehicleMakeNum = " & Nz(Me.cboVehicleMakeName) & _
    " ORDER  BY VehicleModelName"

You don't have a field called VehicleModelName in tblVehicleModel (suspect this should be VehicleMakeName)

[June7]

If you want to fiilter Model by VehicleMakeNum then need to include VehicleMakeNum in Make combobox RowSource.
SELECT VehicleMakeNum, VehicleMakeName FROM tblVehicleMake ORDER BY VehicleMakeName;

Then RowSource for Model:
SELECT VehicleDetailNum, VehicleModelName, VehiclePlate FROM tblVehicleDetail WHERE VehicleMakeNum = cboVehicleMakeName ORDER BY VehicleModelName, VehiclePlate;

And in VBA no need to set RowSource, just:
Me.cboVehicleModel.Requery