 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 2 / September 2007autoincrement PK
I'm trying to simply increment a field in a table when i am in a form. The table is tblhome and the form field is Home_addr_PK. The last record is HA00052. When i create a new record, i would like HA0053 populated in the Home_Addr_PK field. I am getting a "Type mismatch" error when i run this code from a command button (new home address). Please help. Private Sub New_Home_Address_Click() On Error GoTo Err_New_Home_Address_Click DoCmd.GoToRecord , , acNewRec 'Me!Home_Addr_PK = Format(DMax("[Home_Addr_PK])", "tblHome"), 0) + 1, "0000") Exit_New_Home_Address_Click: Exit Sub Err_New_Home_Address_Click: MsgBox Err.Description Resume Exit_New_Home_Address_Click >I'm trying to simply increment a field in a table when i am in a form. >The table is tblhome and the form field is Home_addr_PK. The last record is >HA00052. When i create a new record, i would like HA0053 populated in the >Home_Addr_PK field. I am getting a "Type mismatch" error when i run this code >from a command button (new home address). Please help. The Format function returns a String (and "HA0053" is a string). You can't do arithmatic with strings! You'll need to parse the numeric portion out as a number, increment *it*, and repackage it. Actually, if the HA is constant for all records, you shouldn't include it in the field at all. Instead, just store a Long Integer and add the HA with a format like "\H\A0000" to display a literal H, a literal A, and four digits with leading zeroes. To store the first two alpha characters (whatever they are, HA or otherwise) and still increment the number try Private Sub New_Home_Address_Click() Dim strAddr As String Dim intAddr As Integer On Error GoTo Err_New_Home_Address_Click DoCmd.GoToRecord , , acNewRec strAddr = NZ(DMax("[Home_Addr_PK]", "tblHome"). "HA0000") intAddr = Val(Mid(strAddr, 3) ' extract numeric portion If intAddr < 9999 Then Me!Home_Addr_PK = Left(strAddr, 2) & Format(intAddr + 1, "0000") Else MsgBox "Turn off the computer - out of address numbers", vbOKOnly End If Exit_New_Home_Address_Click: Exit Sub Err_New_Home_Address_Click: MsgBox Err.Description Resume Exit_New_Home_Address_Click End Sub John W. Vinson [MVP] Thanks John, you've given me two options; so you are saying if i just make the home_Addr_pk field an integer with format "\H\A0000" the data value will automatically increment by 1 upon new record creation and the value in the field will be HA0053 (next record) and i dont need seperate VBA code? As you can see, i want to make this a PK for another table and want sequential numbering. Once the value is created i would like it to be "copied" to another field in another table that can link the address with the customer name. Any suggestions? > >I'm trying to simply increment a field in a table when i am in a form. > >The table is tblhome and the form field is Home_addr_PK. The last record is [quoted text clipped - 41 lines] > > John W. Vinson [MVP] >Thanks John, you've given me two options; >so you are saying if i just make the home_Addr_pk field an integer with format >"\H\A0000" the data value will automatically increment by 1 upon new record >creation and the value in the field will be HA0053 (next record) and i dont >need seperate VBA code? No. You'll still need code; but you can simply use the integer value directly, rather than unpackaging it and repackaging it. It just cuts a couple of steps out of the code. >As you can see, i want to make this a PK for another table and want >sequential numbering. Once the value is created i would like it to be >"copied" to another field in another table that can link the address with the >customer name. Any suggestions? To "copy" it use a Subform (unless you're doing something really unusual). A Form based on the "one" side table with a Subform based on the "many", using the ID as the master/child link field, will keep the two in synch. A couple of issues about the sequential number should be mentioned. Autonumbers develop gaps when records are deleted - you'll have to face the same problem! What will you do when someone moves, and record HA0004 becomes invalid and irrelevant to your database? Surely you won't want to renumber HA0005 through HA2156 just to maintain the gap-free sequence! You may instead need to leave the (now invalid) record in your table and mark it as obsolete, either with a yes/no field or by altering the contents from "31140 Circle Dr." to "Invalid" or the like. In either case, you're cluttering your table with data that you KNOW is invalid, just to maintain sequential numbers.... *which your users will probably never need to see*. What good does it do your user to have a "handle" HA2156 on an address? You're using a powerful database; if the user wants to find an address, they can search for the address by the contents of the address, or by a link from another table. It's *NOT* necessary for them to memorize and use a handle. I'd just use an autonumber, gaps and all, and not worry about the sequence; and not expose the primary key value to user view at all. John W. Vinson [MVP] re: VBA code - I'll remove the "unpacking" code and give it a try re: Copying PK - my rationale is; step1 i create a record for an address in the tblhome with PK HA0001. step2, I create a record in tblclient with client name etc and wish to link to tblhome. At this time i haven't linked the client to an address. You are suggesting using a subform (with query) to do this which is fine but the user needs to know the correct address to link, hence adding the PK from tblhome to a field in tblclient. Is this done automatically, manually or through a procedure. re: autonumber sequence, no, i'm not trying to keep records sequential after a delete (i dont think i will be deleting), i just thought using this method could somehow guarantee the n + 1 sequence vs using the autonumber which can develop gaps if a record is created but fields not populated etc. > >Thanks John, you've given me two options; > >so you are saying if i just make the home_Addr_pk field an integer with format [quoted text clipped - 35 lines] > > John W. Vinson [MVP] >re: Copying PK - my rationale is; step1 i create a record for an address in >the tblhome with PK HA0001. step2, I create a record in tblclient with client [quoted text clipped - 3 lines] >hence adding the PK from tblhome to a field in tblclient. Is this done >automatically, manually or through a procedure. I guess I'm not understanding. You know the client's address before you know who the client is? Who are your clients? Are you focusing on the homes with the clients secondary? Does each address pertain to one and only one client? Does each client have one and only one address? If the user wants to find an address... presumably they'll *know the address*, right? How does knowing "31140 Circle Dr" help them know to enter HA2365? Couldn't you just use a combo box storing the concealed ID while displaying the actual address? >re: autonumber sequence, no, i'm not trying to keep records sequential after >a delete (i dont think i will be deleting), i just thought using this method >could somehow guarantee the n + 1 sequence vs using the autonumber which can >develop gaps if a record is created but fields not populated etc. But my question is: *why do you need a n+1 sequence*? What good does it do you? John W. Vinson [MVP] I'll try and explain busines flow 1. there is a prospect table (people's names, tel# etc) not linked to any other table. [reason for this is likely my intermediate design knowledge of Access) 2. When prospect becomes a "client" i want to copy some fields from tblprospect to tblclient (so i dont wish to retype and i dont want to clutter client table with propsects that dont become cleints). At this time i know the prospect address so i want to add this information to tblhome. Primary key is HA1234. I can have multiple clients at an address, hence one-to-many relationship 3. Now i have client info and address info in two seperate tables and want to add HA1234 field to tblclient to link address and client. What is the best way to add on a form/procedure. > >re: Copying PK - my rationale is; step1 i create a record for an address in > >the tblhome with PK HA0001. step2, I create a record in tblclient with client [quoted text clipped - 23 lines] > > John W. Vinson [MVP] >2. When prospect becomes a "client" i want to copy some fields from >tblprospect to tblclient (so i dont wish to retype and i dont want to clutter [quoted text clipped - 5 lines] >to add HA1234 field to tblclient to link address and client. What is the best >way to add on a form/procedure. If it's one address to many clients, the simplest way is to base a form on the Addresses table and use a Subform based on the Clients table, using the AddressID as the master/child link field. Doing so removes any need for the ID to be displayed to or entered by the user - so you can use a random autonumber if you want. John W. Vinson [MVP] I will try what you are suggesting. One more thing, I want to perform a differnet field value increment [PL0001] but now i want to reference an external table (tblclients) from a form not associated with tblclients. The form is frmprospects. I tied using Dmax but it complained it couldn't find tblclients in the expression. > >2. When prospect becomes a "client" i want to copy some fields from > >tblprospect to tblclient (so i dont wish to retype and i dont want to clutter [quoted text clipped - 13 lines] > > John W. Vinson [MVP] not very helpful w/o the complete DMax statement you tried Pieter >I will try what you are suggesting. > One more thing, I want to perform a differnet field value increment [quoted text clipped - 34 lines] >> >> John W. Vinson [MVP] >I will try what you are suggesting. >One more thing, I want to perform a differnet field value increment [PL0001] >but now i want to reference an external table (tblclients) from a form not >associated with tblclients. The form is frmprospects. I tied using Dmax but >it complained it couldn't find tblclients in the expression. By far the simplest way to add a record from tblProspects to tblClients would be by running an Append query, using the form to select the desired client ID as a criterion. That said... post your DMax() expression, or (better) the code using it. John W. Vinson [MVP] I used the same similar code you suggested in an earlier thread... I am launching this from frmProspects Dim strAddr As String Dim intAddr As Integer On Error GoTo Err_New_Client_Click DoCmd.GoToRecord , , acNewRec strAddr = NZ(DMax("[SL_Client_No]", "tblClients"). "SL0000") intAddr = Val(Mid(strAddr, 3) ' extract numeric portion If intAddr < 9999 Then Me!SL_Client_No = Left(strAddr, 2) & Format(intAddr + 1, "0000") Else MsgBox "Out of address numbers", vbOKOnly End If Exit_New_Client_Click: Exit Sub I think the correct method is Dlookup though...please correct me. Second action is to copy specific fields (about 10) from tblProspects to various fields in tblClients (and other related tables) from fmrProspects. Is Append query still the way to go? How do i create Append Query (is there a wizard?) > >I will try what you are suggesting. > >One more thing, I want to perform a differnet field value increment [PL0001] [quoted text clipped - 9 lines] > > John W. Vinson [MVP] >I used the same similar code you suggested in an earlier thread... >I am launching this from frmProspects [quoted text clipped - 20 lines] >Append query still the way to go? How do i create Append Query (is there a >wizard?) Chuck... You are making your own job much more difficult, and restricting the flexibility of your database, by using these "intelligent" multicomponent key fields. I'd *REALLY* suggest abandoning the DL0000 and SL0000 idea. It gets you *NOTHING* except headaches. I have no trace of an idea what you mean by suggesting that "the correct method is dlookup". John W. Vinson [MVP] On Sep 25, 4:43 pm, John W. Vinson <jvinson@STOP_SPAM.WysardOfInfo.com> wrote: > A couple of issues about the sequential number should be mentioned. > Autonumbers develop gaps when records are deleted - you'll have to face the > same problem! What will you do when someone moves, and record HA0004 becomes > invalid and irrelevant to your database? Surely you won't want to renumber > HA0005 through HA2156 just to maintain the gap-free sequence! Well, the OP could change the algorithm from MAX+1 e.g. SELECT MAX(ID) + 1 AS next_ID FROM MyTable; to one that uses the MIN unused value e.g. SELECT MIN(seq) AS next_ID FROM Sequence AS S1 WHERE seq BETWEEN 1 AND 99999 AND NOT EXISTS ( SELECT * FROM MyTable WHERE Sequence.seq = MyTable.ID); This way the gaps eventually get filled. An important consideration is that "you can get duplicate values in your custom counter field if two applications add records in less time than it takes for the cache to refresh and the lazy-write mechanism to flush to disk". See: How To Implement Multiuser Custom Counters in Jet 4.0 and ADO 2.1 Jamie. -- > How To Implement Multiuser Custom Counters in Jet 4.0 and ADO 2.1 http://support.microsoft.com/kb/240317 Jamie. -- Jamie, there is only one user so there is not problem, please see post/question from today - John, can you please respond. > > How To Implement Multiuser Custom Counters in Jet 4.0 and ADO 2.1 > [quoted text clipped - 3 lines] > > -- > Jamie, there is only one user so there is not problem, please see > post/question from today Suggestion: have a table(s) of the complete set of values (HA0000, HA0001, HA0002, HA0003, ... HA9999) ready rolled and *allocate* them as and when required. This way you can _query_ the 'next' value e.g. something like: SELECT MIN(H1.ha_number) FROM MyHaNumbers AS H1 WHERE NOT EXISTS ( SELECT * FROM MyEntityTable AS M1 WHERE H1.ha_number = M1.ha_number); In my head I've got the analogy of a check/cheque book: pre-printed sheets with unique identifiers waiting to completed and handed out. BTW my advice is for free <g>. Jamie. -- >Suggestion: have a table(s) of the complete set of values (HA0000, >HA0001, HA0002, HA0003, ... HA9999) ready rolled and *allocate* them >as and when required. This way you can _query_ the 'next' value e.g. >something like: For that size of domain... absolutely a better idea. Thanks Jamie! (It wouldn't work for a Long Integer because there are too many). I believe that the JET query engine processes frustrated outer joins more efficiently than NOT EXISTS clauses - if Jamie's suggestion is slow try SELECT MIN(H1.ha_number) FROM MyHaNumbers AS H1 RIGHT JOIN MyEntityTable AS M1 ON H1.ha_number = M1.ha_number WHERE M1.ha_number IS NULL; John W. Vinson [MVP] Guys, i have no trouble creating a new record and incrementing HAxxxx. I'm in a seperate form now and i seach on this value (HA1234) using unbound combo box and i want to update a table (tblclients, not related to form im in) and copy HA1234 into a field in tblclients. How do i do this? is it currentdb.execute .......????? > >Suggestion: have a table(s) of the complete set of values (HA0000, > >HA0001, HA0002, HA0003, ... HA9999) ready rolled and *allocate* them [quoted text clipped - 14 lines] > > John W. Vinson [MVP] >Guys, i have no trouble creating a new record and incrementing HAxxxx. >I'm in a seperate form now and i seach on this value (HA1234) using unbound >combo box and i want to update a table (tblclients, not related to form im >in) and copy HA1234 into a field in tblclients. How do i do this? >is it currentdb.execute .......????? How do you know which record in tblClients to update? Do you want to update an existing record, or add a new record? Where is the other data for this record coming from? John W. Vinson [MVP] is a new record with only the SL_Client_No field updated/incremented (SL0002). Then i want to copy fields from existing form (Me![First_Name]) about 10 fields on current form and then the value from a combo box on the same form. > >Guys, i have no trouble creating a new record and incrementing HAxxxx. > >I'm in a seperate form now and i seach on this value (HA1234) using unbound [quoted text clipped - 7 lines] > > John W. Vinson [MVP] >is a new record with only the SL_Client_No field updated/incremented (SL0002). >Then i want to copy fields from existing form (Me![First_Name]) about 10 >fields on current form and then the value from a combo box on the same form. Is this a bound form? Why *from a form* rather than from a table? Data is NOT stored in forms! Are you perhaps loading a bound (or unbound???) form from one table and exporting the data to another table? If you're copying the data from one table to another table, an Append query executed from code would be preferred. If you indeed must use the form as an intermediary, there are a few ways to do it, none ideal - an Append query with ten parameters from the form, or opening a Recordset based on the target table and populating it in code... both rather difficult to do and even harder to maintain. As an Append query, assuming you have data in tblProspects, that you have one of those records selected on the form, and you want to copy all of the fields except the SL_Client_No, something like this should work: Private Sub cmd_CreateClient_Click() Dim strSQL As String Dim db As DAO.Database Dim qd As DAO.Querydef Dim strNextID As String On Error GoTo Proc_Err strNextID = "SL" & Format(Val(Right, DMax("SL_Client_No", [Clients]), 4) _ + 1, "0000") Set db = CurrentDb strSQL = "INSERT INTO Clients ([SL_Client_No], [thisfield], [thatfield])" _ " SELECT '" & strNextID & "' AS SL_Client_No, [thisfield], [thatfield]" _ " FROM Prospects WHERE DL_Prospect_No = '" _ & Forms!yourformname!DL_Prospect_No & "';" Set qd = db.CreateQuerydef("", strSQL) qd.Execute dbFailOnError Proc_Exit: Exit Sub Proc_Err: MsgBox "Error " & Err.Number & " in cmd_CreateClient_Click:" & vbCrLf _ & Err.Description Resume Proc_Exit End Sub John W. Vinson [MVP] Hey John, I must be doing something really wrong with my design as didn't think it would take 25 posts to get it right. As you mentioned, i think i may need to abandon the HA0000 and SL000 PK idea. *bare with me one last time please* Since my last post i took your advise and created a subform in my frmNewAddress to link the address to the client and that works fine. (one-to-many relations with RI turned on) I also added code to automatically increment the SL0000 (in the subform) which updates SL_CLient_No field in tblclients. So far so good. Now the tricky part - there are other tables [tblemployment, tblinvestment etc) that link to tblclient using the PK "SL_Client_No" and i need to propogate the SL0000 field to these other tables and create the record. The tables are linked with RI turned on and cascade (something or other) turned on. tblemployment does not have an autonumber field. why is tblemployment not creating a record with PK of SL0000? Sorry to be a pest!! > >is a new record with only the SL_Client_No field updated/incremented (SL0002). > >Then i want to copy fields from existing form (Me![First_Name]) about 10 [quoted text clipped - 39 lines] > > John W. Vinson [MVP] >Now the tricky part - there are other tables [tblemployment, tblinvestment >etc) that link to tblclient using the PK "SL_Client_No" and i need to >propogate the SL0000 field to these other tables and create the record. The >tables are linked with RI turned on and cascade (something or other) turned >on. tblemployment does not have an autonumber field. why is tblemployment not >creating a record with PK of SL0000? Cascade Updates UPDATES the related table when you *change the value of* an existing primary key. It does not - and cannot, and should not - automagically create a new record. That's not its function. If you want to automagically create a new record in tblEmployment, and have it inherit the clientID - *use a Subform*. That's the appropriate tool. Use the ClientID as the master and child link field of the subform; base the mainform on the Clients table and the subform on tblEmployment. The ID field need not be calculated, need not be entered, needn't even be displayed on either form (hence a meaningless autonumber will work perfectly well as a linker). It is *NOT* necessary or appropriate to create an empty "placeholder" record in tblEmployement or tblInvestment at the time that a record is created in the Clients table. Instead, if you use a Subform, the linking field will be added at the moment that you start entering data into the tblEmployment record... not before. You've simply gotten off on the wrong track by trying to program these keys by yourself, rather than letting Access do automatically what it does very well! John W. Vinson [MVP] On Sep 27, 5:35 pm, John W. Vinson <jvinson@STOP_SPAM.WysardOfInfo.com> wrote: > I believe that the JET query engine processes frustrated outer joins more > efficiently than NOT EXISTS clauses I often get frustrated with JET's outer joins hence why I use NOT EXISTS clauses ;-) Jamie. -- and the bonus is for the most part faster query execution and you can "upsize" the query to MSSQL & Oracle much more easily and it tends to be more readable (IMnHO) Pieter > On Sep 27, 5:35 pm, John W. Vinson > <jvinson@STOP_SPAM.WysardOfInfo.com> wrote: [quoted text clipped - 7 lines] > > -- On Sep 28, 11:17 am, "Pieter Wijnen" <it.isi.llegal.to.send.unsollicited.mail.wijnen.nospam.ple...@online.replace.with.norway> wrote: > and the bonus is for the most part faster query execution > and you can "upsize" the query to MSSQL & Oracle much more easily I don't get your argument about 'upsizing' because NOT EXISTS is supported in both SQL Server and Oracle. Do you mean it foils Access's Upsizing Wizard Thing? So do CHECK constraints but I wouldn't drop table integrity constraints as a result. Have you ever come across the 'Join expression not supported' problem with OUTER JOIN in Jet? See http://tinyurl.com/yunof4. > and it tends to be more readable (IMnHO) Humble or no, that's in the eye of the beholder :) Jamie. -- no, I just meant to say you can port the SQL without (significant) changes Pieter > On Sep 28, 11:17 am, "Pieter Wijnen" > <it.isi.llegal.to.send.unsollicited.mail.wijnen.nospam.ple...@online.replace.with.norway> [quoted text clipped - 17 lines] > > -- |
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.