Pull proper Recordset based on Cascading Combo values

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

Below is a function that I found in a sample application. This function
works well and checks the validity of correct passwords.

Now, I need some help with modifying it... currently, when a user enters a
"Division" and "Branch" plus correct password the line "MsgBox "Login
Successful...Proceed to subform!", vbInformation + vbOKOnly, "Information""
is executed and shows appropriate message box.

I now want to bring up a query that shows all records based on the
"Division" and "Branch" selection. Currently, there are 126 records in
tblKPIAnswers... each Division/Branch combination has 21 testing records.

Please refer to a sample file (Password.zip) at:
http://tombock2004.i8.com/Test/

So, my goal is the following:
1. Open frmLogin
2. Select any of the 6 combinations (e.g. if "Division" = "California", then
Branch = "Los Angeles" or "San Francisco")
3. Click "Login" (enter "test" as the password... it's the same for all 6
combinations)
4. Now, depending on the selected Division/Branch combination, I should see
only 21 records in "qryTest" (either, those of LA or San Francisco... based
on what I chose for the Branch).

How do I make that modification (between '&&&&&&&&&&&&&&&&&) ???

Thanks,
Tom


****************************
Private Sub cmdLogin_Click()

If Nz(Me.cboDivision, "") = "" Then
MsgBox "You must select a division!", vbExclamation + vbOKOnly,
"Error"

ElseIf Nz(Me.cboBranch, "") = "" Then
MsgBox "You must select a branch!", vbExclamation + vbOKOnly,
"Error"

ElseIf Nz(Me.txtPassword, "") = "" Then
MsgBox "You must enter a password!", vbExclamation + vbOKOnly,
"Error"

Else
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

Set db = CurrentDb

strSQL = "SELECT * FROM tblOrganisationAndCredentials " & _
"WHERE [Division] = '" & Me.cboDivision & "' " & _
"AND [Branch] = '" & Me.cboBranch & "' " & _
"AND [Password] = '" & Me.txtPassword & "'"

Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

If rst.EOF Then 'NO RECORDS
MsgBox "Invalid password! Please retry.", vbInformation +
vbOKOnly, "Information"

Else 'A MATCHING RECORD WAS FOUND
MsgBox "Login Successful...Proceed to subform!", vbInformation +
vbOKOnly, "Information"


'&&&&&&&&&&&&&&&&&
'I need some code here that pulls proper record set (in qryTest)
based on Division/Branch selection
'&&&&&&&&&&&&&&&&&



End If

End If
End Sub
****************************
 
Tom said:
Below is a function that I found in a sample application. This function
works well and checks the validity of correct passwords.

Now, I need some help with modifying it... currently, when a user enters a
"Division" and "Branch" plus correct password the line "MsgBox "Login
Successful...Proceed to subform!", vbInformation + vbOKOnly, "Information""
is executed and shows appropriate message box.

I now want to bring up a query that shows all records based on the
"Division" and "Branch" selection. Currently, there are 126 records in
tblKPIAnswers... each Division/Branch combination has 21 testing records.

Please refer to a sample file (Password.zip) at:
http://tombock2004.i8.com/Test/

So, my goal is the following:
1. Open frmLogin
2. Select any of the 6 combinations (e.g. if "Division" = "California", then
Branch = "Los Angeles" or "San Francisco")
3. Click "Login" (enter "test" as the password... it's the same for all 6
combinations)
4. Now, depending on the selected Division/Branch combination, I should see
only 21 records in "qryTest" (either, those of LA or San Francisco... based
on what I chose for the Branch).

How do I make that modification (between '&&&&&&&&&&&&&&&&&) ???

Thanks,
Tom


****************************
Private Sub cmdLogin_Click()

If Nz(Me.cboDivision, "") = "" Then
MsgBox "You must select a division!", vbExclamation + vbOKOnly,
"Error"

ElseIf Nz(Me.cboBranch, "") = "" Then
MsgBox "You must select a branch!", vbExclamation + vbOKOnly,
"Error"

ElseIf Nz(Me.txtPassword, "") = "" Then
MsgBox "You must enter a password!", vbExclamation + vbOKOnly,
"Error"

Else
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

Set db = CurrentDb

strSQL = "SELECT * FROM tblOrganisationAndCredentials " & _
"WHERE [Division] = '" & Me.cboDivision & "' " & _
"AND [Branch] = '" & Me.cboBranch & "' " & _
"AND [Password] = '" & Me.txtPassword & "'"

Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

If rst.EOF Then 'NO RECORDS
MsgBox "Invalid password! Please retry.", vbInformation +
vbOKOnly, "Information"

Else 'A MATCHING RECORD WAS FOUND
MsgBox "Login Successful...Proceed to subform!", vbInformation +
vbOKOnly, "Information"


'&&&&&&&&&&&&&&&&&
'I need some code here that pulls proper record set (in qryTest)
based on Division/Branch selection
'&&&&&&&&&&&&&&&&&



End If

End If
End Sub
****************************
Click to expand...


Open qryTest in design mode.
In the first column of the grid (Division), in the Criteria row, enter

Forms!frmLogIn.cboDivision

In the second column of the grid (Branch), in the Criteria row, enter

Forms!frmLogIn.cboBranch

I would change the query from a Totals query to just a standard Select query.

Where you have &&&&&&&&&&&& in your code add:

DoCmd.OpenQuery "qryTest"


(It would be better to make a new form with the form recordset set to
qryTest and use
DoCmd.OpenForm "frmTheFormName".)


Don't forget to clean up by closing the recordset and destroying the
references you created. Add these lines just above the "End Sub" so it looks
like this:

'<snip>
End If

End If
rst.Close
Set rst = Nothing
Set db = Nothing
End Sub


HTH,

Steve
 
SteveS:

Awesome solution... quick, precise, and produces the proper results.

Thanks so much!!!

Tom


SteveS said:
Tom said:
Below is a function that I found in a sample application. This function
works well and checks the validity of correct passwords.

Now, I need some help with modifying it... currently, when a user enters
a
"Division" and "Branch" plus correct password the line "MsgBox "Login
Successful...Proceed to subform!", vbInformation + vbOKOnly,
"Information""
is executed and shows appropriate message box.

I now want to bring up a query that shows all records based on the
"Division" and "Branch" selection. Currently, there are 126 records in
tblKPIAnswers... each Division/Branch combination has 21 testing records.

Please refer to a sample file (Password.zip) at:
http://tombock2004.i8.com/Test/

So, my goal is the following:
1. Open frmLogin
2. Select any of the 6 combinations (e.g. if "Division" = "California",
then
Branch = "Los Angeles" or "San Francisco")
3. Click "Login" (enter "test" as the password... it's the same for all 6
combinations)
4. Now, depending on the selected Division/Branch combination, I should
see
only 21 records in "qryTest" (either, those of LA or San Francisco...
based
on what I chose for the Branch).

How do I make that modification (between '&&&&&&&&&&&&&&&&&) ???

Thanks,
Tom


****************************
Private Sub cmdLogin_Click()

If Nz(Me.cboDivision, "") = "" Then
MsgBox "You must select a division!", vbExclamation + vbOKOnly,
"Error"

ElseIf Nz(Me.cboBranch, "") = "" Then
MsgBox "You must select a branch!", vbExclamation + vbOKOnly,
"Error"

ElseIf Nz(Me.txtPassword, "") = "" Then
MsgBox "You must enter a password!", vbExclamation + vbOKOnly,
"Error"

Else
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

Set db = CurrentDb

strSQL = "SELECT * FROM tblOrganisationAndCredentials " & _
"WHERE [Division] = '" & Me.cboDivision & "' " & _
"AND [Branch] = '" & Me.cboBranch & "' " & _
"AND [Password] = '" & Me.txtPassword & "'"

Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

If rst.EOF Then 'NO RECORDS
MsgBox "Invalid password! Please retry.", vbInformation +
vbOKOnly, "Information"

Else 'A MATCHING RECORD WAS FOUND
MsgBox "Login Successful...Proceed to subform!",
vbInformation +
vbOKOnly, "Information"


'&&&&&&&&&&&&&&&&&
'I need some code here that pulls proper record set (in qryTest)
based on Division/Branch selection
'&&&&&&&&&&&&&&&&&



End If

End If
End Sub
****************************
Click to expand...


Open qryTest in design mode.
In the first column of the grid (Division), in the Criteria row, enter

Forms!frmLogIn.cboDivision

In the second column of the grid (Branch), in the Criteria row, enter

Forms!frmLogIn.cboBranch

I would change the query from a Totals query to just a standard Select
query.

Where you have &&&&&&&&&&&& in your code add:

DoCmd.OpenQuery "qryTest"


(It would be better to make a new form with the form recordset set to
qryTest and use
DoCmd.OpenForm "frmTheFormName".)


Don't forget to clean up by closing the recordset and destroying the
references you created. Add these lines just above the "End Sub" so it
looks
like this:

'<snip>
End If

End If
rst.Close
Set rst = Nothing
Set db = Nothing
End Sub


HTH,

Steve
Click to expand...
 
Back
Top