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

    Unanswered: INSERT INTO Help. VBA.

    I have the following code in my access project which takes a string (PackageCode) from my tblPackage table, and uses a loop to insert this string with a numerically incrementing suffix. The loop loops to the value in MaxLicenceNumber.

    EG. If PackageCode = MSE00 and MaxLicenceNumber = 4 then in my Licences table the following records would be entered.

    MSE00-001
    MSE00-002
    MSE00-003
    MSE00-004

    Code:
    Private Sub MaxLicenceNumber_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
            
        PackageID = Me.PackageID
        
        turn = 1
        For turn = 1 To Me.MaxLicenceNumber
            strVal = PackageCode & "-" & Right("00" & turn, 3)
            Set curdb = CurrentDb()
            SQLSTmt = "INSERT INTO [tblLicence] ([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 E.G. OO1, O10, 100===
    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 code works fine.

    I would now like to modify this code so that it enters another string from tblPackage: "PackageName" into the "package" field of tblLicence.

    So i would end up with this:

    MSE00-001 | Excel 2000
    MSE00-002 | Excel 2000
    MSE00-003 | Excel 2000
    MSE00-004 | Excel 2000

    Can anyone help?

    Regards
    Joe

  2. #2
    Join Date
    Nov 2002
    Location
    Ohio
    Posts
    90
    HI,

    Your insert code needs to be expanded in two places:

    ([License]) becomes ([License] , [Package])

    and

    ("'" & strVal & "'") becomes

    ("'" & strVal & "','" & PackageId & "'")

    Each new field you add to an insert needs to be added in the "FieldNames" and "Values" sections of the Insert statement. If you're going to do a lot of this, consider building a function or class module that wraps a string with single quotes to reduce your typing.

    It's a major pain in the tail during development to remember strings get single quotes, numbers get nothing and date/times get number signs.

    Good Luck,
    Bruce Baasch

  3. #3
    Join Date
    Nov 2002
    Posts
    21
    Many Many thanks. That was exactly the kind of detailed explanation that i was looking for. Thank you!!!

    Code:
    Private Sub MaxLicenceNumber_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
            
        PackageID = Me.PackageID
        
        turn = 1
        For turn = 1 To Me.MaxLicenceNumber
            strVal = PackageCode & "-" & Right("00" & turn, 3)
            Set curdb = CurrentDb()
            SQLSTmt = "INSERT INTO [tblLicence] ([Licence],[package])VALUES('" & strVal & "','" & PackageID & "')"
            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 E.G. OO1, O10, 100===
    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
    Joe
    Last edited by NeoNemesis; 01-02-03 at 14:09.

Posting Permissions

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