Multiselect lstbox vs Continuous Sub?

[Guest]

I have searched and read through this board for ideas to implement on one of
my forms. I have not yet used listboxes in my application yet. I have used
the form/continuous subform design effectively, but now I want to do
something a little different and would like to get some feedback from other
users as to what would be a "better" approach... listbox or subform.

The general idea:

On a form place an unbound textbox and in the AfterUpdate event I place an
SQL Query (criteria from textbox) and set the control's (where the results
are returned-subform or lstbox?) control source to this SQL query.

Now I have the records I want to deal with. The user would now select
multiple records (via multiselect listbox OR continuous subform with radio
button??). This could be 1-200 records at a time. The following then needs to
happen with the selected records in the OnClick event of a command button:
-have a report printed... 1 page per record
-have the date this happens be stored in the appropriate DATEfield of
each record.

If you have dealt with something similar to this I would be interested in
how you handled it....
THANKS FOR THE HELP THIS BOARD IS GREAT.

 

[Ken Snell [MVP]]

For your described situation, I would use a listbox. If you were to use a
continuous forms view subform, you'll need a field in the subform's record
source to which the radio button (or checkbox) control would be bound.
Unless you have such a field in your table already (not likely), you'd need
to build a temporary table to be the subform's record source table, which is
more work than just using a listbox.

 

[Guest]

Thanks Ken. I did a little looking around on the MSDN Library site for some
pointers on coding, etc. Looks like I have some more learning to do with this
listbox "tool". From what little I have gathered so far, it appears I will
have to "loop through" the .ItemsSelected for each record selected in the
listbox I want to
a)print a report for
b) add the date to the records table.
Would the code to do these things be placed in the OnClick Event of a
command button? Or am I not even in the ballpark here?

 

[Ken Snell [MVP]]

You are correct; you loop through the .ItemsSelected collection of the
listbox and either open a report for each item. Or concatenate all the
primary key values into a "WHERE" type clause that can be used to filter the
report via the WhereCondition argument in the DoCmd.OpenReport action; the
report would be designed then would need to put the items on separate pages.

To see a working example of such a listbox setup where the selected items
are concatenated into a WHERE type clause, see
http://www.cadellsoftware.org/SampleDBs.htm#FilterForm for sample database
that does this.

And yes, I would use the click event of a command button to run the code;
this also is shown in my sample database.

--

Ken Snell
<MS ACCESS MVP>

 

[Albert D.Kallal]

Here is some sub form, and continues forms examples

http://www.members.shaw.ca/AlbertKallal/Articles/Grid.htm

However, I do have a UNBOUND check box example for a continuous form, and I
think that would be ideal for your needs.

Check out my multi-select (unbound check box) continuous form example here:

http://www.members.shaw.ca/AlbertKallal/

 

[Guest]

Gentleman!
Thanks for the replies... busy weekend (it's snowing and the slopes are
calling) and won't be back to work on Access until Monday a.m. I Certainly
appreciate the help and I'm sure I'll have more questions.
Albert-- I frequently visit your webpage and ask myself "how can I make my
interface do that?.." so needless to say I have been to the www.////Grid.htm-
but can't seem to find the unbound checkbox reference

Check out my multi-select (unbound check box) continuous form example here:

http://www.members.shaw.ca/AlbertKallal/

Thanks again...

 

[Guest]

So I have put together a form with a listbox on it. After the user selects
records, they hit a command button which prints a report for each item
selected. This is the code behind the button...

Private Sub cmdPrintGoldenrod_Click()
On Error GoTo Err_cmdPrintGoldenrod_Click

Dim itm As Variant

For Each itm In Me.lstGoldenTwps.ItemsSelected
DoCmd.OpenReport "rptGroupGolden", acViewNormal, , "ID=" &
Me.lstGoldenTwps.ItemData(itm), acWindowNormal
Next itm

Exit_cmdPrintGoldenrod_Click:
Exit Sub

