Copy field values from one record to a new record. 2

[PALman]

While viewing my database in form view I would like to code a button to enable me to open a new record and have all the fields of the active record copied into the fields of the new record, then all I would require to do would be to give the record a new number in the primary key field. Then I would have two different records with the same data but obviously with different record numbers.
Any help is very much appreciated.
PALman.

 

[Steve3110]

You could use something like the following

dim  i as integer
dim number as integer

'Whatever the record number that your copying is:
number = 1

set rs = currentdb.openrecordset("select * from Table1 WHERE recordnumber = " & number ";")
set rs2 = currentdb.openrecordset("Table2")


      rs2.addnew
      for i = 1 to rs.fields.count
         rs2.fields(i).value = rs.fields(i).value
      next i
      rs2.update

set rs = nothing
set rs2 = nothing

I'm unable to test this code right now as i'm not on a computer with access but i hope it gives you the basis to use.

 

[Melagan]

A simple way could be to put your previous record's data in variables, then populate the new record with those variables.

Public Sub cmdYourButton_Click()
dim txt1 as String, txt2 as String, txt3 as String
dim dat1 as Date

txt1 = Me.Control_for_txt1.Value
 txt2 = Me.Control_for_txt2.Value
  txt3 = Me.Control_for_txt3.Value
   dat1 = Me.Control_for_dat1.Value

    DoCmd.GoToRecord , , acNewRec

   Me.Control_for_txt1.Value = txt1
  Me.Control_for_txt2.Value = txt2
 Me.Control_for_txt3.Value = txt3
Me.Control_for_dat1.Value = dat1

Simple & crude but should do the trick.

~Melagan
______
"It's never too late to become what you might have been.

 

[PALman]

Thanks Steve and Melagan,
I shall try both these answers over the next day or so and let you know outcome.
Thanks for very quick replies.
PALman

 

[abaldwin]

Or you could use

Insert into Table2 (select * from table1 WHERE recordnumber = " & number ";"

as your sql statement


Andy Baldwin

"Testing is the most overlooked programming language on the books!"

Ask a great question, get a great answer. Ask a vague question, get a vague answer.
Find out how to get great answers FAQ219-2884.

 

[TheAceMan1]

how are ya PALman . . .

For this to work, put a question mark [blue]?[/blue] in the [blue]Tag[/blue] property of the controls of interest. Then in the [blue]OnClick[/blue] event of your button, copy/paste the following:

[blue]   Dim DQ As String, ctl As Control
   
   DQ = """"
   
   For Each ctl In Me.Controls
      If ctl.Tag = "?" Then
         ctl.DefaultValue = DQ & ctl.Value & DQ
      Next
   Next[/blue]

In form view select your record & hit the button . . .

See Ya! . . . . . .

 

[PALman]

Sorry TheAceMan1,
I had to change the first Next to End If and then tried your coding and I got error... "Object doesn't support this property or method".

 

[TheAceMan1]

PALman . . .

The correction is valid and completes the code (sorry for the error). However I have no problems with it here! I tested with a table I made with the following fields:
[ol][li]Name as Text[/li]
[li]Int as Integer[/li]
[li]Sng as Single[/li]
[li]Cur as Currency[/li]
[li]QDate as Date[/li]
[li]LU as Combobox LookUp[/li][/ol]
This runs the gamut of most types used and again, no problemo!

On what line does the code stop?
Are you using any OLE fields?

See Ya! . . . . . .

 

[PALman]

Code does not stop. Only message "Object doesn't support this property or method". So it probably is not an error.

 

[TheAceMan1]

PALman . . .

You could try removing the question marks and adding them one by one until you get the error.

Also in any vbe editor window [blue]Tools[/blue] - [blue]References[/blue]. What are the checked references?

What are the types of controls you've flagged with the question mark?

What version Access?

See Ya! . . . . . .

 

[PALman]

Hi TheAceMan1
Maybe I did not understand you initial answer. But I have only set one qustion mark in one tag property in the new control button (Called "Copy Previous Record"), and this is where I copied your code.
I have many checked references. Which one or ones should be checked for your program to run?
Access Version 2002.
Thanks,

 

[PALman]

Hi Melegan,
I tried you coding but it stops with the error "Method or Data member not found" and it highlights Control_for_txt1 as the cause of error. Any further help much appreciated.

 

[Melagan]

Hi PALman,

While I appreciate you giving my code a try, I really do think that AceMan's code is better. It sounds to me like you're trying to put his code behide a command button. Thats perfect, except that the TAG property for that command button shouldn't be marked with a '?', but the TAG properties for all of the controls you want to pass through to the next record should be marked with a '?' mark.


As for my code - you should replace my generic control names with your own.

error "Method or Data member not found" and it highlights Control_for_txt1

The error means exactly what it is - [Control_for_txt1] doesn't exist on your form. You just need to replace the name that I used with the names of the controls on your form and you're set. Again, though, I suggest working more with AceMan's code. It'll be a lot easier to maintain once you have it working.

~Melagan
______
"It's never too late to become what you might have been.

 

[PALman]

Steve3110
I tried your coding and unfortunately it stopped with a "Syntax Error" at the line... set rs = currentdb.openrecordset("select * from Table1 WHERE recordnumber = " & number ";") I was thinking would it not be necessary to Dimension the set variables rs and rs2 ? Just a thought. Thanks and any further help much appreciated.

 

[TheAceMan1]

PALman . . .

Put a question mark in the [blue]Tag property[/blue] of all the fields you want copied. Remove the question mark from the [blue]Tag property[/blue] of the Command Button (this was the error).

See Ya! . . . . . .

 

[PALman]

Hi TheAceMan,
Thanks for your latest. Coding is now working great. Also thanks to all who helped in this thread. Thanks Megalan, I took your advice and went with AceMan.