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

Update Main Form from Subform

Expert 100+
P: 296
I'm fairly new to this so I'll try to be as clear as possible. I have a database that stores employee information and all job titles available. I have a main form that is based from a query and displays all job codes and job titles in the Job table. I also have a text box in the main form to keep track of the total number of positions available for this job title:
=DSum("[NumberofPositions]","[JobVacancy]","[code]=[JobVacancy]![JobCode]")
Where JobVacancy is a table that contains JobCode, Department, Division, NumberofPositions and Code is the job code stored in the Job table.

My subform is based on another query that pulls the department and division related to each job code from the JobVacancy table. It also has the NumberofPositions from the JobVacancy table on it. My subform and main form are linked by the job codes and the subform is a continuous form.

Basically what the user will see when they open the main form is the job code and title in the main form, and a box for the Total Positions. (There is a bit more info shown as well, but I believe it's irrelevant for the problem). Then they will have to select a department and division in the subform through combo boxes and input how many positions are available. Each job code may be associated with many department and divisions. Everything works great, except I want the textbox in the main form to update the total number of positions as soon as it is changed in the subform. Currently, it only updates if I close and then reopen the form. Any suggestions would be appreciated!! Please keep in mind that I have never coded in visual basic and would need very specific instructions if that is the solution.

Thanks!
Michelle
Jul 16 '07 #1
Share this Question
Share on Google+
16 Replies


Scott Price
Expert 100+
P: 1,384
In your subform, when a user changes a value that you want reflected in the main form, you will need to write a simple requery code behind the AfterUpdate event of the control they are entering the information into. Step by step, it looks like this:

1. In form design view of subform, bring up the properties window of the text box/combo box etc that the user enters the new data in.
2. Go to the Events tab.
3. Click in the margin to the right of the After Update line.
4. click the "..." character
5. Choose Code builder.
6. In the window that comes up write this code:
Expand|Select|Wrap|Line Numbers
  1.  Private Sub [YourControlName]_AfterUpdate
  2. Forms![YourMainFormName].Requery
  3. End Sub
  4.  
7. Click the Debug menu, and choose compile (1st item on the menulist).
8. Save your changes, and test to see if it works!

You will need to change [YourMainFormName] obviously to the corresponding name you are using. [YourControlName] will be filled in automatically in the VBA window, so you shouldn't need to change it.

Excuse me if I've simplified this process too much, you likely know most of these steps :-) Just put them in in case you didn't.
Jul 17 '07 #2

Scott Price
Expert 100+
P: 1,384
BTW, you can put this requery code in as many of the controls as you want, or just in the last one that will be updated.

Keep in mind that if your query is complex, the requery'ing may slow your db response down noticeably.

Hope this helps.

Regards,
Scott
Jul 17 '07 #3

Expert 100+
P: 296
Thanks for the response! That works great, except as soon as I make a change, it skips back to the first record...I have 257 records so that's kind of annoying. Is there anyway to keep it on the current record after it updates?
Jul 17 '07 #4

Scott Price
Expert 100+
P: 1,384
There is, but I'll have to get back to you on how to do it (off to lunch)...

Regards,
Scott
Jul 17 '07 #5

Expert 100+
P: 296
Thanks Scott. I'll watch for a post.
Jul 17 '07 #6

Scott Price
Expert 100+
P: 1,384
All right, back to work!

This method should work for you as long as you are not affecting any sort order on your subform with whatever updating you do. The reason for this caveat is that we are going to assign a variable to the current record number that you are viewing, updating. This record number is form-specific, i.e. when you look at the bottom left of your form and see the navigation buttons with which record you are looking at, this is what the variable will see. The problem being, that if you are sorting based on some criteria, and your subsequent change affects the sort order of the record you were working on, this variable will redirect you to the record # of the original record. (I'll leave this explanation at the moment so as not to drown you with too many details :)

In design view again, you will bring up the properties for control you made the After Update change to. Go again to the ellipsis ("...") to the right of the Before Update line. Choose again Code Builder. Between the two lines of code that come up in the VBA editor window, write in this code:
Expand|Select|Wrap|Line Numbers
  1. lngrecordnum = Me.CurrentRecord
Now go to the top of the window, where it has Option Compare Database. Immediately below, write these two lines:
Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2.  
  3. Dim lngrecordnum As Long
  4.  
Now go back down to the AfterUpdate subroutine, and immediately after the Requery command that you wrote in already, write this code in:

Expand|Select|Wrap|Line Numbers
  1. DoCmd.GoToRecord acActiveDataObject, , acGoTo, lngrecordnum
Regards,
Scott
Jul 17 '07 #7

Expert 100+
P: 296
Hmm...Still jumps back to the first record. It does update the record in the main form still, though. Here's what I have in my code

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3. Dim lngrecordnum As Long
  4.  
  5. Private Sub NumberofPositions_AfterUpdate()
  6. Forms![JobVacancyMain].Requery
  7. DoCmd.GoToRecord acActiveDataObject, , acGoTo, lngrecordnum
  8. End Sub
  9.  
  10. Private Sub NumberofPositions_BeforeUpdate(Cancel As Integer)
  11. lngrecordnum = Me.CurrentRecord
  12. End Sub
What am I doing wrong??
Jul 17 '07 #8

Expert 100+
P: 296
Just to clarify, it is the main form that jumps back to the first record. I tried changing the line

Expand|Select|Wrap|Line Numbers
  1. lngrecordnum = Me.CurrentRecord
to
Expand|Select|Wrap|Line Numbers
  1. lngrecordnum = Forms![JobVacancyMain].CurrentRecord
and when I tried it I got a Run Time error 2105 - You can't go to the specified record.

When it went to the debugger, lngrecordnum's value was 210, which was the correct record that I want it to stay on. Again, that is the 210th record of the Main form.

It is this line that was highlighted in the debugger:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.GoToRecord acActiveDataObject, , acGoTo, lngrecordnum
Jul 17 '07 #9

Expert 100+
P: 296
Just took a look at the original code that you sent me did again:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3. Dim lngrecordnum As Long
  4.  
  5. Private Sub NumberofPositions_BeforeUpdate(Cancel As Integer)
  6. lngrecordnum = Me.CurrentRecord
  7. End Sub
  8.  
  9. Private Sub NumberofPositions_AfterUpdate()
  10. Forms![JobVacancyMain].Requery
  11. DoCmd.GoToRecord , , acGoTo, lngrecordnum
  12. End Sub
I stand corrected - it did in fact stay on the same record number in the subform, but the main form jumps back to the first record.
(i.e. - if I was entering into the second record in the subform, but it is the 210th record of the main form, it jumps back to the 1st record of the main form but stays on the 2nd record of the subform. What I need is for it to stay on the 210th record of the main form and the 2nd record of the subform)

Hope this helps clarify what I'm trying to do!
Jul 17 '07 #10

Expert 100+
P: 296
Got it to work!! Here's my code:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3. Dim lngrecordnum As Long
  4.  
  5. Private Sub NumberofPositions_BeforeUpdate(Cancel As Integer)
  6. lngrecordnum = Forms![JobVacancyMain].CurrentRecord
  7. End Sub
  8.  
  9. Private Sub NumberofPositions_AfterUpdate()
  10. Forms![JobVacancyMain].Requery
  11. DoCmd.GoToRecord acDataForm, "JobVacancyMain", acGoTo, lngrecordnum
  12. End Sub
Only one thing - the screen flashes on the update. Is there a way to avoid that?
Jul 17 '07 #11

Expert 100+
P: 296
It appears that this will work as well:

Expand|Select|Wrap|Line Numbers
  1. Private Sub NumberofPositions_AfterUpdate()
  2. Forms![JobVacancyMain].Refresh
  3. End Sub
How do I get it to update if I delete a record from the subform?
Jul 17 '07 #12

Scott Price
Expert 100+
P: 1,384
Just got back to my desk... Glad you got it to work! I'm not sure about the flashing screen problem... Haven't encountered that one before.

On your last question, you're asking how to update what when you delete a record from the subform? I'm assuming the main form? If so, in design view of your subform, you'll need to go to the form's properties, under the On Delete event, enter your Requery command of the main form. Note that refreshing will not work (shouldn't anyway according to MS, see the following qoutes)

Yes, in your last post you had changed the Requery to Refresh, which will work as well for updating records. This is what MS's helpfile has to say about differentiating the two methods:
"The Refresh method shows only changes made to records in the current set. Since the Refresh method doesn't actually requery the database, the current set won't include records that have been added or exclude records that have been deleted since the database was last requeried. Nor will it exclude records that no longer satisfy the criteria of the query or filter. To requery the database, use the Requery method. When the record source for a form is requeried, the current set of records will accurately reflect all data in the record source.

Notes

It's often faster to refresh a form or datasheet than to requery it. This is especially true if the initial query was slow to run.
Don't confuse the Refresh method with the Repaint method, which repaints the screen with any pending visual changes."


You're quickly picking up the 'basics' of VBA (so sorry for the bad pun :-) Soon you'll be giving back to others in the forums!

Regards,
Scott
Jul 17 '07 #13

Expert 100+
P: 296
Thanks! I did stick with the requery rather than refresh for the above problem that was solved.
Yes, I did mean update the main form. I had to put the requery in the After Del Confirm rather than the On Delete because it was requerying before the record was actually deleted. It works now. The code is as follows:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_AfterDelConfirm(Status As Integer)
  2. lngrecordnum = Forms![JobVacancyMain].CurrentRecord
  3. lngrecordnumsub = Me.CurrentRecord
  4. Forms![JobVacancyMain].Requery
  5. DoCmd.GoToRecord acDataForm, "JobVacancyMain", acGoTo, lngrecordnum
  6. DoCmd.GoToRecord acActiveDataObject, , acGoTo, lngrecordnumsub
  7. End Sub
As for the flashing that I mentioned, what I meant was that at the moment it requeries, the screen goes to the first record of the main form for a split second before displaying the updated data on the current form. Is there any way to get around that?
Jul 17 '07 #14

Scott Price
Expert 100+
P: 1,384
I'm guessing the flash you described is because of the requery method. When you requery, it consumes more computer resources slowing the whole process down. Did you try the refresh method for the cases that do not include deleting or adding records? I'm wondering also if it may work around that.

Nothing else is occurring to me at the moment, sorry! Glad you've got things working with the AfterDeleteConfirm event.

Regards,
Scott
Jul 18 '07 #15

Expert 100+
P: 296
Hi Scott. Yes, I did try the refresh method, and it still causes the "flash" that i described earlier. I'm not too worried about it at this point because once the initial information is entered into this particular form, it will be more for reference than data entry, except for the occasional update. Thanks for all of your help!!
Jul 18 '07 #16

Scott Price
Expert 100+
P: 1,384
You're welcome, Michelle, glad I could help...

Regards,
Scott
Jul 19 '07 #17

Post your reply

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