Is the ID a text field or a number field? Is it the Unique Primary Key field
for your Products table. I'll assume its a number field.
Instead of a text box, put 3 combo boxes into a form, each based on your
Product table (this ensures that the user does not have to type items in and
thus make mistakes.
When you are creating the combo boxes, you could hide the ID field if you
wish so that the user can choose the product by name or you can have the ID
field and the Product visible.
Just make your first field the ID field ie the unique one.
The user presses a button when they have selected 3 products.
The code behind the button will be something like this (change the field and
report names to match your database)
From what you say, you seem to want the user to choose 3 products to compare
so we won't let them leave anything out.
Here below is the code I'm using in one of my dbs. The form has 3 combos
called cbo1, cbo2, cbo3
The report is called RptAllMonthsAllItemsCross
I've named the command button
cmbRptAllMonthsAllItemsCross
when I inserted it.
The user can choose any combination of combos he wishes and the report will
show up to 3 items.
The report contains CatID and each combo box is based on the Category table.
It's first column has CatID, it's second column has the CategoryName. I let
the Wizard hide the first column so that the user can see the Category
names, although the real value of each combo is CatID
CatID is a number field. If your ProductNumber field is a text field then
the code will be slightly different.
Private Sub cmbRptAllMonthsAllItemsCross_Click()
On Error GoTo Err_cmbRptAllMonthsAllItemsCross_Click
Dim stDocName As String
Dim Item1 As Long
Dim Crit1 As String
Dim Item2 As Long
Dim Crit2 As String
Dim Item3 As Long
Dim Crit3 As String
Dim Crit As String
stDocName = "RptAllMonthsAllItemsCross"
If IsNull(Me.cbo1) Then
Crit1 = ""
Else
Item1 = Me.cbo1
Crit1 = Item1 & ","
End If
If IsNull(Me.cbo2) Then
Crit2 = ""
Else
Item2 = Me.cbo2
Crit2 = Item2 & ","
End If
If IsNull(Me.cbo3) Then
Crit3 = ""
Else
Item3 = Me.cbo3
Crit3 = Item3 & ","
End If
Crit = Crit1 & Crit2 & Crit3
Crit = Left$(Crit, Len(Crit) - 1)
'trim off last comma
Crit = "[CatID]IN(" & Crit & ")"
DoCmd.OpenReport stDocName, acPreview, , Crit
Exit_cmbRptAllMonthsAllItemsCross_Click:
Exit Sub
Err_cmbRptAllMonthsAllItemsCross_Click:
MsgBox Err.Description
Resume Exit_cmbRptAllMonthsAllItemsCross_Click
End Sub
hello.
i have a 3 column report. The 3 products compared on the report are assigned
[quoted text clipped - 15 lines]
Message posted via AccessMonster.com
