I have a Form with two multiselect listboxes to filter and open report and print.Which am able to do.

Now i want the printed records to be flagged in table and the form should be filtered excluding that records when loading it again.How do i do it.

Can somebody help me on this please.

What do you mean you want the printed records flagged in table?

What do you mean you want the form filtered excluding those records when loading "again?"

If you want to flag a record as already selected, then put a Yes/No check box in the table (MAKE THE DEFAULT NO), and then put in on the form, and hide it. When a record is selected, check Yes, and then filter the form where the check box <> Yes, or = No.

Did that help?

mmcdonal

Look it up at: http://wrox.books24x7.com

Thanks for you reply....

What do you mean you want the printed records flagged in table?
What do you mean you want the form filtered excluding those records when loading "again?"
--------
I want to update the table saying the record(s) has been printed and then the form should be filtered or refreshed(for records that has not been updated)which i can do with command button also if the automatic filter is not possible, so that the i can avoid duplicate print(s).

Since iam new to Programming, it will be easy for me if you can help me with an example code.

I am assuming that you are trying to prevent the ListBox from showing items that have already been printed.

I created a table (Table5) with a Yes/No field (SampleCheck), and I created a listbox on a form using the ListBox wizard (List0). For the listbox, I selected the PK, a text field, and the check field. Then I modified the query to show only those items where the check field = No, and then set the column widths to 0";1";0" so the check field was not displayed. The bound column is 1, or the primary key.

On the print button, add code to set the check to yes on the record that is being printed, then refresh the listbox, like this:

Dim rs As ADODB.Recordset
Dim sSQL As String
Dim lRecord As Long

lRecord = Me.List0 'takes the primary key of the record.

sSQL = "UPDATE Table5 SET [SampleCheck] = Yes WHERE [SampleID] = " & lRecord

Set rs = New ADODB.Recordset
rs.Open sSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

'Do Print actions here

Me.List0.Requery

This allows me to make a selection, then when I click the button, that selection disappears from the list.

The issue here is that it permanently removes them from the listbox data, so if you don't want that, then reset them to No when the form is Opened and when the form is closed with an Update Query:

I created an Update Query called qryUPDATETable5:

UPDATE Table5 SET Table5.SampleCheck = No;


Then put this code on the On Open and On Close events of the form:

DoCmd.SetWarnings False
DoCmd.OpenQuery "qryUPDATETable5"
DoCmd.SetWarnings True

This will reset all the records so they show up in the list box until they are removed during the form session, then reset when the form is opened or closed. You could also put this code in a module and just Call Table5ToNo() on the On Close and On Open events.

Did that help?

mmcdonal

Look it up at: http://wrox.books24x7.com

You know what? I missed the "multi" select. Let me mod the code slightly... brb.


mmcdonal

Look it up at: http://wrox.books24x7.com

Okay, here is the code for a multiselect list box:

Dim rs As ADODB.Recordset
Dim sSQL As String
Dim lRecord As Long
Dim ctrl As Control
Dim intCurrentRow As Integer

Set ctrl = Me.List0

For intCurrentRow = 0 To ctrl.ListCount - 1

    If ctrl.Selected(intCurrentRow) Then
        lRecord = ctrl.Column(0, intCurrentRow)
        sSQL = "UPDATE Table5 SET [SampleCheck] = Yes WHERE [SampleID] = " & lRecord
        Set rs = New ADODB.Recordset
        rs.Open sSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
    End If
Next intCurrentRow

'Do Print actions here

Me.List0.Requery

Did that help?


mmcdonal

Look it up at: http://wrox.books24x7.com

Thanks a lot for your reply.

Will wait for your code mod (with multiselect). Until then i will try this code so that i will get to know the process.

Hope you will sent the mod code.Again thanks a lot.

The reply at 8:47:37 is the modded code for multiselect list boxes. Is this what you wanted?

mmcdonal

Look it up at: http://wrox.books24x7.com

Thanks a lot.I think the code (multiselect) is what am looking for. But in the code you have given Samplecheck as Yes/no field and what is "SampleID"?

Do i need to write code for where you have given
'Do Print actions here....

Can you please help.

What is lRecord mean.Its a genearl declaration or specific field name.