Real new to Access 2010. Condition Report system identifies new entries as YY-####, ex, 15-0031. I need a field "CR Number" to automatically produce this format for a new record. This then becomes the main (controlling) field for all forms and reports. I started with "CR Number" as an autonumber with a prefix in 'format' "15-"0000. However, when query for forms, would not accept prefix. Condition Report 15-0009 would be queried only if the user entered "9". The user will be entering the whole CR Number: 15-0009. So I need a field which automatically generates a new number in the format yy-#### that I can query. Please be as specific as you can with answer. Where to type information etc. I have read some solutions for others and tried them but not very helpful. I have tried typing in "Field Properties", Macros, Expressions, etc. Nothing works. I need to know what to type, and where to type it. Please help if can.
Thanks,
Joe
I spent three days searching the internet for a solution to my problem. I had some help from our IT expert and I now have a solution. First, let me repeat what I needed. I needed a Condition Report(CR) Number which will drive my database. I created a field "CR Number". I needed information in that field to be prefixed with the current year, by last two digits, a hyphen, and a sequencing number that reverts back to 0001 at the beginning of each year. For example, I needed a number like: 15-0032 and the first CR of next year would be 16-0001.
There are solutions shown on this site, but not a lot of directions for someone like me with little to no coding or Access experience so I am going to detail not only the solution, but "how to" employ it in MS Access 2010.
I created a table with a field "CR Number". In the "Datasheet View", I clicked on the field/column for "CR Number". I then went to the ribbon under "Table Tools" and clicked on the ribbon "Table". Icons appeared on that ribbon and I clicked on the icon "Before Change". This is MS Access' Macro developer. Then I worked through the helping screens to insert the following code: - If [IsInsert] Then
-
SetLocalVar
-
Name YearPrefix
-
Expression = Format(Date(), "yy")
-
-
SetLocalVar
-
Name nextSeq
-
Expression = 1
-
-
Look Up A Record In
-
SELECT z.[CR Number] FROM [CR Database] AS z ORDER BY z.[CR Number] DESC;
-
Where Condition = [z].[CR Number] Like [YearPrefix] & "-*"
-
Alias z
-
-
SetLocalVar
-
Name nextSeq
-
Expression = Val(Right([z].[CR Number],4)) +1
-
-
SetField
-
Name [CR Number]
-
Value = [YearPrefix] & "-" & Format([nextSeq], "0000")
-
End If
When the screens pop up and you fill in the lines, some of what you are typing (IsInsert, SetLocalVar, Look Up A Record In, SetField, etc.) automatically populates or appears in drop down menus.
I hope this helps anyone else in this situation.
I forgot to mention that the "CR Number" Field in my table was set as Data Type: Text
One last thing,
The name of my Table is "CR Database".
7 1821
Yes, I saw those threads, tried their solutions, esp NeoPa's, did not work for me.
zmbd 5,501
Expert Mod 4TB
Would you please post your SQL/VBA that you attempted along with an example/explanation of what results you were obtaining?
Please format the code/script using the [CODE/] button in the toolbar.
NeoPa 32,556
Expert Mod 16PB
Hey Joe. Why don't you tell us exactly what you used as an attempt to follow the instructions I posted, and also what happened (as not working can describe hundreds of different results). This gives us some information from which to work to deduce what might be going wrong in your case. We know it works generally. What we have no idea about is in what ways your attempts are different from the working ones.
I spent three days searching the internet for a solution to my problem. I had some help from our IT expert and I now have a solution. First, let me repeat what I needed. I needed a Condition Report(CR) Number which will drive my database. I created a field "CR Number". I needed information in that field to be prefixed with the current year, by last two digits, a hyphen, and a sequencing number that reverts back to 0001 at the beginning of each year. For example, I needed a number like: 15-0032 and the first CR of next year would be 16-0001.
There are solutions shown on this site, but not a lot of directions for someone like me with little to no coding or Access experience so I am going to detail not only the solution, but "how to" employ it in MS Access 2010.
I created a table with a field "CR Number". In the "Datasheet View", I clicked on the field/column for "CR Number". I then went to the ribbon under "Table Tools" and clicked on the ribbon "Table". Icons appeared on that ribbon and I clicked on the icon "Before Change". This is MS Access' Macro developer. Then I worked through the helping screens to insert the following code: - If [IsInsert] Then
-
SetLocalVar
-
Name YearPrefix
-
Expression = Format(Date(), "yy")
-
-
SetLocalVar
-
Name nextSeq
-
Expression = 1
-
-
Look Up A Record In
-
SELECT z.[CR Number] FROM [CR Database] AS z ORDER BY z.[CR Number] DESC;
-
Where Condition = [z].[CR Number] Like [YearPrefix] & "-*"
-
Alias z
-
-
SetLocalVar
-
Name nextSeq
-
Expression = Val(Right([z].[CR Number],4)) +1
-
-
SetField
-
Name [CR Number]
-
Value = [YearPrefix] & "-" & Format([nextSeq], "0000")
-
End If
When the screens pop up and you fill in the lines, some of what you are typing (IsInsert, SetLocalVar, Look Up A Record In, SetField, etc.) automatically populates or appears in drop down menus.
I hope this helps anyone else in this situation.
I forgot to mention that the "CR Number" Field in my table was set as Data Type: Text
One last thing,
The name of my Table is "CR Database".
zmbd 5,501
Expert Mod 4TB
Very nice to have a macro version of this.
Also an interesting use of the table-level action macros.
We more than likely would have pointed you towards a VBA or SQL version.
Note that I used the [CODE/] format around your posted script.. it is required for all posted script... and has the benefit that tables of text and the indenting is retained.
@zmbd
From SlingerJM:
I saw the [CODE/], tried it but could not figure it out. So simply did it the old fashion way. I am not a code writer so I doubt I will be submitting anything again; however, this one perplexed me and as much searching has I have done, I saw there were many others in same situation. So since I had a solution that worked, I thought I would share it in the only way I knew how. Thanks for your help on making it correct for the forum
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Job Lot |
last post by:
How can I retrieve the Motherboard, hard drive, NIC Serial Number Using vb.net.
thanx
|
by: Klaus Bonadt |
last post by:
In order to protect software from being copied without licence, I would like
to use something like a key, which fits only to the current system. The
serial number of the CPU or the current...
|
by: Lauren Wilson |
last post by:
Discovered this interesting comment on MSDN:
"To programmatically obtain the hard disk's serial number that the
manufacturer assigns, use the Windows Management Instrumentation (WMI)...
|
by: mzaiady |
last post by:
I need to know how to get the monitor, motherboard and other hardware information and serial number using C,
i can do it with WMI in windows but it giveme just what installed and defalut,
i need...
|
by: preethaAjayan |
last post by:
Could anybody please help me with a piece of code to get hard disk's serial number using C#, not volume serial number, actual number that manufactures give to hard disks.
|
by: =?Utf-8?B?VE5Db2Rlcg==?= |
last post by:
What function do I use to retrieve a drive's serial number using VC++ dot net
2.0?
Thanks..
|
by: steinerh |
last post by:
Hi,
I am using FSO (VB6.0 proff) to retrieve the serial number of my hardisk. Comparing the number with what WIN XP returns and also what I retrieve using the dir command in DOS is really...
|
by: KrisConner |
last post by:
Hi All,
I have developed an application for my Pocket PC and would like to bind it to the device id. Any ideas how I can get the serial number using the vb.net compact framework?
Any help would...
|
by: Scott Kaempfe |
last post by:
I need to identify parts we buy at our plant. I want to give each a unique serial number that is based on the date. We may have multiple parts on a given date so I want to have the unique serial...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |