Check user

[Diogo]

Hi. I'm using the following code to see if a worker is in its vacation
period, preventing him from logging in to the data base.

'Check to see if User is in vacation period

If Me.cboEmployee.SelText = DLookup("Name", "tblVacations") And
DateValue(Now()) = DateValue(DLookup("Date", " tblVacations ")) Then
MsgBox "Worker on Vacations", 48, "Vacations Period."
Exit Sub
End If

If the user name and the corresponding date field correspond to the
criteria the login is prevented.
What I'm I doing wrong?
Please help.
Thanks.

 

[Stefan Hoffmann]

hi,

Hi. I'm using the following code to see if a worker is in its vacation
period, preventing him from logging in to the data base.

'Check to see if User is in vacation period

If Me.cboEmployee.SelText = DLookup("Name", "tblVacations") And

Don't use .SelText. It is the selected, marked part in your combo box,
not necessarily the entire string.
Use .Value instead.

Also Name is a reserved word. Don't use it as field name, otherwise
always use square brackets, e.g. [Name].

DateValue(Now()) = DateValue(DLookup("Date", " tblVacations ")) Then
MsgBox "Worker on Vacations", 48, "Vacations Period."
Exit Sub
End If

What I'm I doing wrong?

A date/time field stores a point in time not a time period. So you need
a field DateBegin and DateEnd to store your period.

Then you can simplfy your query:

Dim Condition As String

Condition = "[Name] = '" & Replace(cboEmployee.Value, "'", "''") & "'"
Condition = Condition & " AND " & _
Format(ADateTime, "\#m\/d\/yyyy hh\:nn\:ss\#") & " " & _
"BETWEEN DateBegin AND DateEnd"

If DCount("*", "tblVacations", _
Condition) > 0 Then
MsgBox "in vacation"
End If


mfG
--> stefan <--

 

[Douglas J. Steele]

Looks as though you don't have a value for ADateTime.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Unfortunatly this didn't worked could someone help please???????
It returns an error:
"Run-time error '3075':
Syntax error (missing operator) in query '[Name]='1'
AND BETWEEN DateBegin AND DateEnd"

hi,

Hi. I'm using the following code to see if a worker is in its vacation
period, preventing him from logging in to the data base.

'Check to see if User is in vacation period

If Me.cboEmployee.SelText = DLookup("Name", "tblVacations") And

Don't use .SelText. It is the selected, marked part in your combo box,
not necessarily the entire string.
Use .Value instead.

Also Name is a reserved word. Don't use it as field name, otherwise
always use square brackets, e.g. [Name].

DateValue(Now()) = DateValue(DLookup("Date", " tblVacations ")) Then
MsgBox "Worker on Vacations", 48, "Vacations Period."
Exit Sub
End If

What I'm I doing wrong?

A date/time field stores a point in time not a time period. So you need
a field DateBegin and DateEnd to store your period.

Then you can simplfy your query:

Dim Condition As String

Condition = "[Name] = '" & Replace(cboEmployee.Value, "'", "''") & "'"
Condition = Condition & " AND " & _
Format(ADateTime, "\#m\/d\/yyyy hh\:nn\:ss\#") & " " & _
"BETWEEN DateBegin AND DateEnd"

If DCount("*", "tblVacations", _
Condition) > 0 Then
MsgBox "in vacation"
End If


mfG
--> stefan <--

 

[Douglas J. Steele]

Assuming that your code is the same as earlier in this thread

Condition = "[Name] = '" & Replace(cboEmployee.Value, "'", "''") & "'"
Condition = Condition & " AND " & _
Format(ADateTime, "\#m\/d\/yyyy hh\:nn\:ss\#") & " " & _
"BETWEEN DateBegin AND DateEnd"

Condition should be something like:

[Name]='1' AND #12/31/2006# BETWEEN DateBegin AND DateEnd

However, the error message is saying that you've got

[Name]='1' AND BETWEEN DateBegin AND DateEnd

That implies that ADateTime didn't have a value, so that Format(ADateTime,
"\#m\/d\/yyyy hh\:nn\:ss\#") generated an empty string.


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

What???
I didn't understand what you mean. Could you give me an example

Looks as though you don't have a value for ADateTime.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Unfortunatly this didn't worked could someone help please???????
It returns an error:
"Run-time error '3075':
Syntax error (missing operator) in query '[Name]='1'
AND BETWEEN DateBegin AND DateEnd"

:

hi,

Diogo wrote:
Hi. I'm using the following code to see if a worker is in its
vacation
period, preventing him from logging in to the data base.

'Check to see if User is in vacation period

If Me.cboEmployee.SelText = DLookup("Name", "tblVacations") And
Don't use .SelText. It is the selected, marked part in your combo box,
not necessarily the entire string.
Use .Value instead.

