How to requery a listbox on a form from another form

[stonward]

Hi again fellows,

I've got an annoying issue I can't seem to fathom. I have a Main or 'Home' form that has a tab control which houses a listbox, which is based on a query, and shows stocklevels.

From my invoicing form, once I've done my stock update calculations (I'm working on a more elegant solution), I need to requery the listbox on the main form to ensure the stock values shown are up to date.

I've tried 'everything'! Open and close the main form from the invoice form, requery the listbox from the invoice form, requery the listbox once the main form has the focus etc. Clicking my reset button on the main form updates the listbox just fine - I've tried cmdReset_Click from the invoice form (worked in Access 97!), but gives an error in 2007.

Sorry its so mundane, yet long to write out! Can someone help?

Many thanks.

stonward

[Rabbit]

Show us the code that you're using to refresh the list box. Also, what is the text of the error message?

[stonward]

I don't get any error messages, Rabbit. The latest thing I tried was DoCmd.Requery(...listbox55) in the Close event of the invoice form.

I'm guessing now that I'm going to have to fully close the invoice form before requerying the listbox. Essentially, I've tried the Requery command at just about every event!

It's feels such a silly, annoying issue (unlike my struggles with Allen Brownes 'onHand' code!), and there aren't many ways really to use requery.

Thanks for your time - especially at this time.

Regards,

stonward

[NeoPa]

It's perfectly possible to do by using :

Call {Reference to ListBox control}.Requery()

The only problem is determining the {Reference to ListBox control}. Referring to Items on a Sub-Form should help with that. If you struggle we can help, but only if you give us all the details you'll need for the reference.

[Rabbit]

Post 1, paragraph 3 (not counting the greeting), last line. You say you get an error. Now you're saying you're not getting an error.

Also, this:

DoCmd.Requery(...listbox55)

is not valid code so it should have thrown an error.

Per NeoPa's post, the most likely cause is an invalid reference to a control, which is why I wanted to see your requery code.

[TheSmileyCoder]

Whats the name of the form, name of the listbox control, and is the listbox in a subform?

[stonward]

Thanks for your replies.

I only got an error when I tried to 'remotely' click a button on my main form - cmdReset_Click.

The listbox is in a two page tab control, but not a subform. The main form is frm_Main, the listbox list55.

So to be completely disambiguous about my listbox:
Form_frm_Main.List55.Requery is about as explicit as I can get I think. The lisbox is on page one of a tab control: I dont have to reference that do I?

Appreciative,

stonward

[TheSmileyCoder]

Ooops! Does tab send the post?

No but Tab (Which switches focus to the Post Reply Button) followed by a Enter or spacebar will submit :)

I don't use tab controls much, but no I don't think you need to include the tab control in the syntax.

Try this:

Forms!frm_Main.List55.Requery

P.s. Save yourself future headache and give the listbox a proper name!

[Seth Schrock]

I don't use tab controls much, but no I don't think you need to include the tab control in the syntax.

You are correct Smiley.

[stonward]

ha ha! Yes, and for anyone else listening, have a firm naming system. I have, but I slip sometimes when I'm fighting to get something done - and I pay for it every time!

Thanks guys,

stonward

[TheSmileyCoder]

You are welcome.

Note that Form_Frm_main is the reference to class object of your form.

When you open the form, then an object of that class gets added to the Forms collection, with a key Frm_Main. Thats why you can use the notations
Forms!Frm_Main
or
Forms("Frm_Main")

You can argue that the object in the forms collection is a Form_frm_Main but that within the collection it has been assigned the key frm_Main. (At least that is my basic understanding of the underlying mechanics).


Per default Access only opens 1 object of each form class, but it is possible to have the same form open multiple times, although it requires a slight bit more work.

[NeoPa]

Stonward:
So to be completely disambiguous about my listbox:
Form_frm_Main.List55.Requery is about as explicit as I can get I think.

Good answer, but strangely, more helpful information would be where it is relative to the code that's running. If, as seems likely, the code is running from somewhere on the same form (within that same form's associated module) then referring to it using an external reference is not to be recommended at all. That simply adds to your maintenance overhead, and is easily avoided using the Me reference.

Thus, in that scenario (Most definitely doesn't work from outside of the scope of that same form.) the code would be :

Call Me.List55.Requery

NB. The external reference will still work of course. It is simply an overly clumsy way to code it in those circumstances.

[stonward]

NeoPa; You've just taught me something new. I didn't know you can call a method like that. Be assured I'll be using that!

Many thanks again,

stonward

[NeoPa]

Very pleased to hear it :-)

I find most of my event procedures start with With Me and end with End With. If you don't know about With then that's another you would probably benefit from picking up.