Prevent Duplicate Job Orders

[rebecky via AccessMonster.com]

I am not sure how to do this......Access 2003 Employer/Job Order Database

The Employer is in the main form, the Job Order is in the subform. Each
Employer can have many Job Orders. What I want to do is check the "Position
Type" to prevent duplicate entry.

For Example: Smiths Coffee House has a Job Order for Waitress, Cook, and
Dishwasher. If a user goes to enter a new Job Order for Waitress at Smiths
Coffee House, I want an alert that tells them this employer already has an
order for Waitress. I am trying to prevent them from entering duplicate Job
Orders.

Thanks!

 

[Klatuu]

This issue goes a little deeper than that (read your follow up question).

Smiths Coffee House may have a current job posting for a waitress so you
don't want to add another; but, what happens when this position is filled?
Next month Smith may want another waitress. How do you know the previous job
posting is not still Active?

Now, as the basics of the question. You would want to use the form Before
Update event in your subform and a DLookup to determine if another posting
exists. You would use the table the subform is based on because it will need
to have the employer and the posting data in it as well as some way to know
if it is an active posting or not:

If Not IsNull(DLookup("[JobPosting]", "PostingTable", "[EmployerID] = "
& Me.txtEmployer & " AND [JobPosting] = """ & Me.txtJobPosting & """" & " And
[ActivePosting] = 0") Then
MsgBox Me.txtEmployer & " Has An Active Posting For " &
Me.txtJobPosting
Cancel = True
Me.Undo
End If

 

[rebecky via AccessMonster.com]

Well, we have a field called "open/filled" from which "filled" is selected
when someone is placed there. We record the placement in another table.
When the position is open again, we just change the status via the
"open/filled" field. Also, there is a field called "number of slots" in
which to enter "how many" waitresses Smith needs. So I am not sure if it
matters wether the position is filled or not.......I just don't want the
exact same position entered twice!

Thank you for your help. Will try your solution right now!

This issue goes a little deeper than that (read your follow up question).

Smiths Coffee House may have a current job posting for a waitress so you
don't want to add another; but, what happens when this position is filled?
Next month Smith may want another waitress. How do you know the previous job
posting is not still Active?

Now, as the basics of the question. You would want to use the form Before
Update event in your subform and a DLookup to determine if another posting
exists. You would use the table the subform is based on because it will need
to have the employer and the posting data in it as well as some way to know
if it is an active posting or not:

If Not IsNull(DLookup("[JobPosting]", "PostingTable", "[EmployerID] = "
& Me.txtEmployer & " AND [JobPosting] = """ & Me.txtJobPosting & """" & " And
[ActivePosting] = 0") Then
MsgBox Me.txtEmployer & " Has An Active Posting For " &
Me.txtJobPosting
Cancel = True
Me.Undo
End If

I am not sure how to do this......Access 2003 Employer/Job Order Database

[quoted text clipped - 9 lines]

Thanks!

 

[Klatuu]

Okay, I see, then all you need to do is a lookup on the employer and job
posting. If the DLookup returns Null, that means it does not yet exist.
--
Dave Hargis, Microsoft Access MVP

Well, we have a field called "open/filled" from which "filled" is selected
when someone is placed there. We record the placement in another table.
When the position is open again, we just change the status via the
"open/filled" field. Also, there is a field called "number of slots" in
which to enter "how many" waitresses Smith needs. So I am not sure if it
matters wether the position is filled or not.......I just don't want the
exact same position entered twice!

Thank you for your help. Will try your solution right now!

This issue goes a little deeper than that (read your follow up question).

Smiths Coffee House may have a current job posting for a waitress so you
don't want to add another; but, what happens when this position is filled?
Next month Smith may want another waitress. How do you know the previous job
posting is not still Active?

Now, as the basics of the question. You would want to use the form Before
Update event in your subform and a DLookup to determine if another posting
exists. You would use the table the subform is based on because it will need
to have the employer and the posting data in it as well as some way to know
if it is an active posting or not:

If Not IsNull(DLookup("[JobPosting]", "PostingTable", "[EmployerID] = "
& Me.txtEmployer & " AND [JobPosting] = """ & Me.txtJobPosting & """" & " And
[ActivePosting] = 0") Then
MsgBox Me.txtEmployer & " Has An Active Posting For " &
Me.txtJobPosting
Cancel = True
Me.Undo
End If

I am not sure how to do this......Access 2003 Employer/Job Order Database

[quoted text clipped - 9 lines]

Thanks!

 

[rebecky via AccessMonster.com]

Uugh. I can't get it right.

The field, in the subform where they enter the Job Order, is called
"PositionType" and the table behind the subform is called JobOrderInfo. So
after they type in that field(PositionType), I want to check that record for
that entry in that field. I am not very good at this and got confused over
your answer.....sorry! ......wouldn't I use the "before update" event on the
field (PositionType) itself?

This issue goes a little deeper than that (read your follow up question).

Smiths Coffee House may have a current job posting for a waitress so you
don't want to add another; but, what happens when this position is filled?
Next month Smith may want another waitress. How do you know the previous job
posting is not still Active?

Now, as the basics of the question. You would want to use the form Before
Update event in your subform and a DLookup to determine if another posting
exists. You would use the table the subform is based on because it will need
to have the employer and the posting data in it as well as some way to know
if it is an active posting or not:

If Not IsNull(DLookup("[JobPosting]", "PostingTable", "[EmployerID] = "
& Me.txtEmployer & " AND [JobPosting] = """ & Me.txtJobPosting & """" & " And
[ActivePosting] = 0") Then
MsgBox Me.txtEmployer & " Has An Active Posting For " &
Me.txtJobPosting
Cancel = True
Me.Undo
End If

I am not sure how to do this......Access 2003 Employer/Job Order Database

[quoted text clipped - 9 lines]

Thanks!

 

[rebecky via AccessMonster.com]

Well, I have no clue if this is even close, and I am getting a syntax error
message.

Am I saying: If the PositionType in the JobOrderInfo table, where the
EmployerID = the employerid of the current record on the subform, = whatever
is typed in by the user then...msgbox etc?

'If Not IsNull(DLookup("[Positiontype]", "JobOrderInfo", "[EmployerID] = " &
Me.EmployerID & " AND [PositionType] = """ & Me.PositionType)) Then
'MsgBox [form_employerpositions1].Employername & " already has a
Posting for" & Me.PositionType
'Cancel = True
'Me.Undo
'End If

