Hi Everyone,
I know this topic has been posted a lot, so I apologize for having to post another one, but the answers I've been finding seem to be more advanced than I know how to use.
I have a table called [MOC Tracking Log], with a field named [MOC ID Number]. The general format for this number is supposed to be (yyyy-0001). The last 4 digits are supposed to increment each time a new record is created (ie, 2007-0002, 2007-0003 etc). I have found some useful code (i think) to do the incrementation part itself. However:
1) I don't know how to set the original format to be (yyyy-0001)
2) I don't know where to put the code.
So far, I have this: -
Private Sub Form_Load()
-
-
Format (Date = "yyyy")
-
-
-
Dim tdf As TableDef
-
Dim fld As Field
-
Dim num As Double
-
-
num = "0001"
-
-
Set tdf = CurrentDb.TableDefs("[MOC Tracking Log]")
-
Set fld = tdf.Fields("[MOC ID Number]")
-
-
fld.Value = (Date & "-" & num)
-
-
fld = ("SELECT Max(Right([MOC ID Number])) As MaxNum FROM [MOC Tracking Log]")
-
-
num = fld!MaxNum + 1
-
-
End Sub
-
which is basically a random jumble of code snippets I've found and can only somewhat make sense of.
Any clarification, or links to understandable tutorial sites would be greatly appreciated.
Thanks,
Tiffany
5 3455
Hi Everyone,
I know this topic has been posted a lot, so I apologize for having to post another one, but the answers I've been finding seem to be more advanced than I know how to use.
I have a table called [MOC Tracking Log], with a field named [MOC ID Number]. The general format for this number is supposed to be (yyyy-0001). The last 4 digits are supposed to increment each time a new record is created (ie, 2007-0002, 2007-0003 etc). I have found some useful code (i think) to do the incrementation part itself. However:
1) I don't know how to set the original format to be (yyyy-0001)
2) I don't know where to put the code.
So far, I have this: -
Private Sub Form_Load()
-
-
Format (Date = "yyyy")
-
-
-
Dim tdf As TableDef
-
Dim fld As Field
-
Dim num As Double
-
-
num = "0001"
-
-
Set tdf = CurrentDb.TableDefs("[MOC Tracking Log]")
-
Set fld = tdf.Fields("[MOC ID Number]")
-
-
fld.Value = (Date & "-" & num)
-
-
fld = ("SELECT Max(Right([MOC ID Number])) As MaxNum FROM [MOC Tracking Log]")
-
-
num = fld!MaxNum + 1
-
-
End Sub
-
which is basically a random jumble of code snippets I've found and can only somewhat make sense of.
Any clarification, or links to understandable tutorial sites would be greatly appreciated.
Thanks,
Tiffany
Form load initially, then you would want this each time you started a new record. -
dim MyCount as long
-
if dcount("*","[MOC Tracking Log]")=0 then
-
me.[MOC ID Number].value = Format(Date(),"yyyy") & "-0001"
-
else
-
MyCount = myval = Right(DMax("[MOC ID Number]", "[MOC Tracking Log]"), 4)
-
me.[MOC ID Number].value = Format(Date(),"yyyy") & mycount
-
end if
-
-
J
hyperpau 184
Recognized Expert New Member
Hi Everyone,
I know this topic has been posted a lot, so I apologize for having to post another one, but the answers I've been finding seem to be more advanced than I know how to use.
I have a table called [MOC Tracking Log], with a field named [MOC ID Number]. The general format for this number is supposed to be (yyyy-0001). The last 4 digits are supposed to increment each time a new record is created (ie, 2007-0002, 2007-0003 etc). I have found some useful code (i think) to do the incrementation part itself. However:
1) I don't know how to set the original format to be (yyyy-0001)
2) I don't know where to put the code.
So far, I have this: -
Private Sub Form_Load()
-
-
Format (Date = "yyyy")
-
-
-
Dim tdf As TableDef
-
Dim fld As Field
-
Dim num As Double
-
-
num = "0001"
-
-
Set tdf = CurrentDb.TableDefs("[MOC Tracking Log]")
-
Set fld = tdf.Fields("[MOC ID Number]")
-
-
fld.Value = (Date & "-" & num)
-
-
fld = ("SELECT Max(Right([MOC ID Number])) As MaxNum FROM [MOC Tracking Log]")
-
-
num = fld!MaxNum + 1
-
-
End Sub
-
which is basically a random jumble of code snippets I've found and can only somewhat make sense of.
Any clarification, or links to understandable tutorial sites would be greatly appreciated.
Thanks,
Tiffany
Change this: - fld.Value = (Date & "-" & num)
-
to this - fld.Value = (Format(Date, "yyyy") & "-" & num)
-
-
dim MyCount as long
-
if dcount("*","[MOC Tracking Log]")=0 then
-
me.[MOC ID Number].value = Format(Date(),"yyyy") & "-0001"
-
else
-
MyCount = myval = Right(DMax("[MOC ID Number]", "[MOC Tracking Log]"), 4)
-
me.[MOC ID Number].value = Format(Date(),"yyyy") & mycount
-
end if
-
Thanks for the help, it seems to be working so far, with the exception of a minor compiler issue. Could you also tell me what the proper format is to replace "Me." for a table and field?
Thanks,
Tiffany
Thanks for the help, it seems to be working so far, with the exception of a minor compiler issue. Could you also tell me what the proper format is to replace "Me." for a table and field?
Thanks,
Tiffany
Hey,
replacing as in putting a variable in place of??
forms(sForm).fo rm(sField).valu e for a simple form. Subforms are different.
Ok thanks, I'll try that
Tiffany
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Amy |
last post by:
I'm trying to add an autoincrementing id to a table based on an
existing field Name, but Name has duplicated records. How can I do
that in ACCESS?
Thanks.
Amy
|
by: HD |
last post by:
Hello. For the following, I would appreciate if anyone could tell me:
if it can be done, how it might done, and/or what search terms I could
use to find the solution myself.
I would like to create a database in which _adding a new record
automatically creates a new field in the table_ (ideally, deleting a
record would also delete the field that was created when that record
was added). Thus, the number of fields in the table would...
|
by: Megan |
last post by:
Hi everybody-
I have 2 tables, Hearings and Rulings. Both have primary keys called,
CaseID, that are autonumbers. I don't want both tables to have the
same autonumber. For example, if Hearings has 55, then I want Rulings
to have a number greater than 55. Two or more Hearings may be entered
before a Ruling is entered. For example, Hearings with CaseIDs= 55,
56, and 57 may be entered before a Ruling or vice versa. There is no
definite...
|
by: ben.werdmuller |
last post by:
Hi,
Is there an easy way in ASP/VBscript to grab an autonumber (primary
key) field just after an SQL insert? This is probably easy, but I'm
stuck ..
Cheers.
|
by: gtwannabe |
last post by:
I'm having a problem with a form that uses AutoNumber as the primary
key. I have an Abort button to delete the current record and close the
form. If AutoNumber is assigned, the code executes a SQL statement
that deletes the current record. I need to be able to detect when
AutoNumber is unassigned (a new blank record) so that I can simply
close the form without running the SQL delete statement.
Unfortunately, no tests I can think of...
| |
by: dympna |
last post by:
HI I am trying to create a number field that will create a unique
incremented number each time a record is added to the database via a
form. but I want that number to be in sequence and start from my
specified number.
I have tried the help in access and used the autonumber and the append
query to assign a specific number for the autonumber to start from,
but the problem that i get is when I add more than one record, the
next auto number...
|
by: Tom_F |
last post by:
To comp.databases.ms-access --
I just discovered, to my more than mild dismay, that some tables in my
Microsoft Access 2003 database have duplicate numbers in the
"AutoNumber" field. (Field Size is set to "Long Integer", and New
Values is set to "Increment".) I know that an old version of the Jet
database engine can cause this problem, but my version of msjet40.dll
is 4.0.8618.0, which is supposedly bug-free in this respect. I am...
|
by: asandiego |
last post by:
Hey guys, this is my first post here but have been checking this site a lot for anything I need.
I hope someone can lead me to what I should do or just an idea to what can be done.
What I'm trying to do:
I have a Form that has a subform. My primary form has a field that autonumber and this field has a one to many relationship to a field in my subform.
The subform has another field that I want to AutoNumber or increment number BUT has to go...
|
by: bkberg05 |
last post by:
Hi - I have a field called Sequence on a continuous subform. The Sequence field belongs to a table called Project_Draw. This table has many 'Draw' records for each 'Project' record. So then the subform is tied to the main form by Project_ID.
I want the Sequence field to automatically increment each time I add a new record. I can't use the autonumber as I need to start a new sequence for each new Project_ID.
I've tried putting a max()...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |