Using one table to induce a msg box in the main table if dup values

[Dhalsim]

Hello,

I'm fairly new to Access and at the final stages of completing a DB. It has a main table without too much relational stuff going on, the main feature is a form that constrains user input.

It's for captive bird post mortem data, I have a text box field on the main table called RINGNO where the ring numbers of dead birds are entered when they die. I want to maintain a second table in which users enter ring numbers of birds who have been tested and found to have disease when alive so that when they die the ring number, before update, is flagged by a message box to say 'this bird was ill' type thing.

So my question is how can I link these two tables. I'm not at all adept at coding but by scrathing around the internet I've found some code that uses DCount to highlight duplicate ring numbers within the same table, but not sure how to do this across two tables, Dcount can't do this i understand.

Private Sub txt_RINGNO_BeforeUpdate(Cancel As Integer)
If DCount("*", "CAPTIVE", "RINGNO = '" & Me.txt_RINGNO & "'") > 0 Then
MsgBox "Warning: this ring number already exists."
Cancel = False
End If

Any help would be greatly appreciated. Thanks

 

[catalin.petrut]

I dont see the use of 2 tables. You can add a few fields like ILL and DEAD (names are only for exemplification), Yes/No fields.

 

[Pat Hartman]

Welcome aboard
I agree with catalin. A single table is more appropriate. Do that will probably require some changes to other aspects of your application. If your forms/reports are bound to tables rather than queries, you might want to change that. You can give the user the option of seeing all birds, just live birds, or just dead birds to customize the results.

 

[Dhalsim]

Thanks for the replies, I figured it out in the end, the following code worked for me:

If DCount("*", "DISEASE REACTORS", "RINGNO = '" & Me.txt_RINGNO & "'") > 0 Then
MsgBox "Attention: this bird was a disease reactor"
Cancel = False
End If

 

[catalin.petrut]

Off topic: when you know that you know a little access? When Pat Hartman agrees with you!

Dhalsim, you know that the suggestions are for a better normalization of your db, right? I'm glad that you found a solution for your problem, but maybe you should take some time to look over Pat's suggestion.

PS: i really dont understand the people that ask a question and don't care about the answer.

 

[Dhalsim]

No i appreciate the help greatly, it's probably the way I explained it but the table is for dead animals only so I couldn't put in another column for ill or sick because there would be no line entry for the bird because it's not dead. So i need a running table next to it to act as a lookup to the main table. Which is why I need two tables.

The code below does part of the job, but it is not specific to each ring number e.g. if the ring number is C1234 if I put in C as a ring number the message will be triggered. Can anyone advice how I can make the code look at the whole ring number instead of part? many thanks.

If DCount("*", "TB REACTORS", "RINGNO = '" & Me.txt_RINGNO & "'") > 0 Then
MsgBox "Attention: this bird was a disease reactor. Check history"
Cancel = False
End If