Selection

  • Thread starter Thread starter Chris
  • Start date Start date
C

Chris

How can I make in a form (continuous forms) a selection (/filter?) of the
records by 3 fields (Ex: Type, Dimensions, Quality) using combo boxes?
 
Chris said:
How can I make in a form (continuous forms) a selection (/filter?) of the
records by 3 fields (Ex: Type, Dimensions, Quality) using combo boxes?
Click to expand...

Have a look on my website at the section called Filter by Form. I have a
simple form that shows you how this is done. Look at the code of the form.
Write here if you can't adapt it for your database.
 
Chris,

This is a Romanian based news group. I strongly advise you to post your question in an English newsgroup to obtain an answer more quickly. However, I also advise you to post more details about your problem, such: the programming language you are using, the technology underlying your application (like .NET, MFC, VB 6 Forms or another technology), and so on.
 
Can you tell me what is wrong here? It doesn't work :
Private Sub cmbOpenForm_Click()
On Error GoTo Err_cmbOpenForm_Click

Dim stDocName As String
Dim Msg1 As String

Dim DenumireCrit As String
Dim MyDenumire As String

Dim DimensiuniCrit As String
Dim MyDimensiuni As String

Dim CalitateaCrit As String
Dim MyCalitatea As String

Dim DenumireField As String
Dim DimensiuniField As String
Dim CalitateaField As String

Dim Counter As Integer

Dim Crit As String

stDocName = "Stoc11"
'the form you are going to open
DenumireField = "[Denumire]"
'the name of your Denumire field in the form you are going to open
DimensiuniField = "[dimensiuni]"
'name of your Dimensiuni field
CalitateaField = "[Calitatea]"
'name of the field you will filter with like

'see which fields you need to include in your string by checking their
'checkboxes. If one is blank, end sub
Msg1 = "Daca bifati o casuta asigurati-va ca are date."

If IsNull(Me.cboDenumire) And Me.ChkDenumire = True Then
MsgBox Msg1
Exit Sub
End If

If IsNull(Me.cboDimensiuni) And Me.ChkDimensiuni = True Then
MsgBox Msg1
Exit Sub
End If

If IsNull(Me.cboCalitatea) And Me.chkCalitatea = True Then
MsgBox Msg1
Exit Sub
End If
Counter = 0
If Me.ChkDenumire = True Then
'include what is in cboDenumire
MyDenumire = Me.cboDenumire
DenumireCrit = DenumireField & "=" & MyDenumire
Counter = Counter + 1
Else
DenumireCrit = ""
End If

If Me.ChkDimensiuni = True Then
MyDimensiuni = Me.cboDimensiuni
DimensiuniCrit = DimensiuniField & "=" & Format(MyDimensiuni, "0")
Counter = Counter + 1
Else
DimensiuniCrit = ""
End If

If Me.chkCalitatea = True Then
MyCalitatea = Me.cboCalitatea
CalitateaCrit = CalitateaField & " Calitatea '*" & MyCalitatea & "*'"
Counter = Counter + 1
Else
CalitateaCrit = ""
End If


Select Case Counter

Case 1
'only one criteria has been chosen
If Me.ChkDenumire = True Then
'that field was chosen
Crit = DenumireCrit
End If

If Me.ChkDimensiuni = True Then
Crit = DimensiuniCrit
End If

If Me.chkCalitatea = True Then
Crit = CalitateaCrit
End If


Case 2
'if 2 fields have been chosen, identify which 2

If Me.ChkDenumire = True And Me.ChkDimensiuni = True Then
Crit = DenumireCrit & " AND " & DimensiuniCrit
End If

If Me.ChkDenumire = True And Me.chkCalitatea = True Then
Crit = DenumireCrit & " AND " & CalitateaCrit
End If

If Me.ChkDimensiuni = True And Me.chkCalitatea = True Then
Crit = DimensiuniCrit & " AND " & CalitateaCrit
End If

Case 3
'all 3 fields have been ticked
Crit = DenumireCrit & " AND " & DimensiuniCrit & " AND " & CalitateaCrit
End Select


DoCmd.OpenForm stDocName, acFormDS, , Crit

Exit_cmbOpenForm_Click:
Exit Sub

Err_cmbOpenForm_Click:
MsgBox Err.Description
Resume Exit_cmbOpenForm_Click


End Sub
 
