how to open a form using docmd.openform with two 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". :banghead:

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

 

[isladogs]

Welcome to AWF

You don't need the [] as the field names don't contain spaces or special characters. Also omit the .Text

If the field spelling really is EntryEmploee then

DoCmd.OpenForm "frmReg", , , "EmployeeName = '" & Me.EntryEmploee & "' And Bossapprove=True"

If its EntryEmployee then

DoCmd.OpenForm "frmReg", , , "EmployeeName = '" & Me.EntryEmployee & "' And Bossapprove=True"

 

[veshand]

Hello ridders!
thanks for your reply
as you ordered, i ommited the [] and .text. i also corrected the "employee"

DoCmd.OpenForm "frmReg", , , "EmployeeName = '" & Me.EntryEmployee.Text & "' And Bossapprove=True"

however, i still get the "Enter Parameter Value-> bossapprove" message!

is it the "and" command or the yes/no type which is causing the problem?!
:banghead:

 

[isladogs]

Neither should be an issue.
Check the spelling of Bossapprove as well - spelling errors cause parameter popups as Access can't find what its looking for

Otherwise, you may have included parameters in the form record source

Open the form in design view then open its record source from the property sheet. You will see a query design window. In the ribbon, click Design then Parameters. Remove Bossapprove from the Query Parameters list if its there

You can also just delete the PARAMETERS section in the record source SQL
e.g. PARAMETERS SearchItem Text ( 255 ); SELECT tblJSONFiles.* FROM tblJSONFiles WHERE (((tblJSONFiles.SearchItem)="CurrencyExchange"));

NOTE:
Cross posted at http://www.accessforums.net/showthread.php?t=72435

Please follow standard forum guidelines about cross posting.
See this link for an explanation https://www.excelguru.ca/content.php?184