Combo Box frustration

  • Thread starter Thread starter Lisa Reber
  • Start date Start date
L

Lisa Reber

Hi - sorry if this is a dumb question, but I'm still
new at this.

Have a report that I want to filter with a pop-up form
like the sample in RptSmp97. I've gotten most of the
commands to work, except the most important - the filters.
Each of the combo boxes shows numeric data when it should
show text.

Source is table Products.
Filter1 = SELECT DISTINCT Products.SupplierID FROM
Products ORDER BY Products.SupplierID;
Filter2 = SELECT DISTINCT Products.CategoryID FROM
Products;
Filter3 = SELECT DISTINCT Products.[Tagged?] FROM
Products;

SupplierID and CategoryID are both look-up fields in
Products (is this a foreign key?)
Tagged is a check box - the combo box shows a drop-down
selection of Yes/No, then displays -1 or 0 respectively.

So, as usual, trying to explain the problem helps me
come up with a possible answer. If I make the report
source a query showing Supplier Name from tblSuppliers;
likewise CategoryID from tblCategories - this should
give me text fields rather than numeric ID's?

Second, when I try filtering only on Tagged = Yes, I get
error message: Syntax error (missing operator) in query
expression '([Tagged?] ="-1" A)'.
The OlClick [Event Procedure] is as follows (lifted almost
entirely from the RptSmp97 code):

Private Sub Set_Filter_Click()

Dim strSQL As String, intCounter As Integer
'Build SQL String
For intCounter = 1 To 3
If Me("Filter" & intCounter) <> "" Then
strSQL = strSQL & "[" & Me("Filter" &
intCounter).Tag & "] " & " = " & Chr(34) & Me("Filter" &
intCounter) & Chr(34) & " And "
End If
Next

If strSQL <> "" Then
'Strip Last " And "
strSQL = left(strSQL, (Len(strSQL) - 3))
'Set the Filter property
Reports![rptInventoryItems].Filter = strSQL
Reports![rptInventoryItems].FilterOn = True
End If

End Sub

So I don't know if the problem is 'not in our stars, but
in ourselves' or something - the code doesn't directly
copy, or that the combo box tagged is yes/no not -1/0.

Any help is greatly appreciated - thanks for your patience!
Regards, Lisa
 
From Lisa - got the Filter1 & Filter2 to show supplier &
category name, still having trouble with the Filter3:
tagged. don't know if it's because it's a check box or not?
 
I'm looking past all the SQL stuff, and guessing that the problem is in the
properties for your combo box. If the combo box is displaying the bound
column only; you will need to apply the following settings in your
properties window for the combobox:

Column Count: 2
Column Widths: 0";1" (Your choice, but 0" is necessary to hide the
first column)
Row Source Type: Value List
Row Source: 0;"No";-1;"Yes"
Bound Column: 1 (I am guessing that your bound column is set
to 2)

I hope this helps.
Paul Johnson

Lisa Reber said:
From Lisa - got the Filter1 & Filter2 to show supplier &
category name, still having trouble with the Filter3:
tagged. don't know if it's because it's a check box or not?
-----Original Message-----
Hi - sorry if this is a dumb question, but I'm still
new at this.

Have a report that I want to filter with a pop-up form
like the sample in RptSmp97. I've gotten most of the
commands to work, except the most important - the filters.
Each of the combo boxes shows numeric data when it should
show text.

Source is table Products.
Filter1 = SELECT DISTINCT Products.SupplierID FROM
Products ORDER BY Products.SupplierID;
Filter2 = SELECT DISTINCT Products.CategoryID FROM
Products;
Filter3 = SELECT DISTINCT Products.[Tagged?] FROM
Products;

SupplierID and CategoryID are both look-up fields in
Products (is this a foreign key?)
Tagged is a check box - the combo box shows a drop-down
selection of Yes/No, then displays -1 or 0 respectively.

So, as usual, trying to explain the problem helps me
come up with a possible answer. If I make the report
source a query showing Supplier Name from tblSuppliers;
likewise CategoryID from tblCategories - this should
give me text fields rather than numeric ID's?

Second, when I try filtering only on Tagged = Yes, I get
error message: Syntax error (missing operator) in query
expression '([Tagged?] ="-1" A)'.
The OlClick [Event Procedure] is as follows (lifted almost
entirely from the RptSmp97 code):

Private Sub Set_Filter_Click()

Dim strSQL As String, intCounter As Integer
'Build SQL String
For intCounter = 1 To 3
If Me("Filter" & intCounter) <> "" Then
strSQL = strSQL & "[" & Me("Filter" &
intCounter).Tag & "] " & " = " & Chr(34) & Me("Filter" &
intCounter) & Chr(34) & " And "
End If
Next

If strSQL <> "" Then
'Strip Last " And "
strSQL = left(strSQL, (Len(strSQL) - 3))
'Set the Filter property
Reports![rptInventoryItems].Filter = strSQL
Reports![rptInventoryItems].FilterOn = True
End If

End Sub

So I don't know if the problem is 'not in our stars, but
in ourselves' or something - the code doesn't directly
copy, or that the combo box tagged is yes/no not -1/0.

Any help is greatly appreciated - thanks for your patience!
Regards, Lisa
.
Click to expand...
Click to expand...
 
Each of the combo boxes shows numeric data when it should
show text.
Click to expand...

Microsoft's misdesigned, misleading, obnoxious, and all but useless
Lookup Wizard I bet...
Source is table Products.
Filter1 = SELECT DISTINCT Products.SupplierID FROM
Products ORDER BY Products.SupplierID;
Filter2 = SELECT DISTINCT Products.CategoryID FROM
Products;
Filter3 = SELECT DISTINCT Products.[Tagged?] FROM
Products;

