Programming Forums - Copying fields between two records

Programming Forums (http://www.programmingforums.org/forumindex.php)

- Visual Basic (http://www.programmingforums.org/forum18.html)

- - Copying fields between two records (http://www.programmingforums.org/showthread.php?t=13059)

Abyss

Apr 25th, 2007 9:04 AM

Copying fields between two records

Hi guys,

I'm working with Access at the minute and have run into a problem. Basically, what I'm trying to do is create an 'add new record' button that will copy certain field values from the old record into the new record. For example

Quote:

Record #1: Name: Bateman Gender: Male MilesWalked: 3

Click new record button

New Record #2 Name: Bateman Gender: Male MilesWalked: blank

So in the first record the name, gender and mileswalked are typed in. Then the 'add new record' button is clicked and only the name and gender values are copied.

I'm trying to do this using and ADO recordset but not having much success. Any suggestions would be a help. Thanks!

pegasus001

Apr 25th, 2007 9:12 AM

If you have an database application do it using MsAccess. About the ADO arent you using querys to add values? Because ado.net is pretty straight forward and i would strongly reccomend switching to .net.

Abyss

Apr 25th, 2007 9:37 AM

Thanks for the reply, pegasus. I'm using MSAccess 2000 and VB 6. At present my code for the 'add new record' button looks like this:

Quote:

Private Sub cmdAddSameCase_Click()

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sSQL As String

On Error GoTo Err_cmdAddSameCase_Click

Set cn = CurrentProject.Connection

I've been experimenting with SELECT and INSERT functions here but haven't made much progress
sSQL = sSQL & " Month = " & Month.Value
sSQL = sSQL & " and Year = " & Year.Value
etc
etc
Set rs = cn.Execute(sSQL)

DoCmd.GoToRecord , , acNewRec

Exit_cmdAddSameCase_Click:
Exit Sub

Err_cmdAddSameCase_Click:
MsgBox Err.Description
Resume Exit_cmdAddSameCase_Click

End Sub

Unfortunately I can't switch to .net as I'm writing this piece of code for work.

As always, suggestions are much appreciated.

Abyss

Apr 26th, 2007 4:38 AM

Found a way to code this copy function. It's not so pretty but it works. If you're interested here's how I did it

Quote:

Private Sub cmdAddSameCase_Click()

On Error GoTo Err_cmdAddSameCase_Click

Dim txt1 As String, txt2 As String, txt3 As String, txt4 As String etc

txt1 = Me.Month.Value
txt2 = Me.Year.Value
txt3 = Me.centre.Value
txt4 = Me.Session.Value
etc
etc

DoCmd.GoToRecord , , acNewRec

Me.Month.Value = txt1
Me.Year.Value = txt2
Me.centre.Value = txt3
Me.Session.Value = txt4
etc
etc

Exit_cmdAddSameCase_Click:
Exit Sub

Err_cmdAddSameCase_Click:
MsgBox Err.Description
Resume Exit_cmdAddSameCase_Click

End Sub

All times are GMT -5. The time now is 12:51 AM.