472,372 Members | 1,513 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,372 software developers and data experts.

Update Main Form from Subform

296 Expert 100+
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
16 33798
Scott Price
1,384 Expert 1GB
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
1,384 Expert 1GB
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
mlcampeau
296 Expert 100+
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
1,384 Expert 1GB
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
mlcampeau
296 Expert 100+
Thanks Scott. I'll watch for a post.
Jul 17 '07 #6
Scott Price
1,384 Expert 1GB
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
mlcampeau
296 Expert 100+
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
mlcampeau
296 Expert 100+
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
mlcampeau
296 Expert 100+
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
mlcampeau
296 Expert 100+
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
mlcampeau
296 Expert 100+
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
1,384 Expert 1GB
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
mlcampeau
296 Expert 100+
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
1,384 Expert 1GB
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
mlcampeau
296 Expert 100+
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
1,384 Expert 1GB
You're welcome, Michelle, glad I could help...

Regards,
Scott
Jul 19 '07 #17

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

Similar topics

9
by: William Wisnieski | last post by:
Hello Everyone, Access 2000 I have a main form with a continuous subform. On the main form I have a text field called . It gets populated based on what the user selects in a field on the...
1
by: Bill Strass | last post by:
Problem: Access main form/subform as front end to SQL Server backend. Add/edit via subforms work fine. Not so with main form. Set up a master-detail form-subform using two views linked from SQL...
0
by: Deano | last post by:
I have a main/subform arrangement which works well. The main form's parent records are about employees. The subform calculates their salary. I have now decided that the user can enter some...
4
by: Dave Boyd | last post by:
Hi, I have two very similar forms each with a subform. The main form gets a few fields from the user and passes this back to a query that the subform is bound to. The requery is done when the...
9
by: PC Datasheet | last post by:
I'm stuck on something that seems should be easy and I need some help. My main form has an option group with five options. My subform chooses from different lists depending on which option is...
30
by: Shannan Casteel via AccessMonster.com | last post by:
I have a subform named "sbfrmParts" with a list of parts along with the quantity and price. I have used a text box in the subform's footer and set the control source to "=Sum(*)". I set the...
4
by: Scotter | last post by:
Hello. I'm having problems understanding how to calculate a total on my main form based on the items in my subform. I've tried runing update querys and thats not working, and Ive tried just...
3
by: Steve | last post by:
Is there a way to put a main form/subform in a continuous form so I can scroll through all the records in the main form? Thanks!
0
by: farid121 | last post by:
I am trying to update the main form with each record in the subform. I have two tables Invoice and Payment table. The main form has the payment details which I need to update for each invoice...
0
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge required to effectively administer and manage Oracle...
0
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was proposed, which integrated multiple engines and...
1
by: Matthew3360 | last post by:
Hi, I have been trying to connect to a local host using php curl. But I am finding it hard to do this. I am doing the curl get request from my web server and have made sure to enable curl. I get a...
0
Oralloy
by: Oralloy | last post by:
Hello Folks, I am trying to hook up a CPU which I designed using SystemC to I/O pins on an FPGA. My problem (spelled failure) is with the synthesis of my design into a bitstream, not the C++...
0
by: Carina712 | last post by:
Setting background colors for Excel documents can help to improve the visual appeal of the document and make it easier to read and understand. Background colors can be used to highlight important...
0
BLUEPANDA
by: BLUEPANDA | last post by:
At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS starter kit that's not only easy to use but also...
1
by: Johno34 | last post by:
I have this click event on my form. It speaks to a Datasheet Subform Private Sub Command260_Click() Dim r As DAO.Recordset Set r = Form_frmABCD.Form.RecordsetClone r.MoveFirst Do If...
1
by: ezappsrUS | last post by:
Hi, I wonder if someone knows where I am going wrong below. I have a continuous form and two labels where only one would be visible depending on the checkbox being checked or not. Below is the...
0
DizelArs
by: DizelArs | last post by:
Hi all) Faced with a problem, element.click() event doesn't work in Safari browser. Tried various tricks like emulating touch event through a function: let clickEvent = new Event('click', {...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.