473,326 Members | 2,438 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Generate Serial number using user data in Access

I need to generate a serial number by selecting drop down menu and adding data in text box using Form.

Drop down Menu :
It consist of table what would have individual ID.
EX : Table : Product1; ID = 23. Product2; ID=24 and so on.

Text box data in a form :
It could be any value "Numeric" Random.

Serial Number EX:
A-B-C-D
A= Selected Product from dropdown menu. Takes its ID from Table.
B= Calculated Value based on user data. Ex. User data = 10 converts to 0011 using some formula. Just an example. ignore calculation on this part.
C = Same as B.
D = Auto sequence from '000' to '999'.

In a Form User selects\enters data:

Product1
10
5

Serial number generated should be :
23-0011-012-000
Jun 3 '19 #1

✓ answered by twinnyfo

First, your examples, particularly for Ω values, is unclear because I can't tell if you are listing three examples or six. The user enters "1"--is the DB calculating "0.01Ω" or "1002" or "1kΩ"?

Second, that is truly immaterial for our purposes. So, if you have standardized resistances, those all could be placed into a table and you could also use a Combo Box for selection. Or if it must be calculated, then that value could be updated to a Text Box.

Third, assuming, then, that you have combo boxes for all your "unknown" parts of your Serial Number, to determine the Serial Number, it would be somewhat like this:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdAddNewProduct_Click()
  2.     Dim strSerial As String
  3.     Dim intSerial As Integer
  4.  
  5.     intSerial = _
  6.         DCount(Expr:="SerialNumber", _
  7.                Domain:="tblProducts", _
  8.                Criteria:="ProductID = " & Me.cboProductID & _
  9.                     " AND Resistance = " & Me.cboResistance & _
  10.                     " AND Percentage = " & Me.cboPercentage)
  11.  
  12.     strSerial = _
  13.         Me.cboProductID & "-" & _
  14.         Me.cboResistance & "-" & _
  15.         Me.cboPercentage & "-" & _
  16.         intSerial
  17.  
  18.     Me.txtSerialNumber = strSerial
  19.  
  20. End Sub
Keep in mind that I am simply typing this code in, so it may require troubleshooting and you will have to adjust tables names, variables and controls to suit your needs.

Hope this hepps!

9 3602
twinnyfo
3,653 Expert Mod 2GB
Dhrumilen,

Welcome to Bytes!

On the surface, the solution is really as simple as using concaatenation for the final string of your Serial number.

There are some serious challenges with this question, though, before we can give you a truly meaningful response.

Primarily, what are your "B" and "C" values calculated from? Without knowing this, it becomes difficult to determine what the following sequence ("D") will be.

We can continue to work through this question, but we will need more details to give you any type of workable solution.

Hope this hepps!
Jun 3 '19 #2
It would be some sort of formula, to convert any value to 4 digit and 2/3 digit number.

Example of B = User enters 1. Calculates 1002. based on 10^n. Its not fixed yet though.
Ex.
0001 --- 0.01Ω 1002 --- 1kΩ
0010 --- 0.1Ω 1232 --- 1.23kΩ
1000 --- 10Ω 1003 --- 10kΩ

For C : I believe this can be setup in Table and to list for user select 0.1, 0.5 ……… to give 1, 2 etc. ID --- User Data %.

1 --- 0.1% 2 --- 0.5% 3 --- 1%
4 --- 2% 5 ---5% 6 --- 10%
7 --- 15% 8 --- 20% 9 --- >20%
Jun 3 '19 #3
twinnyfo
3,653 Expert Mod 2GB
First, your examples, particularly for Ω values, is unclear because I can't tell if you are listing three examples or six. The user enters "1"--is the DB calculating "0.01Ω" or "1002" or "1kΩ"?

Second, that is truly immaterial for our purposes. So, if you have standardized resistances, those all could be placed into a table and you could also use a Combo Box for selection. Or if it must be calculated, then that value could be updated to a Text Box.

Third, assuming, then, that you have combo boxes for all your "unknown" parts of your Serial Number, to determine the Serial Number, it would be somewhat like this:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdAddNewProduct_Click()
  2.     Dim strSerial As String
  3.     Dim intSerial As Integer
  4.  
  5.     intSerial = _
  6.         DCount(Expr:="SerialNumber", _
  7.                Domain:="tblProducts", _
  8.                Criteria:="ProductID = " & Me.cboProductID & _
  9.                     " AND Resistance = " & Me.cboResistance & _
  10.                     " AND Percentage = " & Me.cboPercentage)
  11.  
  12.     strSerial = _
  13.         Me.cboProductID & "-" & _
  14.         Me.cboResistance & "-" & _
  15.         Me.cboPercentage & "-" & _
  16.         intSerial
  17.  
  18.     Me.txtSerialNumber = strSerial
  19.  
  20. End Sub
