Using a popup form to choose what to base the resulting form's rs

[Guest]

Here is what I am attempting t o accomplish....

I want to open a form that asks the user " Which Estate do you wish to edit?" This question is asked and answered via a combo box.

After the user chooses an estate from the list and clicks ok, I want the form to then open the next form and display ALL the records that are associated with that Estate.

And then the estate selection form should either close itself or hide behind the new form.

So I have the following tables -
Estates (EstateID, EstateName)
Artwork (ArtID, EstateID, etc.)

There is a one to many relationship of one estate having numerous artworks.

If that made sense, please help me.

Thanks all,
Chris

 

[Reggie]

Chris one way provided the bound column of your listbox is the EstateID
field you could design a query based on your Estates and Artwork tables.
Join the 2 tables in your query via the EstateID fields. Add the EstateID
field from the Estates table to your query grid. Under this field on the
criteria row type in the following changing the names as appropriate

=Forms!YourForm!YourComboBox

When you click the button to open the form it will always open to EstateID
you select in the combo. You may want to force the user to make a selection
before enabling the open form button but this is bare-bones. Also, when the
user clicks the close button you can choose to hide this popup form by
setting its visible property to false:

Private Sub cmdOpen_Click()
On Error GoTo Err_cmdOpen_Click

Dim stDocName As String
stDocName = "frmEstates"

DoCmd.OpenForm stDocName
Me.Visible = False

Exit_cmdOpen_Click:
Exit Sub

Err_cmdOpen_Click:
MsgBox Err.Description
Resume Exit_cmdOpen_Click
End Sub

Now on the OnClose of the Estates form make the selection form visible

Private Sub Form_Close()
On Error GoTo Err_Close

Forms!frmSelection.Visible = True

Exit_Close:
Exit Sub

Err_Close:
MsgBox Err.Description & Err.Number
Resume Exit_Close
End Sub



--
Reggie

----------

Here is what I am attempting t o accomplish....

I want to open a form that asks the user " Which Estate do you wish to

edit?" This question is asked and answered via a combo box.

After the user chooses an estate from the list and clicks ok, I want the

form to then open the next form and display ALL the records that are
associated with that Estate.

 

[Reggie]

You could also open the form like this:

'if estateID is a number:
DoCmd.OpenForm stDocName, acNormal, , "[EstateID]=" & Me.cboEstate

'if estateID is a text:
DoCmd.OpenForm stDocName, acNormal, , "[EstateID]='" & Me.cboEstate & "'"

--
Reggie

----------

Here is what I am attempting t o accomplish....

I want to open a form that asks the user " Which Estate do you wish to

edit?" This question is asked and answered via a combo box.

After the user chooses an estate from the list and clicks ok, I want the

form to then open the next form and display ALL the records that are
associated with that Estate.