Using three combo boxes to limit the records on a form.

G

Guest

Hello,
I have three for combo boxes that I would like to use to limit the results
on a form. I am having trouble geeting them to work together. I listed my
code below and I think my problem is with the last section:
Form_frmRMISGeneral.RecordSource = LSQL3.

I have three different code examples listed below for reference:

Thank you for your help in advance.

1) Two are used to caputure the Start and End Date form a calendar control
(LSQL & LSQL1)
2) LSQL2 is for the Ship Category
3) LSQL3 is for the the type of Ship in the Ship Category. I set the after
update to goto Sub Set Filter:
Private Sub cboShipTypeHull_AfterUpdate()
'Call subroutine to set filter based on selected Ship Class
SetFilter
End Sub

Sub SetFilter()
Dim LSQL As String
Dim LSQL1 As String
Dim LSQL2 As String
Dim LSQL3 As String

LSQL = "select * from qryMTTC"
LSQL1 = "select * from qryMTTC"
LSQL2 = "select * from qryMTTC"
LSQL3 = "select * from qryMTTC"

LSQL = LSQL & " where date_maintenance_action = '" & cboStartDate & "'"
LSQL1 = LSQL1 & " where date_maintenance_action = '" & cboEndDate & "'"
LSQL2 = LSQL2 & " where ship_class = '" & cboShipClass & "'"
LSQL3 = LSQL3 & " where ship_type_hull = '" & cboShipTypeHull & "'"

' Form_frmRMISGeneral.RecordSource = LSQL
' Form_frmRMISGeneral.RecordSource = LSQL1
' Form_frmRMISGeneral.RecordSource = LSQL2
Form_frmRMISGeneral.RecordSource = LSQL3
End Sub

I modified the following code but nothing happens:
Sub SetFilter()

Dim strShipClass As String
Dim strShipTypeHull As String
Dim StrStartDate As String
Dim strEndDate As String
Dim strFilter As String
' Check that the report is open
If SysCmd(acSysCmdGetObjectState, acForm, "frmRMISSplash") <>
acObjStateOpen Then
MsgBox "You must open the report first."
Exit Sub
End If
' Build criteria string for StartDate field
If IsNull(Me.cboStartDate.Value) Then
StrStartDate = "Like '*'"
Else
StrStartDate = "='" & Me.cboStartDate.Value & "'"
End If
' Build criteria string for EndDate field
If IsNull(Me.cboEndDate.Value) Then
strEndDate = "Like '*'"
Else
strEndDate = "='" & Me.cboEndDate.Value & "'"
End If
' Build criteria string for ShipClass field
If IsNull(Me.cboShipClass.Value) Then
strShipClass = "Like '*'"
Else
strShipClass = "='" & Me.cboShipClass.Value & "'"
End If

' Combine criteria strings into a WHERE clause for the filter
strFilter = "[date_maintenance_action] " & StrStartDate & " AND
[date_maintenance_action] " & strEndDate & " AND [ship_class] " & "
strShipClass & " AND [ship_type_hull] " & strShipTypeHull
' Apply the filter and switch it on
With Forms![frmRMISSplash]
.Filter = strFilter
.FilterOn = True
End With

Code Example 3:
'Dim strWhere As String
'Dim StrStartDate As String
'Dim strEndDate As String
'Dim strShipClass As String
'Dim strShipTypeHull As String

'StrStartDate = "#" & Format(Me.cboStartDate.Value, "mm/dd/yyyy") & "#"
'strEndDate = "#" & Format(Me.cboEndDate.Value, "mm/dd/yyyy") & "#"
'strShipClass = Me.cboShipClass.Value
'strShipTypeHull = Me.cboShipTypeHull.Value

'strWhere = "SELECT qry.MTTC.* " & _
'"FROM qry.MTTC " & _
'"WHERE tbl.Maintenance.date_maintenance_action = "
"when_discovered_date between" & StrStartDate & " and " & strEndDate'" & _
'"AND tbl.Maintenance.ship_class" & strShipClass & _
'"AND tbl.Maintenance.ship_type_hull" & strShipTypeHull & _
'"ORDER BY tbl.Maintenance.ship_class,tbl.Maintenance.ship_type_hull;"
 
S

strive4peace

Dates need to be delimited with #, not '
LSQL = LSQL & " where date_maintenance_action = '" &
cboStartDate & "'"
--> need to be -->
LSQL = LSQL & " where date_maintenance_action = #" &
cboStartDate & "#"

you need to put spaces between your concatenated strings
when you build your filter

ie: you have
StrStartDate = "#" & Format(Me.cboStartDate.Value,
"mm/dd/yyyy") & "#"
"WHERE tbl.Maintenance.date_maintenance_action = "
"when_discovered_date between" & StrStartDate & " and "

also, in a WHERE clause, you need to use
datefield >= startdate and <= enddate
not between

