Trouble - copy data to new record

[fredzeppelin]

Simple need: to fill-in a form-based new record with data from the previously added record.

The FAQ's lead to this:

Private Function SetDefault()
    Dim ctl As Control
    Set ctl = Screen.ActiveControl
    ctl.DefaultValue = """" & ctl.Value & """"
End Function

then bind the function to .AfterUpdate event for the pertinent controls.

I can't get it to work.

If I set a breakpoint in the function, I can watch the controls' default values changing as desired in their property sheets. So, the function seems to be working fine.

I then close the form, saving the record. If I reopen the form to add or edit, the control default values are cleared.

What's happening to the default values?

I know that when I change a form design manually, I'm prompted to save the form design changes. Am I missing a save step in here somewhere? I've tried doing a

DoCmd.Save acForm, Me.Name

but I'm not sure if that's saving the form or the record.



Background:
This is a fairly complex form with tabs and relational subforms and lots of arithmetic code. To try to limit potential trouble, I disable the '* new record' function in the record navigator and handle adds as a home-rolled menu function, that opens the form(on record #1 of the pertinent recordset), verifies the add ("you sure?"), and then

DoCmd.GoToRecord acActiveDataObject, , acNewRec

 

[Eupher]

Hi, fredzeppelin,

You're right, you're missing a step in your save mechanism. The Save method you are invoking saves the current object (or the specified object if you use that argument) - but only if it's open in design view. So the code-generated defaults will disappear as soon as the form is closed and will revert to the saved defaults. You can use code to open the form in design view, write the default values you want, then save the form. But this seems to me a rather kludgy work-around. I gather from your context that you need the last-written record to persist when the database is closed and re-opened, right? If not, public variables in a module could take care of your problem. Are you using an autonumber field to uniquely identify your records? If so, that could be a very handy way to refer to the newest record since autonumbers are never re-used, so the biggest number should always represent the newest record. If no autonumber, you will probably need to create a table to hold the newest primary key between sessions as described here:

http://allenbrowne.com/ser-18.html

HTH,

Ken S.

 

[Pampers]

you can use this in your code to save a record:

DoCmd.RunCommand acCmdSaveRecord

Pampers
Just let it go...

 

[fredzeppelin]

First, thanks .... very much

Second, you said:

I gather from your context that you need the last-written record to persist when the database is closed and re-opened, right?

... just some of the fields, and it doesn't have to work after close/reopen (that's a bonus), but during subsequent adds.

Third,
No I don't need to return to the specific record. I just want to change some combo/list/text box default values on the fly.

In the application, the user repetitively enters spare parts with many identical parameters and individually unique serial numbers and then another series of <different> identical parameters, and so on.
Makes sense to only enter the one field that changes.

Fourth,

I really don't understand. The code snippet in my first post shows up in all discussions and FAQs about repeating field entries.

I'm not trying to contradict you, it just doesn't make any sense to me that the form has to be in design view to make that work. Object Screen.ActiveControl is not even available in Design View.

I agree with you that it's definitely not saving those default values, but it must be working for somebody, with all those FAQ entries.

Fifth,
I'm sure I can bang together a kluge as you suggest, but this other way was so neat and pretty. Public variables just strike me as disorganized programming. Chuckle, I guess that's how I got here to begin with...

Sixth, thanks again ....

...brad

 

[Eupher]

Hi, brad,

I understand you don't need the form to open to the last saved record - however, the technique described in the link I posted demonstrates how to store the most recently saved record into a separate table - doesn't need to be the entire record, just the primary key should suffice - so you can pull the data you want, whether it's when the form reopens, or when adding a new record.

I really don't understand. The code snippet in my first post shows up in all discussions and FAQs about repeating field entries.

Nevertheless, I just tested to verify - although you can use your code snippet to change the default value while the form remains open, I could *not* get DoCmd.Save to permanently save properties of the form in form view. The help files are ambiguous on this, suggesting only that you can't save to a new file name in form view. I know it's odd - because if you manually open the form's property sheet and type in a new default value for your textbox control, then do File->Save on the menu, it *does* save the change when in form view. You're right that Screen.ActiveControl is not available in design view; you'd need to pass that value to a public variable or write to a table before closing the form and re-opening in design view.

Public variables just strike me as disorganized programming.

I agree that managing the scope of your variables can get confusing, but for certain things public variables are really handy - sometimes the only good way to get from A to B.

HTH,

Ken S.

 

[fredzeppelin]

Ok, the light dawned while I was sleeping, and your experiments verify it.

The snippet works fine only while the subject Form remains open. Close it and the changes are gone. Can't save design changes from Form View. Probably makes sense to *real* programmers. That doesn't fit my situation, so I'll workaround ....

I agree that the doc's are less than helpful in the Save Form situation.

.....many thanks for the input and testing effort



.....brad