By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,009 Members | 1,487 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,009 IT Pros & Developers. It's quick & easy.

Pop Up Form to Enter Data, then Close and Requery Main Form

P: 99
I find that I always need to incorporate something like this into my forms, but I've never been happy on how I do it. Can someone advice me on the proper way to do this in Access.

Let's say I have Form "A" and it contains a List Box and a Button. The record source for the list box is a simple select query from one table.

In the List Box, I do not see the item I am looking for, so I want to add it. I click the Button and up pops Form "B". I enter the data into "Form B". Now I want to close Form "B" and I want the list box on Form "A" to requery and show the new element I just added in the List Box.

This is what I do now and I don't like it:

On Form "B" I create an Exit button. The onClick Event will contain code like:

Forms![Form A]!myListBox.requery
docmd.close

But this never seems to work consistently. Also, when I do this, I can not use Form "B" to pop up for any other forms besides Form "A".

I'm sure there's a better way. Anyone know it?

Many Thanks,
Adam
Sep 4 '08 #1
Share this Question
Share on Google+
4 Replies


FishVal
Expert 2.5K+
P: 2,653
Hello, Adam.

There are several ways to do it:
  • First for dummies. ;) Open "Form B" in dialog mode - code flow in "Form A" module will be paused and proceed after "Form B" is closed.
  • Pass "Form A" object variable to property declared in "Form B" module.
  • In "Form B" module declare event and raise it when form is closed, or when ListBox rowsource was updated. In "Form A" module declare with events object varible of "Form B" class and set it to "Form B" instance you have opened. Now you could trap event of "Form A" in "Form B" module.
    BTW you may use "native" events of "Form B" form object but custom event gives you more flexibility.

Regards,
Fish

P.S. I will attach a sample with an example of the last two methods. Method #2 used on "Form B" close, method #3 used when button is pressed on "Form B".
Attached Files
File Type: zip AB.zip (12.0 KB, 147 views)
Sep 4 '08 #2

Megalog
Expert 100+
P: 378
As far as your method not working consistently, I'm not sure how to help you without anymore information. But for a simple way to re-use the same "Form B", you can easily send an OpenArg property when opening the form, and use this to dictate where "Form B" saves it's data to. This works fine when there's only one variable to keep track of.

For multiple variables, you'll want to declare these on the target form, and pass the parameters after opening it. For example, lets say you want to pass a control name, and a form name. You would place something like this at the top of the "Form B" module:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Public mstrSourceControl As String
  5. Public mstrSourceFormName As String
Now when you open "Form B", you would use something similar to the following code in a button on "Form A":

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm FormName:="frmFormB", WindowMode:=acHidden
  2.  
  3. Form_frmFormB.mstrSourceControl = me.lstListBox.name
  4. Form_frmFormB.mstrSourceFormName = Form.Name   
  5. Form_frmFormB.visible = True
Now on "Form B", you can use mstrSourceControl and mstrSourceFormName however you like, and not be restricted to it only working on "Form A" in all instances.
Sep 4 '08 #3

P: 99
Thank you both!

I'll spend some time playing with these methods so I get the hang of it.

Adam
Sep 4 '08 #4

NeoPa
Expert Mod 15k+
P: 31,471
Adam, is the fundamental problem that your form A, after calling Form B to add the new record, cannot easily be triggered to recognise when form B has closed and therefore to call the ListBox.ReQuery?
Sep 5 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.