make a record dirty

[deb]

Main form is fProj - data for project
Subform is fDeliv - data for deliverable
SubSub form is fTL - data for transmittals

I sometimes have data that must be input into the subsubform -( ie
transmittal dates due) before I have data for the Subform.
Days later the transmittal data is ready to send to the customer and then
the field called TLno gets input into the subform

The issue is that I need the autonumber in the subform to populate since it
is a key for the subsubform.
Therefore I do not get an autonumber id for the subform and my subsub form
data is lost.

how can I force a record in the subform when data is input into the
subsubform?
Is there a way to make the subform dirty and then save?

 

[Allen Browne]

To dirty the form, assign a value to a bound control, e.g.:

Me.[Sub1].Form![SomeControl] = Null

 

[David W. Fenton]

To dirty the form, assign a value to a bound control, e.g.:

Me.[Sub1].Form![SomeControl] = Null

Or if you don't want change an existing record:

Me!Sub1.Form!SomeControl = Me!Sub1.Form!SomeControl

Apropose of nothing, I find it odd that you use . syntax for the
form control and ! syntax for a control on the embedded form -- I
would expect all or nothing, but I never use . syntax for controls.
Maybe you don't get compile-time checking for the control on the
subform (I'd expect you don't) so there's no benefit to using it.
Indeed, maybe VBA disallows it? If so, I'd say it's yet another flaw
of using . syntax, because it behaves inconsistently depending on
where you're using it.

But I could be wrong in my speculation.

 

[Allen Browne]

As you guessed, David, I prefer the dot because it catches a misspelling at
design time or compile time: leaving the error undetected until runtime is
undesirable.

Naturally Access can't check for the control in the subform at
design/compile time, because you could change that (altering the
SourceObject.) I don't consider that limitation to be an inconsistency in
the dot-syntax, but a necessary design approach.

So, yes: I was inconsistent in my use of dot verses bang, but there is some
madness in my method.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

To dirty the form, assign a value to a bound control, e.g.:

Me.[Sub1].Form![SomeControl] = Null

Or if you don't want change an existing record:

Me!Sub1.Form!SomeControl = Me!Sub1.Form!SomeControl

Apropose of nothing, I find it odd that you use . syntax for the
form control and ! syntax for a control on the embedded form -- I
would expect all or nothing, but I never use . syntax for controls.
Maybe you don't get compile-time checking for the control on the
subform (I'd expect you don't) so there's no benefit to using it.
Indeed, maybe VBA disallows it? If so, I'd say it's yet another flaw
of using . syntax, because it behaves inconsistently depending on
where you're using it.

But I could be wrong in my speculation.

 

[Clif McIrvin]

Allen, did you fully answer OP's question? It reads to me like she needs
to force the creation of a new record in the subform when she enters
data in the subsubform ... not merely make (the subform) dirty...

--
Clif

To dirty the form, assign a value to a bound control, e.g.:

Me.[Sub1].Form![SomeControl] = Null

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Main form is fProj - data for project
Subform is fDeliv - data for deliverable
SubSub form is fTL - data for transmittals

I sometimes have data that must be input into the subsubform -( ie
transmittal dates due) before I have data for the Subform.
Days later the transmittal data is ready to send to the customer and
then
the field called TLno gets input into the subform

The issue is that I need the autonumber in the subform to populate
since it
is a key for the subsubform.
Therefore I do not get an autonumber id for the subform and my subsub
form
data is lost.

how can I force a record in the subform when data is input into the
subsubform?
Is there a way to make the subform dirty and then save?

 

[David W. Fenton]

Naturally Access can't check for the control in the subform at
design/compile time, because you could change that (altering the
SourceObject.) I don't consider that limitation to be an
inconsistency in the dot-syntax, but a necessary design approach.

Look at it from the perspective of someone trying to understand
which to use:

! works in all cases, whether the control is on the main form or a
subform (or a subform of a subform)

.. works only for controls on the main form

Seems like yet another reason not to use the . operator (which I do
not, under any circumstances, because I don't trust the
behind-the-scenes mechanism that creates the hidden properties that
allow this to work, and have also seen the corruption it rarely
causes).

 

[deb]

You are correct. I need to create a new record. At the time of creation the
user goes directly to the mIn form and the subsub form and enters data.
where as the sub form does not have user data until a later date.

I need either...
When the user creates a record in the main form, have the subform create a
new record.
or
When the user creates a record in the subsubform, have the subform create a
new record.
The subform record is needed to link to the subform data.

Thanks, I know this is out of the norm but it is the way the data is entered.
--
deb

Allen, did you fully answer OP's question? It reads to me like she needs
to force the creation of a new record in the subform when she enters
data in the subsubform ... not merely make (the subform) dirty...

--
Clif

To dirty the form, assign a value to a bound control, e.g.:

Me.[Sub1].Form![SomeControl] = Null

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Main form is fProj - data for project
Subform is fDeliv - data for deliverable
SubSub form is fTL - data for transmittals

I sometimes have data that must be input into the subsubform -( ie
transmittal dates due) before I have data for the Subform.
Days later the transmittal data is ready to send to the customer and
then
the field called TLno gets input into the subform

The issue is that I need the autonumber in the subform to populate
since it
is a key for the subsubform.
Therefore I do not get an autonumber id for the subform and my subsub
form
data is lost.

how can I force a record in the subform when data is input into the
subsubform?
Is there a way to make the subform dirty and then save?

 

[Allen Browne]

You could use the AfterInsert event procedure of the main form to execute an
Append query statement to add a record to the middle table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

You are correct. I need to create a new record. At the time of creation
the
user goes directly to the mIn form and the subsub form and enters data.
where as the sub form does not have user data until a later date.