Err_cmdPrintGoldenrod_Click:
MsgBox Err.Description
Resume Exit_cmdPrintGoldenrod_Click

End Sub

This works great, I am basically filtering the report based on the bound
column (in this case the Primary Key of the record -ID) of the listbox. Is
this a bad idea? (I don't think my bound column will change in this listbox.)
It does work; I guess I'm just surprised that it does as this was my first
attempt.

NEXT QUESTION: How to store the date in the record when the button is
pressed to print the report. I already have a field GOLDENdate in my table
(where the record lives) just waiting to be updated!

 

[Ken Snell [MVP]]

Glad to hear of your success.

Is the GOLDENdate field in the form's RecordSource, or is it in a table that
is not in the form's Recordsource? Makes a difference as to how you store
the date value.

If in the form's recordsource, just add a code step like this:

Me.GOLDENdate.Value = Date()


If it's in a separate table, you'll need to know the primary key of the
record that needs to be updated with the date value. Post that information
and we'll go from there.

 

[Guest]

Good morning Ken,

I had added the field GOLDENdate to the ROWSOURCE of the listbox (Column
width set to 0) in hopes of trying to update it with the 'Me.' statement,
with no luck. However, the syntax I was using was not correct. I do have the
primary key also in the ROWSOURCE. This is the column the listbox is bound
to. The main form is unbound. Sounds like I am pretty close here? I'm sure
I'll just kick myself when I hear the solution....

 

[Ken Snell [MVP]]

You can't do any data updates via the listbox's RowSource itself. However,
that information allows us to run an update query to do what you seek.

I'll use generic names in this example, so replace them with the real names.
Add these steps after your DoCmd.OpenReport step:

Dim dbs As DAO.Database
Dim strSQL As String
strSQL = "UPDATE TableName SET GOLDENdate=" & _
Date() & " WHERE ID=" & Me.lstGoldenTwps.ItemData(itm)
Set dbs = CurrentDb
dbs.Execute strSQL, dbFailOnError
dbs.Close
Set dbs = Nothing
--

Ken Snell
<MS ACCESS MVP>

 

[Guest]

Ken,

Well, that is some code that I have not used before. It does update the date
of the correct filed in the correct table, BUT the date it updated for today
is 12/30/1899.
The field GOLDENdate is set as Short Date. There must be another setting
somewhere??

 

[Ken Snell [MVP]]

Let's try this syntax for the date field:

Dim dbs As DAO.Database
Dim strSQL As String
strSQL = "UPDATE TableName SET GOLDENdate=#" & _
Format(Date(), "mm\/dd\/yyyy") & _
" WHERE ID=" & Me.lstGoldenTwps.ItemData(itm)
Set dbs = CurrentDb
dbs.Execute strSQL, dbFailOnError
dbs.Close
Set dbs = Nothing

--

Ken Snell
<MS ACCESS MVP>

 

[Guest]

Ken,

With a little tweak to your syntax in the "Format(Date...", it is working!
strSQL = "UPDATE tblSurveys SET GOLDENdate=" & _
Format(Date, "\#mm\/dd\/yyyy\#") & " WHERE ID=" & _
Me.lstGoldenTwps.ItemData(itm)

I'll have to read about DOA.databse stuff. Is this a way to execute commands
over all objects (tables, forms, reports) no matter "where" you may be in the
database?

Anyways, THANK YOU for your guidance. I would have spent much more time
fumbling around with this on my own. I've learned lots from this board thanks
to you MVPs...

 

[Ken Snell [MVP]]

Must be a holiday coming up... I did neglect to add the trailing "#"
delimiter for the date value. My sincere apologies, and I'm glad that you
were able to get past my omission.

DAO can be used to manipulate tables, queries, database properties, and just
about everything in the database file. Very useful for numerous "miracles" <
g >. The Database object is a high-level object that allows you to
"represent" the entire database file and its properties and objects. DAO is
very worthwhile to know if you're going to do great things in ACCESS.

Good luck, and you're welcome.
--

Ken Snell
<MS ACCESS MVP>