Nicely 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? 


0
David
5/1/2007 12:29:45 AM
access 16762 articles. 3 followers. Follow

7 Replies
592 Views

Similar Articles

[PageSpeed] 21

On Mon, 30 Apr 2007 17:29:45 -0700, "David Portwood" <Mondrogan@yahoo.com>
wrote:

>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]
0
John
5/1/2007 1:14:36 AM
I'll give it a try in the morning. Thanks.

"John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message 
news:pv4d335tstn1ehiv5mdimtih31a6dcu61j@4ax.com...
> On Mon, 30 Apr 2007 17:29:45 -0700, "David Portwood" <Mondrogan@yahoo.com>
> wrote:
>
>>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] 


0
David
5/1/2007 5:24:55 AM
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)




-- 
Wayne
Manchester, England.



"John W. Vinson" wrote:

> On Mon, 30 Apr 2007 17:29:45 -0700, "David Portwood" <Mondrogan@yahoo.com>
> wrote:
> 
> >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]
> 
0
Utf
5/16/2007 4:54:01 PM
On Wed, 16 May 2007 09:54:01 -0700, Wayne-I-M
<WayneIM@discussions.microsoft.com> wrote:

>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)

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]
0
John
5/18/2007 12:55:18 AM
On Mon, 23 Jul 2007 08:32:05 -0700, rwilliams616
<rwilliams616@discussions.microsoft.com> wrote:

>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]
0
John
7/23/2007 6:02:42 PM
On Mon, 23 Jul 2007 11:26:01 -0700, rwilliams616
<rwilliams616@discussions.microsoft.com> wrote:

>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.

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]
0
John
7/23/2007 7:19:23 PM
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.

"rwilliams616" <rwilliams616@discussions.microsoft.com> wrote in message 
news:49CAF8DC-FE87-4BBC-92CC-791BFCFDBDAD@microsoft.com...
> 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
>
> "John W. Vinson" wrote:
>
>> On Mon, 30 Apr 2007 17:29:45 -0700, "David Portwood" 
>> <Mondrogan@yahoo.com>
>> wrote:
>>
>> >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]
>> 


0
David
7/24/2007 3:31:52 AM
Reply:

Similar Artilces:

subform processing problem
I have a form bound to my Issues table. The form contains a subform bound to my client table. Relationship Issues to Client is many to 1. The subform retrieves data correctly for existing issues and clients. However when I am creating a new Issue, I cant figure out how to populate the client. For a new Issue, the subform appears completely gray. When I click my button to choose a client the code fails with 'you cannot assign a value to this object' - when I attempt to populate the subform fields. When this button is clicked, I first populate the linking field in the main form, thi...

Ranking scores within a subset of records
I need to rank the scores each judge gives a competitor in an event. I will be using a query based on 4 or 5 tables but have created a table with the fields Ineed to make the SQL easier to follow. what I have is Twirler Entry ID Score Judge Rank Savannah Miller 8499 96.00 A Savannah Miller 8499 89.00 B Savannah Miller 8499 82.00 C Haley Schlattmann 9039 86.50 B Haley Schlattmann 9039 78.50 A Ashley Meyer 9236 86.70 A Ashley Meyer 9236 85.90 C Ashley Meyer 9236 80.90 B Jacy Alexander 9526 87.50 B Jacy Alexander 9526 85.00 A This is the SQL I have sta...

Lookup & max, or match & offset within an array
I'm trying to construct a formula that does the following: Finds the maximum value (a date, if that matters) within row 2 of sheet 1 that corresponds to a cell in row 1 (in the same column as the max value), which in turn matches a value in column A of sheet 2. See e.g. below: I'd like a formula which searches for "#" and finds "March." I am having difficulty combining the hlookup with a max function, and similar difficulty combining the match and offset functions as an alternative. Sheet 1 A B C D 1 * # # @ 2 Jan Feb March April Sheet 2 ...

Remove line break within cell
I have a document that was provided to me which I have to make some changes to and save as a text file for importing into a database. However, there are breaks after each line signified by a small square. I want to remove all those breaks because when saving as a txt file, it thinks it is a real break. How can I remove those crazy little squares. You may need the drawing toolbar to select them. Right click in any toolbar and select Drawing. Right click in a toolbar and select CUSTOMIZE, Categories DRAWING, Drag the "Select multiple objects" command to the drawing tool...

Posted Dates/User Ids Change when Payment is Applied
The posted date for a transaction was originally 3/11/05 (that was when AP actually posted the transaction in GP). Once we issued a check for this invoice, the posted date changed to the date we issued the check (3/15/05). The same thing happens with users who posted. The person who posts the payment replaces the person who posts the transaction. Why do you have "user who posted" for both invoices and payments yet only the person who posts the payment is the user that is displayed for both once the payment has been posted? That does not seem logical. Each batch should re...

