Using check boxes to limit a report.

  • Thread starter Thread starter Cynd
  • Start date Start date
C

Cynd

I have a table which lists all possible items for a vendor. I want to create
a form that shows all of the items. I want the user to be able to place check
marks next to the items they want. I then want to run a report which shows
all of the information about the items, but only for the items that had been
checked. What is a good way to approach this? I can't seem to find a way to
link the check boxes to the item table and don't know how to create a report
once they are linked. Can someone please direct me on how to do this? Or at
least to somewhere that I can find out how to do this? Thank you in advance
for any help.
 
Cynd,
A simple solution is to add a True/False (ex. PrintMe) field to your
Items table.
Create a dialog form that you will open before the report is called.
On that form, create a continuous subform that shows all the Items and
the PrintMe field. Users can check PrintMe for each Item you want to report
on.
On that form, a button to open the report.
In the query behind the report... use PrintMe = True as a criteria.

One convenience you can add to the dialog form is a CheckAll button, and
a UnCheckAll button, that will, respectively, check all Items, or check no
Items.
This can be done by 2 Update queries behind those buttons.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."
 
That seems to be working fine. Thank you. I have two clarification questions,
though:
1) For the Update Queries, how can I get it to either not show the "you are
about to change x number of records permanently, are you sure you want to
continue" dialog, or to change the phrasing to a more user-friendly statement?
2) After the update queries have been run, the data on the sub-form does not
show the checkmarks being either removed or added (depending on the query).
How can I get the view to refresh after doing the update so the user knows
that it worked?
 
Cynd,

1. Two ways (there are probably others) that I use to prevent the warning
messages. I prefer method "b". Method "a" turns of warning messages until
the SetWarnings = True code is encountered. If you have an error popup and
jump out of this sequence of code, you may not get back to the SetWarnings =
True statement, which means that "ALL" future warnings would not be shown,
at least until the next SetWarnings = True is encountered. The Execute
command omits the warnings, but I strongly encourage the optional
dbFailOnError option. This will fire an error which you can handle with
your error handler if the SQL statement causes an error.

a. Docmd.SetWarnings = False
your code
Docmd.SetWarnings = True

b. Currentdb.Execute strSQL,, dbFailOnError

2. After running the updates (Check All or Uncheck All), you should
requery your form using:

me.requery

HTH
Dale
 
I was able to enter the requery command, but can't seem to figure out where
to place the failonerror line. Could you please tell me where it should be
placed in the code? The exact coding is:
Private Sub CheckALL_Click()
On Error GoTo Err_PRINTall_Click

Dim stDocName As String

stDocName = "UpdateToPRINTall"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Me.Requery

Exit_PRINTall_Click:
Exit Sub

Err_PRINTall_Click:
MsgBox Err.Description
Resume Exit_PRINTall_Click

End Sub
 
Cynd,

I would modify your code as follows, although the error handling might be a
little more extensive. I assume that your query "UpdateToPrintAll" reads
something like"

Update yourTableName SET ToPrint = -1

************************************
Private Sub CheckAll_Click

Dim strSQL as string

On Error Goto CheckAllError

currentdb.Execute "UpdateToPrintAll",, dbFailOnError
me.requery

Exit Sub
CheckAllError:

msgbox "Error encountered: " & err.Number & vbcrlf & err.description
End Sub

BTW, as a naming convention, I try to preface the names of all my queries
with "qry_". This allows me to easily see that "qry_UpdateToPrintAll" is a
query, and makes it significantly easier for the guy that tries to modify my
code in the future to know what he is looking at. In this case, I might go
so far as to name my query "qry_YourTableName_UpdateToPrintAll" as this
would tell me at a glance what table I am trying to update the ToPrint
column in. My forms are almost always based on queries that look somthing
like: qry_formname_Recordsource or qry_formname_cboState. I strongly advise
you to adopt a naming convention and follow it religiously. Lookup "VBA
+naming +conventions" in Google for some examples.

HTH
Dale
 
When I entered your code:
Private Sub CheckALL_Click()
Dim strSQL As String

On Error GoTo CheckAllError

CurrentDb.Execute ("UpdateToPRINTall"), , dbFailOnError

Me.Requery

Exit Sub
CheckAllError:

MsgBox "Error encountered: " & Err.Number & vbCrLf & Err.Description
End Sub

and rem'd all of mine for that click, I got a compile error stating wrong
number of arguments or invalid property assignment. Can you see why I would
get that error? (And BTY, as for naming conventions, all of my db programming
is done by me, and no one ever looks at the code except for when I'm asking
for help. I do understand what you are saying, though, and appreciate the
concern.)
 
Cynd,

Take the parenthesis from around your query name, they are not needed.
Should look like:

Currentdb.execute "UpdateToPrintAll", , dbFailOnError

HTH
Dale
 
Cynd said:
When I entered your code:
Private Sub CheckALL_Click()
Dim strSQL As String

On Error GoTo CheckAllError

CurrentDb.Execute ("UpdateToPRINTall"), , dbFailOnError
Click to expand...


You should only have one comma before dbFailOnError.
 
Back
Top