Using DLookup on a query to set value on AfterUpdate

  • Thread starter Thread starter Sandy
  • Start date Start date
S

Sandy

I am getting an Error as follows:

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

I have the following Event on AfterUpdate on the JobType field:

Private Sub JobType_AfterUpdate()
[JobNum]=DLookup("masof JobNum","Q_JobNumMax","JobType= " & [JobType]
End Sub

There are several types of job types
Each Job type has a range of numbers that can be assigned to that job.
ex TN - 10,000 to 19,999
TE - 20,000 to 29,999
etc

So when I create a new job and select TN as the job type, once that
selection has been made, I would like to get the MaxofJob Num for TN jobs
from the query, add 1 to it, and make that the next job number.

Thanks for your help!
sandra
 
Thanks - Had the JobType set as a number instead of string!

ruralguy via AccessMonster.com said:
Here's a good reference for the DLookup() syntax:
http://www.mvps.org/access/general/gen0018.htm
...your error is almost always a problem with the syntax.
I am getting an Error as follows:

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

I have the following Event on AfterUpdate on the JobType field:

Private Sub JobType_AfterUpdate()
[JobNum]=DLookup("masof JobNum","Q_JobNumMax","JobType= " & [JobType]
End Sub

There are several types of job types
Each Job type has a range of numbers that can be assigned to that job.
ex TN - 10,000 to 19,999
TE - 20,000 to 29,999
etc

So when I create a new job and select TN as the job type, once that
selection has been made, I would like to get the MaxofJob Num for TN jobs
from the query, add 1 to it, and make that the next job number.

Thanks for your help!
sandra
Click to expand...

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com
Click to expand...
 
Hi Sandra

Did you copy and paste the code below, or just type it into your message?
Because it has a couple of errors (or typos) in it:
- You have "masof JobNum" instead of "maxofJob Num".
- You have no closing parenthesis

Other problems are:
- if a field name contains non-alphanumeric characters (not a good idea!)
you should enclose it in square brackets.
- it looks like JobType is a text field, so you should enclose the match
value in quotes.

I suggest you alter your query (Q_JobNumMax) to give Max([Job Num]) a field
name other than the default.

In the grid cell where you have Job Num, change it to MaxJobNum: [JobNum]

Then add quotes to your DLookup function call.

Finally, use Nz to cater for the case where you do not yet have a job of
that type.

Final result:

[JobNum] = Nz( DLookup( "MaxJobNum", "Q_JobNumMax", _
"JobType='" & [JobType] & "'" ), 0 ) + 1
 
I am getting an Error as follows:

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

I have the following Event on AfterUpdate on the JobType field:

Private Sub JobType_AfterUpdate()
[JobNum]=DLookup("masof JobNum","Q_JobNumMax","JobType= " & [JobType]
End Sub

There are several types of job types
Each Job type has a range of numbers that can be assigned to that job.
ex TN - 10,000 to 19,999
TE - 20,000 to 29,999
etc

So when I create a new job and select TN as the job type, once that
selection has been made, I would like to get the MaxofJob Num for TN jobs
from the query, add 1 to it, and make that the next job number.
Click to expand...

Well, you have several errors there: no brackets around the fieldname (which
needs them as it contains a blank), no quotes around the jobtype, no closing
parenthesis, no +1. I'd also use DMax() to find the current max job num
directly rather than using a separate query.

Try

Private Sub JobType_AfterUpdate()
If Not IsNull(Me!JobType) Then
Me![JobNum]=NZ(DMax("[JobNum]","[jobtablename]", _
"JobType= '" & Me![JobType] & "'")) + 1
End If
End Sub


You may need to fiddle around with the NZ() function to put in the starting
job number; if there is no record at all for TE, for example, this algorithm
will assign 1 rather than 20000.
 
Back
Top