how to open a form using docmd.openform with 2 criteria

[veshand]

Hello everyone!!
I am trying to open a second form ("frmManager") using docmd.OpenForm order in the first form ("frmLogin"), which has got two different criteria:

1-on one hand, i want it to open the form into specific username data ([EntryEmploee]) which is provided by user. this field is specified by a combo box and returns name of usernames in text. users can be either managers or employees! which means two levels of security.

2-on the other hand, i want it to open only those records which are stated as yes, in a yes/no field ([Bossapprove]). this field is reserved for the manager. whenever this field is filled as yes, i dont want the employees to be able to see the records to edit or delete! it means that once the managers click on this field, the employees wont have access to that specific record!

its wise to mention that the second form directly edits one main table and two levels of sub datasheets (tblEmployee->tblDay->tblTimesheet)

whenever i use the following command, access asks me to specify the value for "Bossapprove".

DoCmd.OpenForm "frmReg", , , "[EmployeeName] = '" & Me.EntryEmploee.Text & "'and [Bossapprove]=true"

i know i can use a query instead, but i believe im this -><- close to do it just using a single VBA code!

help me out guys!!


for further information, i insert the whole form VBAcodes too!
Option Compare Database

Private Sub Command1_Click()
Dim User As String


Dim UserLevel As Integer
Dim TempPass As String
Dim ID As Integer
Dim UserName As String
Dim TempID As String

If IsNull(Me.EntryEmploee) Then
MsgBox "please select a user", vbInformation, "Username required"
Me.EntryEmploee.SetFocus
ElseIf IsNull(Me.EntryPass) Then
MsgBox "Please enter Password", vbInformation, "Password required"
Me.EntryPass.SetFocus
Else
Me.EntryEmploee.SetFocus
If (IsNull(DLookup("EmployeeName", "tblEmployee", "EmployeeName = '" & Me.EntryEmploee.Text & "' And UserPassword = '" & Me.EntryPass.Value & "'"))) Then
MsgBox "Invalid Username or Password!"
Else
TempID = Me.EntryEmploee.Text
UserName = DLookup("[EmployeeName]", "tblEmployee", "[EmployeeName] = '" & Me.EntryEmploee.Text & "'")
UserLevel = DLookup("[UserType]", "tblEmployee", "[EmployeeName] = '" & Me.EntryEmploee.Text & "'")
TempPass = DLookup("[UserPassword]", "tblEmployee", "[EmployeeName] = '" & Me.EntryEmploee.Text & "'")
UserLogin = DLookup("[EmployeeName]", "tblEmployee", "[EmployeeName] = '" & Me.EntryEmploee.Text & "'")

If (TempPass = "password") Then
MsgBox "Please change Password", vbInformation, "New password required"
DoCmd.OpenForm "frmUserinfo", , , "[UserLogin] = " & UserLogin
Else
'open different form according to user level
If UserLevel = 1 Then ' for admin
DoCmd.OpenForm "frmAdmin"
ElseIf UserLevel = 2 Then
DoCmd.OpenForm "frmManager", , , "[EmployeeName] = '" & Me.EntryEmploee.Text & "'"
Else
DoCmd.OpenForm "frmReg", , , "[EmployeeName] = '" & Me.EntryEmploee.Text & "'and [Bossapprove]=true"
DoCmd.Close acForm, "frmLogin"
End If
DoCmd.Close acForm, "frmLogin"
End If
End If
End If
End Sub


Private Sub Form_Load()
Me.EntryEmploee.SetFocus
End Sub

[CJ_London]

you can only refer to the .text property whist that particular control has the focus - since your code is on command1, it does not have to focus. There is no need to reference the .value property because that is the default. So I'm surprised the code runs at all.

you are also missing a space between ' and and

I presume [Bossapprove] exists in the recordsource to your form and is spelt correctly - you seem to have other spelling differences e.g. EntryEmploee

[isladogs]

Cross posted and answered at https://www.access-programmers.co.uk...d.php?t=300100

Welcome to this forum as well.
However, please follow standard forum guidelines about cross posting. See this link for an explanation https://www.excelguru.ca/content.php?184