Please help translate expression into VBA

[Accesser]

Hi,

I'm not sure how to code the following:

If SomeControl on a loaded form (behind a popup option group form) had the focus, then copy the value (or assign the value to a temporary control like TempSomeControl), then in a new record added to the loaded form, paste or apply the value to the new SomeControl.

Your expertise would be greatly appreciated.

Thanks,
Eric

 

[SeeThru]

What opens the pop-up form?

Trouble is, if you use a button, it is the button that gets the focus, so there will never be an instance where 'somecontrol' has the focus when the popup opens.

you could potentially use previous control if this was true...

Popup_Open_Click:
if form1!previouscontrol=SomeControl then
docmd.openform "POPUP",,,,,,form1!somecontrol.value (as an opening arg)


and on the popup

Popupfor_onopen:
if not isnull(OpenArgs) then
docmd.gotorecord.acnewrecord
me!thenewfield=openargs
end if



Untested code, BTW!

 

[Accesser]

Thanks for replying SeeThru,

If the current value of SomeControl (a combobox) has already been entered, a NotinList event triggers the pop-up form that contains an option group. The options in the option group are:

1. Insert all of the previous record's values into a new record for revision.
2. Only transfer the current field's data value into a new record.
3. Revert to the previous record with this data.
4. Cancel and close this message window.

I'm unsure how to code this between the loaded form and pop-up form. For option #2, I only want the value of whichever field in the loaded form to be picked up and then put into the corresponding field in a new record. But I don't know how to globally transfer the value of whichever field had the focus last in the loaded form. Does that clarify?

Thanks

 

[SeeThru]

It would probably take a bit of fiddling...

you would have to get each selection in the option group to run a different bit of code, using on_click or a separate OK button.

for 1) you would need to close the popup, go to a new record (docmd.gotorecord, acnewrec) and then copy the fields. I havent done this before - anyone know the simplest way?

2) very similar, but limit the copying to one field

3) Not sure what you mean!

4) Just close the pop-up ?

One way of copying the record would be assigning each field to a variable so...

Dim var1, var2, var3...as variant

docmd. close "Popupform"

var1=form!field1
var2=form!field2
....

docmd.gotorecord, acnewrec

me!field1=var1
me!field2=var2
....


but there are probably better ways.

 

[Accesser]

For Option #1

I was able to make doable code for setting the values of the temp variables to those of the underlying record's controls, creating a new record, then transferring the control values into the new record.

var1=form!field1
var2=form!field2
....

docmd.gotorecord, acnewrec

me!field1=var1
me!field2=var2

Works great!!



For Option #2

I still don't know how to code this (my initial post). The problem is that the code (in summary) needs to identify which field had/has the focus in the loaded form, then select/copy its value, then go to a new record, and insert the value into the new record--without carrying over the values from the other controls.

However, this functionality must be controlled by the option/cmd buttons on the pop-up form.


For Option #3

This should just go back to the original record or the 1st record that contained the particular value selected/typed in the particular field (combobox).


Any further insights would be greatly appreciated.