I suck at reading code (short concentration span!). Can I suggest that you
put in a couple of lines just before
DoCmd.OpenForm stDocName, acFormDS, , Crit
which say
MsgBox stDocName
MsgBox Crit

Just so you can check if the criteria string and document name are correct.

Post the criteria string that you get here.

Can I also suggest that you put an apostrophe in front of the line
On Error GoTo Err_cmbOpenForm_Click
so that you get an actual error message which will take you to any faulty
line of code.



BTW, what happens when you run the code? Nothing at all? The form opens but
it isn't filtered at all?
Another thing, you have a few Exit Subs. Do you really want the sub to exit
at that point.
Finally, you will notice from my sample form that you need a different type
'punctuation' for different types of data (number, string, date)
Make sure that all your quote marks and apostrophes are correct.
Evi


Chris said:
Can you tell me what is wrong here? It doesn't work :
Private Sub cmbOpenForm_Click()
On Error GoTo Err_cmbOpenForm_Click

Dim stDocName As String
Dim Msg1 As String

Dim DenumireCrit As String
Dim MyDenumire As String

Dim DimensiuniCrit As String
Dim MyDimensiuni As String

Dim CalitateaCrit As String
Dim MyCalitatea As String

Dim DenumireField As String
Dim DimensiuniField As String
Dim CalitateaField As String

Dim Counter As Integer

Dim Crit As String

stDocName = "Stoc11"
'the form you are going to open
DenumireField = "[Denumire]"
'the name of your Denumire field in the form you are going to open
DimensiuniField = "[dimensiuni]"
'name of your Dimensiuni field
CalitateaField = "[Calitatea]"
'name of the field you will filter with like

'see which fields you need to include in your string by checking their
'checkboxes. If one is blank, end sub
Msg1 = "Daca bifati o casuta asigurati-va ca are date."

If IsNull(Me.cboDenumire) And Me.ChkDenumire = True Then
MsgBox Msg1
Exit Sub
End If

If IsNull(Me.cboDimensiuni) And Me.ChkDimensiuni = True Then
MsgBox Msg1
Exit Sub
End If

If IsNull(Me.cboCalitatea) And Me.chkCalitatea = True Then
MsgBox Msg1
Exit Sub
End If
Counter = 0
If Me.ChkDenumire = True Then
'include what is in cboDenumire
MyDenumire = Me.cboDenumire
DenumireCrit = DenumireField & "=" & MyDenumire
Counter = Counter + 1
Else
DenumireCrit = ""
End If

If Me.ChkDimensiuni = True Then
MyDimensiuni = Me.cboDimensiuni
DimensiuniCrit = DimensiuniField & "=" & Format(MyDimensiuni, "0")
Counter = Counter + 1
Else
DimensiuniCrit = ""
End If

If Me.chkCalitatea = True Then
MyCalitatea = Me.cboCalitatea
CalitateaCrit = CalitateaField & " Calitatea '*" & MyCalitatea & "*'"
Counter = Counter + 1
Else
CalitateaCrit = ""
End If


Select Case Counter

Case 1
'only one criteria has been chosen
If Me.ChkDenumire = True Then
'that field was chosen
Crit = DenumireCrit
End If

If Me.ChkDimensiuni = True Then
Crit = DimensiuniCrit
End If

If Me.chkCalitatea = True Then
Crit = CalitateaCrit
End If


Case 2
'if 2 fields have been chosen, identify which 2

If Me.ChkDenumire = True And Me.ChkDimensiuni = True Then
Crit = DenumireCrit & " AND " & DimensiuniCrit
End If

If Me.ChkDenumire = True And Me.chkCalitatea = True Then
Crit = DenumireCrit & " AND " & CalitateaCrit
End If

If Me.ChkDimensiuni = True And Me.chkCalitatea = True Then
Crit = DimensiuniCrit & " AND " & CalitateaCrit
End If

Case 3
'all 3 fields have been ticked
Crit = DenumireCrit & " AND " & DimensiuniCrit & " AND " & CalitateaCrit
End Select


DoCmd.OpenForm stDocName, acFormDS, , Crit

Exit_cmbOpenForm_Click:
Exit Sub

Err_cmbOpenForm_Click:
MsgBox Err.Description
Resume Exit_cmbOpenForm_Click


End Sub

of
the boxes?

Have a look on my website at the section called Filter by Form. I have a
simple form that shows you how this is done. Look at the code of the form.
Write here if you can't adapt it for your database.
Click to expand...
Click to expand...
 
Back
Top