Reject selection if Item is found in another query.

[Perfac]

Please see attached small DB.


My son(qualified, but not available now) helped me long ago, and as far as I know it worked then. Maybe I changed something, and just don't know how. Make a selection on the form and see the message please.
The result needed.
a. On the form when a selection is made but the ID is already found in t01CombinedEntities.Entfea_IDa the message "Entity Feature is already in use by & DLookup("EntityNameA", "t01CombinedEntities", "EntFtr_IDa=" & Me.cboEntFea.value)" must appear.
b. The selection must not be allowed if the ID already exists in t01CombinedEntities.Entfea_IDa.


As example, if "Sars (Income Tax)" is selected the ID is 3. It already exists in t01CombinedEntities.Entfea_IDa. If the 3 in t01CombinedEntities.Entfea_IDa is changed. Then the selection on the form of "Sars (Income Tax)" should be accepted.

[xps35]

All sorts of things have been changed here.
The error that occurs is because variables are not declared. If you use "option explicit", you must declare each variable. For example

Dim strMsg as String

For my convenience, I have removed "option explicit". The next error that occurs is that "t01EntityFeature" does not exist in the DLookup. That's right, because there is a s missing.


I have also corrected that error. Then I got stuck on the following error again. In the DLookup, look you for a record where "EntFtrID" is equal to the value you chose in the combo box. However, that field does not exist ("EntfeaID" does exist). There is als a p missing in "ApearanceA"


At this point I just stopped. You can't expect that if you make such a mess of it, we'll correct it for you.

Option Compare Database
Option Explicit
Dim Temp As String


Private Sub cboEntFea_AfterUpdate()
If Not Me.cboEntFea.Value = Temp Then
    If DLookup("ApearanceA", "t01EntityFeature", "EntFtrID=" & Me.cboEntFea.Value) = "Once" Then
        If Not IsNull(DLookup("CmbEntIDa", "t01CombinedEntities", "Entfea_IDa =" & Me.cboEntFea.Value)) Then
            strMsg = "Entity Feature is already in use by " & DLookup("EntityNameA", "t01CombinedEntities", "Entfea_IDa=" & Me.cboEntFea.Value)
            Me!EntFtr_ID015.Value = Temp
            strTitle = "Select Entity Feature"
            Ret_type = MsgBox(strMsg, vbOKOnly, strTitle)
        End If
    End If
End If
End Sub

[Bob Fitz]

I think the last error is due to using a lookup in the table rather than a related table

[orange]

Perfac,

I agree with the other responders --you absolutely need to use Option Explicit.
You have several un-Dimmed variables and you have misspelled field names in your tables.

I adjusted/corrected many, but couldn't resolve
Me.EntFtr_ID015.Value

[Perfac]

Thanks Vlad. My development has around 250 forms and 250 reports. Somebody, maybe you, advised me 5 years ago to add "Option Explicit". Every object in my system starts with that. The small attachment here, I did not focus on that, missed some sleep. Sorry for the simple spelling errors.

[Perfac]

Maybe there are no spelling errors now. The "option explicit" was on the code page of the form. Must it be done differently? I am self trained and put in some time on this, but I am not sure. I believe I will find an alternative way as soon as I am more fresh.

[Gicu]

Can you please have a look to see if that is what you're after? Note that the temp variable is now a long not a string as you are "masking" the actual values of the IDs using the format property.

Cheers,

[xps35]

The "option explicit" was on the code page of the form. Must it be done differently?

As said: if you use the option, you must declare all variables.

You can also make a solution without code. Make sure you only show valid features:

SELECT EntfeaID, Feature 
FROM t01EntityFeatures 
WHERE (((t01EntityFeatures.AppearanceA)<>"Once")) OR (((t01EntityFeatures.EntfeaID) Not In (SELECT  Entfea_IDa FROM [t01CombinedEntities])) AND ((t01EntityFeatures.AppearanceA)="Once")) ORDER BY Feature

See example database.

[Perfac]

Hi Vlad. Great. Yes that is exactly what I am after and it works correct on the attachment. I copied the code to my database and it is currently not working. No error message. But even if the Entity Feature item with ID 3 is in my "Combined entity" table, it allows me to select it again elsewhere, and it should not. I fight on today with this. As I said, my son set it up a couple of years ago, he maybe set up the temp variable in a Module, I am less than average on that. I don't have many modules. I sent Ruben(son) an email, but he is now far away, and busy.

[Perfac]

Thank you sir xps35. That is clever, and I use such strategy sometimes. But here I want my system to inform me against which entity the choice was made, so I can go and deselect it if it is what has to be done.

[Perfac]

I copied all the applicable objects to a clean database, from my real database. If you open the form f01Agents you will find the combo box "Entity Feature". Any selection returns the same error message. On the code page you will see the code you suggested Vlad. I added some Modules in which Ruben(son) possibly set up the temp variable. I am not good enough for that. I'm turning 60 soon and my memory not as good as when I was an international chess champion. Apologies for simple mistakes.

I am busy trying an alternative effort within my ability but maybe the above is a very small thing.

[Welshgasman]

I added some Modules in which Ruben(son) possibly set up the temp variable. I am not good enough for that. I'm turning 60 soon and my memory not as good as when I was an international chess champion.

I am 68 today and my memory is not that great these days.
However I can copy and paste, Google and make an effort to understand the code I find/am given.

[xps35]

I'm turning 60 soon and my memory not as good as when I was an international chess champion.

Wisdom comes with age.
64 here.

[Gicu]

@Perfac
Please have a look at the updated file; as others said, it is hard to work with samples that don't compile (and I also understand it is hard to create one from a very complex application with many objects ).
Note that I edited the row source of the combo to show you the existing entity name in the combined table for any entity having "appearance"="once". So that would give you a visual clue that you cannot really select it; but if you do the code (now in the BeforeUpdate event) will stop you.

Cheers,

[Welshgasman]

@Gicu,
I think O/P has moved on from this thread with the crosspost https://www.accessforums.net/showthread.php?t=86330 ?