SupplierID and CategoryID are both look-up fields in
Products (is this a foreign key?)
Click to expand...

Exactly. The Lookup feature CONCEALS the actual contents of your table
from view. It *LOOKS* like you have a supplier name in the table; you
don't! What you have in the table is a numeric ID. And when you do
more than the very simplest things with the table (such as sorting, or
creating a query, or a filter) this will trip you up.
Tagged is a check box - the combo box shows a drop-down
selection of Yes/No, then displays -1 or 0 respectively.
Click to expand...

That's because YES is stored as -1, and NO as 0. Why not just use a
checkbox on the criteria form?
So, as usual, trying to explain the problem helps me
come up with a possible answer. If I make the report
source a query showing Supplier Name from tblSuppliers;
likewise CategoryID from tblCategories - this should
give me text fields rather than numeric ID's?
Click to expand...

Exactly. Join tblSuppliers to your table by SupplierID.
Second, when I try filtering only on Tagged = Yes, I get
error message: Syntax error (missing operator) in query
expression '([Tagged?] ="-1" A)'.
Click to expand...

Lose the quote marks. They work when the field is a String but are
prohibited when the field is numeric.
The OlClick [Event Procedure] is as follows (lifted almost
entirely from the RptSmp97 code):

Private Sub Set_Filter_Click()

Dim strSQL As String, intCounter As Integer
'Build SQL String
For intCounter = 1 To 3
If Me("Filter" & intCounter) <> "" Then
strSQL = strSQL & "[" & Me("Filter" &
intCounter).Tag & "] " & " = " & Chr(34) & Me("Filter" &
intCounter) & Chr(34) & " And "
End If
Next

If strSQL <> "" Then
'Strip Last " And "
strSQL = left(strSQL, (Len(strSQL) - 3))
'Set the Filter property
Reports![rptInventoryItems].Filter = strSQL
Reports![rptInventoryItems].FilterOn = True
End If

End Sub

So I don't know if the problem is 'not in our stars, but
in ourselves' or something - the code doesn't directly
copy, or that the combo box tagged is yes/no not -1/0.
Click to expand...

You'll need some more complex code to determine the datatype of the
field you're searching. Text fields should have either " (which is
Chr(34) ) or ' as a delimiter; Numeric and Yes/No fields should have
no delimiter at all; Date fields should have # as a delimiter.
 
John - thanks! You're always a big help. Not sure about
one part of your answer, and will have to dig in to the
other part. anyhow:
That's because YES is stored as -1, and NO as 0. Why not just use a
checkbox on the criteria form?
Click to expand...

Sorry to be ignorant, but what do you mean by "Why not
just use a checkbox on the criteria form?" I suspect if I
change how I'm using this field, the problem in SQL below
might be made to disappear (?) Plus I know pretty much
zip about SQL. Anyhow, the way I'm using "Tagged" is as a
checkbox on product entry form, but it can be a text field
elsewhere. If it's a text field in the source for this
report, then none of the fields will be numeric. If it can
be done, it sounds like a good way around the problem?
Let me know what you think, and thanks a bunch.
So, as usual, trying to explain the problem helps me
come up with a possible answer. If I make the report
source a query showing Supplier Name from tblSuppliers;
likewise CategoryID from tblCategories - this should
give me text fields rather than numeric ID's?
Click to expand...

Exactly. Join tblSuppliers to your table by SupplierID.
Second, when I try filtering only on Tagged = Yes, I get
error message: Syntax error (missing operator) in query
expression '([Tagged?] ="-1" A)'.
Click to expand...

Lose the quote marks. They work when the field is a String but are
prohibited when the field is numeric.
The OlClick [Event Procedure] is as follows (lifted almost
entirely from the RptSmp97 code):

Private Sub Set_Filter_Click()

Dim strSQL As String, intCounter As Integer
'Build SQL String
For intCounter = 1 To 3
If Me("Filter" & intCounter) <> "" Then
strSQL = strSQL & "[" & Me("Filter" &
intCounter).Tag & "] " & " = " & Chr(34) & Me("Filter" &
intCounter) & Chr(34) & " And "
End If
Next

If strSQL <> "" Then
'Strip Last " And "
strSQL = left(strSQL, (Len(strSQL) - 3))
'Set the Filter property
Reports![rptInventoryItems].Filter = strSQL
Reports![rptInventoryItems].FilterOn = True
End If

End Sub

So I don't know if the problem is 'not in our stars, but
in ourselves' or something - the code doesn't directly
copy, or that the combo box tagged is yes/no not -1/0.
Click to expand...

You'll need some more complex code to determine the datatype of the
field you're searching. Text fields should have either " (which is
Chr(34) ) or ' as a delimiter; Numeric and Yes/No fields should have
no delimiter at all; Date fields should have # as a delimiter.



.
Click to expand...
 
Have been working on this - see below. Thanks!
Second, when I try filtering only on Tagged = Yes, I get
error message: Syntax error (missing operator) in query
expression '([Tagged?] ="-1" A)'.
Click to expand...

Lose the quote marks. They work when the field is a String but are
prohibited when the field is numeric.
Click to expand...

Tried to simplify the process, and made a new pop-up filter
form with only VendorCode and Category. I still get the
same Syntax error message, except the "-1" is filled with
the text I selected. So, am I right in thinking that even
though the query that is the source for the report and
for RowSource, it is still looking for a numeric entry,
namely the key ID#? It seems like this will !never! work,
unless the source data is a simple table with no look-ups.
 
John - thanks! You're always a big help. Not sure about
one part of your answer, and will have to dig in to the
other part. anyhow:
Click to expand...

Lisa, I just came upon this unanswered message - did you get your
combo box problem resolved?
 
Back
Top