also, you need to remove
tbl.Maintenance.date_maintenance_action =
from the criteria

you also need to remember to include operators

strShipClass = Me.cboShipClass.Value
"AND tbl.Maintenance.ship_class" & strShipClass

where is your equal sign =
or some other operator?

after you build SQL or a where clause,

debug.print variablename

then, after you run the code
CTRL-G
to view the debug window

often, by manual inspection will see what is wrong

if it is SQL you are building, paste it into the SQL view of
a query and run it -- Access will tell you where the problem is


Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com


Hello,
I have three for combo boxes that I would like to use to limit the results
on a form. I am having trouble geeting them to work together. I listed my
code below and I think my problem is with the last section:
Form_frmRMISGeneral.RecordSource = LSQL3.

I have three different code examples listed below for reference:

Thank you for your help in advance.

1) Two are used to caputure the Start and End Date form a calendar control
(LSQL & LSQL1)
2) LSQL2 is for the Ship Category
3) LSQL3 is for the the type of Ship in the Ship Category. I set the after
update to goto Sub Set Filter:
Private Sub cboShipTypeHull_AfterUpdate()
'Call subroutine to set filter based on selected Ship Class
SetFilter
End Sub

Sub SetFilter()
Dim LSQL As String
Dim LSQL1 As String
Dim LSQL2 As String
Dim LSQL3 As String

LSQL = "select * from qryMTTC"
LSQL1 = "select * from qryMTTC"
LSQL2 = "select * from qryMTTC"
LSQL3 = "select * from qryMTTC"

LSQL = LSQL & " where date_maintenance_action = '" & cboStartDate & "'"
LSQL1 = LSQL1 & " where date_maintenance_action = '" & cboEndDate & "'"
LSQL2 = LSQL2 & " where ship_class = '" & cboShipClass & "'"
LSQL3 = LSQL3 & " where ship_type_hull = '" & cboShipTypeHull & "'"

' Form_frmRMISGeneral.RecordSource = LSQL
' Form_frmRMISGeneral.RecordSource = LSQL1
' Form_frmRMISGeneral.RecordSource = LSQL2
Form_frmRMISGeneral.RecordSource = LSQL3
End Sub

I modified the following code but nothing happens:
Sub SetFilter()

Dim strShipClass As String
Dim strShipTypeHull As String
Dim StrStartDate As String
Dim strEndDate As String
Dim strFilter As String
' Check that the report is open
If SysCmd(acSysCmdGetObjectState, acForm, "frmRMISSplash") <>
acObjStateOpen Then
MsgBox "You must open the report first."
Exit Sub
End If
' Build criteria string for StartDate field
If IsNull(Me.cboStartDate.Value) Then
StrStartDate = "Like '*'"
Else
StrStartDate = "='" & Me.cboStartDate.Value & "'"
End If
' Build criteria string for EndDate field
If IsNull(Me.cboEndDate.Value) Then
strEndDate = "Like '*'"
Else
strEndDate = "='" & Me.cboEndDate.Value & "'"
End If
' Build criteria string for ShipClass field
If IsNull(Me.cboShipClass.Value) Then
strShipClass = "Like '*'"
Else
strShipClass = "='" & Me.cboShipClass.Value & "'"
End If

' Combine criteria strings into a WHERE clause for the filter
strFilter = "[date_maintenance_action] " & StrStartDate & " AND
[date_maintenance_action] " & strEndDate & " AND [ship_class] " & "
strShipClass & " AND [ship_type_hull] " & strShipTypeHull
' Apply the filter and switch it on
With Forms![frmRMISSplash]
.Filter = strFilter
.FilterOn = True
End With

Code Example 3:
'Dim strWhere As String
'Dim StrStartDate As String
'Dim strEndDate As String
'Dim strShipClass As String
'Dim strShipTypeHull As String

'StrStartDate = "#" & Format(Me.cboStartDate.Value, "mm/dd/yyyy") & "#"
'strEndDate = "#" & Format(Me.cboEndDate.Value, "mm/dd/yyyy") & "#"
'strShipClass = Me.cboShipClass.Value
'strShipTypeHull = Me.cboShipTypeHull.Value

'strWhere = "SELECT qry.MTTC.* " & _
'"FROM qry.MTTC " & _
'"WHERE tbl.Maintenance.date_maintenance_action = "
"when_discovered_date between" & StrStartDate & " and " & strEndDate'" & _
'"AND tbl.Maintenance.ship_class" & strShipClass & _
'"AND tbl.Maintenance.ship_type_hull" & strShipTypeHull & _
'"ORDER BY tbl.Maintenance.ship_class,tbl.Maintenance.ship_type_hull;"
Click to expand...
 

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

Using three combo boxes to limit the records on a form 1
pass date from calendar to query on a report form 2

Top