By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,998 Members | 2,837 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,998 IT Pros & Developers. It's quick & easy.

Multiselect Listbox selection and update flag in table

P: 1
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.
Dec 19 '07 #1
Share this Question
Share on Google+
3 Replies


Expert 100+
P: 374
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.

Expand|Select|Wrap|Line Numbers
  1. 'this code will go into the form that has the combo boxes
  2. 'Requires reference to Microsoft DAO 3.6 or Higher Library.
  3. 'Simply get into code editor, click tools > Reference and select Microsoft DAO 3.x depending on what version of access you have installed.
  4. Function SetFlag(PrimaryKey as Double) 
  5.     Dim MyDB as DAO.Database
  6.     Dim MyRS as DAO.RecordSet
  7.  
  8.     Set MyDB = CurrentDB()
  9.     Set MyRS = MyDB.OpenRecordset("SELECT * FROM [Table Name] " & _
  10.                               "WHERE [Field Name of Primary Key] = " & PrimaryKey _
  11.                               , dbOpenDynaset)
  12.  
  13.     With MyRS
  14.                If Not .EOF Then
  15.                         .Edit
  16.                         !TheFlagFieldName = True
  17.                         .Update
  18.                End if
  19.      End With
  20.  
  21.      MyRS.Close
  22.      MyDB.Close
  23.  
  24.      Set MyRS = Nothing
  25.      Set MyDB = Nothing
  26.  
  27. End Function
  28.  
  29. Function SetPrintedFlag(PrimaryKey as Double) 
  30.     Dim MyDB as DAO.Database
  31.     Dim MyRS as DAO.RecordSet
  32.  
  33.     Set MyDB = CurrentDB()
  34.     Set MyRS = MyDB.OpenRecordset("SELECT * FROM [Table Name] " & _
  35.                               "WHERE [Field Name of Primary Key] = " & PrimaryKey _
  36.                               , dbOpenDynaset)
  37.  
  38.     With MyRS
  39.                If Not .EOF Then
  40.                         .Edit
  41.                         !TheFlagFieldName = True
  42.                         .Update
  43.                End if
  44.      End With
  45.  
  46.      MyRS.Close
  47.      MyDB.Close
  48.  
  49.      Set MyRS = Nothing
  50.      Set MyDB = Nothing
  51.  
  52. End Function
  53.  
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.
Jan 5 '08 #2

P: 1
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
Oct 20 '08 #3

GazMathias
Expert 100+
P: 189
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):
Expand|Select|Wrap|Line Numbers
  1. For Each varItem In Me!ListName.ItemsSelected
  2. strCriteria = strCriteria & "FieldInTableName = " & Me!ListName.ItemData(varItem) & " OR "
  3. 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:
Expand|Select|Wrap|Line Numbers
  1. strCriteria = Left(strCriteria, Len(strCriteria) - 3)
Update the table:
Expand|Select|Wrap|Line Numbers
  1. strSQL = "UPDATE TableName SET Flag = True WHERE "
  2. strSQL = strSQL & strCriteria & ";"
  3. 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.
Oct 20 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.