Adding a new record with fields from an old record

[jeswel77]

I'm using Access '97 and in my Form i have a button that adds a fresh new blank record. How can i have it add a new record based on 3 fields from the current record i'm on?

Thanks so much for you help
Jessi

 

[lupins46]

Several ways...just one here

In the afterupdate event procedure for the form, save the values from the three controls into variables.

In the form current event procedure, test for a new record. If it is one then test that the stored values are present and if so copy them to the relevant controls.
In genberal declarations:

Dim fld1
dim fld2
dim fld3

Then

Form_afterupdate()

fld1 = me.control1
fld2 = me.control2
fld3 = me.control3

end sub


and

Form_current()

if not me.newrecord then exit sub ' if not a new record then exit
if isnull(fld1) then exit sub ' assume values are all there or all missing
me.control1 = fld1
me.control2=fld2
me.control3= fld3

end sub

 

[PHV]

Another way is to play with the DefaultValue property:
Me![name of control].DefaultValue = Me![name of control].Value

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[PHV]

Another way is to play with the DefaultValue property:
Me![name of control].DefaultValue = Me![name of control].Value

This code (for the 3 controls) may be in the Click event procedure of the "Add" button.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jeswel77]

Lupins46 and PH thanks so much for helping me out.

PH- i gave this code a try and i keep getting a type mismatch error. Not sure what i should change. the field provid is numeric..does that make a difference?

Again..thanks so much!!!!
jessi

 

[lupins46]

show a few lines of code which includes the error line (and indicate the error line).

 

[jeswel77]

hi lupins46,

the code i used (that is based on my newrecord button) is as follows:

Private Sub Command203_Click()
DoCmd.GoToRecord , , acNewRec
Me![provid].DefaultValue = Me![provid].Value
End Sub

When i click that button i get a prompt that says

runtime error 13
type mismatch.

I'm not given the option to debug...so there is no error line in the code.

i'm probably doing this wrong...

thanks
jessi

 

[IDilbert6]

I have a similar situation where I want to save on data entry efforts by repeating the data from 2 fields of the previous record into the new record. In your reply, please know that I do not know coding. I have copied and pasted the code below, which works only some of the time:

Option Compare Database
Option Explicit
Private Sub Ship_Date_AfterUpdate()
Me![Ship Date].DefaultValue = """" & Me![Ship Date].Value & """"
End Sub
Private Sub Organization_Name_AfterUpdate()
Me![Organization Name].DefaultValue = """" & Me![Organization Name].Value & """"
End Sub

All works fine for the first several (8 or 9) records. Then the next record displays "#Name?" in the "Organization Name" field plus several other fields. (The "Ship Date" field still correctly defaults to the previous record's value.) Can anyone tell me why this happens, or at least how to make it not happen?


Thanks in advance.

 

[lupins46]

The syntax used by IDilbert6 is what you need.
But you need to set the default value as you save the previous record. It's too late to do it when you have moved to a new record because Me![provid].Value will now be empty.