 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 1 / November 2004Running a Query from a form
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. 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")  SignatureAllen 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. > Hi > I am new at setting up a database and requesting info from groups so [quoted text clipped - 15 lines] > 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. 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 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. SignatureAllen 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. > Thankyou for your reply > [quoted text clipped - 9 lines] > 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). 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. 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. SignatureAllen 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. > Hi and thanks for your help > [quoted text clipped - 13 lines] > message is also displayed when I use the original code. Any ideas on > what I am doing wrong please. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.