Passing values from one Form to the Next to filter records

[nika.duncan]

Hi all,


I have a database that measures your customer service relationship. From the login form "FrmLogin" I want a check to be done to find out which department you're from and only open & list records relating to that department. On the login form it does picks up the department but when the other form opens "Copy of frmUserDetails" and you click on the combo box to enter the name of staff to measure a pop box comes up asking for parameter value, if you enter the department the listing appear of all the staff for that department. But the count of records below shows the entire database and not the actual listing in the search criteria. If I use the next and previous arrows below I will be able to view records of other department. I have the following questions:

1. How do I save the department info listed on the first form "FrmLogin" after close so that it passes that value to the "Copy of frmUserDetails" form so that I wouldn't have to enter the parameter value for the department.
2. How do I only show the listing of only what is in the search or can I disable that section so that they can't scroll through the database

For the "Copy of frmUserDetails" form I ran a query to filter the data hence the parameter request
I have included the code for the login and the query in sql view for anyone to have a look at it.

Private Sub CmdLogin_Click()'/First thing needed is to decide which option to take
'/If there is an entry in the txtoldpwd field this is an existing password
'/If there is an entry in the txtconpwd field this is a new password setup
'/If there is an entry in all three then the user has reset their password


'/If password has been reset then all the validation has taken place
'/so can open the main menu straight away


Me.DepartmentName = Me.CboUser.Column(8)
DeptName = DLookup("[DepartmentName]", "tbl-Permissions", "CboUser = Forms!FrmLogin!CboUser")


If bReset = True Then
    '/Open the main form and close this one
    'DoCmd.OpenForm "FrmMain"
   ' MsgBox "You main form/ switchboard would be loaded here.", vbInformation + vbOKOnly, "End Of Demo"
     Select Case Me.CboUser.Column(7)
        Case 1
            DoCmd.OpenForm "frmAdminMode"
        Case 2
           DeptName = DLookup("[DepartmentName]", "tbl-Permissions", "CboUser = Forms!FrmLogin!CboUser") 'This is where I want to pass the department to the new form
                      
            DoCmd.OpenForm "Copy Of frmUserDetailsInfo", acNormal, Me.CboUser.Column(8)
            
          ' DoCmd.OpenForm "Copy Of frmUserDetailsInfo"
            'DoCmd.OpenForm "Copy of frmUserDetailsInfo", acNormal, , , , acWindowNormal
            
        Case 3
            DoCmd.OpenForm "frmSupervisors"
        Case 4
            DoCmd.OpenForm "frmReportsRequester"
    End Select
    
    Me.Visible = False
        DoCmd.Close acForm, "FrmLogin"
    Exit Sub
End If




If Trim(Me.TxtOldPWD & "") <> "" Then
    '/does it match the users password
    If Trim(Me.TxtOldPWD & "") <> DecryptKey(Me.CboUser.Column(4)) Then
        '/No Match
        MsgBox "The password you have entered cannot be recognised.", vbExclamation + vbOKOnly, "Invalid Password"
        Me.TxtOldPWD = ""
        Attempts = Attempts + 1
        
        '/Three tries and your out
        If Attempts = MaxAttempts Then
            MsgBox "Maximum number of attempts has been reached", vbExclamation + vbOKOnly, "Login aborted"
            DoCmd.Quit
            Exit Sub
        End If
        Exit Sub
    End If
    '/Open the main form and close this one
   ' MsgBox "You main form/ switchboard would be loaded here.", vbInformation + vbOKOnly, "End Of Demo"
     Select Case Me.CboUser.Column(7)
        Case 1
            DoCmd.OpenForm "frmAdminMode"
        Case 2
             Me.Filter = "[DepartmentName] = " & Forms!FrmLogin!CboUser.Column(8)
            ' DoCmd.OpenForm "Copy Of frmUserDetailsInfo", acNormal, Me.CboUser.Column(8)
           DeptName = DLookup("[DepartmentName]", "tbl-Permissions", "CboUser = Forms!FrmLogin!CboUser")
 
            DoCmd.OpenForm "Copy Of frmUserDetailsInfo"
        Case 3
            DoCmd.OpenForm "frmSupervisors"
        Case 4
            DoCmd.OpenForm "frmReportsRequester"
    
    End Select
   
   DoCmd.Close acForm, "FrmLogin"
