If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Add record to mainform when user enters data in subform?
Didn't get any help with my first post so I'll try again. I need to be able
to add a record to the mainform as soon as a user enters data in the subform. I've tried using the before insert even on the subform and Access doesn't like it telling me that I can't add a record on the many side of the relationship without first adding a record on the one side of the relationship. I'm really at a loss here. Any questions comments or ideas are welcome. Thank You -- Message posted via http://www.accessmonster.com |
#2
|
|||
|
|||
Daryl Mhoon wrote:
Didn't get any help with my first post so I'll try again. I need to be able to add a record to the mainform as soon as a user enters data in the subform. I've tried using the before insert even on the subform and Access doesn't like it telling me that I can't add a record on the many side of the relationship without first adding a record on the one side of the relationship. I'm really at a loss here. Any questions comments or ideas are welcome. You cannot do what you are attempting. The parent record must exist FIRST. Is there a reason you want to do it in reverse order? -- I don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com |
#3
|
|||
|
|||
Yes there is! One form is frmPayments, the second is subfrmPaymentMethods.
1 to many relationship. One payment can have multiple methods, (cash, check, credit). Each payment has a date and a memo field (Balance due no later than 7/15/05 etc...) Each method has an amount pmntMethod and its own memo field (3rd party check #1234). Because the default value of the PmntDate field is set to Date(), the user wants to start adding records to the subform before the main form. I've tried setting the PmntDate and adding a record to frmPayments on the BeforeInsert event of subfrmPaymentMethods. Didn't work... Thank you VERY MUCH for responding. There has to be a way I can do this. I could force the user to enter the date before entering the methods, but that seems a waste of keystrokes when most payments will be processed the day they are paid. Is my logic twisted? Any suggestions? -- Message posted via http://www.accessmonster.com |
#4
|
|||
|
|||
OK I can forsee another question. "Why don't you just put all that info in
one table?" I'm in property management. Tenants can pay their rent in very unorthodox ways. I have one guy that gets paid twice a month. He purchases a money order with his 2nd paycheck so that he won't spend it on something else. Then he signs over his 1st paycheck of the month and pays the rest with cash and coin. I have some girls from France that paid six month's ahead with traveler's checks. Then I have roomates that pay with two checks. (I had one fella rob a 7-11 then pay his rent in cash. He was arrested an hour later in his living room eating stolen potatoe chips and watching porn. Fortunately I was able to keep the money.) Then of course some checks bounce. If I can look up all checks and memos for each check it's easier to reverse the payment and create an NSF charge. I've thought long and hard on the structure of this database and I know what I need. Unfortunately I'm learning VBA from scratch. -- Message posted via http://www.accessmonster.com |
#5
|
|||
|
|||
Daryl Mhoon wrote:
Yes there is! One form is frmPayments, the second is subfrmPaymentMethods. 1 to many relationship. One payment can have multiple methods, (cash, check, credit). Each payment has a date and a memo field (Balance due no later than 7/15/05 etc...) Each method has an amount pmntMethod and its own memo field (3rd party check #1234). Because the default value of the PmntDate field is set to Date(), the user wants to start adding records to the subform before the main form. I've tried setting the PmntDate and adding a record to frmPayments on the BeforeInsert event of subfrmPaymentMethods. Didn't work... Thank you VERY MUCH for responding. There has to be a way I can do this. I could force the user to enter the date before entering the methods, but that seems a waste of keystrokes when most payments will be processed the day they are paid. Is my logic twisted? Any suggestions? Just give them a button to press that automatically populates the PmntDate field instead of using a default value and then have them press that before they try to enter sub-records. -- I don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com |
#6
|
|||
|
|||
Daryl Mhoon wrote:
OK I can forsee another question. "Why don't you just put all that info in one table?" No, that is a bad idea given your description. -- I don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com |
#7
|
|||
|
|||
I was trying to avoid buttons...
I really don't want to add a child before I add the parent. I want Access to realize when the user attempts to enter the subform then generate a parent record with the default info then allow the user to continue entering info on the subform. Can I use code to add a recordset on BeforeInsert? Can I place an unbound subform on the main form and place a "Process Payment" button that will add a recordset to both main and sub form? If so do I have to Change DataEntry for the form to "No?" I'm SO sorry I cannot ask more intelligent questions. I've done some incredible things with Access, since I've subscribed to this forum I've done much more. I'm not that stupid, I'm just hard headed and inexperienced. Thanx, -- Message posted via http://www.accessmonster.com |
#8
|
|||
|
|||
Rick,
I just had a fabulous idea involving a control button. It's gonna take some time (have I mentioned how inexperience I am..?) Please check back on this post and I'll let you know how it goes! It's amazing how a little bit of outside input can help!!! Sometimes you have to step back and take a deep breath. DM -- Message posted via http://www.accessmonster.com |
#9
|
|||
|
|||
you can "trick" Access into adding a record in the main form when you
*begin* adding a record in the subform. first, remove the DefaultValue setting in the date control in the main form. then add the following code in the *subform* BeforeInsert event, as Private Sub Form_BeforeInsert(Cancel As Integer) With Me.Parent If .NewRecord Then .MyDateField = Date .Dirty = False End If End With End Sub substitute the correct name for your date control, of course. i did see your post where you said you tried this and it didn't work for you. i'm guessing you tried some kind of SaveRecord command, which doesn't work. i've tested the above code, and so has a user who posted in May asking for a solution to essentially the same issue - it works for both of us. note, if your main form record's primary key is generated programmatically (rather than being an Autonumber), you'll have to include that code in the If statement, *before* the line ".Dirty = False". hth "Daryl Mhoon" wrote in message ... I was trying to avoid buttons... I really don't want to add a child before I add the parent. I want Access to realize when the user attempts to enter the subform then generate a parent record with the default info then allow the user to continue entering info on the subform. Can I use code to add a recordset on BeforeInsert? Can I place an unbound subform on the main form and place a "Process Payment" button that will add a recordset to both main and sub form? If so do I have to Change DataEntry for the form to "No?" I'm SO sorry I cannot ask more intelligent questions. I've done some incredible things with Access, since I've subscribed to this forum I've done much more. I'm not that stupid, I'm just hard headed and inexperienced. Thanx, -- Message posted via http://www.accessmonster.com |
#10
|
|||
|
|||
Thanks Tina,
Worked beautifully! I had the feeling I was using the right event. I just didn't have the right code. Rick, The button I worked on last night worked perfect. I set the subform. AllowAdditions to False, added a button called "ctlToday" that would update the date field, allow additions to the subform then dissapear when clicked. I prefer to use a few buttons as possible. Users see buttons and they start playing with their mouse. When they keep their hands on the keyboard they are much more effecient. Thanks to everybody. I've been fumbling around with that delimna for too long. I can't express how happy I am for having found this website... Daryl -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200507/1 |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Creating Records in tables automatically | peterg290935 | Using Forms | 8 | June 22nd, 2005 08:12 AM |
Display record in a form after user enters criteria using dialog b | need some help over here | Using Forms | 1 | February 24th, 2005 01:59 PM |
Format on data to import to Access tables? (I need your advice) | Niklas Östergren | General Discussion | 5 | December 13th, 2004 02:54 PM |
How do I get 3 series in sync with the x-axis? | zizbird | Charts and Charting | 10 | October 25th, 2004 01:23 PM |
Columns and layout problems | Phil | Setting Up & Running Reports | 4 | August 18th, 2004 01:34 PM |