Want to copy existing record - VB/Access

Hello all!

I have an application with a VB6 front-end and an Access 97 back-end.  The primary table in the database is a general information Group table.  The Primary Key for the table is GroupNo.  I am looking for the best method to copy an existing record to a new record with the same GroupNo concatenated with a designated suffix.  This is quite easy entirely within Access using Insert SQL.  What is the best way in VB?


Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

also use SQL...VB supports it....and since you used it in Access before, it should be easy for you......the syntax is exactly the same..you just need some general declarations before you issue SQL statements...contact me if you need the declaration codes...
lmerrellAuthor Commented:
Suppose I had a form with a combo box to select an existing GroupNo and a textbox for entry of the suffix.  I can build the sql to reference these controls with know problem.  Could you give me some example code to execute it.  I'm feeling real lazy today.  :-)


lmerrellAuthor Commented:
I meant "with no problem."  I had three people talking to me while I was typing my response.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

here's the code to setup the database:

Option Explicit
Const DBName As String = "\Tables.mdb"
Dim db As Database
Dim rs As Recordset
Dim strSQl As String
Dim I As Integer
Dim dbe As New DBEngine

Private Sub Form_Load()
    On Error GoTo LogErr
    'compact Database
    dbe.CompactDatabase App.Path & DBName, App.Path & "\CTable.mdb"
    Kill App.Path & DBName
    Name App.Path & "\CTable.mdb" As App.Path & DBName
    'open the database
    Set db = OpenDatabase(App.Path & DBName)
    Exit Sub
    lstSession.AddItem "Error: " & Err.Number & " (" & Err.Description & ")"
    'highlight the error message
    lstSession.Selected(lstSession.NewIndex) = True
    Resume Next
End Sub

Private Sub Form_Resize()
    On Error Resume Next
    If WindowState = vbMinimized Then
        Me.Visible = False
    End If
End Sub

Private Sub Form_Unload(Cancel As Integer)
    On Error Resume Next
        'if you're using System Tray
        Dim lngRetVal As Long
        'return the correct windows handler just in case something went wrong
        lngRetVal = SetWindowLong(lngHwnd, GWL_WNDPROC, lngPrevWndProc)
        'remove icon from the System Tray
        Shell_NotifyIconA NIM_DELETE, nidTray
        UnloadAll           'unload all forms
        Cancel = 1
    End If
End Sub

'in the event you want, add the following code...for example Command1_Click()
        'change your SQL statement here....
        strSQl = "SELECT * FROM Users WHERE ULoginName Like '" & UserName & "'"
        Set rs = db.OpenRecordset(strSQl)
        If Not (rs.BOF And rs.EOF) Then
            Do While Not rs.EOF

                 'do your stuff here.....

      end if
      'skip this is you're going to leave the db open...
      set rs=nothing
      set db=nothing

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
by the way.."UnloadAll" is a function I wrote....it's not internal to VB....if you want the code (it's only a few lines)..msg me...
lmerrellAuthor Commented:
st_steve -

I was hoping to use an action query instead of doing this using a recordset through code.

What about using an Execute method?

mySQL = "INSERT INTO tblGroupStatic blah blah blah"
Set db = OpenDatabase(App.Path & DBName)
db.Execute mySQL

does this look right?
     You forgot to declare the type for lstSession since this is not a native VB type.
lmerrellAuthor Commented:
st_steve - FYI, the execute method does work.  In fact it works quite well.  I'm feeling generous and you did joustle me into thinking so...

"johnny6"....lstSession is a listbox to display the status of my program..other than that..it has no connection with DB or Access....

"lmerrell"..glad I can help :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.