checking for duplicate values between subform and query data

[raddrummer]

I have a main form that has combo boxes linked to a query that
outputs the results of the query to text boxes on a subform. We
have a job code (naming) convention here that assigns the first 2
digits based on the location selected from the combo box, the next 2
from the department, and so on...

I've set up the subform to requery itself any time data from the
combo boxes on the main form is changed, and that is working fine.
The problem I'm having is with writing code to check the NewJobCode
field on the subform against the main table list of existing codes
(called ExistingCodes.JCode) for duplicates.

I want to have an error message pop up if duplicates are found.
Any ideas?

[ADezii]

I have a main form that has combo boxes linked to a query that
outputs the results of the query to text boxes on a subform. We
have a job code (naming) convention here that assigns the first 2
digits based on the location selected from the combo box, the next 2
from the department, and so on...

I've set up the subform to requery itself any time data from the
combo boxes on the main form is changed, and that is working fine.
The problem I'm having is with writing code to check the NewJobCode
field on the subform against the main table list of existing codes
(called ExistingCodes.JCode) for duplicates.

I want to have an error message pop up if duplicates are found.
Any ideas?

Enter similar code in the BeforeUpdate() Event of the [NewJobCode] Field on your Sub-Form:

Private Sub txtNewJobCode_BeforeUpdate(Cancel As Integer)
'Assumptions:
  'Sub-Form Name = subfJobs
  '[NewJobCode] Field in Sub-Form is named txtNewJobCode
  'code is executed from the [txtNewJobCode] Field in the Sub-Form
  'Table containing Unique codes is tblJobCodes
  'Field containing codes in tblJobCodes is named [JCode]
 
'Let's make sure there is a valid entry in txtNewJobCode
If Not IsNull(Me![txtNewJobCode]) Then
  If DCount("*", "tblJobCodes", "[JCode]=" & Me![txtNewJobCode]) > 1 Then     'a Dupe!
    MsgBox "Job Code entered in Sub-Form already exists", vbCritical, "Invalid Entry"
     Cancel = True
  Else
    'entry must be unique
  End If
Else
  'ignore, Null value in Field
End If
End Sub

[raddrummer]

Thanks for the info. Unfortunatley I can't seem to get it to work. Do I need to make any declarations after the Sub header? Do I need to create any functions to get this to work?

[ADezii]

Thanks for the info. Unfortunatley I can't seem to get it to work. Do I need to make any declarations after the Sub header? Do I need to create any functions to get this to work?

Do I need to make any declarations after the Sub header? Do I need to create any functions to get this to work?

You need none of the above. You do, however, have to substitute your Table Name, Field Name, Sub-Form Name, and the Sub-Form Field Name with you own values. If you are really stuck, and it is practical, you can send the Database to me as an Attachment and I'll have a look at it. Should it get to this point, and you are willing to do this, just let me know.

[raddrummer]

I'd really, really appreciate that. What email address should I use for you? I see that the interface on this page doesn't allow for attachments.

You need none of the above. You do, however, have to substitute your Table Name, Field Name, Sub-Form Name, and the Sub-Form Field Name with you own values. If you are really stuck, and it is practical, you can send the Database to me as an Attachment and I'll have a look at it. Should it get to this point, and you are willing to do this, just let me know.

[ADezii]

I'd really, really appreciate that. What email address should I use for you? I see that the interface on this page doesn't allow for attachments.

I'm sending you my Private E-Maill address in Private Message. Send an E-Mail to that address with the Database as an Attachment.

[raddrummer]

Adezii,

Thank you so, so much for your help! The code works great!!I would not have been able to figure this out on my own.
Thanks again!

I'm sending you my Private E-Maill address in Private Message. Send an E-Mail to that address with the Database as an Attachment.

[ADezii]

Adezii,

Thank you so, so much for your help! The code works great!!I would not have been able to figure this out on my own.
Thanks again!

You are quite welcome. Normally, I would insist that the results be posted in this Thread for all to see, but in your unique circumstance, it would not make much sense without having the actual Database in front of you.