 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 2 / July 2007Nicely consecutive IDs within a subform
On a main form I have an ID field, call it MainID. On a subform I have an ID field, call it SubID. The forms are linked 1:M. I'd like SubID to renumber from 1 for each master record. Right now SubID is Autonumber so of course it doesn't do that. Is there any way I can make this happen? >On a main form I have an ID field, call it MainID. On a subform I have an ID >field, call it SubID. The forms are linked 1:M. I'd like SubID to renumber >from 1 for each master record. Right now SubID is Autonumber so of course it >doesn't do that. Is there any way I can make this happen? Change the datatype from Autonumber to Long Integer. In the Subform's Beforeinsert event put code like: Private Sub Form_BeforeInsert(Cancel as Integer) Me.SubID = NZ(DMax("[SubID]", "tablename", "[MainID] = " & Me.MainID)) + 1 End Sub This will look up the largest existing SubID for the currently selected MainID; if there is no such record, NZ() will return a 0. The +1 will increment to the next unused SubID. John W. Vinson [MVP] I'll give it a try in the morning. Thanks. >>On a main form I have an ID field, call it MainID. On a subform I have an >>ID [quoted text clipped - 15 lines] > > John W. Vinson [MVP] Hi All I use this to increment a subform record number Private Sub Form_BeforeInsert(Cancel As Integer) Me.PayItemID = Nz(DMax("[PayItemID]", "tblPaymentItems", "[PayID] = " & Me.PayID)) + 1 End Sub So you get MainRecord 1 LinkedRecord 1.0, 1.1, 1.2, 1.3, 1.4 and 1.5 etc This works fine. Just a thought to make it look a little better. Would it be possible to increment "letters" instead of numbers - there are never more than 15 linked records. So you may get MainRecord 1 LinkedRecord 1A, 1B, 1C, 1D and 1E etc MainRecord 2 LinkedRecord 2A, 2B, 2C, 2D and 2E etc The actual records would show A B C etc (the 2A, 2B etc are concenated)  SignatureWayne Manchester, England. > >On a main form I have an ID field, call it MainID. On a subform I have an ID > >field, call it SubID. The forms are linked 1:M. I'd like SubID to renumber [quoted text clipped - 13 lines] > > John W. Vinson [MVP] >Hi All > [quoted text clipped - 20 lines] > >The actual records would show A B C etc (the 2A, 2B etc are concenated) Good move... <concatenating separate fields that is> Try Me.PayItemID = Chr(NZ(Asc(DMax("[PayItemID]", "tblPaymentItems", "PayID = " & Me.PayID), 64) + 1)) This will return A (Chr(65)) for the first PayItemID, and the next letter alphabetically for existing letters. A trap for Z would be useful... "never" is a long time, and you could someday end up with LinkedRecord values 312[, 312\, 312] and 312^ (on beyond zebra!) John W. Vinson [MVP] Hi John, I am trying to do the same thing and have followed your advice. Whenever I create a new subform record, however, the SubID is blank. Any ideas on what could be causing this? Thanks > >On a main form I have an ID field, call it MainID. On a subform I have an ID > >field, call it SubID. The forms are linked 1:M. I'd like SubID to renumber [quoted text clipped - 13 lines] > > John W. Vinson [MVP] >Hi John, > >I am trying to do the same thing and have followed your advice. Whenever I >create a new subform record, however, the SubID is blank. Any ideas on what >could be causing this? If you're using the BeforeInsert event, the SubID will be blank until you actually enter data in some other field on the form. Is that not the case? John W. Vinson [MVP] Of course I realized that just before I read your response. :) However, when I do attempt to enter data it gives me the following 3075 run-time error: Syntax error (missing operator) in query expression "[MainID] = " Do you know what could be causing that? I entered the code exactly as you had provided. > >Hi John, > > [quoted text clipped - 6 lines] > > John W. Vinson [MVP] >Of course I realized that just before I read your response. :) > [quoted text clipped - 5 lines] >Do you know what could be causing that? I entered the code exactly as you >had provided. Do you have a control on your form named MainID? The code assumes that you do; that it's the Child Link Field of the subform, so that it's populated with a foreign key value to the main table record; and that there is at least one record in the mainform's table. John W. Vinson [MVP] I decided not to go this route because of performance problems caused by the DMax() function. If you are pulling data across a network in a multiuser environment, this kind of function is going to be very slow. So I've left my subform ID as Autonumber and even though it doesn't look quite so pretty, it works just fine. I would be willing to look at this again if someone knows of another way to achieve the objective without using a domain aggregate function. > Hi John, > [quoted text clipped - 31 lines] >> >> John W. Vinson [MVP] |
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.