Else
    '/Has the user go a password
    If DecryptKey(Me.CboUser.Column(4)) <> "Not Set" Then
        MsgBox "You must enter you password first", vbExclamation + vbOKOnly, "Mandatory Requirement"
        Attempts = Attempts + 1
        
        '/three tries and your out.
        If Attempts = MaxAttempts Then
            MsgBox "Maximum number of attempts has been reached", vbExclamation + vbOKOnly, "Login aborted"
            DoCmd.Quit
            Exit Sub
        Else
            Exit Sub
        End If
        
    End If
    '/New password setup
    '/Compare both both entries as matching
    '/Is there an entry in either of the text boxes
    If Trim(Me.TxtNewPWD & "") = "" Then
        MsgBox "You must enter a new password. Cannot be left blank.", vbExclamation + vbOKOnly, "Invalid Password"
        Exit Sub
    End If
    If Trim(Me.TxtConPWD & "") = "" Then
        MsgBox "You must confirm the new password. Cannot be left blank.", vbExclamation + vbOKOnly, "Invalid Password Confirmation"
        Exit Sub
    End If
    '/do they match
    If Trim(Me.TxtNewPWD & "") <> Trim(Me.TxtConPWD & "") Then
        MsgBox "Passwords do not match.", vbExclamation + vbOKOnly, "Invalid Password Confirmation"
        Me.TxtConPWD.SetFocus
        Exit Sub
    Else
        '/they both match so we can add this new password to the users record
        Dim rs As DAO.Recordset
        Set rs = CurrentDb.OpenRecordset("Select * From [Tbl-Permissions] Where FKUserID=" & Me.CboUser.Column(0))
        If Not rs.EOF And Not rs.BOF Then
            rs.Edit
            rs("LoginName") = StrUserName
            rs("PWD") = EncryptKey(Me.TxtConPWD)
            rs("fldDatePWD") = Date
            rs.Update
            rs.Close
        End If
        Set rs = Nothing
        MsgBox "You password has been set", vbInformation + vbOKOnly, "New Password"


        '/Open the main form and close this one
        'MsgBox "You main form/ switchboard would be loaded here.", vbInformation + vbOKOnly, "End Of Demo"
         Select Case Me.CboUser.Column(7)
        Case 1
            DoCmd.OpenForm "frmAdminMode"
        Case 2
         ' Me.Filter = "[DepartmentName] = " & Forms!FrmLogin!CboUser.Column(8)
            'DoCmd.OpenForm "Copy Of frmUserDetailsInfo", acNormal, Me.CboUser.Column(8)
            DeptName = DLookup("[DepartmentName]", "tbl-Permissions", "CboUser = Forms!FrmLogin!CboUser")
 
            DoCmd.OpenForm "Copy Of frmUserDetailsInfo"
        Case 3
            DoCmd.OpenForm "frmSupervisors"
        Case 4
            DoCmd.OpenForm "frmReportsRequester"
    End Select
        'DoCmd.OpenForm "FrmMain"
        DoCmd.Close acForm, "FrmLogin"
    End If
    
    
End If




        
  ' Me.Filter = "[DepartmentName] = " & Forms!FrmLogin!CboUser.Column(8)


End Sub


'"Copy of frmUserDetails Query in SQL view 

SELECT UserDetails.StaffName, UserDetails.*, UserDetails.DepartmentName
FROM UserDetails
WHERE (((UserDetails.DepartmentName)=[Forms]![FrmLogin]![DepartmentName]))
ORDER BY UserDetails.StaffName;

Any assistance is welcomed.
Thanks.
Nika

[ItsMe]

...you click on the combo box to enter the name of staff to measure a pop box comes up asking for parameter value...

You should probably address the issue with the parameter value. Sounds like the control name that is in the Combobox's Control Source property is not available or the control is not bound to a valid field within the form's recordset.

[ssanfu]

Case 2
DeptName = DLookup("[DepartmentName]", "tbl-Permissions", "CboUser = Forms!FrmLogin!CboUser") 'This is where I want to pass the department to the new form

DoCmd.OpenForm "Copy Of frmUserDetailsInfo", acNormal, Me.CboUser.Column(8)

' DoCmd.OpenForm "Copy Of frmUserDetailsInfo"
'DoCmd.OpenForm "Copy of frmUserDetailsInfo", acNormal, , , , acWindowNormal

I would use the "openargs" argument of the Openform command.

Syntax:
DoCmd.OpenForm formname[, view][, filtername][, wherecondition][, datamode][, windowmode][, openargs]

Example:

DoCmd.OpenForm "Copy Of frmUserDetailsInfo", acNormal,,,,, DeptName

The commas are required. I deleted "Me.CboUser.Column(8)" from the "filtername" property to make it clearer.

Then, in "Copy Of frmUserDetailsInfo" (shouldn't use spaces in names), you could use the form open event to check if "openargs" has a value and process as required.

From Help;

openargs	A string expression. This expression is used to set the form's OpenArgs property. This setting can then be used by code in a form module, such as the Open event procedure. The OpenArgs property can also be referred to in macros and expressions.
	For example, suppose that the form you open is a continuous-form list of clients. If you want the focus to move to a specific client record when the form opens, you can specify the client name with the openargs argument, and then use the FindRecord method to move the focus to the record for the client with the specified name.
	This argument is available only in Visual Basic.

Another way is to open the new form, push the value into a hidden unbound control on the new form, then close the old form. The value of "DeptName" would then be available in the new form.