Using DLookup on a query to set value on AfterUpdate

[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

 

[Sandy]

Thanks - Had the JobType set as a number instead of string!

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

--
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

 

[Graham Mandeno]

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

 

[John W. Vinson]

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.

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.