Thanks. I'll give some details.
RIntegrity, One-one, cascade updates/deletes.
The delete button is on a form (Jobfrm) with a query as RSource. (Its
place
on tab, but I think that's not an issue, right?)
The invoice form (a subform on tab0, beside the delete button) has an RS
of
a table.
The related subforms are on other tabs with RS of table or query.
I have understood when tabs are used, they are transparent as to
reference.
That each of my subforms though on a tab are really on the parent form --
JobFrm. Correct?
If so, and since all related with RIntegrity and cascade deletes, and the
related records are IN FACT DELETED, why do I get the previous mentioned
messaged -- counting one for each of the related subforms?
In short, all works fine except those pesky messages. And they don't
appear
unless I try to close the form.
--
Thanks for your help,
Chris
Douglas J. Steele said:
Sorry, you're not giving enough details.
What the record source of the parent form and of the subform? Do you have
Referential Integrity set between the tables? If you've got a
parent-child
relationship for the tables that make up the record source of the form
and
subform (as is usual when using subforms), are you trying to delete from
the
parent table or the child table?
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
Chris said:
Thanks, Doug. I missed that reference and when I correct it the record
is
deleted. However, for each related table I get an error that I cannot
change
or add because one is required in a related table.
I tried a requery of the subform control (Invoicefrm) after I turn back
on
the messages and I still get the above message.
How do I eliminate the message about EACH related table?
--
Thanks for your help,
Chris
:
To refer to a control on a subform, you need to use
Me!NameOfSubformControl.Form!InvNum
Note that depending on how you added the subform to the parent form,
the
name of the subform control may be different than the name of the form
being
used as the subform: it's the name of the control on the parent form
that
you need to use.
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
Thanks for responding.
Yes, the field is number, currency type. I'm Access 2007.
When I hover over the VBA code that is highlighted, it shows value
of
Me.InvNum is null. My command button to delete is on a form and the
subform
shows InvNum.
I tried setting the focus to the subform first -- same error.
I also tried Me.dirty=false -- same error.
I also tried setting above focus then Me.dirty=false -- same error.
Any ideas?
--
Thanks for your help,
Chris
:
You sure Me.[InvNum] is returning a value?
If it is, is the InvNum field text? If so, you need quotes:
DoCmd.RunSQL "DELETE [tbl 2 Job].* FROM [tbl 2 Job] " & _
"Where [tbl 2 Job].InvNum = '" & Me.[InvNum] & "'"
Exagerated for clarity, that second line is
"Where [tbl 2 Job].InvNum = ' " & Me.[InvNum] & " ' "
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
Novice needs help please.
I am attempting to use code I found here (surrounded by messages
on/off)
to
delete the current record at subform and the related ones (in
other,
sibling
subforms.) The error is:
-- syntax error (missing operator) in query expression '[tbl 2
Job].InvNum
=' --
My code is:
' run a delete query to delete the related records from the
subform
DoCmd.RunSQL "DELETE [tbl 2 Job].* FROM [tbl 2 Job] Where [tbl 2
_
Job].InvNum = " & Me.[InvNum]
'line continuation is only for newsgroup.
' Delete current record on viewed form
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
