Update a Record with a matching Listbox value.

[akeller]

Yes, of course, the code that I gave you to do the updates would not change much - you would simply need to reference the new listbox holding the selected items and changing from looping through the ItemsSelected to looping through all items (https://stackoverflow.com/questions/...ugh-a-list-box) because do not reselect them in the listbox once you move them there from "available". Here is a link you might want to have a look at: https://office-watch.com/2010/adding...-items-part-2/ (the topic came up yesterday in this forum:https://www.accessforums.net/showthr...826#post417826).
You may also want to consider the way you do the update itself; right now you are overwriting the entire content of the comments field for the selected records with the "new" comment. As I do not know the specifics of your db that might be OK. I usually design dbs to have the "comments" (or "notes") in another table in which I store the main record ID as a foreign key, the comment, timestamp (using Now()) and the user that added the comments. In this way I have a nice historical "comments" table.

Cheers,
Vlad

I tried following the tutorial from the office-watch link you provided but am unsuccessful. I copied some of the code from the link and made a few minor adjustments but it's not working.

Any more help would be greatly appreciated.

Private Sub cmdAdd_Click()
Dim strSQL As String
strSQL = "SELECT [qryCreateHold].[WorkOrderID_FK], [qryCreateHold].[WorkOrder] FROM qryCreateHold ORDER BY [WorkOrder]; "
On Error GoTo ErrorHandler

   Set lstSelected = Me![lstSelectWorkOrders]
   Set lstAvailable = Me![lstAvailableWorkOrders]

    'Check that at least one item has been selected
   If lstAvailable.ItemsSelected.Count = 0 Then
      MsgBox "Please selecet at least one Work Order.", vbOK
      lstAvailable.SetFocus
      GoTo ErrorHandlerExit

   End If

   

   'Add selected items to an array, since removing an item

   'from the list with RemoveItem clears the selections

   intItem = 0

   lngCount = lstAvailable.ItemsSelected.Count

   ReDim Listitems("lngCount – 1")
   

   For Each varItem In lstAvailable.ItemsSelected

      Listitems(intItem) = Nz(lstAvailable.Column(0, varItem))

      intItem = intItem + 1

   Next varItem

      

   For i = 0 To lngCount - 1

      strItem = Listitems(i)

      

      'Append selected item to Selected Items value list

      lstSelected.AddItem strItem

      

      'Delete selected item from Available Items value list

      lstAvailable.RemoveItem strItem

   Next i

   

ErrorHandlerExit:

   Exit Sub

 

ErrorHandler:

   MsgBox "Error " & Err.Number & " in YourProcName procedure: " & Err.Description, vbCritical, "Application error"
 
   Resume ErrorHandlerExit

 

End Sub

 

Private Sub cmdRemove_Click()

 

On Error GoTo ErrorHandler

 

   Set lstSelected = Me![lstSelectedItems]

   Set lstAvailable = Me![lstAvailableItems]

   

   'Check that at least one item has been selected

   If lstSelected.ItemsSelected.Count = 0 Then

      MsgBox "Please selecet at least one Work Order.", vbOK

      lstSelected.SetFocus

      GoTo ErrorHandlerExit

   End If

   

   'Add selected items to an array, since removing an item

   'from the list with RemoveItem clears the selections

   intItem = 0

   lngCount = lstSelected.ItemsSelected.Count

   ReDim Listitems("lngCount – 1")

   

   For Each varItem In lstSelected.ItemsSelected

      Listitems(intItem) = Nz(lstSelected.Column(0, varItem))

      intItem = intItem + 1

   Next varItem

      

   For i = 0 To lngCount - 1

      strItem = Listitems(i)

      

      'Append selected item to Selected Items value list

      lstAvailable.AddItem strItem

      

      'Delete selected item from Available Items value list

      lstSelected.RemoveItem strItem

   Next i

         

ErrorHandlerExit:

   Exit Sub

 

ErrorHandler:

   MsgBox "Error " & Err.Number & " in YourProcName procedure: " & Err.Description, vbCritical, "Application "

   Resume ErrorHandlerExit

 

End Sub

 

Private Sub Form_Load()

 

On Error GoTo ErrorHandler

 

   DoCmd.RunCommand acCmdSizeToFitForm

   

   Me![lstAvailableItems].RowSource = strSQL
   Me![lstSelectedItems].RowSource = “”

      

ErrorHandlerExit:

   Exit Sub

 

ErrorHandler:

   MsgBox "Error " & Err.Number & " in YourProcName procedure: " & Err.Description, vbCritical, "Application "

   Resume ErrorHandlerExit

[Gicu]

Can you upload a striped down db with what you have so I could have a look?

Cheers,
Vlad

[akeller]

I'm not able to do that currently. Strict workplace rules.

[Gicu]

Sorry, but I cannot help if I can't see what "some of the code" you copied. I wouldn't need any data, just the empty source table, query and the new form with the two listboxes.

[akeller]

I will upload a stripped version from home. I'm unable to upload or download files at work.

[akeller]

Well I was unable to get the "stripped" down version today. I accidentally emailed myself the an older version that did not have the current tables and forms created yet. Hopefully I can get that tomorrow.

In the mean time, here is an example of what I am after. http://www.msofficegurus.com/post/Ac...box-Items.aspx

It would need to check the Yes/No field and update the comments for the selected items upon clicking a continue button.
I'm such a newb at this and have been banging my head on my desk.

[Gicu]

Yes, this a very common Access functionality, I had my first paired list box form adapted from one of the greatest Access "bibles" by Ken Getz and others a very long time ago. Basically the double clicks are calling the same code behind the single record moving buttons (< and>). There should be lots of examples on the web for you, but if you manage to upload what you have to date I could probably tweak it for you tomorrow. In the link you provided there is some "filtering as you type" code, I didn't think you needed that or do you?

Cheers,
Vlad

[akeller]

Yes, this a very common Access functionality, I had my first paired list box form adapted from one of the greatest Access "bibles" by Ken Getz and others a very long time ago. Basically the double clicks are calling the same code behind the single record moving buttons (< and>). There should be lots of examples on the web for you, but if you manage to upload what you have to date I could probably tweak it for you tomorrow. In the link you provided there is some "filtering as you type" code, I didn't think you needed that or do you?

Cheers,
Vlad

Yes, I have came across lots of examples, however, I'm so new, Its hard for me to understand what exactly I need to tweak to work for my application. I'm definitely trying though, I have spent the probably 12 hours a day the last several days trying different codes and searching forums. I think the biggest probably with the examples I have found is that a lot of them are requiring a separate temp table to "show/hide" the values in one listbox to another based on a query. I don't thing this will work for me as I will have multiple users.

As for the filtering, no I do not need that feature. Hopefully I can get the stripped DB uploaded later this afternoon. We have very strict policies here and I'm not allowed to send or receive files due to cyber security reasons.

[Gicu]

The temp table approach is probably the easiest and it would work for you as the temp table is to be located in the front-end of each user (in the split db that is critical for safe multiuser deployment). The temp table would only need two or three fields: WorkOrderID (long integer), IsSelected(Yes/No) and WorkOrder (text - this is optional as you could use a query to bring it in from the main table). So each user will select their own batch of workorders from the entire pool (back-end table) enter their comments and click the update button which would have much easier code to do the final update - instead of looping through the records and updating them one by one you would run an update query to do it in batch. The update query would use an equijoin between the WorkOrderID keys in the temp table and your main work order table from the back-end with a where clause of IsSelected=True.

Cheers,
Vlad

[akeller]

OK attached is a very stripped down version of the DB.

Forgive all the error messages about missing photos. They are linked from my work PC so they are not available.

After opening, just right click the Login Form and go to Design View to by-pass. Then open the form frmMain. This displays the current jobs available. Under the job information, you will see the Place Hold button. Click this to open up "Form1"(currently in "Unassigned Objects"). This form is where I'm trying to place the work orders on hold. I placed a Yes/No box in the table tblWorkOrders to Show/Hide in the Listboxes.

The main table is tblIncomingJobJoin. This is where the comments(I like your Idea about not overwriting the information.) and the Hold Yes/No field is located.

Also, my query for the Inwork Tab stopped working after I compressed the file and emailed it to myself. I have no idea why, it does function on my work PC though.

[Gicu]

Please have a look at the attached updated db and see if that was what you wanted. Please note that I have made some changes to some other objects (the login form/tblLocalVars, etc.) you can see what I touched by sorting by the modified date of the objects.

I don't think the tblWorkOrders needs that extra Yes/No field (Show); I created a new table (tmpIncomingJobHoldUpdater) which will need to remain a local front-end table (similar to tblLocalVars and probably many others that hold descriptions that do not change often) and this is what I used in my new form frmHoldUpdate (which was based on your Form1). I think the functionality of this latest form is all there, you might want to tweak the wording on the comments. To move one record in either direction you use the add/remove buttons or double click the item in the list box; to move multiple you need to first select them holding down Shift and clicking on them then use the buttons.

And I don't think you have a problem with the Inwork query, it is just that the stripped down data has no records that have the Claimed =True and the right InspectorID (1) - I have done one and you'll see it shows in the tab.

Cheers,
Vlad

[akeller]

Please have a look at the attached updated db and see if that was what you wanted. Please note that I have made some changes to some other objects (the login form/tblLocalVars, etc.) you can see what I touched by sorting by the modified date of the objects.

I don't think the tblWorkOrders needs that extra Yes/No field (Show); I created a new table (tmpIncomingJobHoldUpdater) which will need to remain a local front-end table (similar to tblLocalVars and probably many others that hold descriptions that do not change often) and this is what I used in my new form frmHoldUpdate (which was based on your Form1). I think the functionality of this latest form is all there, you might want to tweak the wording on the comments. To move one record in either direction you use the add/remove buttons or double click the item in the list box; to move multiple you need to first select them holding down Shift and clicking on them then use the buttons.

And I don't think you have a problem with the Inwork query, it is just that the stripped down data has no records that have the Claimed =True and the right InspectorID (1) - I have done one and you'll see it shows in the tab.

Cheers,
Vlad

Thank you so much, I will talk a look at it when I get into work.

[akeller]

Please have a look at the attached updated db and see if that was what you wanted. Please note that I have made some changes to some other objects (the login form/tblLocalVars, etc.) you can see what I touched by sorting by the modified date of the objects.

I don't think the tblWorkOrders needs that extra Yes/No field (Show); I created a new table (tmpIncomingJobHoldUpdater) which will need to remain a local front-end table (similar to tblLocalVars and probably many others that hold descriptions that do not change often) and this is what I used in my new form frmHoldUpdate (which was based on your Form1). I think the functionality of this latest form is all there, you might want to tweak the wording on the comments. To move one record in either direction you use the add/remove buttons or double click the item in the list box; to move multiple you need to first select them holding down Shift and clicking on them then use the buttons.

And I don't think you have a problem with the Inwork query, it is just that the stripped down data has no records that have the Claimed =True and the right InspectorID (1) - I have done one and you'll see it shows in the tab.

Cheers,
Vlad

Vlad,

Thank you so much for helping me, I really appreciate.

Your DB update is very close to what I'm needing but with only a few changes. I've tried to make the changes but I'm pretty sure I will just screw it up.

Here is what I was needing.

frmMain = "Place Hold" and "Claim Job" buttons located on the Detail Section of the form. Reason for this is that there is a query that populates all work orders that are assigned to the specific "IncomingJobID_FK". This is loaded for each incoming job on the continuous form. It automatically populates the correct workorders for each job in the "frmHoldUpdate" and "Form1". The query it uses is "qryCreateHold". Also, this does the same thing when pressing the "Claim Job" button.

tmpIncomingJobHoldUpdater = This table needs the "WorkOrders" from "tblWorkOrders", or "qryCreateHold". Not the "Description".

These are the main issues I currently notice. When I click on the "Place Hold" button, it only displays the information from the first continuous form on "frmMain"

I've tried to update the sSQL vba on the "Place Hold" button to SELECT from the qryCreateHold but I'm receiving the errors.

Thanks again for your help.

[Gicu]

Will update later today!

Cheers,
Vlad

[akeller]

Will update later today!

Cheers,
Vlad

Ok great thanks.

It did work, but I had to click on each section of the continuous form to select it before clicking the Place Hold button in the Header. I'm not sure the users would understand that.