Carry value over to new record w/macro

[bbrazeau]

I have a button on a form to add a new record. Is there a way to carry over one of the controls "a lookup combobox" value to the new record using macro actions? The "lookup combobox" gets its value from the key field of another table if that makes a difference.
I've found answers to do this with VB code, but would like to keep it simple.
Thank You.

[Robeen]

Hi bbrazeau,

I know you specified 'using macro actions' but I wanted to share my experience with this same situation with you.

I had a really simple Data Entry Form on which I wanted users to be able to move to a new record and carry over the Department for which they are entering data to the New Record.

Access was pushing me in the direction of using a Macro for this but when I opened the Macro to do this - I was not able to find an action that would perform this function for me and open the new record with the Department from the previous record that the user had just entered data for.

As I was short on time, I went to writing code and that took care of the issue for me.

What I did was in the Macro I chose the 'RunCode' action and then plugged in the code to open the new record with the Department from the previous record.

I know it looks not simple - but it really is.
Let us know if we can help you with the code.

Plus . . . I don't know if there IS a specific Macro Action to perform this task. There MAY be a way to use a combination of macro actions to achieve what you want but I have not seen one that specifically does this.

I'd like to know if you ARE able to find one.

[bbrazeau]

Thank you for your reply Robeen. It's hard for me to believe that this type of functionallity is not so common a request that someone has not found a simple way to do it. Indeed I'm surprised theres not a wizard built in to access to accomplish this, like the open form button wizard asking if you would like to find specific data, and allowing you to link 2 fields from the source form to the dstination form. I have found:
'******** Code Start **********
const cQuote="""" 'Thats two quotes
me!Control.DefaultValue = cQuote & me!Control.Value & cQuote
'******** Code End **********
In a thread that states this will work if attached to a button that will run the code in VBA. Trouble is I have little to no VBA experience. Could you walk me thru the method you used? When I insert a "RunCode" action before "GoToRecord" access wants a function name??

[bbrazeau]

If there is something wrong with this method, could someone elaborate, because I've tested this method and it seems to work fine.

1). Create a "textbox" on form with general number for a format.
2). Set the textboxe's "Visible" property to "No".
3). Create a command button on the form "Create new record".
4). Add the following actions,arguments to the underlying macro.
OnError, Next,
SetValue, [Forms]![RFS]![Text44],[RFS]![Project_reference]
GoToRecord, ,,New,
SetValue, [Forms]![Project_reference],[Forms]![Text44]
MsgBox, =[MacroError].[Description],Yes,None,
If there are drawbacks to this method that will rear their ugly heads later I'd like someone to warn me please! Thank You.

[bbrazeau]

To All, I came back to this thread because in my DB I ran across another instance where I wanted to Copy a Value to a new record. This time, however, I wanted to open a different form and carry over a value(pk) from the 1st to a (fk) in the 2nd. I din't think to try this method because I figured the change in forms wouldn't allow it to work.(last time it was same form just new record)
I did some searching and ended up using DoCmd.Openform to new record. Then used OpenArgs in the On Open event of the 2nd form. I got this to work, but after that if you opened the 2nd form by any other means it would remove the value in that field on the 1st record.
I decided to try the method I used in this thread and low and behold it works! I can click a command button on my 1st form and the 2nd opens to a new record with the control filled in correctly! I can open the 2nd form from the navigation pane and no data is lost! I'll ask the question again.. Is there a drawback to performing this task in this fashion?(hiding a textbox control on the 1st form) Someone Please respond.

[Robeen]

I don't see a problem with what you're doing.

I've used hidden controls on forms and on reports as well to accomplish various tasks.

I haven't seen any warnings against doing something like this either . . .

If it's working for you - go with it.