Refresh a list box in a sub-form attached to a query 1

[Najemikon]

Hi,

I've pulled out so much hair, I swear I'm bald!

I have a jobsheet system & instead of opening the main form, I open a list box on a seperate form first, which displays a query showing open jobs. The user selects one, clicks a button & it closes the list & opens the chosen record in the main form. It does this by creating a recordset clone with a bookmark.

Works a treat. Except now I've added a button that closes a selected job & importantly, keeps the list open. I can't get the list to refresh & remove the closed job! A general search of this forum & others reveals I'm not the only one.

I've tried record set closing & reopening, refresh & requery commands all over the place, but it just won't do it. You click close & it stubbornly stays on the screen. Click it again & you get a VBA error, because of course, the record is no longer in the query.

But, close another record & the first disappears! Close the form & reopen & it's gone as well.

With this in mind, I'd thought I'd try a vulgar piece of programming after 'Close' has been pressed, which uses DoCmd to close the form & reopen it. Makes no difference! If I manually close it & reopen it, it works. Do it through VBA it doesn't.

Any ideas gratefully received...

 

[lupins46]

Can't reproduce the problem here.
Can you post the code on the button that you use to close the job.

 

[Najemikon]

Hi Lupins,

This is the code on the button. I know those fields get updated fine & the query filters on the last field affected(incOpen). I've tried the refresh/requery/close form commands after that line, but also in other events on the form, such as afterupdate.



Private Sub btnClose_Click()

Dim rst As dao.Recordset

Set rst = Form_frmIncidents.RecordsetClone

rst.Edit

rst.FindFirst "incNumber =" & OpenJobsList.Column(8)

Form_frmIncidents.Bookmark = rst.Bookmark

Form_frmIncidents.incClosedBy = CurrentUser
Form_frmIncidents.incClosedDate = Now()
Form_frmIncidents.incOpen = "No"

rst.Close

End Sub

 

[lupins46]

Where are you saving the record you've just changed?

( I can't really relate this code to your description of your app - I understood you had Form1 containing the listbox and a button on form1 'that closes a selected job & importantly, keeps the list open'. To this extent I can't see where a second form is necessary or relevant)

 

[Najemikon]

Sorry, my original description was probably unclear: the select button opens a second form with that record. This button doesn't need, nor does it try to open that form. We are dealing with one form only.

I thought the record was saved when the recordset is closed ('rst.close')? I've tried using 'rst.update' but it made no difference & with either, if I open the table manually or in another form, I can see the fields have been changed. It just won't pass those details to the list box.

 

[lupins46]

You must save the record explicitly with 'rst.update'.

Is the form with the listbox 'frmIncidents'?

 

[Najemikon]

Ah, what you've just said there makes me wonder if I've been approaching this wrong. As I said, rst.update didn't work either.

'frmIncidents' is the main form we use for editing an individual record. 'subfrmOpenIncidents' is the one with the listbox. In fact, the form itself is unbound; it's job is simply to display the listbox.

Originally it opens a recordset of 'frmIncidents' because the Select button needs to find a record on it. But should the listbox form ('subfrmOpenIncidents') be bound to the table so it can open a recordset of itself?

So the line would be:

Set rst = Form_subfrmOpenIncidents.RecordsetClone

Am I on the right lines? Maybe I'm finding the limitations of being a 'cut & paste' programmer!

 

[TheAceMan1]

How are ya Najemikon . . . . .

[blue] . . . now I've [purple]added a button that closes a selected job[/purple] & importantly, keeps the list open[/blue]

[purple]What signifies a Closed Job to you?[/purple]

[blue]or[/blue]

If you were looking at your tables, how would you tell an [purple]Open Job[/purple] from a [purple]Closed Job?[/purple]

See Ya! . . . . . .

 

[Najemikon]

Hi TheAceMan. Cool post!

Very simple. There is a field called 'incOpen' which is set to "Yes" by default. This is what the query behind the listbox uses as a filter & is set to "No" by the button's code:

formForm_frmIncidents.incOpen = "No"

If I open the table, I can see straightaway that it has worked because there it no longer has a tick.

Thanks!

 

[TheAceMan1]

OK Najemikon . . . . .

I know your anxious, so try this:

[blue]Private Sub btnClose_Click()
   Dim rst As dao.Recordset
   
   Set rst = Form_frmIncidents.RecordsetClone
   
   With rst
      .FindFirst "incNumber =" & OpenJobsList.Column(8)
      .Edit
         !incClosedBy = CurrentUser
         !incClosedDate = Now()
         !incOpen = [purple][b]False[/b][/purple]
      .Update
   End With
   
   Me.Requery
   
   Set rst = Nothing

End Sub[/blue]

[purple]Give it a whirl & let me know . . . .[/purple]

See Ya! . . . . . .

 

[Najemikon]

Wow! It worked! You certainly lived up to your name. The only thing I had to change was me.requery to me.listbox.requery, but I had tried that line before, with no success.

Now if I had to guess, I'd say 'set rst = nothing' after the requery is what did it, but I'm intrigued by the correction of "No" to False. Surely that shouldn't make such difference?

But whatever. Once more this forum has proved to be an absolute goldmine!

 

[TheAceMan1]

[blue]I'm intrigued by the correction of "No" to False. Surely that shouldn't make such difference?[/blue]

[blue]All the difference in the world![/blue]

First, [purple]Yes/No[/purple] - [purple]On/Off[/purple] - [purple]True/False[/purple] are used when you want to [blue]display a checkbox literally[/blue]. For instance . . . you bind a textbox to a checkbox field. If you set the Format Property of the textbox to Yes/No the literal displays.

Now . . . in [purple]VBA[/purple] you don't use the literals. You use the intrinsic constants:
[ol][li][blue]True[/blue] . . . same as [blue]-1[/blue][/li]
[li][blue]False[/blue] . . . same as [blue]0[/blue][/li][/ol]
[purple]This was the main reason it didn't work . . . wrong assignment in VBA[/purple]. So when your using VBA, [purple]always use True/False for CheckBoxes . . . .[/purple]

[blue]Cheers![/blue]

See Ya! . . . . . .

 

[Najemikon]

That might explain a few other niggles I've been having actually. Nothing important, just features elsewhere in the code that don't flow as they should. I've never devoted a lot of time to sorting them out, but I've always used 'Yes' & 'No'!

Thank you very much!