new subform record

NigelShaw · Mar 13, 2008

Hi,

im stuck. how can i create a new record on a subform? originally, this was a pop up form and everything worked ok as the new record was set on the pop up form but i have now made it into a subform. my existing code is -

private sub addtherecord()
If Labour.Visible = False Then
Forms![mainform]![MainFormSubForm]![Pay Reg].Value = True
Forms![mainform]![MainFormSubForm]![subform subform].SetFocus
DoCmd.GoToRecord , , acNewRec
end sub

pay reg is a tick box from the table so when it is made true, a new record is set on the subform. there is a sub subform that is updated so i can make many payments to a person based on the above record being created. i need the sub subform to create a new record leaving the subform record unchanged. i can create the subform new record but the current code above creates a new record on the subform not the sub subform.

sorry for all of the subs.....

regs,

NS

ajetrumpet · Mar 14, 2008

Nigel,

Study this first:

http://www.access-programmers.co.uk/forums/showthread.php?t=127981

NigelShaw · Mar 14, 2008

Hi Adam,

i have seen this. i dont see that i am doing anything wrong. i am setting my focus to the sub subform-
forms![mainform]![subform]![subsubform].setfocus
this should take me to the form in focus
then i added
docmd.gotorecord,,acnewrec
which should create a new record on the focused form but it doesnt. it creates a new record on the subform not the sub subform.

regs,

NS

ajetrumpet · Mar 14, 2008

Nigel,

First of all, there is no .Form! section of your syntax, and everything on the syntax page above has that section in it. Also, I assume you have double nested forms here? As in, sub1's "subform" is actually nested INSIDE sub1, and not sitting right next to it, nested only inside of the main? It makes a difference...

Here is the correct syntax (I hope!) for getting to sub2, if it's nested inside of sub1, from a location on the main form:

Code:

Forms!MainForm!Sub1Control.Form!Sub2Control.Form!ControlNameOrAction

There is a pattern here too, so, just for kicks, I bet that you could get to a sub that is nested 5 deep from the main by writing this:

Code:

Forms!MainForm!Sub1Control.Form!Sub2Control.Form!
   Sub3Control.Form!Sub4Control.Form!
      Sub5Control.Form!ControlNameOrAction

Hmm...I wonder...

NigelShaw · Mar 14, 2008

Hi Adam,

here are the details

main = [contractors]
subform = [MainFormSubForm]
subsubform = [Payments-Subform]

both subforms are nested. subsubform being nested into subform and subform nested into main.

i am trying to add a new record in subsubform when exiting a textbox object on the subform. ( see attached )

here is my current code-

Private Sub MaterialTxt_Exit(Cancel As Integer)

Forms![contractors]![MainFormSubForm]![Payment Registered].Value = True
' ticks box to create payment number on subformForms![contractors]![MainFormSubForm]![Payments_subform].SetFocus
' sets focus to the sub subform

DoCmd.GoToRecord , , acNewRec
' trying to add new record on the sub subform

Forms![contractors]![MainFormSubForm]![Payments_subform]![InvoiceNo].Value = InvNoTxt.Value
'adding value to new record
Forms![contractors]![MainFormSubForm]![Payments_subform]![InvoiceDescription].Value = DescriptionTxt.Value
'adding value to new record
Forms![contractors]![MainFormSubForm]![Payments_subform]![Material].Value = MaterialTxt.Value
'adding value to new record
Forms![contractors]![MainFormSubForm].SetFocus
'setting focus to subform.

Me!InvNoTxt.Value = "" ' resetting input box
Me!DescriptionTxt.Value = "" ' resetting input box
Me!MaterialTxt.Value = ""' 'resetting input box
Me!InvNoTxt.SetFocus ' setting focus to input box
Forms![contractors]![PaymentFiguresQry].Requery ' refreshing a payment summary box.

Exit Sub

this worked fine before i made it into a subform. when the form is opened as a popup, it works perfectly so it must be an issue getting to the end nested form. i tried your way as described above but i got errors saying cannot find field.
the whole emphasis is to get many items in the sub subform related to 1 payment number on the subform. i am not trying to access the form from the main parent form.

regs,

NS

NigelShaw · Mar 14, 2008

