need sequential number in field to count the number of visits per client

[tzinda]

I have a Client form and a Subform. They are linked by Client ID. Each Client can have multiple visits. There is a button on the Subform to Open a Next Record so that a new visit can be recorded. When this new record opens I want the field named Number of visits to +1. I currently have a macro on the button to open the New record. I know that this is very simple for someone out there, but I am stuck.

[NeoPa]

Hi.

You need to be clearer about what you want. Do you want a new value to be stored somewhere or simply shown on the form?

The former will lead you into problems but the latter is quite easy to do with a TextBox showing :

=Count([Field])

It's hard to be more specific when you don't share the necessary details, but we can help more when the question is more clear.

[tzinda]

Hi, I need the number to be stored in the Number of Visits field. So I believe that I have to tie it to the Macro on the button Add New Visit. Right now the Macro is just to open a New Record.

[PhilOfWalton]

As NeoPa says, what you want is quite easy, but unwise. What happens when you delete a record? There will be a break in your numbers. What happens if you have forgotten to to add a visit and the date of that visit precedes the last visit entered for that client? Then the numbers will be out of sequence with the dates.

The normal way of handling this situation is to have the main form for the client, and a continuous subform showing all the visits in date order.

If you really do insist on a visit number, have a look at something like

DMax("VisitNo", TblVisits", "ClientID = " & ClientID) + 1
 

Phil

[tzinda]

I do have separate forms for the Client with the Visit information as a subform, but since the visit information requires Subforms it cannot be a continuous form. Is there a way for an expression to count how many previous visits there are for a ClientID? /So let's say clientID 10 is in for the 5th time, is there a way to count say how many previous visits using the VisitID (autonumber)for ClientID 10?

[PhilOfWalton]

The Visits can be a continuous subform if you wish. It's up to you.

The no of visits can be found using a vaguely similar expression to the one in my last post. This assumes you want to display the Total Visits on the main Client form, anf there is a field on this form called "ClientID".

TotalVisits = DCount("VisitID", "TblVisits", "ClientID = " & ClientID)
 

Phil

[tzinda]

Thanks, this worked for my purposes!! I appreciate the help!!

[NeoPa]

You may notice the difference between what I suggested and what Phil suggested. Aggregate, and Domain Aggregate, functions are very much not the same. Aggregate functions make use of data already available whereas Domain Aggregate ones (These generally start with a D.) do not.

I would never recommend using Domain Aggregate functions where the Aggregate equivalents are available. In this situation they are, or at least should be.