Refreshing Form from SubForm data

[slenish]

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??

[TheSmileyCoder]

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.

[slenish]

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.

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.

[mseo]

hi,
you can use the AfterUpdate event I assumed the name of the combobox controll is customer_id

 Private Sub Customer_ID_AfterUpdate()
Me.Customer_ID.Requery
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

[slenish]

hi,
you can use the AfterUpdate event I assumed the name of the combobox controll is customer_id

 Private Sub Customer_ID_AfterUpdate()
Me.Customer_ID.Requery
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

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?

[slenish]

hi,
you can use the AfterUpdate event I assumed the name of the combobox controll is customer_id

 Private Sub Customer_ID_AfterUpdate()
Me.Customer_ID.Requery
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

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?

[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

Public bDataAdded as boolean
Public lngKey as long

2)

Private sub cmb_Test_NotInList(newData as String,Response as Integer)
  if vbYes=Msgbox("Item not found, do you wish to add the item now?",vbYesNo)
    'Proceed
  Else
    Me.cmb_Test.Undo
    Response=acDataErrContinue
    Exit Sub
  End If

  'User wants to add new item, open form for him
    bDataAdded=False 'Set this public variable to keep track of whether data was added
    DoCmd.OpenForm "frm_AddNewData",acNormal,,,acFormAdd,acDialog,NewData
  'This code will not proceed until user closes the frm_AddNewData form.
  '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

Private Sub Form_AfterInsert()
  bDataAdded=True
  lngKey=me.MyPrimaryKeyField
  Docmd.Close
End Sub

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

If bDataAdded Then
  'Data was added
  Me.cmb_Test.Undo 'Access wont let you save unless you do this undo first
  Me.cmb_Test.Requery
  Me.Cmb_Test=lngKey
Else
  'User cancelled his adding of new item
  Me.Cmb_Test.Undo
End If
Response=acDataErrContinue 'Proceed without sending user a error msg.
End Sub

[NeoPa]

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.

[slenish]

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.

[NeoPa]

QUOTE=slenish;3 561963
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.

[slenish]

QUOTE=slenish;3 561963
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.

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!

[mseo]

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