Account# Increment

  • Thread starter Thread starter Anthony Viscomi
  • Start date Start date
A

Anthony Viscomi

I have a tbl (Dealer_Account_Data); within the tbl there a 2 key number
fields (Prop_ID & Dealer_Acct_Num).

The Prop_ID is always 5 digits and the Dealer_Acct_Num is 6 digits made up
of the 1st 5 digits of the Prop_ID and and incremtal 6 digit for each new
dealer that takes over the property.

What I would like to do is automatically populate the Dealer_Acct_Num field
with the next available/incremantal account #. I can get the Prop_ID from
the from the Prop_ID field that currently resides on the existing form. My
problem is creating the "lookup" that finds the most recent Dealer_Acct_Num
and increments it by 1.

Any thoughts?

Thanks in advance!
Anthony
 
I have a tbl (Dealer_Account_Data); within the tbl there a 2 key number
fields (Prop_ID & Dealer_Acct_Num).

The Prop_ID is always 5 digits and the Dealer_Acct_Num is 6 digits made up
of the 1st 5 digits of the Prop_ID and and incremtal 6 digit for each new
dealer that takes over the property.

What I would like to do is automatically populate the Dealer_Acct_Num field
with the next available/incremantal account #. I can get the Prop_ID from
the from the Prop_ID field that currently resides on the existing form. My
problem is creating the "lookup" that finds the most recent Dealer_Acct_Num
and increments it by 1.

Any thoughts?

Thanks in advance!
Anthony
Click to expand...

"Smart keys" are generally not a good idea. Sounds more like you have
tbl (Dealer_Account_Data); within the tbl there a 2 key number
fields (Prop_ID & Dealer_Acct_Num)
Click to expand...

tblDealer(DealerID (PK), FirstName, LastName...)
tblDealerManagesProperty(FKDealerID, FKPropertyID, StartDate,EndDate)
tblProperty(PropertyID, ...)

Then the current property manager is the latest... i.e. the one with
the latest start date with EndDate=Null.
 
The Prop_ID resides within a Property table allof those values are unique;
the Dealer Account table is linked to the Property table via the Prop_ID.
Thus, allowing me to show a Dealer history for a Property.

What I am trying to accomplish is develop an "idiot proof" method that'll
allow my users to add a new dealer to a particuliar property; which in
return will flag the current dealer as "old/past". I can accomplish that by
placing this simplecode behind an event
Me.Present_Past_Dealer = "Past
Me.End_Date = Now()
DoCmd.GoToRecord , , acNewRec

The Prop_ID field on the NewRec will be populated from the main form that
the Dealer_Account_sbfrm is linked to.

I would like to be able to look up all Dealer_Account_Num's for the Prop_ID
on the current form find the most recent, increment it by 1 and place the
new value in the NewRec. Make sense?
 
Back
Top