Thank you

Okay, I see, then all you need to do is a lookup on the employer and job
posting. If the DLookup returns Null, that means it does not yet exist.

Well, we have a field called "open/filled" from which "filled" is selected
when someone is placed there. We record the placement in another table.

[quoted text clipped - 32 lines]

 

[Klatuu]

You could.
--
Dave Hargis, Microsoft Access MVP

Uugh. I can't get it right.

The field, in the subform where they enter the Job Order, is called
"PositionType" and the table behind the subform is called JobOrderInfo. So
after they type in that field(PositionType), I want to check that record for
that entry in that field. I am not very good at this and got confused over
your answer.....sorry! ......wouldn't I use the "before update" event on the
field (PositionType) itself?

This issue goes a little deeper than that (read your follow up question).

Smiths Coffee House may have a current job posting for a waitress so you
don't want to add another; but, what happens when this position is filled?
Next month Smith may want another waitress. How do you know the previous job
posting is not still Active?

Now, as the basics of the question. You would want to use the form Before
Update event in your subform and a DLookup to determine if another posting
exists. You would use the table the subform is based on because it will need
to have the employer and the posting data in it as well as some way to know
if it is an active posting or not:

If Not IsNull(DLookup("[JobPosting]", "PostingTable", "[EmployerID] = "
& Me.txtEmployer & " AND [JobPosting] = """ & Me.txtJobPosting & """" & " And
[ActivePosting] = 0") Then
MsgBox Me.txtEmployer & " Has An Active Posting For " &
Me.txtJobPosting
Cancel = True
Me.Undo
End If

I am not sure how to do this......Access 2003 Employer/Job Order Database

[quoted text clipped - 9 lines]

Thanks!

 

[Klatuu]

Yes, sort of, but there is also a syntax error.

What you are asking is Is there an exsting Record in the table JobOrderInfo
that has the EmployerID field with a value exactly like the value in myform
control EmployerID and the Positiontype field with the same value as my form
control PositionType?

What the DLookup will do is if it finds a record where both fields match the
controls, it will return the vale in the Positiontype field. If it does not
find a match in both fields, it will return Null. In this case, we don't
care what the value is, we already know it. All we want to know is whether
such a record exists. If the record already extists - Not(IsNull(DLookup....
, we don't want to add it again.

Now, as to the syntax:

I am not sure what fields or controls you are using and what data types.
Please post back with the following:

Name of Field in the table JobOrderInf you are using to identifiy the employer
The Data Type of that field

Name of Field in the table JobOrderInf you are using to identifiy the job
position
The Data Type of that field

You changed names on me from the orginal questions, so we need to get the
correct names and data types so I can help you make it work.
--
Dave Hargis, Microsoft Access MVP

Well, I have no clue if this is even close, and I am getting a syntax error
message.

Am I saying: If the PositionType in the JobOrderInfo table, where the
EmployerID = the employerid of the current record on the subform, = whatever
is typed in by the user then...msgbox etc?

'If Not IsNull(DLookup("[Positiontype]", "JobOrderInfo", "[EmployerID] = " &
Me.EmployerID & " AND [PositionType] = """ & Me.PositionType)) Then
'MsgBox [form_employerpositions1].Employername & " already has a
Posting for" & Me.PositionType
'Cancel = True
'Me.Undo
'End If

