Log on facility combo box not recognising values

[shaunacol]

I have a log on facility which is working well but when the user chooses their user name from a combo box I want it to change the hidden fields in the form I have as well.

So I have this code behind a button to log on

Private Sub LogOn_Click()


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

If IsNull(Me.cboEmployee) Or Me.cboEmployee = "" Then
MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
Me.cboEmployee.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 tblEmployees to see if this
'matches value chosen in combo box

If Me.txtPassword.Value = Me.Stfpass.Value Then

'Close logon form and open splash screen



If Me.StfAccess.Value = "manager" Then
DoCmd.OpenForm "My_data Manager"
Else
DoCmd.OpenForm "my_data Staff"
End If
DoCmd.Close acForm, "Log-on", acSaveNo

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

'If User Enters incorrect password 3 times database will shutdown

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

End Sub


As I said this works except for the part where it looks at the staffaccess level. The reason it doesnt work is that staffAccess is a hidden field. So....when the comboBox cboemplyee changes there is a macro behind the afterupdate to make sure that the right hidden staffaccess level is shown. This works in one of my preivous versions of the database but has now stopped! The version that works contains a SEARCH FOR RECORD macro with the following where condition

="[Staff ID] = " & Str(Nz([Screen].[ActiveControl];0))

I tried to change this to me.cboemployee 0 me.staffID but get an error on this as well.

Its driving me mad now as I know it works on a previous version

 

[MajP]

what is the SQl for cboEmployee?
What is the bound column?
What is the data type of the bound column?
What is the column count?

Your search looks wrong

I make this a lot simpler

dim empID as long (assuming that is what it is)
dim staffAccess as string
dim staffPass as string
dim strWhere as string

'You already checked this to be not null
empID = me.cboEmployee.value
'if a non bound column then empID = me.cboEmployee.column(N)
'where n is the column number - 1 since 0 based
'debug.print empID
strWhere = "StaffID = " & empID
'or if string
'strWhere = "staffId = '" & empID & "'"
'debug.print strWhere
staffPass = dlookup("staffPassFieldname", "tblEmployee",strWhere)
'debug.print staffPass
staffAccess = dlookup("staffAccessFieldName","tblEmployee", strWhere)
'debug.print staffAccess

simple from here.

 

[shaunacol]

what is the SQl for cboEmployee? I dont have SQl , it is an embedded macro in the after update property with details above
What is the bound column? bound column for cboemployee is 1
What is the data type of the bound column? data type is number
What is the column count? column count is 2 with the bound column hidden so it just shows peoples name

Ill have a look at the code above, many thanks

 

[MajP]

The rowsource of the combo box will be a SQL string or value list. What is the rowsource?

 

[shaunacol]

Oh I see sorry, its been a long week already. Its an unbound combo box but the row source for the drop down is TblStaff

 

[MajP]

empID = me.cboEmployee.column(x)
staffAccess = me.cboEmployee.column
staffPass = me.cboEmployee.column(z)

x y z are the column number - 1. Columns are zero based.