By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,969 Members | 1,579 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,969 IT Pros & Developers. It's quick & easy.

Generate Serial number using user data in Access

P: 4
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
2 Weeks Ago #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!

Share this Question
Share on Google+
9 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,184
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!
2 Weeks Ago #2

P: 4
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%
2 Weeks Ago #3

twinnyfo
Expert Mod 2.5K+
P: 3,184
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!
2 Weeks Ago #4

P: 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.
2 Weeks Ago #5

twinnyfo
Expert Mod 2.5K+
P: 3,184
Your explanation makes sense. I just was not sure of how the calculations worked. Best of luck!

Let us know how you get along!
2 Weeks Ago #6

P: 4
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.
1 Week Ago #7

NeoPa
Expert Mod 15k+
P: 31,347
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.
1 Week Ago #8

twinnyfo
Expert Mod 2.5K+
P: 3,184
Hey NeoPa! Thanks for fixing my code. I was air-typing, and sometimes I get it wrong (usually). Thanks again for keeping me straight!

:-)
1 Week Ago #9

NeoPa
Expert Mod 15k+
P: 31,347
A tiny typo is all Twinny. Such mistakes are very rare in fact. No more frequent than similar ones from my posts I expect.
1 Week Ago #10

Post your reply

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