forgot to attach this. sorry for the poor child like writing....... did it with a mouse and an unsteady hand!!

NigelShaw · Mar 14, 2008

this is what i am trying to achieve. the records are created in the text boxes above and are entered into the datasheet with the textbox "material" onexit.
each line is a new record.

ajetrumpet · Mar 14, 2008

One more question:

At the time you run this code, you are exiting a box on the subform #1?

nevermind

<edit>

And you just want to insert a new record in sub2, correct? Just a blank one right? Not a record populated with the values from all of the sub1 text box values?

NigelShaw · Mar 15, 2008

Hi,

sorry for the delay back....

yes, the textbox i am exiting is on subform 1.

the way it works is i exit the field on subform 1
a new record is created on subform 2 and the values from the textboxes on subform 1 are entered into the new record.
the textboxes on subform 1 get the focus for a new entry and it carries on in rotation until i press close. subform 1 is linked to a payment number query so only 1 record is produced. subform 2 is linked to a payment query linked via the paymentnumberID so many payments entered are linked to the payment number from subform 1.

like i mentioned before, this worked very well when i had the form as a popup. it is only since i have changed it to a subform that i cant link it together.

so to summarize the answer to your last question,

i want to insert a new record in sub2 then populate the record with the values from the textboxes in sub1.

hope this is clear enough to understand.

i can post an example db but cannot do it until later as i am currently on a Mac.

many thanks,

Nigel

ajetrumpet · Mar 18, 2008

NigelShaw said:
subform 2 is linked to a payment query linked via the paymentnumberID so many payments entered are linked to the payment number from subform 1.

i want to insert a new record in sub2 then populate the record with the values from the textboxes in sub1.

Nigel,

One thing first: If the query is a non-updateable one, then this will never be able to work. Are you sure it doesn't fall into that category? What's the SQL of it? Have you seen Allen Browne's laundry list of non-updateable query specifications? It is here:

http://www.allenbrowne.com/ser-61.html

Non-updateable queries cannot be edited when they are OPEN. To edit one of these things, you have to add the data to the source, and then requery it...

NigelShaw · Mar 18, 2008

Hi Adam,

the query is updateable as it works fine when the form is used like a pop up. i will extract the forms and sample tables and post them for viewing.

thanks,

Nigel

NigelShaw · Mar 18, 2008

Hi,

i have looked through my Db. when the data gets entered into the sub subform, the record is NOT being entered into the table. this subform writes direct to the table without queries. when it is in a pop up form, it wrires to it so i think the problem is getting to the sub sub form itself.

in popup, my code sets focus on the subform and creates a new record which is correct.
in subform mode, my code sets focus on the sub subform and creates a new record but the new record is created on the subform.

how do i set focus to the subforms subform and create a new record there?

regs,

Nigel

NigelShaw · Mar 18, 2008

Hi,

to further update, i can manually enter my data in the datasheet on the sub subform. the problem seems only creating the new record on the sub subform.

to date, i can-
enter text into text boxes on the mainforms subform

in the "onexit" of the text boxes, i can get the info from the text boxes on the mainform subform to the mainforms subforms subform.

i can reset the focus to the text box on the mainforms subform.

i cannot create a new record on the mainforms subforms subform. the record is getting over written and it is not entered into the table as i have checked. when i enter data manually, it is entered to the table as the new record is created.

please advise me on how i can create the new record?

i placed a set focus to the sub sub form and a docmd for a new record but the new record is made on the subform not the sub subform.

regs,

NS

ajetrumpet · Mar 19, 2008

NigelShaw said:
please advise me on how i can create the new record?

i placed a set focus to the sub sub form and a docmd for a new record but the new record is made on the subform not the sub subform.

What's the syntax you are using when you get the new record on the first subform??

If I can get that from you, I might be able to give you an answer here (faster than you think)...

ajetrumpet · Mar 19, 2008

OK Nigel,

I believe there is a problem with this. Go into your SUBSUBFORM and tell me if you can add a new record just by giving the form a focus with a mouse click. Does the nav. button for the new record work when you just click on it (without pushing any "data entry" key first)??

NigelShaw · Mar 19, 2008

Hi Adam,

thanks for your help. can i email the Db across instead as everything is linked and it would take hours to re-create or strip down.

