Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2002

    Unanswered: VBA Help, complete newbie!

    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:


    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
    MSW001001 PC002
    MSW001002 PC010

    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)
    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.


  2. #2
    Join Date
    Nov 2002
    Hi Joe,

    If you're getting an "unrecognized field/format" error, try blanks around the parentheses:

    CurrentDb.Execute "INSERT INTO LicenceTable ( License ) VALUES ( '" & strVal & "' )"

    If you're getting a "reserved word/parameter" error, try [] around the offending field.

    Good Luck,
    Bruce Baasch

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts