NeoNemesis
11-21-02, 07:52
| 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: MSE001001 MSE001002 MSE001003 MSE001004 etc MSW001001 MSW001002 etc These numbers become the unique ID of each Licence. Each one of these can then be assigned to the computer which it will be installed on: MSE001001 PC001 MSE001002 PC002 MSE001003 PC003 MSE001004 PC004 etc MSW001001 PC002 MSW001002 PC010 etc 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 & "')" Next i End Sub 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) Else GetLicenceInc = Val(nVal) End If End Function 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. Joe |