Use VBA to insert a new record in a subform?

How do I use VBA to insert a *new* record into a subform?

I have a master form with client information, and a sub form with billing
information. I want to click on a button ("Add Data") and have a record
inserted into the subform's table, with date created and some information
from the main table inserted into the proper fields in the subform.

After the record is inserted, the data entry operator enters a Date Of
Service and a billing code.

I've created code to copy the the information I need from the main table and
place it in the proper fields, but it always goes to the first record in the
subform and overwrites the existing data. I have to manually enter the
subform and move to a new record, then click on the "Add Data" button, it
then inserts the data on the new record line.

Thanks,

Scott

re: Use VBA to insert a new record in a subform?

You could:
- set focus to the subform control;
- set focus to a control within the subform;
- move to a new record;
- write the values.
With Me.[NameOfYourSubformControlHere]
.Form.SetFocus
.Form![SomeControl].SetFocus
RunCommand acCmdRecordsGotoNew
.Form!SomeControl = SomeValue
.Form!AnotherControl = AnotherValue
'etc.
End With

Alternatively, you could add the record to the RecordsetClone of the
subform:
With Me.[NameOfYourSubformControlHere].Form.RecordsetClone
.AddNew
!SomeControl = SomeValue
!AnotherContorl = AnotherValue
'etc.
.Update
End With

Use the first approach if you want the user to be able to complete or back
out of saving the record, and the 2nd approach if you want to record saved
regardless of what the user does.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"YFS DBA" <NyOfSsPdAbMa@prexar.com> wrote in message
news:vpahmba14tq7c4@corp.supernews.com...[color=blue]
> How do I use VBA to insert a *new* record into a subform?
>
> I have a master form with client information, and a sub form with billing
> information. I want to click on a button ("Add Data") and have a record
> inserted into the subform's table, with date created and some information
> from the main table inserted into the proper fields in the subform.
>
> After the record is inserted, the data entry operator enters a Date Of
> Service and a billing code.
>
> I've created code to copy the the information I need from the main table[/color]
and[color=blue]
> place it in the proper fields, but it always goes to the first record in[/color]
the[color=blue]
> subform and overwrites the existing data. I have to manually enter the
> subform and move to a new record, then click on the "Add Data" button, it
> then inserts the data on the new record line.
>
> Thanks,
>
> Scott[/color]

re: Use VBA to insert a new record in a subform?

Hi Allen, thanks for the help!

It works, except when I don't have any existing records, then the
"RunCommand acCmdRecordsGoToNew" command is unavailable.

I think I should be able to use a DCount function to see if any records
exist in the subform, if not, then the sub wouldn't use the RunCommand
acCmdRecordsGoToNew command.

Any ideas on how to count the records in the subform? Or is there an easier
(or smarter!) way?

Thanks again,

Scott

"Allen Browne" <allenbrowne@SeeSig.invalid> wrote in message
news:3f9548f6$0$23609$5a62ac22@freenews.iinet.net. au...[color=blue]
> You could:
> - set focus to the subform control;
> - set focus to a control within the subform;
> - move to a new record;
> - write the values.
> With Me.[NameOfYourSubformControlHere]
> .Form.SetFocus
> .Form![SomeControl].SetFocus
> RunCommand acCmdRecordsGotoNew
> .Form!SomeControl = SomeValue
> .Form!AnotherControl = AnotherValue
> 'etc.
> End With
>
> Alternatively, you could add the record to the RecordsetClone of the
> subform:
> With Me.[NameOfYourSubformControlHere].Form.RecordsetClone
> .AddNew
> !SomeControl = SomeValue
> !AnotherContorl = AnotherValue
> 'etc.
> .Update
> End With
>
> Use the first approach if you want the user to be able to complete or back
> out of saving the record, and the 2nd approach if you want to record saved
> regardless of what the user does.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.[/color]

re: Use VBA to insert a new record in a subform?

Disregard message about counting records and disabling Add New Record, I
figured out how to use the Count function.! Life is good!

: )

Scott


"Allen Browne" <allenbrowne@SeeSig.invalid> wrote in message
news:3f9548f6$0$23609$5a62ac22@freenews.iinet.net. au...[color=blue]
> You could:
> - set focus to the subform control;
> - set focus to a control within the subform;
> - move to a new record;
> - write the values.
> With Me.[NameOfYourSubformControlHere]
> .Form.SetFocus
> .Form![SomeControl].SetFocus
> RunCommand acCmdRecordsGotoNew
> .Form!SomeControl = SomeValue
> .Form!AnotherControl = AnotherValue
> 'etc.
> End With
>
> Alternatively, you could add the record to the RecordsetClone of the
> subform:
> With Me.[NameOfYourSubformControlHere].Form.RecordsetClone
> .AddNew
> !SomeControl = SomeValue
> !AnotherContorl = AnotherValue
> 'etc.
> .Update
> End With
>
> Use the first approach if you want the user to be able to complete or back
> out of saving the record, and the 2nd approach if you want to record saved
> regardless of what the user does.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "YFS DBA" <NyOfSsPdAbMa@prexar.com> wrote in message
> news:vpahmba14tq7c4@corp.supernews.com...[color=green]
> > How do I use VBA to insert a *new* record into a subform?
> >
> > I have a master form with client information, and a sub form with[/color][/color]
billing[color=blue][color=green]
> > information. I want to click on a button ("Add Data") and have a record
> > inserted into the subform's table, with date created and some[/color][/color]
information[color=blue][color=green]
> > from the main table inserted into the proper fields in the subform.
> >
> > After the record is inserted, the data entry operator enters a Date Of
> > Service and a billing code.
> >
> > I've created code to copy the the information I need from the main table[/color]
> and[color=green]
> > place it in the proper fields, but it always goes to the first record in[/color]
> the[color=green]
> > subform and overwrites the existing data. I have to manually enter the
> > subform and move to a new record, then click on the "Add Data" button,[/color][/color]
it[color=blue][color=green]
> > then inserts the data on the new record line.
> >
> > Thanks,
> >
> > Scott[/color]
>
>[/color]

re: Use VBA to insert a new record in a subform?

Hi. I was reading this thread, hoping to be able to replicate what you explain to a database of my own.
I became rather confused by what you mean by a subform control and a control within a subform...

My Form is named Movimentacao, my subform HistoriaSubform, I have a control button, 7 fields in my subform, and 6 text boxes from which I want to update my data.
Which is which...

Thabnk You