Report generated from text box fields

  • Thread starter Thread starter misschanda via AccessMonster.com
  • Start date Start date
M

misschanda via AccessMonster.com

hello.
i have a 3 column report. The 3 products compared on the report are assigned
when the report opens and asks the user for the ID..

is there are way to create 3 unbound text fields and have the report compare
the IDs of the products listed in the text box..

i have done something similar that searches based on what was entered in a
text box, but wasnt sure how to have the report pick up three different text
boxes....


this is an example of what i have in another application that opens up a
query

Like "*" & [forms]![SEARCHMENU]![text37] & "*"
 
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






misschanda via AccessMonster.com said:
hello.
i have a 3 column report. The 3 products compared on the report are assigned
when the report opens and asks the user for the ID..

is there are way to create 3 unbound text fields and have the report compare
the IDs of the products listed in the text box..

i have done something similar that searches based on what was entered in a
text box, but wasnt sure how to have the report pick up three different text
boxes....


this is an example of what i have in another application that opens up a
query

Like "*" & [forms]![SEARCHMENU]![text37] & "*"
Click to expand...
 
thanks worked great
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
Click to expand...
[quoted text clipped - 15 lines]
Message posted via AccessMonster.com
Click to expand...
Click to expand...
 
Back
Top