We help IT Professionals succeed at work.

Requerying a listbox

jondecker76
jondecker76 asked
on
381 Views
Last Modified: 2013-11-28
I have a Listobx on a form (Form_A) that is populated by Table_A
There is a button on the same Form_A  to Add a record to Table_A.
          -CLicking on the "Add"  button opens a new form (Form_B) to enter information. Once this information is entered, the information is Inserted into Table_A, and Form_B closes.

The Problem now is that there is new data in Table_A, and the listbox on Form_A has not updated to reflect this.

In my VBA code for the "Add" button on Form A I have tried the following to requery the listbox (thinking it would do the requery after returning from Form_B)
     DoCmd.OpenForm stDocName, , , stLinkCriteria, , , CStr(Me.lstTrips.value)
     Me.lstAffected.Requery

This did not work, as the Me.lstAffected.Requery would happen before new data was entered on From_B


So now, directly inside the Form_B VBA code, after I execute my SQL statement to add the data, I tried:
Forms.frmModuleData_Trip.lstAffected.Requery
But then I get an error "Object does not support that property or method."

How can I get my listBox on Form_A to requery from VBA code in Form_B?

thanks
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2016

Commented:

try this

Forms![frmModuleData_Trip]!lstAffected.Requery


is  frmModuleData_Trip the name of Form A you mentioned above?
CERTIFIED EXPERT

Commented:
'Forms.frmModuleData_Trip.lstAffected.Requery'

I would have expected the code to work.

It might worth trying with different syntax..

Forms("frmModuleData_Trip")!lstAffected.Requery

Author

Commented:
Tried the different syntax. I get an error that "Project can't find the form 'frmModuleData_Trip' referred to in a Macro Expression or VBA code."

But I assure you that frmModuleData_Trip does indeed exist in the project. At the time that Form_B is called, frmModuleData_Trip is a subform nested 2 layers deep, if that makes a difference.

I can take screenshots if you want, but again, frmModuleData_Trip does exist!
CERTIFIED EXPERT
Top Expert 2016

Commented:
CERTIFIED EXPERT

Commented:
Not as far as Access is concerned it doesn't!
Once it's a subform it's just a control in another form.
Yes it makes an enormous difference!

To get 2 levels down you need a lengthy reference

Forms!mainformname!sub1name.Form!sub2name.Form!listboxname.requery



Author

Commented:
Ok I will test this out..

But, why is this so ugly? I have programed in C, C++, Ruby, PHP, Python and a few other lesser-known languages, and have never seen such horrible structure. Is it something that I am doing wrong while learning VBA, or is it just in the nature of VBA?

For example, what I am trying to accomplish is simple:
Click a button to add a new entry to a listbox
this opens a new form to fill in new data
click a button on that new form to insert that data in the database and close this form
Update a listbox to show the changes


It would be considered good programming practice to do this all in one place (for maintainability), but VBA is forcing me to have some code in the OnCLick() event, then other code in the new form to update the list.
If I change how it works in the future, I would have to remember that the list gets refreshed from another form..

DOes anyone see my point? Can anyone offer any suggestions in learning to deal with this in VBA?
CERTIFIED EXPERT

Commented:
"It would be considered good programming practice to do this all in one place "

Really?  Wouldn't you want the object and the code to move together if you re-used the object?

Author

Commented:
Yes, that is what I said - all in one place...
Ideally, my Form_B (in my example) could work to populate the database, and could be opened by any form. But now, since I had to break up my code, and there is a hard-coded requery pointing back to a specific form, now it can't be re-used.

VBA forces you to break what I would consider good programming practice, and now I have code in 2 places, making maintainability bad, as well as not being able to re-use things because of the now hard-coded requery.

Is it something that I'm doing wrong and not understanding, or is it just how things are with VBA?
CERTIFIED EXPERT
Top Expert 2016
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Wow, I never knew that - this is exactly what I was looking for!
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.