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

Refreshing Form from SubForm data

100+
P: 283
I created a Subform that pops open by using the NotInList Function. but after I enter the information and add the record and close my SubForm my MainForm does not refresh unless I close it.

My question is how can I make it refresh after I have entered in the SubForm information and also when it refreshes it will auto populate the boxes in my MainForm??
Apr 10 '10 #1

✓ answered by TheSmileyCoder

No offense but that sounds like a crappy way of doing it.

The approach to use could be like this:
  1. Place some public variables in a module
  2. On the NotInList event, open a DIALOG form to add the new data
  3. On the Data Entry form, add code to handle whether or not user actually adds data.
  4. Proceeding in the NotInList event, we handle the outcome of the Data Entry

'Disclaimer: All code written directly in post, so there may be typos :)
1)
In a module, I have placed these 2 variables
Expand|Select|Wrap|Line Numbers
  1. Public bDataAdded as boolean
  2. Public lngKey as long


2)
Expand|Select|Wrap|Line Numbers
  1. Private sub cmb_Test_NotInList(newData as String,Response as Integer)
  2.   if vbYes=Msgbox("Item not found, do you wish to add the item now?",vbYesNo)
  3.     'Proceed
  4.   Else
  5.     Me.cmb_Test.Undo
  6.     Response=acDataErrContinue
  7.     Exit Sub
  8.   End If
  9.  
  10.   'User wants to add new item, open form for him
  11.     bDataAdded=False 'Set this public variable to keep track of whether data was added
  12.     DoCmd.OpenForm "frm_AddNewData",acNormal,,,acFormAdd,acDialog,NewData
  13.   'This code will not proceed until user closes the frm_AddNewData form.
  14.   'For the rest of this code look at 4)

3)
Now we have opened the data entry form. We need to handle whether or not user actually adds data, or cancels. So add to the forms AfterInsert event
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_AfterInsert()
  2.   bDataAdded=True
  3.   lngKey=me.MyPrimaryKeyField
  4.   Docmd.Close
  5. End Sub
  6.  
Whether or not you want to tie that into buttons or whatever, depends on your application.



4)
Now that we have added data, lets proceed where we left off in 2), with the rest of the code for the NotInListEvent
Expand|Select|Wrap|Line Numbers
  1. If bDataAdded Then
  2.   'Data was added
  3.   Me.cmb_Test.Undo 'Access wont let you save unless you do this undo first
  4.   Me.cmb_Test.Requery
  5.   Me.Cmb_Test=lngKey
  6. Else
  7.   'User cancelled his adding of new item
  8.   Me.Cmb_Test.Undo
  9. End If
  10. Response=acDataErrContinue 'Proceed without sending user a error msg.
  11. End Sub
  12.  

Share this Question
Share on Google+
11 Replies


TheSmileyCoder
Expert Mod 100+
P: 2,321
A subform is a form embedded into another main form. Since your talking about opening and closing it, im guessing you don't mean a subform. If you open the subform with the acdialog property, the code in your main form will be "paused" until you close the subform. So after the line opening the subform, just add a line saying to requery the combobox in question.

You can then use a public variable to hold the key of hte newly added record, and then select it via code.

If you need more specific instructions, let us know.
Apr 10 '10 #2

100+
P: 283
@TheSmileyOne
Thanks for the reply TheSmileyOne,

Later on I realized that i am not actually doing a subForm. I just have it so a seperate Form opens up after you wish to add something to the list. So I need to know how to make my main form refresh or re-query the information that has been added to the table its pulling from. I already have it set up to pull from a query so should I just add something in to each box as you said to update after you close the form?

Some examples would be great!

Appreciate the help.
Apr 11 '10 #3

mseo
100+
P: 181
hi,
you can use the AfterUpdate event I assumed the name of the combobox controll is customer_id
Expand|Select|Wrap|Line Numbers
  1.  Private Sub Customer_ID_AfterUpdate()
  2. Me.Customer_ID.Requery
  3. End Sub 
this code will work fine and you don't have to close and reopen the form,
if you didn't find the new record in the combobox, just select any other record within the combobox and research for the record that you recently inserted you will find it I guess this is the point
hope that help
Apr 11 '10 #4

100+
P: 283
@mseo
Hi Mseo,

Well you are right that does work but I have to select another record first inorder to make it update. Which is kind of a pain. I dont want the user to have to do that every time.

Do you know if there is a way to set an auto update that will make the form refresh every so many sec or min?
Apr 12 '10 #5

100+
P: 283
@mseo
Update:

Ok I went off of what you said Mseo and started playing around with things more and found out that I can use Me.Refresh in the On Got Focus function and it will requery or refresh the box so it will pull up the number with out having to click on one first, but for some reason when I first leave the pop up form and go back to the main form it also gives me debug error because of the refresh and im not sure why but if i close out of the debug then it works any one got any ideas how i could fix that so the debug error does not pop up?
Apr 12 '10 #6

