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

    Unanswered: Inserting and Removing Records

    Hi, I am a complete novice when it comes to VB coding. I have managed to cobble together the following script so which adds records into a table called "LicenceTable". The problem.

    I am creating a database that will (hopefully) be able to catalogue the Software Titles that my company has and will be able to record which software is installed where. This means that if there are 10 copies of MS Word 2000 (the Id of this is MSW001, the value in the "MaxLicenceNumber" for this field will be 10 and 10 records will be inserted into the "LicenceTable" so it will look like this

    MSW001001
    MSW001002
    MSW001003
    MSW001004
    etc.

    The script below adds records into this table in the format:

    001
    002
    003

    The script loops until the "MaxLicenceNumber" is reached. However in front of this number i would like to insert the string contained within the "ProductID" field of the same table that "MaxLicenceNumber" is on.

    If the user decreases the value in the "MaxLicenceNumber" field then it will launch a form which will allow him to remove licences.

    Here is the current code...

    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


    Can anyone help??

    Regards

    Joe

  2. #2
    Join Date
    Nov 2002
    Location
    Ohio
    Posts
    90
    You're probably looking for this:

    SQLSTmt = "DELETE FROM [LicenceTable] WHERE [Licence] = '" & strVal & "'"

    Good Luck,
    Bruce Baasch

  3. #3
    Join Date
    Nov 2002
    Posts
    21
    Any help with any of the other problems?

Posting Permissions

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