Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Nov 2002
    Posts
    21

    Unanswered: VBA Newbie...INSERT INTO

    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

  2. #2
    Join Date
    Aug 2002
    Location
    Northampton, England
    Posts
    266
    Joe.
    What error message are you getting?

  3. #3
    Join Date
    Nov 2002
    Location
    Hamilton,Ontario
    Posts
    132

    Talking Re: VBA Newbie...INSERT INTO

    Dim curdb as database
    Dim strVal As String
    Dim SQLSTmt as String
    strVal = Me![Description]
    Set curdb = CurrentDb()
    SQLSTmt = "INSERT INTO [LicenceTable] ([License])VALUES('" & strVal & "')"
    Debug.Print Me![Item Number]
    Debug.Print SQLSTmt
    curdb.Execute (SQLSTmt)



    'this works perfectly fine. I have tested it out . Now if it still doesnt read the value of StrVal then check ur function.
    1-Debug.Pring StrVal will help as you will check the value of that variable in Immediate window.
    2-highlight strVal adn press Shift+f9 and see if it even displays any value for strVal(if it doesnt then def somethng is wrong with function which is genertaing this value.
    3- work on debug mode, set a break point at SQLSTmt, step in by pressing F8 adn step through ur code as u debug it using Debug.print strval, make sure u click on View and click on Immediate window.
    I hope this helps
    Good Luck

  4. #4
    Join Date
    Nov 2002
    Posts
    21
    Originally posted by DJN
    Joe.
    What error message are you getting?
    Im not getting any error messages, it just isnt inserting any records into my table...

  5. #5
    Join Date
    Nov 2002
    Posts
    21

    Re: VBA Newbie...INSERT INTO

    Originally posted by VBAPROGRAMMER
    Dim curdb as database
    Dim strVal As String
    Dim SQLSTmt as String
    strVal = Me![Description]
    Set curdb = CurrentDb()
    SQLSTmt = "INSERT INTO [LicenceTable] ([License])VALUES('" & strVal & "')"
    Debug.Print Me![Item Number]
    Debug.Print SQLSTmt
    curdb.Execute (SQLSTmt)



    'this works perfectly fine. I have tested it out . Now if it still doesnt read the value of StrVal then check ur function.
    1-Debug.Pring StrVal will help as you will check the value of that variable in Immediate window.
    2-highlight strVal adn press Shift+f9 and see if it even displays any value for strVal(if it doesnt then def somethng is wrong with function which is genertaing this value.
    3- work on debug mode, set a break point at SQLSTmt, step in by pressing F8 adn step through ur code as u debug it using Debug.print strval, make sure u click on View and click on Immediate window.
    I hope this helps
    Good Luck
    Will try this ASAP. Thank you, i will report back with the result!

    Neo

  6. #6
    Join Date
    Nov 2002
    Location
    The Netherlands
    Posts
    61
    Dim curdb as database
    Dim strVal As String
    Dim SQLSTmt as String
    strVal = Me![Description]
    Set curdb = CurrentDb()
    SQLSTmt = "INSERT INTO [LicenceTable] ([License])VALUES('" & strVal & "')"
    Debug.Print Me![Item Number]
    Debug.Print SQLSTmt
    DoCmd.RUNSQL SQLSTmt

    Won't this work?

  7. #7
    Join Date
    Nov 2002
    Posts
    21
    The result
    Attached Thumbnails Attached Thumbnails digit.jpg  

  8. #8
    Join Date
    Nov 2002
    Location
    Hamilton,Ontario
    Posts
    132
    Originally posted by DeathWing
    Dim curdb as database
    Dim strVal As String
    Dim SQLSTmt as String
    strVal = Me![Description]
    Set curdb = CurrentDb()
    SQLSTmt = "INSERT INTO [LicenceTable] ([License])VALUES('" & strVal & "')"
    Debug.Print Me![Item Number]
    Debug.Print SQLSTmt
    DoCmd.RUNSQL SQLSTmt

    Won't this work?


    Dim curdb as database
    Dim strVal As String
    Dim SQLSTmt as String
    strVal = 'call ur function here
    Set curdb = CurrentDb()
    SQLSTmt = "INSERT INTO [LicenceTable] ([License])VALUES('" & strVal & "')"
    Debug.Print SQLSTmt
    DoCmd.RUNSQL SQLSTmt


    This is test for me , just to find out if the Sql statment is fine. In my case its workign just fine. so the problem might be in ur function.
    hope that helps

  9. #9
    Join Date
    Nov 2002
    Posts
    21
    it doesnt appear to like the

    Dim curdb as database

    bit...lots of you have suggested it so i am wondering why that bit is picking up the bug??

    Neo

  10. #10
    Join Date
    Nov 2002
    Posts
    50
    The possible solution to the error ur getting is.. you have to add a reference

    go to tools and references

    then look for "Microsoft DAO 3.6 Object Library"
    add it

    and then u shouldnt' get that error.

    Try it may be it will fix it

    Originally posted by NeoNemesis
    The result
    Deep

  11. #11
    Join Date
    Nov 2002
    Location
    Hamilton,Ontario
    Posts
    132
    Originally posted by NeoNemesis
    it doesnt appear to like the

    Dim curdb as database

    bit...lots of you have suggested it so i am wondering why that bit is picking up the bug??

    Neo
    how did u set up ur database variable?
    since u pasted the code in orgional text
    ur code
    -----------------------------------------
    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



    my question..
    where are u setting up currentDb?

  12. #12
    Join Date
    Nov 2002
    Posts
    21
    I have pointed it to my function now and added the Reference tool as suggested, this is the new result...
    Attached Thumbnails Attached Thumbnails digit1.jpg  

  13. #13
    Join Date
    Nov 2002
    Location
    Hamilton,Ontario
    Posts
    132
    Originally posted by NeoNemesis
    I have pointed it to my function now and added the Reference tool as suggested, this is the new result...
    problem is ur not passing the argument to ur function,try this

    strVal = GetLicenceInc(i)

    or try this

    strVal = GetLicenceInc(nVal)

  14. #14
    Join Date
    Nov 2002
    Posts
    50
    it looks like GetLicenseInc is a function.. with a passing one variable or more



    it should be something like

    strVal = GetLicenseInc(string, val, or more)


    Originally posted by NeoNemesis
    I have pointed it to my function now and added the Reference tool as suggested, this is the new result...
    Last edited by Programmer; 11-21-02 at 13:29.
    Deep

  15. #15
    Join Date
    Nov 2002
    Location
    Hamilton,Ontario
    Posts
    132
    Originally posted by Programmer
    it looks like GetLicenseInc is a function.. with a passing one variable or more



    it should be something like

    strVal = GetLicenseInc(string, val, or more)

    this should work
    strVal = GetLicenceInc(Val)

Posting Permissions

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