How do I force data to update/requery? (1 Viewer)

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:


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.

That's certainly a big green tick

Replace the requery line with
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?

I had to comment out some code to get the project to compile.
I think the subform or mainform is corrupted and needs to be rebuilt.
It also doesn't make sense to have those two combos on the subform since they are not related to the subform. They are related to the parent record.

Change the code to the below:

isladogs said:

Replace the requery line with

Code:Copy to clipboard

Forms!FrmOverview.Requery

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

Click to expand...

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.

isladogs said:

Have you abandoned the dimmer idea from the previous thread?

Click to expand...

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.

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.

JHB said:

Change the code to the below:

Code:Copy to clipboard

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

Click to expand...

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.

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

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.

isladogs said:

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

Click to expand...

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.

Like Doc I used to manage groups of programmers. I would almost rather have the "no comments" guy than a couple of people I had which were lazy about modifying existing comments. I can't tell you how much time my group wasted by assuming that the comments actually reflected reality.

So, I agree with Doc in that you should ALWAYS comment code you know to be "strange" and which will make people say to them selves "What was he smoking when he wrote this?" But be careful about comments and variables that include data that may have to be changed. One of my favorites from my first programming job was the Dept88 variable. Who knew that sometime after the code was written, the logic was expanded so that not only Department 88 but 66 and 67 ALSO followed this logic path. This is from the COBOL era when we were still working with punched cards so there was no way anyone was going to modify every reference to Dept88 to change its name. With today's editors, change all's are trivial and so this is much less of a problem but I still run into stupid stuff like this that the person making the change was too lazy to rename the variable.