the syntax i m using to try and get the new record is
DoCmd.GotoRecord, , AcNewRec
so...
my subform on exit code it

If LabourTxt.Visible = False Then 'ignore textbox if not visible
Forms![contractors]![MainFormSubForm]![Payment Registered].Value = True
Forms![contractors]![MainFormSubForm]![Payments_subform].SetFocus
DoCmd.GoToRecord, , acNewRec

Forms![contractors]![MainFormSubForm]![Payments_subform]![InvoiceNo].Value = InvNoTxt.Value
Forms![contractors]![MainFormSubForm]![Payments_subform]![InvoiceDescription].Value = DescriptionTxt.Value
Forms![contractors]![MainFormSubForm]![Payments_subform]![Material].Value = MaterialTxt.Value
Forms![contractors]![MainFormSubForm]![Payments_subform]![PaymentNoID].Value = Forms![contractors]![MainFormSubForm]![PaymentNoID].Value
Forms![contractors]![MainFormSubForm]![Payments_subform]![Labour].Value = ""
Forms![contractors]![MainFormSubForm]![Payments_subform]![paymententered].Value = True
DoCmd.Save

Forms![contractors]![MainFormSubForm].SetFocus

Me!InvNoTxt.Value = ""
Me!DescriptionTxt.Value = ""
Me!MaterialTxt.Value = ""
Me!InvNoTxt.SetFocus
Forms![contractors]![PaymentFiguresQry].Requery
Else
Forms![contractors]![PaymentFiguresQry].Requery
LabourTxt.SetFocus
End If

Exit Sub

If LabourTxt.Visible = True Then
LabourTxt.SetFocus
End If
Exit Sub

i have also tried
DoCmd.GoToRecord acDataForm, stdocname, acNewRec
i set stdocname to the mainform subform subform.
i get the error message saying the form is not open!!

its VV frustrating. im thinking it is staring me in the face.

many thanks,

Nigel

NigelShaw · Mar 19, 2008

ajetrumpet said:
OK Nigel,

I believe there is a problem with this. Go into your SUBSUBFORM and tell me if you can add a new record just by giving the form a focus with a mouse click. Does the nav. button for the new record work when you just click on it (without pushing any "data entry" key first)??

Hi,

just seen this message.
i can enter the data manually by clicking in the subsubform. the data is entered into the table fine and all is linked together with correct info. when i use the code, data is entered into the subsubform but not written to the table. when i check the table, it is blank. it seems to be in view but over written every time the code runs.

NS

ajetrumpet · Mar 19, 2008

NigelShaw said:
its VV frustrating. im thinking it is staring me in the face.

Nigel

Nigel,

Sorry you had to write all of that. Refer to my post right before that long one that you have written. I may have found something strange here. I have recreated your problem with one of my databases, and I've found that I can only add a record on a SUBSUB form if I press a key when one of the controls on that form has the focus. On press of any key, the PK of that form automatically populates, but if there is no other data typed in (or populated), the record is not saved on close.

Does that ring a bell???

I have all of the syntax that is correct to go through this process, but the phenomenon that I have experienced above is preventing me from executing it. That may be your problem as well....

(does your SUBSUB have a PK??)

ajetrumpet · Mar 19, 2008

NigelShaw said:
when i use the code, data is entered into the subsubform but not written to the table. when i check the table, it is blank.

See the above post for this problem...

NigelShaw · Mar 19, 2008

Hi Adam,

i dont have a button to press on the subsubform. when it was a pop up, the data was entered in the exact same way but only went as far as one subform. the code worked and new records were written. i did however, have a close button.
i changed this format to an unbound subform. when certain buttons are clicked, the sourceobject is changed to different forms. my mainform became a subform and my subform became a sub subform. this is where my problem now lays. if i revert to a pop-up, it works again. could it be something to do with a subform having a subform?

NS

new subform record

Registered User.

Banned

Registered User.

Banned

Registered User.

Registered User.

Attachments

Registered User.

Attachments

Banned

Registered User.

Banned

Registered User.

Registered User.

Registered User.

Banned

Banned

Registered User.

Registered User.

Banned

Banned

Registered User.

Similar threads

Users who are viewing this thread