How do I force data to update/requery?

[Cark]

I have created a Multi Select List set up where I want the user to do the following:

The user clicks the Edit Icon to go into an "Editing Mode" (achieved by a Form opening up). The user can then highlight an item in the Fleet List or Position List e.g Bus and then click the + or x to move them from the Fleet List to Applicable Fleets. This can also be done by double-clicking on an item. Once the user is happy with their edits, they then click the big green tick and they are then taken back to FrmOverview and the Applicable Fleets and Applicable Positions lists should then be updated with their changes.

I achieve this by using the code snippet:

Forms![FrmOverview].Form.Recordset.Requery

This works well for the Applicable Fleets selection which uses the Form FrmSelectFleet, however it does not seem to work for the second Form FrmSelectPositions.

As a bit of background to how I created the lists, I created Applicable Fleets first, got it working and then I tried copying the same methodology for the Applicable Positions.

How to recreate the issue for troubleshooting purposes:

1. Open up FrmOverview
2. Click the Page and Pencil Icon on "Applicable Fleets"
3. Double Click "Bus" to move it from Fleet List to Applicable Fleets
4. Click the big green Tick Icon to go back to FrmOverview - see how it has been updated in Applicable Fleets on FrmOverview?
5. Click the Page and Pencil Icon on "Applicable Positions"
6. Double Click "001" to move it from Position List to Applicable Positions
7. Click the big green Tick Icon to go back to FrmOverview - see how it hasn't been updated in Applicable Positions on FrmOverview?

Any ideas on how to fix it? I am pretty new to Recordsets etc.

 

[isladogs]

That's certainly a big green tick

Replace the requery line with

Forms!FrmOverview.Requery

That works for the fleet list but the position list isn't updating so there's another issue. However I've no time to check further.

Have you abandoned the dimmer idea from the previous thread?

 

[JHB]

Change the code to the below:

Private Sub ReqLists()

    Me.lstSelectedFleets.Requery
[COLOR=red]    Me.lstSelectedPositions.Requery
[/COLOR]    
    Me.lstSelectedFleets.Value = Null
[COLOR=Red]    Me.lstSelectedPositions.Value = Null
[/COLOR]
End Sub

 

[Cark]

Replace the requery line with

Forms!FrmOverview.Requery

That works for the fleet list but the position list isn't updating so there's another issue.

Hey isladogs, I previously used the Forms!FrmOverview.Requery code, however that made my Form jump to the very first record of data, which was not what I wanted to happen. I did a bit of googling around and read that using Forms![FrmOverview].Form.Recordset.Requery would solve the issue of it jumping to the first record. After making that tweak it worked perfectly for the Applicable Fleets list, however it did not work for the second list I created.

Have you abandoned the dimmer idea from the previous thread?

No I definitely have not abandoned it and in my "actual" database, I am using the dimmer idea with what appears to be great success. I just removed it for this example to try and simplify things down to the core issue I am having.

 

[Cark]

And we have a winner! It is always the simple things that I miss... Any personal tips for how to prevent me missing stuff like this? I definitely think I should comment on my code more.

Change the code to the below:

Private Sub ReqLists()

    Me.lstSelectedFleets.Requery
[COLOR=red]    Me.lstSelectedPositions.Requery
[/COLOR]    
    Me.lstSelectedFleets.Value = Null
[COLOR=Red]    Me.lstSelectedPositions.Value = Null
[/COLOR]
End Sub

 

[isladogs]

I've never used the requery syntax you supplied and am unclear why it would work as you describe..
To solve the issue with requery, the normal approach is to save the current record value before requerying then afterwards do a go to record line to return to that record.

Your second form may be corrupted as it should work in exactly the same way as the other. I would suggest shrinking both forms so they are less 'in your face' and also to reduce white space. They don't fit on a 10" tablet making it harder for me to test yesterday.

 

[isladogs]

Posts crossed....
So you requery in the main form as well as from the external form. Why both?
You could have just written Me.Requery to requery the entire form.

In the second part you don't need .Value as its the default

In answer to your question, its definitely a good idea to add lots of comments. Makes life much easier when you return to the code later

 

[The_Doc_Man]

I will chime in on the narrow topic of comments in code.

When I was a design manager in the software department of a company that did various kinds of automated control systems, I wrote a customized text parser that we used for command line analysis. However, once that tool was in my tool kit, I got curious. At the time this happened, there was a big push in the industry to not write code that totally lacked comments. I had been burned by being given something to update for modern hardware and the guy who wrote it use assembly language with zero comments. I could have killed him, but he moved on to another company and I figured they could have him.

Anyway, I ran a statistical analysis and found that I could tell who wrote a particular code module based on percentage of two kinds of comments: inline (to the right of some executable instruction) and block (only spaces or nothing to the left of the comment marker). Those two percentages reflected each programmer's personal style. Two more percentages that helped were lines with ONLY a comment marker and lines that were totally blank, but the inline and block comment percentages were definitely good indicators.

Since I was the department head, I took aside the folks with the fewest comments and gently advised them to leave better tracks behind themselves. I explained that if they were busy one day, I could assign modifications to someone else rather than force them to stop what they were doing. That got the message across.

Folks don't appreciate the value of comments until they have to debug something that doesn't have any. Making a relevant comment helps orient your thinking and allows you to pick up the thrust of the code much quicker. It also helps the folks who follow in your footsteps. Think of it as a TINY monument for posterity instead of a pain in the posterior.

 

[Cark]

Posts crossed....
So you requery in the main form as well as from the external form. Why both?
You could have just written Me.Requery to requery the entire form.

In the second part you don't need .Value as its the default

In answer to your question, its definitely a good idea to add lots of comments. Makes life much easier when you return to the code later

The reason I did it in both is that it was the only method that seemed to work... Doing just the main form frmOverview didn't work and I am not sure why it didn't flow through the subforms as well.