Is it possible to view Excel split screen by tab within same file
I would like to view split screen tabs within the same Excel workbook. Is this possible? Hi SS. Go to Window - Arrange - and select the view you would like. It shows all tabs. Minimize or close the ones you don't want. I find the vertical view the easiest to use. HTH -- Sincerely, Michael Colvin "SS" wrote: > I would like to view split screen tabs within the same Excel workbook. Is > this possible? Window|new window Window|Arrange (horizontal/vertical/whatever you want) Check windows of activeworkbook (if you only want that one workbook) You'll see som...

User modifying personal info in pab within outlook
I have Windows 2k Ad and exchange 2k enterprise, I want users in my company to be able to modify only their personal info in personal address book within outlook, how can i do this? regards Oswaldo. Oswaldo. wrote: > I have Windows 2k Ad and exchange 2k enterprise, I want users in my > company to be able to modify only their personal info in personal > address book within outlook, how can i do this? I'm not quite sure what you're asking. Firstly, what version of Outlook are you using? With OL2000 and up, there is no need to use a .pab - just use the contacts folder. Speci...

Event ID 4000 Disable SMTP Logging under IIS
I have SMTP Logging for some reason on my Mail Gateway which runs IIS SMTP and then sends the mail to Exchange. Does anyone know how to turn down logging on IIS SMTP. The option for logging in not checked under properties Thanks Norman ...

Subform producing error...?
Folks I am trying to sum a "simple" expression in a text box on a sub form. It is producing #Error# - any ideas what I am doing wrong, I presume something simple and obvious, but I can't spot it. Expression is: =Sum(IIf([Date Paid]>=[Forms]![01 Anna Start]![2 Months Start],[Amount],0)) [Forms]![01 Anna Start]![2 Months Start] is calculated using this expression on the main form [01 Anna Start], and is producing the right date: =DateSerial(Year(Date()),Month(Date())-2,1) All responses gratefully received! Russell. -- Message posted via AccessMonster...

SMTPSVC Event ID: 2012 and 2013
I am still getting these errors/warning in my system log after changing the SMTPSVC to use only TCP. What else can I do to make these warnings or errors go away. This is on a Windows 2003/Exchange 2003 system. the command I use to make this change is: SMTPMD set smtpsvc/useTcpDns 1 Is these correct, I have been able to verify this. -- Eric Sabo NT Administrator I am getting these errors every hour on the hour. I have tried everything ppl have posted, nothing works.... "Sabo, Eric" <sabo_e@cup.edu> wrote in message news:eAPOixSMEHA.1388@TK2MSFTNGP09.phx.gbl......

Vendor ID
SBF Uses Vendor ID and Quickbooks does not. Any Idea anyone on how Vendor ID is generated in SBF after the Import Export Wizard is run. I know that it takes the First Word in the Vendor Name but it concatenates it with a number which I am not aware of as to how its generated. There is a newsgroup specifically for Small Business Financials. Have you posted this question there? -- Charles Allen, MVP "Ki" wrote: > SBF Uses Vendor ID and Quickbooks does not. Any Idea anyone on how > Vendor ID is generated in SBF after the Import Export Wizard is run. I > know that it ta...

Product ID look for
We have completed a Scribe integraton and we are working with the product section of CRM. When we do a search CRM only "looks for" the product name not the product ID. The product ID is where we keep all of the part numbers we need to find. Any suggestion of how to set the search to look in the product ID field rather than Products name? On Tue, 4 Dec 2007 11:23:02 -0800, Ali 123eDistribution.com <Ali123eDistributioncom@discussions.microsoft.com> wrote: >We have completed a Scribe integraton and we are working with the product >section of CRM. When we do a sea...

Breaking up data within a cell
Hello everyone, In Excel 2003 WinXp I have several thousand lines of data which have imported as - (as an example) Cell A1 B2342, 12,32223, JFGK, 1 Please can you advise me how to make the above line become 5 separate cells (a1:15) rather than one?? Thanks in anticipation. John North Yorkshire UK Should read a1:a5 not as shown! Sorry "John P" <jp@jpwebs.co.uk> wrote in message news:%23I7ui2oPEHA.3172@TK2MSFTNGP10.phx.gbl... > Hello everyone, > > In Excel 2003 WinXp > > I have several thousand lines of data which have imported as - (as an > example) ...

event id 1202
Hi the following error is appearing on our server: Source:scecli Event ID:1202 Security policies are propagated with warning 0x4b8.An extended error has occurred. Does anybody know what might have caused this and how it can be eradicated? Thanks Patrick Here is an article that may assist in trouble shooting the issue: Q324383 Troubleshooting SCECLI 1202 Events -- Tyrone Cowart Microsoft PSS Please do not send email directly to this alias. This alias is for newsgroup purposes only. This posting is provided "AS IS" with no warranties, and confers no rights. "Patrick"...

what does an "invalid sam id" meen in Outlook 2007?
I'm trying to import a .csv to Outlook. I keep getting "invalid sam id" message. the error with act3.sam translator message appears also. SAM = Security Access Manager and is not an Outlook related issue. Invalid SAM ID indicates a connection failure with your servers - something changed in your environment which made the SAM ID for the machine you are using invalid between the server and workstation. Are you using BCM? -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.c...

