Prevent Duplicate Job Orders

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!

--
Message posted via http://www.accessmonster.com

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
--
Dave Hargis, Microsoft Access MVP


"rebecky via AccessMonster.com" wrote:

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!

--
Message posted via http://www.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!


Klatuu wrote:
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!

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200808/1

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


"rebecky via AccessMonster.com" wrote:

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!


Klatuu wrote:
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!

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200808/1

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?


Klatuu wrote:
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!

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200808/1

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

Klatuu wrote:
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!

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200808/1

You could.
--
Dave Hargis, Microsoft Access MVP


"rebecky via AccessMonster.com" wrote:

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?


Klatuu wrote:
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!

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200808/1

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


"rebecky via AccessMonster.com" wrote:

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

Klatuu wrote:
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!

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200808/1

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


Klatuu wrote:
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!

--
Message posted via http://www.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

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


"rebecky via AccessMonster.com" wrote:

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


Klatuu wrote:
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!

--
Message posted via http://www.accessmonster.com