Keep in mind that I am simply typing this code in, so it may require troubleshooting and you will have to adjust tables names, variables and controls to suit your needs.

Hope this hepps!
Jun 3 '19 #4
Hi Twinnyfo,

The user enters "1"--is the DB calculating "1002"

kΩ is unit.

So that would be 1 kΩ.
As I mentioned not fixed on formula for it yet.

And I do agree with you I could make it Combo Box. But This Ranges from 0 to 1000 kΩ incrementing in points.

Plus This is only about Resistors, there are various other type of components. The reason why I choose to use DB Calculation for it.

Let me Implement, as you guided. You have already given me the way. Thank you for your help. I will feed back you once implemented.
Jun 3 '19 #5
twinnyfo
3,653 Expert Mod 2GB
Your explanation makes sense. I just was not sure of how the calculations worked. Best of luck!

Let us know how you get along!
Jun 3 '19 #6
Expand|Select|Wrap|Line Numbers
  1. Private Sub ADD_Click()
  2.    Dim strSerial As String
  3.    Dim intSerial As Integer
  4.  
  5.    intSerial = _
  6.        DCount(PartNumber1:="partNumber1", _
  7.               Domain:="Resistors", _
  8.               Criteria:="RES Type = " & Me.cboResType & _
  9.                    " AND Tolerance = " & Me.cboResTol & _
  10.                    " AND Value = " & Me.Value)
  11.  
  12.    strSerial = _
  13.        Me.cboResType & "-" & _
  14.        Me.cboResTol & "-" & _
  15.        Me.Value & "-" & _
  16.        intSerial
  17.  
  18.    Me.PartNumber = strSerial
  19.  
  20. End Sub
I get

Run Time Error '3075'

Expression error. It does take value selected in consideration, but reports error. I tried adding expression. but didn't work. I tried to add field name from Resistor table.
Jun 10 '19 #7
NeoPa
32,556 Expert Mod 16PB
In line #6 you refer to the Expr parameter as PartNumber1 instead. Not only that, your Criteria parameter is badly formed. You should prepare and check this as a first step :
Expand|Select|Wrap|Line Numbers
  1. Dim strWhere As String
  2.  
  3. With Me
  4.     strWhere = "([RES Type]=%RT) AND " _
  5.              & "([Tolerance]=%TL) AND " _
  6.              & "([Value]=%VL)"
  7.     strWhere = Replace(strWhere, "%RT", .cboResType)
  8.     strWhere = Replace(strWhere, "%TL", .cboResTol)
  9.     strWhere = Replace(strWhere, "%VL", .Value)
  10. End With
Now, there are some fairly obvious problems with this. Me.Value makes no sense. That would be the value of your Form object :-s All values added to this string, just as in your original code, are treated as numeric literals. That may be appropriate but seems unlikely from the names. If strings or dates are required then we have a different approach required (See Quotes (') and Double-Quotes (") - Where and When to use them).

From this point you can observe the value of strWhere and see if it's appropriate and matches what it is you actually want to do. If not, and I suspect not at this point, then at least you know where to look in order to fix it.
Jun 10 '19 #8
twinnyfo
3,653 Expert Mod 2GB
Hey NeoPa! Thanks for fixing my code. I was air-typing, and sometimes I get it wrong (usually). Thanks again for keeping me straight!

:-)
Jun 10 '19 #9
NeoPa
32,556 Expert Mod 16PB
A tiny typo is all Twinny. Such mistakes are very rare in fact. No more frequent than similar ones from my posts I expect.
Jun 11 '19 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: Roxie Aho | last post by:
Using Access 2003. Linked table from SQL Server has phone as 1234567890. Query has phone formatted as (@@@) @@@-@@@@, displaying (123) 456-7890. Data access page is based on the query. Phone...
0
by: hansiman | last post by:
How do I read a stored proc return value ( return(@return_value) ) using MS Data Access Application Block. Normally I use: Dim conn As SqlConnection = New SqlConnection("connectionstring")...
0
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...
2
by: comtec | last post by:
Hello All people I want to read the volume serial number of the partition of HDD , and put it in variable . it's possible thanks in advance
1
by: cmrhema | last post by:
A big hello to everyone I have dropped on a gridview on asp.net coding done is C# 2005. Here I display the records when a certain condition satisfies. eg. I want to display the details of all...
0
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...
3
by: jeenajos | last post by:
Hi all, I need to insert a column in a table as sno but it should generated numbers from 1,2,3,.....It is similar to ms excel's auto number.Tel me a way to do it in SQL Express. Cheers Jeen
3
swarnalaxmi
by: swarnalaxmi | last post by:
to display serial number for rows in gridview
7
by: SlingerJM | last post by:
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...
2
by: muhammadayub77 | last post by:
Hi I am using ms access 2013. Often in my organization an excel sheet with the data needs to be imported in ms access for official use. While creating reports before that i want a table to have a...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.