I need either...
When the user creates a record in the main form, have the subform create a
new record.
or
When the user creates a record in the subsubform, have the subform create
a
new record.
The subform record is needed to link to the subform data.

Thanks, I know this is out of the norm but it is the way the data is
entered.
--
deb

Allen, did you fully answer OP's question? It reads to me like she needs
to force the creation of a new record in the subform when she enters
data in the subsubform ... not merely make (the subform) dirty...

--
Clif

To dirty the form, assign a value to a bound control, e.g.:

Me.[Sub1].Form![SomeControl] = Null


Main form is fProj - data for project
Subform is fDeliv - data for deliverable
SubSub form is fTL - data for transmittals

I sometimes have data that must be input into the subsubform -( ie
transmittal dates due) before I have data for the Subform.
Days later the transmittal data is ready to send to the customer and
then
the field called TLno gets input into the subform

The issue is that I need the autonumber in the subform to populate
since it
is a key for the subsubform.
Therefore I do not get an autonumber id for the subform and my subsub
form
data is lost.

how can I force a record in the subform when data is input into the
subsubform?
Is there a way to make the subform dirty and then save?

 

[deb]

Do you have an example of an append query statement. I have not created one
of these before.

Thank you very much for your reply.

--
deb

You could use the AfterInsert event procedure of the main form to execute an
Append query statement to add a record to the middle table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

You are correct. I need to create a new record. At the time of creation
the
user goes directly to the mIn form and the subsub form and enters data.
where as the sub form does not have user data until a later date.

I need either...
When the user creates a record in the main form, have the subform create a
new record.
or
When the user creates a record in the subsubform, have the subform create
a
new record.
The subform record is needed to link to the subform data.

Thanks, I know this is out of the norm but it is the way the data is
entered.
--
deb

Allen, did you fully answer OP's question? It reads to me like she needs
to force the creation of a new record in the subform when she enters
data in the subsubform ... not merely make (the subform) dirty...

--
Clif

To dirty the form, assign a value to a bound control, e.g.:

Me.[Sub1].Form![SomeControl] = Null


Main form is fProj - data for project
Subform is fDeliv - data for deliverable
SubSub form is fTL - data for transmittals

I sometimes have data that must be input into the subsubform -( ie
transmittal dates due) before I have data for the Subform.
Days later the transmittal data is ready to send to the customer and
then
the field called TLno gets input into the subform

The issue is that I need the autonumber in the subform to populate
since it
is a key for the subsubform.
Therefore I do not get an autonumber id for the subform and my subsub
form
data is lost.

how can I force a record in the subform when data is input into the
subsubform?
Is there a way to make the subform dirty and then save?

 

[Allen Browne]

You can use the query design window to help you.

1. Create a query, without any table.

2. Change it to an Append query (Append on Query menu.)
Access will ask you which table to append to.
Choose your middle table.

3. Type 99 into the Field row.
In the Append row under this, choose the field from the middle subform that
matches the field on your main form (i.e. the foreign key field.)

4. Switch to SQL View (View menu.)
There's a sample of the statement you need.

5. Set your form's AfterInsert property to:
[Event Procedure]

6. Click the Build Button (...) beside the property.
Access opens the code window.
Set up the code like this:

Private Sub Form_AfterInsert()
Dim db As DAO.Database
dim strSql As String
strSql = "INSERT INTO YourMiddleTable (YourForeignKeyField) " & _
"SELECT " & Me.YourMainFormID & " AS Expr1;"
Set db = dbEngine(0)(0)
db.Execute strSql, dbFailOnError
'Debug.Print "Added " & db.RecordsAffected & " record(s.)"
Set db = Nothing
End Sub

If this kind of thing is new, this might help:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Do you have an example of an append query statement. I have not created
one
of these before.

Thank you very much for your reply.

--
deb

You could use the AfterInsert event procedure of the main form to execute
an
Append query statement to add a record to the middle table.

You are correct. I need to create a new record. At the time of
creation
the
user goes directly to the mIn form and the subsub form and enters data.
where as the sub form does not have user data until a later date.

I need either...
When the user creates a record in the main form, have the subform
create a
new record.
or
When the user creates a record in the subsubform, have the subform
create
a
new record.
The subform record is needed to link to the subform data.

Thanks, I know this is out of the norm but it is the way the data is
entered.
--
deb


:

Allen, did you fully answer OP's question? It reads to me like she
needs
to force the creation of a new record in the subform when she enters
data in the subsubform ... not merely make (the subform) dirty...

--
Clif

To dirty the form, assign a value to a bound control, e.g.:

Me.[Sub1].Form![SomeControl] = Null


Main form is fProj - data for project
Subform is fDeliv - data for deliverable
SubSub form is fTL - data for transmittals

I sometimes have data that must be input into the subsubform -( ie
transmittal dates due) before I have data for the Subform.
Days later the transmittal data is ready to send to the customer
and
then
the field called TLno gets input into the subform

The issue is that I need the autonumber in the subform to populate
since it
is a key for the subsubform.
Therefore I do not get an autonumber id for the subform and my
subsub
form
data is lost.

how can I force a record in the subform when data is input into the
subsubform?
Is there a way to make the subform dirty and then save?

 

[David W. Fenton]

You could use the AfterInsert event procedure of the main form to
execute an Append query statement to add a record to the middle
table.

Why not just have the BeforeInsert event of the subform dirty the
parent form. Is that too late (too lazy to fire up Access and try it
right now)?

 

[Allen Browne]

Try it, David.

Of course, you would need to go back to the main form and check it's not at
a new record first.