Also Name is a reserved word. Don't use it as field name, otherwise
always use square brackets, e.g. [Name].

DateValue(Now()) = DateValue(DLookup("Date", " tblVacations ")) Then
MsgBox "Worker on Vacations", 48, "Vacations Period."
Exit Sub
End If

What I'm I doing wrong?
A date/time field stores a point in time not a time period. So you
need
a field DateBegin and DateEnd to store your period.

Then you can simplfy your query:

Dim Condition As String

Condition = "[Name] = '" & Replace(cboEmployee.Value, "'", "''") &
"'"
Condition = Condition & " AND " & _
Format(ADateTime, "\#m\/d\/yyyy hh\:nn\:ss\#") & " " &
_
"BETWEEN DateBegin AND DateEnd"

If DCount("*", "tblVacations", _
Condition) > 0 Then
MsgBox "in vacation"
End If


mfG
--> stefan <--

 

[Stefan Hoffmann]

hi Diogo,

Unfortunatly this didn't worked could someone help please???????
It returns an error:
"Run-time error '3075':
Syntax error (missing operator) in query '[Name]='1'
AND BETWEEN DateBegin AND DateEnd"

As Douglas already wrote: you need to fill the variable ADateTime, which
is in your case Now().
How do you store the vacations. Can you give us an example, as i assume
that you do it wrong.

mfG
--> stefan <--

 

[Diogo]

I'm storing the employee name in a table called tblemployee, but this name is
the one he uses to login. In that same table is the employee's password wich
he uses to confirm. I then have a seconde table, called tblvacations that has
3 entries: Name, vacationbegin, and vacationend.
What I need is that based on the name entered in the form, and before the
form sets focus to the password field, it should go to the second table and
for that name, see if the employee is suposed to be on its vacations period,
and if so it should prevent the login. I need it to see the current date and
see if it falls within that time frame "vacationbegin" and "vacationend".
I hope some one can help. Thaks

hi Diogo,

Unfortunatly this didn't worked could someone help please???????
It returns an error:
"Run-time error '3075':
Syntax error (missing operator) in query '[Name]='1'
AND BETWEEN DateBegin AND DateEnd"

As Douglas already wrote: you need to fill the variable ADateTime, which
is in your case Now().
How do you store the vacations. Can you give us an example, as i assume
that you do it wrong.

mfG
--> stefan <--

 

[Stefan Hoffmann]

hi Diogo,

I'm storing the employee name in a table called tblemployee, but this name is
the one he uses to login. In that same table is the employee's password wich
he uses to confirm. I then have a seconde table, called tblvacations that has
3 entries: Name, vacationbegin, and vacationend.
What I need is that based on the name entered in the form, and before the
form sets focus to the password field, it should go to the second table and
for that name, see if the employee is suposed to be on its vacations period,
and if so it should prevent the login. I need it to see the current date and
see if it falls within that time frame "vacationbegin" and "vacationend".

Your table tblVacations should not store the employee name, cause that
is redundant. Use a table layout like the following:

tblEmployee:
ID long integer (auto increment, primary key)
LoginName string (not null, not empty, unique index)
Password String
....

tblVacation:
ID long integer (auto increment, primary key)
Employee_ID long integer (foreign key)
VacationBegin date/time (not null)
VacationEnd date/time (not null, > VacationBegin)

Use the following code:

Private Sub cmdLogin_Click()

Dim Employee_ID As Integer
Dim InVacation As Boolean

Employee_ID = Nz(DLookup("ID", _
"tblEmployee", _
"LoginName = '" & txtLogin.Value & " '"), -1)
If Employee_ID = -1 Then
MsgBox "Wrong login name or password.", vbOkOnly Or vbInformation
Exit Sub
End If

InVacation = (DCount("ID", _
"tblVacation", _
"Employee_ID = " & Employee_ID & " AND " & _
"VacationBegin < Now() AND " & _
"VacationEnd > Now()") > 0)

If InVacation Then
MsgBox "Login prevented.", vbOkOnly Or vbCritical
Exit Sub
End If

'your other login code

End Sub


mfG
--> stefan <--

 

[Stefan Hoffmann]

hi,

Employee_ID = Nz(DLookup("ID", _
"tblEmployee", _
"LoginName = '" & txtLogin.Value & " '"), -1)

Not quite correct, the condition must be:

"LoginName = '" & Replace(txtLogin.Value, "'", "''") & "'"

There was a single space to much and no escaping for the inverted comma.


mfG
--> stefan <--

 

[Douglas J. Steele]

You positive the table name is tblEmployee, and the two fields in it as ID
and LoginName?

That very misleading error message will occur if you've got something wrong
in the DLookup statement.

For that matter, are you sure that the control on the form is txtLogin? (Try
Me.txtLogin if it is)