Darren DICK
darrend at nimble.com.au
Wed Feb 22 00:07:01 CST 2006
Yep - that's OK - Just build it using the PK This air code =- not tested but you'll get the idea Eg Dim intMyNewCounter as integer intMyNewCounter = Left(SomeYearOnlyDate,1) & right(SomeYearOnlyDate,1) & "-" & Format(me.IncidentID,"000") Then put intMyNewCounter into some field in a table as you are planning Display it - if you must - on some form - no problem But in your searches search on the PK not this formatted Sequence Number HTH See ya Darren ------------------------------ T: 0424 696 433 -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Joe Hecht Sent: Wednesday, 22 February 2006 3:15 PM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Some vba help please That's my plan but the client is insisting on a counter type display as mentioned. Joe Hecht jmhecht at earthlink.net -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darren DICK Sent: Tuesday, February 21, 2006 8:05 PM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Some vba help please Hi Joe For what it's worth I would add a totally new column make it an autonumber Call it something Like IncidentID And make it the PK for the table And use that resulting IncidentID for all things like searches, building strings etc Darren ------------------------------ T: 0424 696 433 -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Joe Hecht Sent: Wednesday, 22 February 2006 12:48 PM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Some vba help please Charlotte I am trying to make a new field. Their current key system 1. Will cause duplicate values in 2010 2. Does not allow proper numerical sort now. Current system is first and last digit of year + next sequence number 19-01 19-02 20-01 20-112 20-02 And so on I want to build 2000-001 2000-002 2000-003 Etc Joe Hecht jmhecht at earthlink.net -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Charlotte Foust Sent: Tuesday, February 21, 2006 5:39 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Some vba help please It looks to me like you're trying to use the same NewAccidentID for all the records. Surely that isn't what you wanted is it? Charlotte -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Joe Hecht Sent: Tuesday, February 21, 2006 5:12 PM To: 'Access Developers discussion and problem solving' Subject: [AccessD] Some vba help please Private Sub Accident_Date_AfterUpdate() Me.txtNewAccidentID = Right(txtAccidentDate, 4) & " - " & DMax(NewAccidentID, [20_tblAccidentmaster]) End Sub I am trying to build a new key for each record in a table. Right(txtAccidentDate, 4) gets the year. " & right(DMax(NewAccidentID, [20_tblAccidentmaster]),3)+1 gets the next sequential number. 1. The code is not firing. I am getting no error or value. 2. Does this code look correct to my purpose. Joe Hecht jmhecht at earthlink.net -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com