Check for existing record in a subform

[ekta]

I want to check for existing records on my subform. The problem is that my form is based on an intersection table.

Table 1
CompanyID
CompanyName
Contact

Table 2
OpportunityID
OpportunityName
Description

Intersection Table
CompanyID
OpportunityID

What I have done is that I have a form based on Table 2. On that form I placed a subform to see all the companies related to that Opportunity. So I created a query with fields OpportunityID, CompanyID, CompanyName and Contact and based my subform on this query. I want to check for existing records on this subform. I saw the code somewhere to check for existing record.
I tried this
If DCount("[CompanyID]", "tblIntersection", "[CompanyID] = " & Me![CompanyID] & "") > 0

But this does not work. I think I need to check for both OpportunityID and CompanyID to check for existing record. I can't figure out how to do this.
I really need to get this done. I would appreciate any help.

Thanx
Ekta

 

[ShannonMarie]

A couple of ideas

One thing that as access users we need to keep in mind is the fact that forms do not hold data--tables hold data. The information that you are looking for is pulled from a query and placed where? That is where you need to call your lookup from. The DCount function is used to return a numeric value of records that have nonnull values. If you are wanting an actual list you might try to use the DLookup feature with the formula to pull the info. I'm still learning, but I hope that this can help a little.

 

[Jack Cowley]

Try this:

If DCount("[CompanyID]", "tblIntersection", "[CompanyID] = " & Me![CompanyID] & " And [OpportunityID] = " & Me![OpportunityID]) > 0 Then
Do something
End If

hth,
Jack

 

[ekta]

Thanx Jack..this works gr8888

Juss one more thing.

If DCount("[CompanyID]", "tblIntersection", "[CompanyID] = " & Me![CompanyID] & " And [OpportunityID] = " & Me![OpportunityID]) > 0 Then
MsgBox "Duplicate Company Name, please select a different option"
DoCmd.CancelEvent
Me!CompanyID.Undo
End If

Me!CompanyID.undo gives me an error. I know, I will have to undo OpportunityID as well. But how?
Error is
"Object doesn't support this property or method"
If the user selects an existing record, he gets the msg that you entered a duplicate company name but when the user clicks ok the duplicate company name is entered. I want the fields to be empty.

 

[Cosmos75]

Unique Entry using index

Why don't you just add an index to your intersection table (a.k.a junction table) so that each record must have a unique CompanyID/OpportunityID combination?

This will only allow a CompanyID/OpportunityID combination to show up once, but keep in mind this will allow for the same CompanyID to be paired with another OpportunityID and vice versa.

Or did I completely not understand what you are trying to do?

 

[Jack Cowley]

Can your run your code from a Before Update event (form or control)? If so then you can cancel the event and the Undo should not give you a problem. Always do validations in Before Update events as this gives you better control over what happens. In your current situation you can try Me!CompanyID = Null...

hth,
Jack

 

[ekta]

I tried Me.undo and it works

Thanks for your help Jack.

 

[Jack Cowley]

Cool! I am glad you got it sorted Ms. Ekta!

Jack