Multiselect Listbox selection and update flag in table

[Suresh62]

I ahve two multiselect listbox for filtering report and printing.

Now i want to update my table with flag saying its being printed and when the form is loaded again the flagged records should be filtered.How do i write VbA code for this.

Can anyone help me please.

I have been trying to get correct code for this.
Thanks in advance.

[PianoMan64]

Can you provide a little more detail as to how you're using the comboboxes to filter the output of the reports.

Are you using both combo boxes and clicking some button to being printing?

If that is the case, then you simply need to update the field that you're using for your Flag and then once those flags are set, then you have another flag that says they are printed, and update that as well.

The other change I would make would be to make the combo boxes into ListControls that way you can see what items you've selected to print.

'this code will go into the form that has the combo boxes
'Requires reference to Microsoft DAO 3.6 or Higher Library.
'Simply get into code editor, click tools > Reference and select Microsoft DAO 3.x depending on what version of access you have installed.
Function SetFlag(PrimaryKey as Double) 
    Dim MyDB as DAO.Database
    Dim MyRS as DAO.RecordSet
 
    Set MyDB = CurrentDB()
    Set MyRS = MyDB.OpenRecordset("SELECT * FROM [Table Name] " & _
                              "WHERE [Field Name of Primary Key] = " & PrimaryKey _
                              , dbOpenDynaset)
 
    With MyRS
               If Not .EOF Then
                        .Edit
                        !TheFlagFieldName = True
                        .Update
               End if
     End With
 
     MyRS.Close
     MyDB.Close
 
     Set MyRS = Nothing
     Set MyDB = Nothing
 
End Function
 
Function SetPrintedFlag(PrimaryKey as Double) 
    Dim MyDB as DAO.Database
    Dim MyRS as DAO.RecordSet
 
    Set MyDB = CurrentDB()
    Set MyRS = MyDB.OpenRecordset("SELECT * FROM [Table Name] " & _
                              "WHERE [Field Name of Primary Key] = " & PrimaryKey _
                              , dbOpenDynaset)
 
    With MyRS
               If Not .EOF Then
                        .Edit
                        !TheFlagFieldName = True
                        .Update
               End if
     End With
 
     MyRS.Close
     MyDB.Close
 
     Set MyRS = Nothing
     Set MyDB = Nothing
 
End Function
 

This will get you started. What I need to find out from you now in order to finish this example is, are you using the combo boxes to use them as filters to what you're going to print, or are these combo boxes records that are in a table that you want to select and print?

let me know and I can give you the rest of the code.

Hope this helps,

Joe P.

[uconn12]

How do I update a flag in a table based on a listbox? I have a multi select list box, the user selects multiple rows in the list box, then clicks a button. Based on the rows selected, I want the flag in a table for those rows to change from Yes to No.
Thanks

[GazMathias]

You need to get each ID from the list, then build an UPDATE SQL statement like this:


Get each ID in the list (where ListName is the name of your list):

For Each varItem In Me!ListName.ItemsSelected
strCriteria = strCriteria & "FieldInTableName = " & Me!ListName.ItemData(varItem) & " OR "
Next varItem

(Make sure that the listbox has the column bound to the ID you want recorded).

Remove the last "OR ", cos we don't need it:

strCriteria = Left(strCriteria, Len(strCriteria) - 3)

Update the table:

strSQL = "UPDATE TableName SET Flag = True WHERE "
strSQL = strSQL & strCriteria & ";"
DoCmd.RunSQL strSQL

I prefer the "strSQL = strSQL &" method over the "&_" because I find it easier to read, but that is of course user preference and you can do it the way you feel comfortable with.