Continuous subform behavior
Greetings all, I have a continuous subform on an unbound form that I want to be able to edit records. When I click into to a text box on the subform and begin to edit the text, I can edit, but the original text gets deleted as soon as I start typing. The record is not deleted, just the data in the field linked to the text box. The form's data entry and allows additions are set to "no" and allow edits and deletions are set to "yes". I would like to be able to click into the textbox and freely edit without the original data being deleted. Any ideas what...

MTS-ID
I have a user that is trying to send an email with three small Excel spreadsheets. An undeliverable email is returned with the following message. "The recipient name is not recognized The MTS-ID of the original message is: c=US;a= ; p= Organization name; l=server name - 031212195939A-11183" I am unclear as to what this message means and how I can resolve the issue so that the email is delivered to the intended recipient. I am on Exchange 5.5, SP4. Thanks. Emily "Emily Blum" <anonymous@discussions.microsoft.com> wrote: >I have a user that is trying to...

Reports Recordsource from subform datasheet (filtered)
Hi all, I have a form with a datasheet (as subform). Of course the user can sort/filter the datasheet. I also have a button to run a report on the form. What I would like to do is have the report print with the same data as the subform datasheet. I need to pass the filter and sort information to the report from the form. Somebody jog me please. Thanks in advance, Bonnie ...

Show Record In Subform If Either Of Two Fields Match frmComboBox S
I am having trouble figuring out how to accomplish the following in Access 2003: I have two queries as the basis for my form. 1. One with a set of baseball teams in a league. Each record is one team, and the key is TeamID. 2. One with the league's schedule. Each record is one scheduled game. Each record has a TeamID in the VisTeam field, and another TeamID in the HomeTeam field. I want to set up a form and subform where: A. The main form has a combo box to select one of the teams. B. The subform shows all the games and only the games where that team is scheduled as either VisTeam or...

Password Protect Word from within Access 2003
I have inherited an Access application that uses Word templates for letters/invoices. My client now wants the resulting documents password protected so they cannot be altered once generated. Any help would be appreciated. JMCS Are you wanting to password protect the templates or the documents generated from them? If its the later, I would go to a process where once the document is created in Word you convert the document to a pdf as doing so will lock down changes to it by default. The other alternative is to snoop around the Word newsgroup about automatically saving a doc...

ID no: c1036e71
Has anyone seen this error in Exchange 2000? Exchange System Manager: The Queue Viewer Snapin is unable to retrieve an interfact to the queues. The queue Viewer Snapin will be disabled until you refresh it. Check the event log and verify that the approporiate services are running ID no: c1036e71 Exchange System Manager I have searched MS and found nothing, searched google and found some MSDN class test questions and it states to restart the MTA Stacks, which i have done with no luck in changing the outcome. This server has gotten currupt and i am trying to limp it along for a few ...

Message Id and Message Journaling
Hi, Background: We have outsourced message archiving. As part of this process we point all of our inbound and outbound traffic to this firm. To capture internal mails we have to turn on message journaling. This then creates an issue because all external mails will show up at the archiver twice. Once because they are our "Bridge Head Server" and once by the journaling mechanism. Since you are charged by space used this isn't necessarily a good thing. They have a process in place to remove the duplicates by removing all message ids that are the same. (has solved thi...

Event ID :7024
Hi, Who can help me to solve this error? When I want to strat The Information Store service of Exchange 5.5 server on a windows2k server machine,it couldn't start and in the eventlog there is this event id 7024 and this description:"The Microsoft Exchange Information Store service terminated with service-specific error 4294966767." Could you please help me? It's so important for me my Echange server was down. Thank you for the help. -------- Regards Hi Charisma, Take a look at the following article, it may help to shed a little light on your situation. 246272 XADM: Se...

Event ID 509??
Anyone know what this means?? Source: ESE Event Id: 509 Information Store (1234) First Storage Group: A request to read from the file C:\Progfile\exchsrvr\mdbdata\priv1.edb at offset 3687149568 (0x00000000dbc57000) for 32768 (0x00008000) bytes succeeded, but took an abnormally long time (102 seconds) to be serviced by the OS. In addition, 20 other I/O requests to this file have also taken an abnormally long time to be serviced since the last message regarding this problem was posted 107 seconds ago. This problem is likely due to faulty hardware. Please contact your hardare vend...

Buyer ID and Buyer Description
I would like to add the Buyer Description field to our POP Blank Form. The ID is the person's name, but the Buyer Description is used to sort items when received. Any suggestions? Yes. Unfortunately, the developers did not create a relationship between the POP HDR table and the Buyer table. You will need to add a relationship within Report Writer by clicking the Tables button. You will find the POP_PO table. Open it. Click Relationships. Click New. Click the ellipsis button to the right of Secondary Table. Select Buyer Master. Select POP_Buyer_IDxID as the table key. Sel...