Hello everyone,
I have the following problem. I need to create a very simple database that would just store records and produce a couple of reports. I have three tables: one with the roster , one has records of the inventory items people from the roster receive and another one contains types of inventory we have. Everything is very simple except for one part. Every time we make a record of an inventory item given to someone, it requires not only employee id and inventory type from the existing tables. It needs us to enter a serial number of an item. This serial number contains a letter and a number. Looks something like this - M100. Many people receive a consecutive set of inventory items. For example, from M100 to M150. There is no way to have a separate table with all serial numbers because they constantly change. That is why we need to have two text boxes that would allow us to input a range of serial numbers or just one number. Then the program should separate numbers from letters, evaluate the range, create new records of numbers and then put new numbers and a letter back together into one field in the table where we have all inventory records.
I found the following code online that allows me to find numbers within a range, but it only works for numbers.
Dim varRange As Variant
Dim lngLow As Long
Dim lngHigh As Long
Dim lngCounter As Long
DoCmd.Hourglass True
If Not IsNull(Me![txtBox]) Then
'Place the Upper and Lower Range into an Array with a
'Space (" ") as a Delimiter
varRange = Split(Me![txtBox], " ")
'There must be exactly 2 Elements in the Array, they must both be Numbers, and the 1st must be > the 2nd
If UBound(varRange) = 1 And IsNumeric(varRange(0)) And IsNumeric(varRange(1)) And _
(varRange(1) > varRange(0)) Then
lngLow = varRange(0)
lngHigh = varRange(1)
For lngCounter = lngLow To lngHigh
CurrentDb.Execute "INSERT INTO UnitTable ([UnitField]) VALUES (" & lngCounter & ");", dbFailOnError
Next
End If
End If
DoCmd.Hourglass False
I then found a piece of code that is supposed to separate numbers from letters, but I can’t find a way to make it work.
Public Function FindNum(strName As String) As String
Dim strTemp As String
Dim i As Integer
For i = 1 To Len(strName)
strTemp = Mid(strName, i, 1)
If (Asc(strTemp) < 91 And Asc(strTemp) > 64) Or (Asc(strTemp) < 128 And Asc(strTemp) > 96) Then
FindNum = Right$(strName, Len(strName) - i)
End If
Next i
End Function
It would be amazing if someone could give me a few tips on how to solve the problem and how to alter the code to make it work for my specific situation. I have extremely limited experience with coding in Access and mostly use macros. :banghead:
Thank you in advance!
I have the following problem. I need to create a very simple database that would just store records and produce a couple of reports. I have three tables: one with the roster , one has records of the inventory items people from the roster receive and another one contains types of inventory we have. Everything is very simple except for one part. Every time we make a record of an inventory item given to someone, it requires not only employee id and inventory type from the existing tables. It needs us to enter a serial number of an item. This serial number contains a letter and a number. Looks something like this - M100. Many people receive a consecutive set of inventory items. For example, from M100 to M150. There is no way to have a separate table with all serial numbers because they constantly change. That is why we need to have two text boxes that would allow us to input a range of serial numbers or just one number. Then the program should separate numbers from letters, evaluate the range, create new records of numbers and then put new numbers and a letter back together into one field in the table where we have all inventory records.
I found the following code online that allows me to find numbers within a range, but it only works for numbers.
Dim varRange As Variant
Dim lngLow As Long
Dim lngHigh As Long
Dim lngCounter As Long
DoCmd.Hourglass True
If Not IsNull(Me![txtBox]) Then
'Place the Upper and Lower Range into an Array with a
'Space (" ") as a Delimiter
varRange = Split(Me![txtBox], " ")
'There must be exactly 2 Elements in the Array, they must both be Numbers, and the 1st must be > the 2nd
If UBound(varRange) = 1 And IsNumeric(varRange(0)) And IsNumeric(varRange(1)) And _
(varRange(1) > varRange(0)) Then
lngLow = varRange(0)
lngHigh = varRange(1)
For lngCounter = lngLow To lngHigh
CurrentDb.Execute "INSERT INTO UnitTable ([UnitField]) VALUES (" & lngCounter & ");", dbFailOnError
Next
End If
End If
DoCmd.Hourglass False
I then found a piece of code that is supposed to separate numbers from letters, but I can’t find a way to make it work.
Public Function FindNum(strName As String) As String
Dim strTemp As String
Dim i As Integer
For i = 1 To Len(strName)
strTemp = Mid(strName, i, 1)
If (Asc(strTemp) < 91 And Asc(strTemp) > 64) Or (Asc(strTemp) < 128 And Asc(strTemp) > 96) Then
FindNum = Right$(strName, Len(strName) - i)
End If
Next i
End Function
It would be amazing if someone could give me a few tips on how to solve the problem and how to alter the code to make it work for my specific situation. I have extremely limited experience with coding in Access and mostly use macros. :banghead:
Thank you in advance!