Adding data to a subform programmatically

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

My form is set up based on a query with a subform. I have an unbound text
box which has the following code:

=====================================================
Private Sub CallEvent_AfterUpdate()

Dim fldIn As String
Dim fldOutA As String
Dim fldOutB As String
Dim fldOutC As String

fldIn = Forms![frm_CFS]![CallEvent]

If IsNull(fldIn) Then Exit Sub

fldOutA = UCase(ParseText(fldIn, 0, ";")) ' ID Number
fldOutB = UCase(ParseText(fldIn, 1, ";")) ' Time
fldOutC = UCase(ParseText(fldIn, 2, ";")) ' Details

If fldOutA = "" Then fldOutA = ParseText(Forms![frm_CFS]![OfcrPri], 0, " --
")
If fldOutB = "" Then fldOutB = Format(Time(), "Short Time")
If fldOutC = "" Then Exit Sub

' will not work - start section
Forms![frm_CFS]![CE_CCNo] = Forms![frm_CFS]![CCNo]
Forms![frm_CFS]![CE_Date] = Format(Date, "yyyy-mm-dd")
Forms![frm_CFS]![CE_Time] = fldOutB
Forms![frm_CFS]![CE_Unit] = fldOutA
Forms![frm_CFS]![CE_User] = Left(CurrentUser(), 3)
Forms![frm_CFS]![CE_Event] = fldOutC
' end section

Forms![frm_CFS]![CallEvent] = ""

End Sub
=====================================================


The section identified above as "will not work" is trying to take the values
from the text box and add them to the recordset in the subform. It will not
write the values stating that it cannot find the field in the subform.

I also tried the Child! command and it did not work either.

Any suggestions would be appreciated.
 
That change in my code worked perfectly, thank you. I do have one follow-up
question.

When my code runs it overwrites the first record in the subform instead of
adding a new record.




Alex Dybenko said:
Hi,
if your subform CONTROL name is XXX then you can refer to control on a form
as:

me!XXX.Form!Mycontrol

or from other form:

Forms![frm_CFS]![XXX].Form![CE_CCNo]

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

"Scott Whetsell, A.S. - WVSP"
My form is set up based on a query with a subform. I have an unbound text
box which has the following code:

=====================================================
Private Sub CallEvent_AfterUpdate()

Dim fldIn As String
Dim fldOutA As String
Dim fldOutB As String
Dim fldOutC As String

fldIn = Forms![frm_CFS]![CallEvent]

If IsNull(fldIn) Then Exit Sub

fldOutA = UCase(ParseText(fldIn, 0, ";")) ' ID Number
fldOutB = UCase(ParseText(fldIn, 1, ";")) ' Time
fldOutC = UCase(ParseText(fldIn, 2, ";")) ' Details

If fldOutA = "" Then fldOutA = ParseText(Forms![frm_CFS]![OfcrPri], 0,
--
")
If fldOutB = "" Then fldOutB = Format(Time(), "Short Time")
If fldOutC = "" Then Exit Sub

' will not work - start section
Forms![frm_CFS]![CE_CCNo] = Forms![frm_CFS]![CCNo]
Forms![frm_CFS]![CE_Date] = Format(Date, "yyyy-mm-dd")
Forms![frm_CFS]![CE_Time] = fldOutB
Forms![frm_CFS]![CE_Unit] = fldOutA
Forms![frm_CFS]![CE_User] = Left(CurrentUser(), 3)
Forms![frm_CFS]![CE_Event] = fldOutC
' end section

Forms![frm_CFS]![CallEvent] = ""

End Sub
=====================================================


The section identified above as "will not work" is trying to take the
values
from the text box and add them to the recordset in the subform. It will
not
write the values stating that it cannot find the field in the subform.

I also tried the Child! command and it did not work either.

Any suggestions would be appreciated.
Click to expand...
Click to expand...
 
then you need to go to a new record first using docmd.gotorecord
or better - use append query to insert new record and then requery subform

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

"Scott Whetsell, A.S. - WVSP"
That change in my code worked perfectly, thank you. I do have one
follow-up
question.

When my code runs it overwrites the first record in the subform instead of
adding a new record.




Alex Dybenko said:
Hi,
if your subform CONTROL name is XXX then you can refer to control on a
form
as:

me!XXX.Form!Mycontrol

or from other form:

Forms![frm_CFS]![XXX].Form![CE_CCNo]

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

"Scott Whetsell, A.S. - WVSP"
My form is set up based on a query with a subform. I have an unbound
text
box which has the following code:

=====================================================
Private Sub CallEvent_AfterUpdate()

Dim fldIn As String
Dim fldOutA As String
Dim fldOutB As String
Dim fldOutC As String

fldIn = Forms![frm_CFS]![CallEvent]

If IsNull(fldIn) Then Exit Sub

fldOutA = UCase(ParseText(fldIn, 0, ";")) ' ID Number
fldOutB = UCase(ParseText(fldIn, 1, ";")) ' Time
fldOutC = UCase(ParseText(fldIn, 2, ";")) ' Details

If fldOutA = "" Then fldOutA = ParseText(Forms![frm_CFS]![OfcrPri], 0,
--
")
If fldOutB = "" Then fldOutB = Format(Time(), "Short Time")
If fldOutC = "" Then Exit Sub

' will not work - start section
Forms![frm_CFS]![CE_CCNo] = Forms![frm_CFS]![CCNo]
Forms![frm_CFS]![CE_Date] = Format(Date, "yyyy-mm-dd")
Forms![frm_CFS]![CE_Time] = fldOutB
Forms![frm_CFS]![CE_Unit] = fldOutA
Forms![frm_CFS]![CE_User] = Left(CurrentUser(), 3)
Forms![frm_CFS]![CE_Event] = fldOutC
' end section

Forms![frm_CFS]![CallEvent] = ""

End Sub
=====================================================


The section identified above as "will not work" is trying to take the
values
from the text box and add them to the recordset in the subform. It
will
not
write the values stating that it cannot find the field in the subform.

I also tried the Child! command and it did not work either.

Any suggestions would be appreciated.
Click to expand...
Click to expand...
Click to expand...
 
I have modified my code as below, but I am receiving the error that the
object is not open on the DoCmd line. What I am doing wrong?

I did consider your suggestion of using an append query, but they may need
to change the data after its been entered into the subform. I am not sure if
an append query is appropriate for that.

Thanks.

===========================================================

With Forms![frm_CFS]![CallEventssub]
Me.CallEventssub.SetFocus
DoCmd.GoToRecord acActiveDataObject, "Me!CallEventssub.Form", acNewRec
Forms![frm_CFS]![CallEventssub].Form![CE_CCNo] = Forms![frm_CFS]![CCNo]
Forms![frm_CFS]![CallEventssub].Form![CE_Date] = Format(Date, "yyyy-mm-dd")
Forms![frm_CFS]![CallEventssub].Form![CE_Time] = fldOutB
Forms![frm_CFS]![CallEventssub].Form![CE_Unit] = fldOutA
Forms![frm_CFS]![CallEventssub].Form![CE_User] = Left(CurrentUser(), 3)
Forms![frm_CFS]![CallEventssub].Form![CE_Event] = fldOutC
End With

===========================================================



Alex Dybenko said:
then you need to go to a new record first using docmd.gotorecord
or better - use append query to insert new record and then requery subform

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

"Scott Whetsell, A.S. - WVSP"
That change in my code worked perfectly, thank you. I do have one
follow-up
question.

When my code runs it overwrites the first record in the subform instead of
adding a new record.




Alex Dybenko said:
Hi,
if your subform CONTROL name is XXX then you can refer to control on a
form
as:

me!XXX.Form!Mycontrol

or from other form:

Forms![frm_CFS]![XXX].Form![CE_CCNo]

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

"Scott Whetsell, A.S. - WVSP"
My form is set up based on a query with a subform. I have an unbound
text
box which has the following code:

=====================================================
Private Sub CallEvent_AfterUpdate()

Dim fldIn As String
Dim fldOutA As String
Dim fldOutB As String
Dim fldOutC As String

fldIn = Forms![frm_CFS]![CallEvent]

If IsNull(fldIn) Then Exit Sub

fldOutA = UCase(ParseText(fldIn, 0, ";")) ' ID Number
fldOutB = UCase(ParseText(fldIn, 1, ";")) ' Time
fldOutC = UCase(ParseText(fldIn, 2, ";")) ' Details

If fldOutA = "" Then fldOutA = ParseText(Forms![frm_CFS]![OfcrPri], 0,
--
")
If fldOutB = "" Then fldOutB = Format(Time(), "Short Time")
If fldOutC = "" Then Exit Sub

' will not work - start section
Forms![frm_CFS]![CE_CCNo] = Forms![frm_CFS]![CCNo]
Forms![frm_CFS]![CE_Date] = Format(Date, "yyyy-mm-dd")
Forms![frm_CFS]![CE_Time] = fldOutB
Forms![frm_CFS]![CE_Unit] = fldOutA
Forms![frm_CFS]![CE_User] = Left(CurrentUser(), 3)
Forms![frm_CFS]![CE_Event] = fldOutC
' end section

Forms![frm_CFS]![CallEvent] = ""

End Sub
=====================================================


The section identified above as "will not work" is trying to take the
values
from the text box and add them to the recordset in the subform. It
will
not
write the values stating that it cannot find the field in the subform.

I also tried the Child! command and it did not work either.

Any suggestions would be appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Try:
DoCmd.GoToRecord , , acNewRec

instead of

DoCmd.GoToRecord acActiveDataObject, "Me!CallEventssub.Form", acNewRec

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com


"Scott Whetsell, A.S. - WVSP"
I have modified my code as below, but I am receiving the error that the
object is not open on the DoCmd line. What I am doing wrong?

I did consider your suggestion of using an append query, but they may need
to change the data after its been entered into the subform. I am not sure
if
an append query is appropriate for that.

Thanks.

===========================================================

With Forms![frm_CFS]![CallEventssub]
Me.CallEventssub.SetFocus
DoCmd.GoToRecord acActiveDataObject, "Me!CallEventssub.Form", acNewRec
Forms![frm_CFS]![CallEventssub].Form![CE_CCNo] = Forms![frm_CFS]![CCNo]
Forms![frm_CFS]![CallEventssub].Form![CE_Date] = Format(Date,
"yyyy-mm-dd")
Forms![frm_CFS]![CallEventssub].Form![CE_Time] = fldOutB
Forms![frm_CFS]![CallEventssub].Form![CE_Unit] = fldOutA
Forms![frm_CFS]![CallEventssub].Form![CE_User] = Left(CurrentUser(), 3)
Forms![frm_CFS]![CallEventssub].Form![CE_Event] = fldOutC
End With

===========================================================



Alex Dybenko said:
then you need to go to a new record first using docmd.gotorecord
or better - use append query to insert new record and then requery
subform

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

"Scott Whetsell, A.S. - WVSP"
That change in my code worked perfectly, thank you. I do have one
follow-up
question.

When my code runs it overwrites the first record in the subform instead
of
adding a new record.




:

Hi,
if your subform CONTROL name is XXX then you can refer to control on a
form
as:

me!XXX.Form!Mycontrol

or from other form:

Forms![frm_CFS]![XXX].Form![CE_CCNo]

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

"Scott Whetsell, A.S. - WVSP"
My form is set up based on a query with a subform. I have an
unbound
text
box which has the following code:

=====================================================
Private Sub CallEvent_AfterUpdate()

Dim fldIn As String
Dim fldOutA As String
Dim fldOutB As String
Dim fldOutC As String

fldIn = Forms![frm_CFS]![CallEvent]

If IsNull(fldIn) Then Exit Sub

fldOutA = UCase(ParseText(fldIn, 0, ";")) ' ID Number
fldOutB = UCase(ParseText(fldIn, 1, ";")) ' Time
fldOutC = UCase(ParseText(fldIn, 2, ";")) ' Details

If fldOutA = "" Then fldOutA = ParseText(Forms![frm_CFS]![OfcrPri],
0,
--
")
If fldOutB = "" Then fldOutB = Format(Time(), "Short Time")
If fldOutC = "" Then Exit Sub

' will not work - start section
Forms![frm_CFS]![CE_CCNo] = Forms![frm_CFS]![CCNo]
Forms![frm_CFS]![CE_Date] = Format(Date, "yyyy-mm-dd")
Forms![frm_CFS]![CE_Time] = fldOutB
Forms![frm_CFS]![CE_Unit] = fldOutA
Forms![frm_CFS]![CE_User] = Left(CurrentUser(), 3)
Forms![frm_CFS]![CE_Event] = fldOutC
' end section

Forms![frm_CFS]![CallEvent] = ""

End Sub
=====================================================


The section identified above as "will not work" is trying to take
the
values
from the text box and add them to the recordset in the subform. It
will
not
write the values stating that it cannot find the field in the
subform.

I also tried the Child! command and it did not work either.

Any suggestions would be appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Thank you for your help. I modifed that code as below and it works perfectly.

===============================================================

With Forms![frm_CFS]![CallEventssub]
Me.CallEventssub.SetFocus
DoCmd.GoToRecord , , acNewRec
Forms![frm_CFS]![CallEventssub].Form![CE_CCNo] = Forms![frm_CFS]![CCNo]
Forms![frm_CFS]![CallEventssub].Form![CE_Date] = Format(Date, "yyyy-mm-dd")
Forms![frm_CFS]![CallEventssub].Form![CE_Time] = fldOutB
Forms![frm_CFS]![CallEventssub].Form![CE_Unit] = fldOutA
Forms![frm_CFS]![CallEventssub].Form![CE_User] = Left(CurrentUser(), 3)
Forms![frm_CFS]![CallEventssub].Form![CE_Event] = fldOutC
End With

Me.CallEvent.SetFocus
Forms![frm_CFS]![CallEvent].Value = ""
Me.CallEventssub.Requery

===============================================================

Alex Dybenko said:
Try:
DoCmd.GoToRecord , , acNewRec

instead of

DoCmd.GoToRecord acActiveDataObject, "Me!CallEventssub.Form", acNewRec

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com


"Scott Whetsell, A.S. - WVSP"
I have modified my code as below, but I am receiving the error that the
object is not open on the DoCmd line. What I am doing wrong?

I did consider your suggestion of using an append query, but they may need
to change the data after its been entered into the subform. I am not sure
if
an append query is appropriate for that.

Thanks.

===========================================================

With Forms![frm_CFS]![CallEventssub]
Me.CallEventssub.SetFocus
DoCmd.GoToRecord acActiveDataObject, "Me!CallEventssub.Form", acNewRec
Forms![frm_CFS]![CallEventssub].Form![CE_CCNo] = Forms![frm_CFS]![CCNo]
Forms![frm_CFS]![CallEventssub].Form![CE_Date] = Format(Date,
"yyyy-mm-dd")
Forms![frm_CFS]![CallEventssub].Form![CE_Time] = fldOutB
Forms![frm_CFS]![CallEventssub].Form![CE_Unit] = fldOutA
Forms![frm_CFS]![CallEventssub].Form![CE_User] = Left(CurrentUser(), 3)
Forms![frm_CFS]![CallEventssub].Form![CE_Event] = fldOutC
End With

===========================================================



Alex Dybenko said:
then you need to go to a new record first using docmd.gotorecord
or better - use append query to insert new record and then requery
subform

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

"Scott Whetsell, A.S. - WVSP"
That change in my code worked perfectly, thank you. I do have one
follow-up
question.

When my code runs it overwrites the first record in the subform instead
of
adding a new record.




:

Hi,
if your subform CONTROL name is XXX then you can refer to control on a
form
as:

me!XXX.Form!Mycontrol

or from other form:

Forms![frm_CFS]![XXX].Form![CE_CCNo]

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

"Scott Whetsell, A.S. - WVSP"
My form is set up based on a query with a subform. I have an
unbound
text
box which has the following code:

=====================================================
Private Sub CallEvent_AfterUpdate()

Dim fldIn As String
Dim fldOutA As String
Dim fldOutB As String
Dim fldOutC As String

fldIn = Forms![frm_CFS]![CallEvent]

If IsNull(fldIn) Then Exit Sub

fldOutA = UCase(ParseText(fldIn, 0, ";")) ' ID Number
fldOutB = UCase(ParseText(fldIn, 1, ";")) ' Time
fldOutC = UCase(ParseText(fldIn, 2, ";")) ' Details

If fldOutA = "" Then fldOutA = ParseText(Forms![frm_CFS]![OfcrPri],
0,
--
")
If fldOutB = "" Then fldOutB = Format(Time(), "Short Time")
If fldOutC = "" Then Exit Sub

' will not work - start section
Forms![frm_CFS]![CE_CCNo] = Forms![frm_CFS]![CCNo]
Forms![frm_CFS]![CE_Date] = Format(Date, "yyyy-mm-dd")
Forms![frm_CFS]![CE_Time] = fldOutB
Forms![frm_CFS]![CE_Unit] = fldOutA
Forms![frm_CFS]![CE_User] = Left(CurrentUser(), 3)
Forms![frm_CFS]![CE_Event] = fldOutC
' end section

Forms![frm_CFS]![CallEvent] = ""

End Sub
=====================================================


The section identified above as "will not work" is trying to take
the
values
from the text box and add them to the recordset in the subform. It
will
not
write the values stating that it cannot find the field in the
subform.

I also tried the Child! command and it did not work either.

Any suggestions would be appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top