How to get next number?

[Guest]

Hello,

I've developed asp applications with SQL Server. Haven't developed too many
with Access.

I have a form that shows the sales folks different areas. For each area,
there is a date and the folks that worked that area. There is also a list of
addresses per area. I 've created the normalized tables and have an idea of
how to put the form together. Start with area, then write query to bring in
areadetails (date, workers, and manager of area for that day).

I can show the visit number of that area. But when it is time to key in the
next visit for that area, I am stuck. I have a table that shows how many
times an area has been worked.

areaVisitCount
area number
visitCount number

When I show this field on the main form, how do I increment the number and
save it to the table and then bring up the data entry form show the new visit
count?

***********************************************************
In SQL, I would do an insert statement and increment the number. When the
user pushes the date entry button, how do I in the button event code, run an
SQL insert to insert a new visitcount in the areavisitcount table and then
open the data entry form showing the latest visitcount?
***********************************************************

I hope this is clear. Any ideas from anyone would be great!

Tony

 

[Perry]

Various possibilities, but here's one approach:

Look at DMax() function to get you the incremented number in yr form.

To update the newly acquired number, use something like:
(assuming Table1 is the targettable, ID represents the numberfield and
TimeStamp another field)

Private Sub Form_BeforeUpdate(Cancel As Integer)
CurrentDb.Execute _
"UPDATE Table1 SET Table1.ID = DMax('ID','Table1')+1, Table1.[TimeStamp]
= Now()"
End Sub

Krgrds,
Perry