Thank you

Okay, I see, then all you need to do is a lookup on the employer and job
posting. If the DLookup returns Null, that means it does not yet exist.

Well, we have a field called "open/filled" from which "filled" is selected
when someone is placed there. We record the placement in another table.

[quoted text clipped - 32 lines]

Thanks!

 

[rebecky via AccessMonster.com]

The Employer is Identified by [EmployerID] in the JobOrderInfo table - number
(long integer)
The Job Position field is called [PositionType] in the JobOrderInfo table -
text

Yes, sort of, but there is also a syntax error.

What you are asking is Is there an exsting Record in the table JobOrderInfo
that has the EmployerID field with a value exactly like the value in myform
control EmployerID and the Positiontype field with the same value as my form
control PositionType?

What the DLookup will do is if it finds a record where both fields match the
controls, it will return the vale in the Positiontype field. If it does not
find a match in both fields, it will return Null. In this case, we don't
care what the value is, we already know it. All we want to know is whether
such a record exists. If the record already extists - Not(IsNull(DLookup....
, we don't want to add it again.

Now, as to the syntax:

I am not sure what fields or controls you are using and what data types.
Please post back with the following:

Name of Field in the table JobOrderInf you are using to identifiy the employer
The Data Type of that field

Name of Field in the table JobOrderInf you are using to identifiy the job
position
The Data Type of that field

You changed names on me from the orginal questions, so we need to get the
correct names and data types so I can help you make it work.

Well, I have no clue if this is even close, and I am getting a syntax error
message.

[quoted text clipped - 20 lines]

 

[Klatuu]

The Employer is Identified by [EmployerID] in the JobOrderInfo table - number
(long integer)
The Job Position field is called [PositionType] in the JobOrderInfo table -
text

If Not IsNull("[PositionType]", "JobOrderInfo", "[EmployerID] = " &
Me.EmployerID & " AND [PositionType] = """ & Me.PositionType & """") Then
MsgBox "Already There"
Cancel = True
Me.Undo
End If

Not sure about the control names on your form. Now, we are talking about
the subform here, but the code is going to be in the subform's code module,
so Me will refere to the subform.
--
Dave Hargis, Microsoft Access MVP

The Employer is Identified by [EmployerID] in the JobOrderInfo table - number
(long integer)
The Job Position field is called [PositionType] in the JobOrderInfo table -
text

Yes, sort of, but there is also a syntax error.

What you are asking is Is there an exsting Record in the table JobOrderInfo
that has the EmployerID field with a value exactly like the value in myform
control EmployerID and the Positiontype field with the same value as my form
control PositionType?

What the DLookup will do is if it finds a record where both fields match the
controls, it will return the vale in the Positiontype field. If it does not
find a match in both fields, it will return Null. In this case, we don't
care what the value is, we already know it. All we want to know is whether
such a record exists. If the record already extists - Not(IsNull(DLookup....
, we don't want to add it again.

Now, as to the syntax:

I am not sure what fields or controls you are using and what data types.
Please post back with the following:

Name of Field in the table JobOrderInf you are using to identifiy the employer
The Data Type of that field

Name of Field in the table JobOrderInf you are using to identifiy the job
position
The Data Type of that field

You changed names on me from the orginal questions, so we need to get the
correct names and data types so I can help you make it work.

Well, I have no clue if this is even close, and I am getting a syntax error
message.

[quoted text clipped - 20 lines]

Thanks!

 

[rebecky via AccessMonster.com]

There is a "compile error".....wrong number of arguments or invalid property
assignment. I put this code in the "before update" event of the text box
[PositionType]

I don't mean to take up so much of your time, but greatly appreciate your
assistance!

The Employer is Identified by [EmployerID] in the JobOrderInfo table - number
(long integer)
The Job Position field is called [PositionType] in the JobOrderInfo table -
text

If Not IsNull("[PositionType]", "JobOrderInfo", "[EmployerID] = " &
Me.EmployerID & " AND [PositionType] = """ & Me.PositionType & """") Then
MsgBox "Already There"
Cancel = True
Me.Undo
End If

Not sure about the control names on your form. Now, we are talking about
the subform here, but the code is going to be in the subform's code module,
so Me will refere to the subform.

The Employer is Identified by [EmployerID] in the JobOrderInfo table - number
(long integer)

[quoted text clipped - 34 lines]

 

[Klatuu]

Post the code in your before update event exactly as you have it.
--
Dave Hargis, Microsoft Access MVP

There is a "compile error".....wrong number of arguments or invalid property
assignment. I put this code in the "before update" event of the text box
[PositionType]

I don't mean to take up so much of your time, but greatly appreciate your
assistance!

The Employer is Identified by [EmployerID] in the JobOrderInfo table - number
(long integer)
The Job Position field is called [PositionType] in the JobOrderInfo table -
text

If Not IsNull("[PositionType]", "JobOrderInfo", "[EmployerID] = " &
Me.EmployerID & " AND [PositionType] = """ & Me.PositionType & """") Then
MsgBox "Already There"
Cancel = True
Me.Undo
End If

Not sure about the control names on your form. Now, we are talking about
the subform here, but the code is going to be in the subform's code module,
so Me will refere to the subform.

The Employer is Identified by [EmployerID] in the JobOrderInfo table - number
(long integer)

[quoted text clipped - 34 lines]

Thanks!

 

[rebecky via AccessMonster.com]

ok. Thank you

Post the code in your before update event exactly as you have it.

There is a "compile error".....wrong number of arguments or invalid property
assignment. I put this code in the "before update" event of the text box

[quoted text clipped - 23 lines]

 

[Klatuu]

Let me ask this.
When you say you put it in the Before Update event of the text box. did you
open the VB editor and put it in there or did you just paste it into the text
box for the Before Update event in the properties dialog?

You want the text box in the dialog to be empty.
Click on the small command button next to it. The one with the 3 dots.
Select code builder from the Pop up.
The VB editor will open.
The code goes in the VB editor where it opens.

--
Dave Hargis, Microsoft Access MVP

ok. Thank you

Post the code in your before update event exactly as you have it.

There is a "compile error".....wrong number of arguments or invalid property
assignment. I put this code in the "before update" event of the text box

[quoted text clipped - 23 lines]

Thanks!

 

[rebecky via AccessMonster.com]

Yes. That is where I put it, but I decided to use THIS one cause I got it to
work.
Thank you for all your help!!!!

Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset
Dim Response As String
Dim intAnswer As Integer

Set rsc = Me.RecordsetClone

SID = Me.PositionType.Value
stLinkCriteria = "[PositionType]=" & "'" & SID & "'"

'Check JobOrderInfo table for duplicate position for same employer
If DCount("PositionType", "JobOrderInfo", stLinkCriteria) > 0 Then

'Message box warning of duplication
intAnswer = MsgBox("WARNING! Duplicate Entry. A Position of " _
& SID & " has already been entered for this Employer. Do You Wish to
Continue Entering this Position?", vbYesNo, "Ted version 2.1")

If intAnswer = vbYes Then
Exit Sub
Else
Me.Undo
End If





End If

Set rsc = Nothing

Let me ask this.
When you say you put it in the Before Update event of the text box. did you
open the VB editor and put it in there or did you just paste it into the text
box for the Before Update event in the properties dialog?

You want the text box in the dialog to be empty.
Click on the small command button next to it. The one with the 3 dots.
Select code builder from the Pop up.
The VB editor will open.
The code goes in the VB editor where it opens.

ok. Thank you

Post the code in your before update event exactly as you have it. [quoted text clipped - 3 lines]

Thanks!

 

[Klatuu]

You are doing very nearly the same thing except it is less efficient.
The DCount has to read the entire database, That is why I used the DLookup.
You are not checking for the employer.
Why do you have a recordset dimmed?
If that code is in the before udpate event, it will still update the record.
You have not canceled the the update.
--
Dave Hargis, Microsoft Access MVP

Yes. That is where I put it, but I decided to use THIS one cause I got it to
work.
Thank you for all your help!!!!

Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset
Dim Response As String
Dim intAnswer As Integer

Set rsc = Me.RecordsetClone

SID = Me.PositionType.Value
stLinkCriteria = "[PositionType]=" & "'" & SID & "'"

'Check JobOrderInfo table for duplicate position for same employer
If DCount("PositionType", "JobOrderInfo", stLinkCriteria) > 0 Then

'Message box warning of duplication
intAnswer = MsgBox("WARNING! Duplicate Entry. A Position of " _
& SID & " has already been entered for this Employer. Do You Wish to
Continue Entering this Position?", vbYesNo, "Ted version 2.1")

If intAnswer = vbYes Then
Exit Sub
Else
Me.Undo
End If





End If

Set rsc = Nothing

Let me ask this.
When you say you put it in the Before Update event of the text box. did you
open the VB editor and put it in there or did you just paste it into the text
box for the Before Update event in the properties dialog?

You want the text box in the dialog to be empty.
Click on the small command button next to it. The one with the 3 dots.
Select code builder from the Pop up.
The VB editor will open.
The code goes in the VB editor where it opens.

ok. Thank you
Post the code in your before update event exactly as you have it.

[quoted text clipped - 3 lines]

Thanks!

 

[rebecky via AccessMonster.com]

oh my gosh...you are right. It is not what I wanted to do at all! I will
get back at it tomorrow. Done for the day here. Thanks again!

You are doing very nearly the same thing except it is less efficient.
The DCount has to read the entire database, That is why I used the DLookup.
You are not checking for the employer.
Why do you have a recordset dimmed?
If that code is in the before udpate event, it will still update the record.
You have not canceled the the update.

Yes. That is where I put it, but I decided to use THIS one cause I got it to
work.

[quoted text clipped - 51 lines]

 

[rebecky via AccessMonster.com]

Hello. I pasted the code in the beforeupdate event of the control
[PositionType] exactly as you gave it and I am still getting this "compile
error".....wrong number of arguments or invalid property
assignment.

Any ideas?

Thank you

Post the code in your before update event exactly as you have it.

There is a "compile error".....wrong number of arguments or invalid property
assignment. I put this code in the "before update" event of the text box

[quoted text clipped - 23 lines]