Form_AfterUpdate Issue

[Jody]

BACKGROUND:
I have a table (tblLogChange) whose purpose is to log the
primary key value and timestamp of changes made in another
table (tblProspSubscr). This is done with the following
code in the Form_AfterUpdate event of the form
(frmSubscriberMain) whose record source is a query based
on tblProspSubscr:

Dim StrSQL
StrSQL = "INSERT INTO tblLogChange(SubscriberID,
ChangedOn) SELECT " _
& Forms!frmSubscriberMain.ProspSubscrID & " AS
SubscriberID, Now() AS ChangedOn;"
DBEngine(0)(0).Execute StrSQL

The event code executes fine and the two fields are
consistently recorded in tblLogChange.

QUESTION:
I'd like to also record changes in tblLogChange made in a
table related to tblProspSubscr called tblLocation (where
one subscriber may have many locations.

The form whose record source is based on tblLocation is
called frmLocation and actually resides within a Page
(Tab) that sits on frmSubsriberMain.

I tried the same code in the Form_AfterUpdate in
frmLocation substituting only the form name:

Dim StrSQL
StrSQL = "INSERT INTO tblLogChange(SubscriberID,
ChangedOn) SELECT " _
& Forms!frmLocation.LocationID & " AS SubscriberID, Now()
AS ChangedOn;"
DBEngine(0)(0).Execute StrSQL

I get an error 2450 '....can't find frmLocation....'

I can't understand why the Forms!frmLocation.LocationID
doesn't work as it is contained within frmLocation.

Does it have something to do with the Page container or
some other reason that may be evident?

Any help is greatly appreciated.

Cheers,
Jody

 

[Ken Snell]

frmLocation is a subform of the mainform frmSubscriberMain, right? You must
reference a subform through the mainform; the subform is not open on its own
in this situation.

Assuming that the name of the subform control (the control that holds the
subform on the main form) is named frmLocation, change your code to this:

StrSQL = "INSERT INTO tblLogChange(SubscriberID, ChangedOn) SELECT " _
& Forms!frmSubscriberMain!frmLocation!LocationID & " AS SubscriberID, Now()
AS ChangedOn;"

If the name of the subform control is something else (e.g., Child3), then it
would be this:

StrSQL = "INSERT INTO tblLogChange(SubscriberID, ChangedOn) SELECT " _
& Forms!frmSubscriberMain!Child3!LocationID & " AS SubscriberID, Now() AS
ChangedOn;"

 

[Jody]

thanks Ken. It worked fine. My final statement in the
subform 'frmLocation' Form_AfterUpdate event was:

Forms!frmSubscriberMain!frmLocation!ProspSubscrID (instead
of LocationID, which I mistakenly wrote before)

ProspSubscrID is an essential field, as it allows me to
trace location record changes related to a subscriber.

I have a related follow-on question - that I thought would
be too complicated to ask initially:

Applying your suggestion allowed me to log the foreign key
data value from the ProspSubscrID field (tblLocation) (via
a frmLocation event). I previously accomplished (on my
own) logging the primary key data value from the
ProspSubscrID field (tblProspSubscr) (via a
frmSubscriberMain event).

Overall, what I'm attempting to do is to log ALL database
record changes related to subscribers. Subscriber changes
can extend to tables directly related to tblProspSubscr
(like tblLocation), but more challenging, to tables
related in turn to those tables (two levels of one-to-
many)- (for example, tblLocnPhn). In other words, a
subcriber can have many locations, and in turn, a
location can have many phones.

Facilitating the logging for record changes in tblLocation
was relatively easy as I still had ProspSubscrID as the
foreign key - and I could shoot that data value over to
the SubscriberID field in my log table. However,
tblLocnPhn is linked to tblLocation via LocationID, and I
lose the direction connection of ProspSubscrID.

I'd like to be able to create a Form_AfterUpdate event in
frmLocnPhn (which is a subform of frmLocation) that would
somehow derive the ProspSubscrID from tblLocation, even
though the linkage would be made through LocationID.

Any ideas about how to do this? Any additional help would
be most appreciated.

Thanks,
Jody

-----Original Message-----
frmLocation is a subform of the mainform

frmSubscriberMain, right? You must

 

[Ken Snell]

I must admit that I'm not sure I'm fully understanding what you seek to
accomplish, but what I'm comprehending is that you want to get the value of
ProspSubscrID into the sub-subform's code so that you can use it for logging
purposes?

If so, just use Forms!frmSubscriber!ProspSubscrID as the value of that
field, and use it in your code.

Is that what you want? If not, post back....because then I obviously missed
your topic's intent!

 

[Jody]

Hi Ken,

Sorry I'm just getting back to you. Given my lack of VBA
knowledge, I think I was making this more difficult than
it needed to be.

You're theory was correct - I can access the ProspSubscrID
data, regardless of which form level I am at. I didn't
realize that.

Thanks!!
Jody

 

[Ken Snell]

You're welcome.

Hi Ken,

Sorry I'm just getting back to you. Given my lack of VBA
knowledge, I think I was making this more difficult than
it needed to be.

You're theory was correct - I can access the ProspSubscrID
data, regardless of which form level I am at. I didn't
realize that.

Thanks!!
Jody