Passing variables from child to master form

Question

I have made an Access 2007 db. I will be writing some basic vba for the appropriate event of a form so that a modal form is displayed when the original form is opened. This isn't difficult, but that form will have a drop down box of IDs from a particular table. The user will select an ID, but I want the selected ID from this form to go back to the parent form. E.g.:

1. Car form opened
2. Event is fired to open a modal form

IDs for a FK to cars has to be selected from a combobox (eg ID of driver - for simplicity, lets assume one car can have many drivers but not vice versa so 1:n only)

There is a button to confirm the selection. On clicking this button, the form closes, and the selected Driver ID is automatically inserted into a DriverID textbox on the car form (probably will be read only).

The last step I am not sure about. How can that be done in VBA?

Answer 1

You could write a function that will display the form modally and then return the user selected value as a return value of it (similar to msgbox/inputbox).

EDIT: You could write a function such as following.

Function GetUserSelectedCarID() as string
dim myPopupForm as Form
set myPopupForm = new Form

myPopupForm.Show vbModal
GetUserSelectedCarID = myPopupForm.UserSelectedCar
End Function

UserSelectedCar is a property that stored the user selection on the popup form.

EDIT2: You could also add a property on popup form to see if the user clicked OK or Cancel. It will return blank from the above function, if user clicked Cancel.

Answer 2

To gain 'access' (get it?!) to controls on separate running forms you can use the following:

Forms("AnOpenFormName").Controls("ControlName") = value

Edit: I used the wrong brackets AMG!

Answer 3

All of the above answers are good. I especially like the third way of wrapping this kind of functionlity into reusable function calls. The only thing I would add is do not CLOSE the modal form...otherwise you cannot get the selected value. Instead do me.visible=false.

So you do somethiing like this.

Public Function GetCArKey as Integer

dim intReturn as integer

docmd.openform "MyModalForm",,,,,,acdialog

'(the click event of the okay button of this form does me.visible=false...clicking Cancel will close the form.)

if isloaded("MyModalForm") then
   intReturn=Forms("MyModalForm").Controls("ControlName").Value
end if

GetCarKey=intReturn

End Function

Answer 4

This is one of the worst Access threads I've ever seen on StackOverflow.com, because every answer is wrong in at least some crucial aspect -- not a single one of them would actually run if you pasted them into VBA in an Access database.

The key principles here:

Open the dialog form modally (with the acDialog arguments)

Don't close it when the value has been confirmed, but instead set it's .Visible property to False.

Then in the calling form, read the value out of the hidden form, and then close it.

Something like this:

  DoCmd.OpenForm "dlgPickDriver", , , , , acDialog 
  If IsLoaded("dlgPickDriver") Then
     Me!DriverID = Forms!dlgPickDriver!cmbDriver
     DoCmd.Close acForm, "dlgPickDriver"
  End If

[IsLoaded is a Microsoft-provided function; I posted it here on StackOverflow recently, but would assume that the vast majority of Access developers writing VBA would have been using it forever]

I would recommend against having code that runs in the dialog form poke the data into the parent form because it's then impossible to use the dialog form in multiple locations. Having the dialog form know as little as possible about the context in which it's called is good programming practice. On the other hand, it's true the calling form needs to know the control name it's getting the value from, but that's a more sensible context than the other way around, in my opinion.