I have the following problem, for which i though that the flowing code would work for.....
I have got a little bit of a problem, one that i would like some help getting my head around, it involves MS Access 2000 and its VBA....
I will attempt to explain it properly.
I am making a database system the will catalogue all of the software that a company uses. There are many software titles, such as MS Word, MS Excel or WinXP. As this company wants to be completely legal they attempt to only have each program installed on as many machines as they have licences for them, this can be a problem if no-one knows what is where, hense this system.
Using the three examples above...
I have given each software package a unique identifier, for MS Excel it is MSE001 (MS Excel 2000 1), MS Word is MSW001 and for WinXP is MSWXP.
Now i have another table called "Licence Info". I want this to be populated automatically as I enter the details of any software titles into the database. The details will go into the "Software" table. As i enter the details of each software package I will put in all of the standard things (Name, VEndor, Serial etc) and with these details will also go the "Max licence number", for example there are 10 licences bought for Excel so the number in this field is 10.
Now here comes the bit that i need help with. I would like to have the Licence entity populated with each software title multiplied by its Max Licence value. Now onto the result i would like to have 001, 002, 003 etc put on the end, so it would result as:
What i would like help with is scripting the code that will populate my "Licence" table with the Licence ID's only.
The Code in the AfterUpdate() function
Dim strVal As String
Dim i As Integer
Private Sub Max_Licence_Number_AfterUpdate()
For i = 1 To nMaxLicenceNumber
' create license item
strVal = GetLicenceInc(i)
CurrentDb.Execute "INSERT INTO LicenceTable (License)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)
However, i cannot get this INSERT INTO to work. I have had lots of help even getting the code that far as I have very little Visual basic knowledge.... any questions you need i will try to answer, and any help would be very greatly appreciated.