Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2002
    Posts
    21

    Unanswered: INSERT INTO not inserting

    I have had a lot of help cobbling together a script which is designed to perform a solution to the following problem:

    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 i As Integer
    Dim turn As Integer


    Private Sub Max_Licence_Number_AfterUpdate()
    For turn = 1 To nMaxLicenceNumber


    Dim curdb As Database
    Dim strVal As String
    Dim SQLSTmt As String
    strVal = GetLicenceInc(i)
    Set curdb = CurrentDb()
    SQLSTmt = "INSERT INTO [LicenceTable] ([Licence])VALUES('" & strVal & "')"
    Debug.Print GetLicenceInc(i)
    Debug.Print SQLSTmt
    curdb.Execute (SQLSTmt)
    Next turn

    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

    A copy of the database can be found here:

    http://www.cratefarm.vispa.com/digIT2help.zip

    I just cant see why it is not working, mainly because i know next to nothing about VBA.

    Regards

    Joe

  2. #2
    Join Date
    Mar 2002
    Posts
    192
    There may be more but your mainproblem is that

    nMaxLicenceNumber is not initialized and is set to NULL so the rest of your code does not execute. ... Jumps straigt to End sub

  3. #3
    Join Date
    Mar 2002
    Posts
    192
    There may be more but your mainproblem is that

    nMaxLicenceNumber is not initialized and is set to NULL so the rest of your code does not execute. ... Jumps straight to End sub

  4. #4
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    that might not be the problem if nMax... is an object on a form or somewhere else
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  5. #5
    Join Date
    Mar 2002
    Posts
    192
    Before I replied I downloaded the program and troubleshot file from the form he spoke of. In this case it IS the problem.

  6. #6
    Join Date
    Nov 2002
    Posts
    21
    Indeed you were right, the problem you solution you discussed was right...

    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 & "')"
    Debug.Print PackageID
    Debug.Print strVal
    Debug.Print SQLSTmt
    curdb.Execute (SQLSTmt)
    Next turn

    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

    This inserts the values 001,002, 003, depending on the MaxLicenceValue. However it doesnt add a prefix of the PackageID in front of each of them which i intended it to.

    It also, if i take a MaxlivcenceNumber downwards it doesnt remove anything and i need to do that and have no idea how!

Posting Permissions

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