Filtering a form based on selection from combo box in another form

[Guest]

Hello,

I have the following code. What I want is for when the user selects the
region from cboREGION, enter's the password, and clicks the GO button, I want
the frmENTER_LEAK_FORM to only display the communities that are in the
specified Region which was selected in the previous form (frmRegions) from
cboREGION. How would I do this?

Private Sub cmdLogin_Click()

'Check to see if data is entered into the UserName combo box

If IsNull(Me.cboREGION) Or Me.cboREGION = "" Then
MsgBox "You must enter a Region.", vbOKOnly, "Required Data"
Me.cboREGION.SetFocus
Exit Sub
End If

'Check to see if data is entered into the password box

If IsNull(Me.txtPassword) Or Me.txtPassword = "" Then
MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
Me.txtPassword.SetFocus
Exit Sub
End If

'Check value of password in tblRegions to see if this matches value chose in
combo box

If Me.txtPassword.Value = DLookup("strRegPassword", "tblRegions",
"[lngRegID]=" & Me.cboREGION.Value) Then
lngMyRegID = Me.cboREGION.Value

'Close logon form and open enter leak form

DoCmd.Close acForm, "frmSELECT_REGION", acSaveNo
DoCmd.OpenForm "frmENTER_LEAK_FORM"

Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, "Invalid Entry!"
Me.txtPassword.SetFocus
End If

'If User Enters incorrect password 3 times the database will shut down

intLogonAttempts = intLogonAttempts + 1
If intLogonAttempts > 3 Then
MsgBox "You do not have access to this database.Please contact the Databas
Administrator.", vbCritical, "Restricted Access!"
Application.Quit
End If


End Sub

 

[Ron2006]

Don't close the logon form, and in the query that is the basis for the
"frmENTER_LEAK_FORM" have the criteria point to the combobox on the
logon form.


An alternative
1) create a "FormtoHide" with a field called "LogonRegin"
2) in the onopen event of the logon form open this new form "acHidden"
3) In the afterupdate event for the combo box
Forms![FormtoHide]![LogonRegin] = me.regioncomboname
4) Have the criteria of the query for region to be
Forms![FormtoHide]![LogonRegin]
This approach allows you to use that criteria on any query/report/form
that you would create throughout the execution of the mdb.

An alternative
before the close of frmSelect_Region load strCriteria with the region
DoCmd.OpenForm "frmENTER_LEAK_FORM" and supply the strCriteria