By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,829 Members | 673 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,829 IT Pros & Developers. It's quick & easy.

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

P: 4
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.
Jun 5 '17 #1

✓ answered by 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 :
Expand|Select|Wrap|Line Numbers
  1. =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.

Share this Question
Share on Google+
7 Replies


NeoPa
Expert Mod 15k+
P: 31,418
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 :
Expand|Select|Wrap|Line Numbers
  1. =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.
Jun 5 '17 #2

P: 4
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.
Jun 6 '17 #3

PhilOfWalton
Expert 100+
P: 1,430
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

Expand|Select|Wrap|Line Numbers
  1. DMax("VisitNo", TblVisits", "ClientID = " & ClientID) + 1
  2.  
Phil
Jun 6 '17 #4

P: 4
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?
Jun 6 '17 #5

PhilOfWalton
Expert 100+
P: 1,430
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".

Expand|Select|Wrap|Line Numbers
  1. TotalVisits = DCount("VisitID", "TblVisits", "ClientID = " & ClientID)
  2.  
Phil
Jun 6 '17 #6

P: 4
Thanks, this worked for my purposes!! I appreciate the help!!
Jun 6 '17 #7

NeoPa
Expert Mod 15k+
P: 31,418
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.
Jun 6 '17 #8

Post your reply

Sign in to post your reply or Sign up for a free account.