TheSmileyCoder
Expert Mod 100+
P: 2,321
No offense but that sounds like a crappy way of doing it.

The approach to use could be like this:
  1. Place some public variables in a module
  2. On the NotInList event, open a DIALOG form to add the new data
  3. On the Data Entry form, add code to handle whether or not user actually adds data.
  4. Proceeding in the NotInList event, we handle the outcome of the Data Entry

'Disclaimer: All code written directly in post, so there may be typos :)
1)
In a module, I have placed these 2 variables
Expand|Select|Wrap|Line Numbers
  1. Public bDataAdded as boolean
  2. Public lngKey as long


2)
Expand|Select|Wrap|Line Numbers
  1. Private sub cmb_Test_NotInList(newData as String,Response as Integer)
  2.   if vbYes=Msgbox("Item not found, do you wish to add the item now?",vbYesNo)
  3.     'Proceed
  4.   Else
  5.     Me.cmb_Test.Undo
  6.     Response=acDataErrContinue
  7.     Exit Sub
  8.   End If
  9.  
  10.   'User wants to add new item, open form for him
  11.     bDataAdded=False 'Set this public variable to keep track of whether data was added
  12.     DoCmd.OpenForm "frm_AddNewData",acNormal,,,acFormAdd,acDialog,NewData
  13.   'This code will not proceed until user closes the frm_AddNewData form.
  14.   'For the rest of this code look at 4)

3)
Now we have opened the data entry form. We need to handle whether or not user actually adds data, or cancels. So add to the forms AfterInsert event
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_AfterInsert()
  2.   bDataAdded=True
  3.   lngKey=me.MyPrimaryKeyField
  4.   Docmd.Close
  5. End Sub
  6.  
Whether or not you want to tie that into buttons or whatever, depends on your application.



4)
Now that we have added data, lets proceed where we left off in 2), with the rest of the code for the NotInListEvent
Expand|Select|Wrap|Line Numbers
  1. If bDataAdded Then
  2.   'Data was added
  3.   Me.cmb_Test.Undo 'Access wont let you save unless you do this undo first
  4.   Me.cmb_Test.Requery
  5.   Me.Cmb_Test=lngKey
  6. Else
  7.   'User cancelled his adding of new item
  8.   Me.Cmb_Test.Undo
  9. End If
  10. Response=acDataErrContinue 'Proceed without sending user a error msg.
  11. End Sub
  12.  
Apr 12 '10 #7

NeoPa
Expert Mod 15k+
P: 31,768
One important point is to use .requery rather than .Refresh. .Refresh will update only those items that are already showing in the control (or recordset, or whatever), whereas .Requery essentially reruns the whole query.

That would be the active ingregient as it were. How and when you trigger this is down to your careful consideration of exactly when, and under which circumstances, you need this to occur.
Apr 12 '10 #8

100+
P: 283
Hi TheSmileyOne,

WOW thats is a lot of code!

Wanted to give you guys an update I actually got it to work a little easier way before I saw your post.

What I did was when you go to close the second form I set the focus to go back to the text box you started on then once you refocus on that text box I set it to erase the information you had orginally typed and at the same time it requerys the list. All you have to do is type in the ID number again to the box and BAM it works perfectly :D took me a while to get it to work right but it works great.

I definalty am going to play around with that code you did Smiley. I know it will come in handy for this purpose or others. Thank you very very much for taking the time to do that!!

NeoPa,

where you been? I was hoping you might post to a couple other forums that I did but i havent heard from you in a while. Thanks for the info on the .Refresh. I kept playing with it for a while but found it not to be as useful as i orginally thought and moved on to to try other things.
Apr 13 '10 #9

NeoPa
Expert Mod 15k+
P: 31,768
QUOTE=slenish;3561963
NeoPa,

where you been? ...

/QUOTE
Oh, You know. A life and all ;) Easter. Work. All sorts of other inconvenient stuff that gets in the way of posting here sometimes. Even site changes that I have to be involved in, get in the way!

Never mind. It seems like you've got some good answers anyway. Things never work well when they depend too heavily on too few individuals. Luckily, we seem to be blessed here with a number of competent experts who can all help greatly across a variety of subjects. I certainly couldn't cover all that myself. Apart from the volume of course, I simply don't have the knowledge to cover it all.
Apr 13 '10 #10

100+
P: 283
@NeoPa
A life, im trying to remember what that is like...haha! I know what you mean by inconvenient stuff, seems to happen to me way to much! Hope your easter was good.

Yea it is nice there are so many knowledgable people on this site that can help out. One person covering it all would be way to over whelming!

Good to hear from you again!
Apr 14 '10 #11

mseo
100+
P: 181
hi slenish
your answer is good
but sometimes the user who inserts the data that will be viewed in the combobox not the same user who enters data in the form which has combobox so we should use the module level variables then use the requery as TheSmileyOne did
thank you
Apr 14 '10 #12

Post your reply

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