Hi, I am a complete novice when it comes to VB coding. I have managed to cobble together the following script so which adds records into a table called "LicenceTable". The problem.
I am creating a database that will (hopefully) be able to catalogue the Software Titles that my company has and will be able to record which software is installed where. This means that if there are 10 copies of MS Word 2000 (the Id of this is MSW001, the value in the "MaxLicenceNumber" for this field will be 10 and 10 records will be inserted into the "LicenceTable" so it will look like this
The script below adds records into this table in the format:
The script loops until the "MaxLicenceNumber" is reached. However in front of this number i would like to insert the string contained within the "ProductID" field of the same table that "MaxLicenceNumber" is on.
If the user decreases the value in the "MaxLicenceNumber" field then it will launch a form which will allow him to remove licences.
Here is the current code...
Private Sub Max_Licence_Number_AfterUpdate()
Dim i As Integer
Dim turn As Integer
Dim curdb As Database
Dim strVal As String
Dim SQLSTmt As String
Dim PackageID As String
turn = 1
For turn = 1 To Me.Max_Licence_Number
strVal = GetLicenceInc(turn)
Set curdb = CurrentDb()
SQLSTmt = "INSERT INTO [LicenceTable] ([Licence])VALUES('" & strVal & "')"
Private Function GetLicenceInc(nVal As Integer) As String
' add string prefix
If nVal < 10 Then
GetLicenceInc = "00" & Val(nVal)
ElseIf nVal >= 10 And nVal < 100 Then
GetLicenceInc = "0" & Val(nVal)
GetLicenceInc = Val(nVal)