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

Use VBA to insert a new record in a subform?

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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" <Ny**********@prexar.com> wrote in message
news:vp************@corp.supernews.com...
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

Nov 12 '05 #2

P: n/a
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" <al*********@SeeSig.invalid> wrote in message
news:3f***********************@freenews.iinet.net. au...
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.

Nov 12 '05 #3

P: n/a
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" <al*********@SeeSig.invalid> wrote in message
news:3f***********************@freenews.iinet.net. au...
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" <Ny**********@prexar.com> wrote in message
news:vp************@corp.supernews.com...
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


Nov 12 '05 #4

P: 1
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
Jun 21 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.