Running a Query from a form

[Rupert Street]

Hi
I am new at setting up a database and requesting info from groups so
please excuse any mistakes that I inadvertently make.

I have set up a table with client info including a ClientID. The
ClientID is made up of the first letter of the client name plus a
sequential number eg A10 (Adam Bros P/L) J03 (Jolly, TJ & Sons) and I
want it calculated for me so as to minimise mistakes.

I set up a form to enter the client info & included a textbox (Control
Source)=Left$([Client],1). I then figured if I ran a query
automatically with the SQL statement SELECT Count ([ClientID]) AS
NewNumber FROM Clients WHERE [ClientID] Like 'A??'; that would work
out how many clients started with that letter then the ClientID
control would be =Left$([Client],1) & Sum([Query1]![NewNumber]+1).

What I don't know how to do is run the query from the form
automatically, how to set up a variable to collect the letter from the
textbox and if I am doing it correctly in the first place.

[Allen Browne]

The Count won't work, because if you delete Client A8, that could would give
you A10 again even though you already have an A10.

Why not just use an autonumber? Saves all the hassles, and solves the
multi-user issues. For example, if you have 2 users adding new clients
starting with A at once, your own code is likely to give them the same key.
To solve that properly means writing code to handling the locking issues,
and introduce random delays and retries to overcome the clash. The
AutoNumber solves this for you.

If you really want to use your code your own, you need to use codes like
A000001 so you can quickly identify the maximum value assigned to the prefix
letter, increment it, and create the new key. Use the BeforeUpdate event
procedure of your form, since that is the last possible moment before the
record is saved.

The kind of thing you will want is:
Left([ClientName], 1) & Format(Val(Nz(DMax("ClientID", "Clients" , "ClientID
Like """ & Left([ClientName], 1) & "*"""), 0)) + 1, "000000")

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Rupert Street" <ja**********@yahoo.com.au> wrote in message
news:3a*************************@posting.google.co m...

Hi
I am new at setting up a database and requesting info from groups so
please excuse any mistakes that I inadvertently make.

I have set up a table with client info including a ClientID. The
ClientID is made up of the first letter of the client name plus a
sequential number eg A10 (Adam Bros P/L) J03 (Jolly, TJ & Sons) and I
want it calculated for me so as to minimise mistakes.

I set up a form to enter the client info & included a textbox (Control
Source)=Left$([Client],1). I then figured if I ran a query
automatically with the SQL statement SELECT Count ([ClientID]) AS
NewNumber FROM Clients WHERE [ClientID] Like 'A??'; that would work
out how many clients started with that letter then the ClientID
control would be =Left$([Client],1) & Sum([Query1]![NewNumber]+1).

What I don't know how to do is run the query from the form
automatically, how to set up a variable to collect the letter from the
textbox and if I am doing it correctly in the first place.

[Rupert Street]

Thankyou for your reply

I am not sure that autonumber would work and I am not sure that I
explained the numbering correctly. If I have 10 'A' clients the next
one would be A11 then if the next client was a 'P' and there were 3
existing 'P's the next one would be P4.

Clients aren't deleted even though they might no longer exist and only 1
person has access to set up a new client so that gets around the
deletion and locking issues.

I am presuming that Val = value but I couldn't figure out what Nz meant
(I could use some humour here but I won't).

Yours R
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Allen Browne]

Nz() converts a Null to a zero or other value.
Val() converts a string to a number.
DMax() gets the maximum used so far.
Left() takes the left characters of a field, and Mid() some of the
characters from the middle.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Rupert Street" <ja**********@yahoo.com.au> wrote in message
news:41**********************@news.newsgroups.ws.. .

Thankyou for your reply

I am not sure that autonumber would work and I am not sure that I
explained the numbering correctly. If I have 10 'A' clients the next
one would be A11 then if the next client was a 'P' and there were 3
existing 'P's the next one would be P4.

Clients aren't deleted even though they might no longer exist and only 1
person has access to set up a new client so that gets around the
deletion and locking issues.

I am presuming that Val = value but I couldn't figure out what Nz meant
(I could use some humour here but I won't).

[Rupert Street]

Hi and thanks for your help

I have tried entering the code into the form but found that the ClientID
was always displayed as "Alpha"01 not "Alpha"max+1 eg P01 not P08. I
changed the code to read:
Left([Client],1) & Format(Right(Nz(DMax("ClientID", "Clients", "ClientID
Like """ & Left([Client],1) & "*"""),0),2)+1, "0000")

When I put this code into the BeforeUpdate event on the form nothing is
displayed in the ClientID box and when I go to close the form or enter
another client the message "Index or Primary key can't contain a null
value" is displayed.

When I put the code into the control source for ClientID it displays the
new ID but the same message is displayed when I close etc. The same
message is also displayed when I use the original code. Any ideas on
what I am doing wrong please.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Allen Browne]

In your code, press F9 on the line where this runs. That will set a break
point, so you can check the values and experiment until you get the results
you want.

When the code runs and breaks, press Ctrl+G to open the Immediate window.
You can ask it for values there.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Rupert Street" <ja**********@yahoo.com.au> wrote in message
news:41**********************@news.newsgroups.ws.. .

Hi and thanks for your help

I have tried entering the code into the form but found that the ClientID
was always displayed as "Alpha"01 not "Alpha"max+1 eg P01 not P08. I
changed the code to read:
Left([Client],1) & Format(Right(Nz(DMax("ClientID", "Clients", "ClientID
Like """ & Left([Client],1) & "*"""),0),2)+1, "0000")

When I put this code into the BeforeUpdate event on the form nothing is
displayed in the ClientID box and when I go to close the form or enter
another client the message "Index or Primary key can't contain a null
value" is displayed.

When I put the code into the control source for ClientID it displays the
new ID but the same message is displayed when I close etc. The same
message is also displayed when I use the original code. Any ideas on
what I am doing wrong please.