Reference autonumber (1 Viewer)

kodehunt

Registered User.
Local time
Today, 09:15
Joined
Mar 30, 2012
Messages
13
So I have a list of records in a continuous form. Within that record is a checkbox called GRT. GRT stands for Gross Receipts Tax, when it is checked I have an after update event that adds a new auto-generated record for the GRT entry. The new record copies the date from the origional record, calculates GRT from the origional records amount field and some other things like that to create the new record. Creating the GRT record entry has went well.

But, if the user removes the GRT checkbox from, or deletes the origional record, I need to delete the corresponding auto-created GRT record. I thought I could do this by simply grabbing the origional records ID and copying it into the auto-generated records field called "AssociatedID".

Problem is, when I reference the origional records record ID, it always comed in as "-1". I can display the ID on the form and it shows correct, but if I msgbox the records ID, it shows as "-1". I cannot figure out how to capture the origional records ID so I can insert it into the new auto-generated record so the two records are linked so I can later use that link to delete the proper associated record.

How do I reference the origional records ID or am I approaching this all wrong? Thanks for your help.
 

WayneRyan

AWF VIP
Local time
Today, 15:15
Joined
Nov 19, 2002
Messages
7,122
Kodehunt,

1) You can define a relationship between the continuous forms table and the
auto-generated table.

Join them on ID <--> AssociatedID

Select Cascade delete. That will solve the deletion problem.

2) When they uncheck the checkbox:

CurrentDb.Execute "Delete From Auto-GeneratedTable Where AssociatedID = " & Me.ID

hth,
Wayne
 

kodehunt

Registered User.
Local time
Today, 09:15
Joined
Mar 30, 2012
Messages
13
Both transactions post to the same table as they are really the same transaction, they just decided they want to split out GRT into it's own line item.
Even if I did have them in separate tables joined as you say, somehow I would have to capture ID to write it to the second table under AssociatedID. It is the capturing of ID that Access doesn't seem to allow.
When I reference or message box Me.ID it always comes back as "-1", even though i can see it's 6211 on the form.
It is the capturing of Me.ID's value that I need to figure out, once I have it, I think my problems are solved.
 

WayneRyan

AWF VIP
Local time
Today, 15:15
Joined
Nov 19, 2002
Messages
7,122
I take it that you ID field is an autonumber.

If so, once you see it on the form (Me.ID), it should also be in the table.

How is the other record "auto-generated"?

Wayne
 

kodehunt

Registered User.
Local time
Today, 09:15
Joined
Mar 30, 2012
Messages
13
Yes it is an autonumber. Yes, I see it on the form and in the table. Problem is, and I guess because it is an autonumber, I cannot reference it. The other record is generated on an after update routing if the GRT check box is created. I is an Insert Into command. During this routine is when I wanted to capture the origional records ID and put it in the AssocID on the auto generated record.

I've had another thought though. I think I'll assign a unique number to the origional records AssocID field by doing some sort of Max(AssocID) + 1 type of thing, then use that number to assign to the auto generated record and that should associate the two records. Any thoughts on that?
 

Users who are viewing this thread

Top Bottom