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

make sure a record added in a subform gets appended to the underlying table.

P: 59
Hello,
I am having a problem getting a new record in a subform to append to the underlying table. When I add a new record to the main form, the subform is blank, except for the ID field, which is populated because of its link to the main form. I would like this new record, populated at this point only with the ID from the main form, to then be appended to the underlying table. (The rest of the attributes for that record will be entered in a different form.)

Right now, even though I see the ID field populated on a new record in the subform, the record is not added to the table. If I manually enter the same value into the ID field on the subform, then the record does get added.

I tried to add a "Save Record" button to the subform, but, sadly, this did not work.

After searching the forum posts over a few days, I thought I had finally found the forum thread that would provide the answer! -- AccessIdiot/NeoPa/Denhurt's thread from March 2007 make sure data is entered before going to new form?, but the question I was following got a bit lost in the midst of additional questions... :(

I would greatly appreciate any advice. I assume I just haven't found the right code yet.

Here are my tables / forms:
the main form is:
frmAddBldgNm
txtBldgID
txtBldgNm
txtComplexNm

this form is based on an underlying table:
tblBldgs
BldgID
BldgNm
ComplexNm
etc.

The subform is:
sfrmBldgCont
txtBldgID

This subform is based on an underlying table:
tblContBldg
BldgID
ContactID

thanks in advance,
Bridget
Feb 15 '08 #1
Share this Question
Share on Google+
4 Replies


NeoPa
Expert Mod 15k+
P: 31,276
Do you have a validation rule preventing the subform record from beeing added ether in your subform,main form or table of "if Null", can't be blank, if you have this set to any controle wheather in form, main form or even the table it can prevent entry on a record (I have experienced an Itentical situaltion, my problem was a siple valdation rule for one of the txt boxs on my form, and does the rest of your form work correctly? (just checking the basics first)
This was unfortunately added via the Report link rather than the reply link.
Feb 17 '08 #2

Expert 100+
P: 446
Hi Brigette

I think the problem is (as you have found yourself) that a record in the sub-form is not created until you enter data for it.

I understand what you say about the ID field seemingly taking it's ID from the parent record but that's just a bit of 'Access magic' where it is completing something for you which does not exist yet.

Even if you had an autonumber field in your sub-form you would not see it advance until you start to enter data into a field.

If you REALLY want to create a blank record in the subform (and I am not happy that I would want to do this; I guess there is something your not telling us!) then you will have to do it with code after some action in the main form. You may then need to run something like;-
Expand|Select|Wrap|Line Numbers
  1.  
  2. Application.SetOption "Confirm Action Queries", False
  3.  
  4. DoCmd.RunSQL "INSERT INTO tblConstBldg ( BldgID ) values(" & Me!BldgID & ");"
  5.  
  6. Application.SetOption "Confirm Action Queries", False
  7.  
I'm sure you have other fields in your application but this is all you gave us to go on. For instance, if you had a date field called [cDate] in the sub-form, then you could use the On_Click or On_Enter event to enter today's date if it was blank e.g.
Expand|Select|Wrap|Line Numbers
  1. If IsNull(Me!cDate) Then Me!cDate = Date
This would do the trick but require a definite action on behalf of the user.

Hope this has helped a bit
S7
Feb 17 '08 #3

NeoPa
Expert Mod 15k+
P: 31,276
what your describing sounds more like a validation problem , then code, because a record should add when you submitt it unless you have a valdation rule, make sure you don't have a [table name].undo with an incomplete form.
Another one mis-posted.
Feb 17 '08 #4

P: 59
Thank you S7, Trevor and NeoPa (for moving Trevor's posts) for your responses!
I will try some of the code from S& and see if I can make it work. Otherwise, as you pointed out, I may need to think of another strategy. Thanks again.
Bridget

Hi Brigette

I think the problem is (as you have found yourself) that a record in the sub-form is not created until you enter data for it.

I understand what you say about the ID field seemingly taking it's ID from the parent record but that's just a bit of 'Access magic' where it is completing something for you which does not exist yet.

Even if you had an autonumber field in your sub-form you would not see it advance until you start to enter data into a field.

If you REALLY want to create a blank record in the subform (and I am not happy that I would want to do this; I guess there is something your not telling us!) then you will have to do it with code after some action in the main form. You may then need to run something like;-
Expand|Select|Wrap|Line Numbers
  1.  
  2. Application.SetOption "Confirm Action Queries", False
  3.  
  4. DoCmd.RunSQL "INSERT INTO tblConstBldg ( BldgID ) values(" & Me!BldgID & ");"
  5.  
  6. Application.SetOption "Confirm Action Queries", False
  7.  
I'm sure you have other fields in your application but this is all you gave us to go on. For instance, if you had a date field called [cDate] in the sub-form, then you could use the On_Click or On_Enter event to enter today's date if it was blank e.g.
Expand|Select|Wrap|Line Numbers
  1. If IsNull(Me!cDate) Then Me!cDate = Date
This would do the trick but require a definite action on behalf of the user.

Hope this has helped a bit
S7
Feb 19 '08 #5

Post your reply

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