Don't understand error

  • Thread starter Thread starter AngiW
  • Start date Start date
A

AngiW

I'm getting an error I've never seen before and the help is blank, so
hopefully, someone can help me. The only thing I can think of is that
technically, the dlookup isn't right. The Hours field is in the positions
table that is connected to the employee main table with key Position, which is
then connected to the Used table with the employee id key. But I don't think
this is the error I would've received if that was the problem. Is there a way
to connect two tables that are connected by another table? The msgbox is wrong
too...have to work on that next.

Run-time error '2001':
You canceled the previous operation.

I didn't do anything, but tab out of the field. Here's my code:
Private Sub To_Exit(Cancel As Integer)
Dim Msg2, Style, response
Dim DaysUsed, HoursUsed
Msg2 = "Total hours taken: " & HoursUsed
Style = vbOKOnly + vbExclamation

DaysUsed = DateDiff("d", Me.From, Me.To)
HoursUsed = DaysUsed * (DLookup("[Hours]", "employee main", "[Employee ID] =" &
Me.EmployeeID))
response = MsgBox(Msg2, Style)



End Sub
 
I think you need to place []s around table and field names that contain
spaces. A better solution is to never create tables or fields with spaces in
the name. When you set the value of Msg2, you haven't set the value of
HoursUsed yet.
You might want to change the code to place it in the after update event.

Do you know how to set a break point and step through your code?
 
Duane,
Thank you for the reply. I have done stepping through code before, but never
used break points. I did as you said and I'm still getting the same error.
It's still highlighting the HoursUsed= line. As I said before, I don't
understand the error, you canceled the previous operation. What does that
mean??? Thanks again!
 
Sometimes error messages are not what they should be. Did you fix the line
of code as suggested? What does your code look like now?
 
It automatically takes the [] off of the table in the dlookup after I save and
exit. I don't think it needs them because i'm using that same line elsewhere
in code and it works fine. As I said before, Hours is NOT a field in the
Employee Main table. It's in the Positions table. I'm using employee main for
the employee id. Is that the problem and if so, how do i connect the Used with
Positions using the Employee Main table? Or can't I?

Private Sub txtTo_Exit(Cancel As Integer)
Dim DaysUsed, HoursUsed

DaysUsed = DateDiff("d", Me.txtFrom, Me.txtTo)
HoursUsed = DaysUsed * (DLookup("[Hours]", "employee main", "[Employee ID] =" &
Me.EmployeeID))

Dim Msg2, Style, response
Msg2 = "Total hours taken: " & HoursUsed
Style = vbOKOnly + vbExclamation

response = MsgBox(Msg2, Style)

End Sub
 
If Hours is not in the EmployeeMain table then your code will definitely
break. You can create a query to replace the table name in DLookup().
 
Duane,
I didn't think what I was doing was right. My next question is, how do I
create a query to replace the table name in DLookup().
Click to expand...

I've been working on this doing it different ways and I obviously have no clue.
Here's the info that is relevant:

[Accruals used].[employeeid]
relationship with
[employee main].[employee id]

[employee main].position
relationship with
[positions].position

[positions].hours

I need the result of hourstaken * hours for that employee. If you could just
point me in the right direction of what I need to do to get started, I can
hopefully figure it out. Thanks again!
 
If you have no clue how to create a query then you should find a good book
or other reference on queries. You need to add the tables that combine
EmployeeID all the way to Hours.

--
Duane Hookom
MS Access MVP


AngiW said:
Duane,
I didn't think what I was doing was right. My next question is, how do I
create a query to replace the table name in DLookup().
Click to expand...

I've been working on this doing it different ways and I obviously have no clue.
Here's the info that is relevant:

[Accruals used].[employeeid]
relationship with
[employee main].[employee id]

[employee main].position
relationship with
[positions].position

[positions].hours

I need the result of hourstaken * hours for that employee. If you could just
point me in the right direction of what I need to do to get started, I can
hopefully figure it out. Thanks again!
Click to expand...
 
I have created a query that does connect the tables, I just didn't know where
to put it so that it replaces the table in the code. I've created thousands of
queries, that's not the problem. I think I figured it out, but it's still not
working. Getting the same error as before: You canceled the previous
operation. I know you're getting frustrated and I apologize...I've read the
books, I've read the help, nothing tells me what I'm doing wrong or why I'm
getting this error. I step through, and that doesn't help either. It won't
let me step over or out. Please be patient with me.

Here's the query HoursUsedQ:
SELECT Positions.Hours
FROM Positions INNER JOIN ([Employee Main] INNER JOIN [Accruals Used] ON
[Employee Main].[Employee ID] = [Accruals Used].EmployeeID) ON
Positions.Position = [Employee Main].Position;

Here's the new code:
HoursUsed = DaysUsed * DLookup("[Hours]", "HoursUsedQ", "[Employee ID] =" &
Me.EmployeeID)
 
You must also Select the EmployeeID field in the query. You have only
selected the Hours field.
 
THANK GOD! IT'S OVER! That was it! Thank you so much, Duane! I'm sure I'll
be on here again!

Sincerely,
Angi
 
Back
Top