Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2015
    Posts
    5

    Unanswered: Creating an alert or message box if a value is found within another table.

    Project background: I work in the fish biology field in Washington in the Columbia River basin and am in the process of creating a new DB in Acces 2010 based upon the skeleton of an old one. Just about
    everything is working well so far. Some of the fish that we deal with have RFID tags implanted in them from a variety of sources such as our fish hatchery, dams in the Columbia River and other fish hatchery. As adult salmon/steelhead return to the hatchery we take biological measurements such as length/age/weight/presence of RFID tag etc. etc. This information is recorded in a Form I designed.

    Problem: I will have a list of unique RFID tags that were implanted at our hatchery and I would like to be able to distinguish those tags from any that were implanted elsewhere. This is complicated by the fact that I would like to retain all RFID codes, rather than only RFID codes from our hatchery.

    I know that if I only wanted our RFID codes to be retained I could use DLookup but what I would like to happen is to have either a window appear that lets me know that the fish originated from our hatchery, or a different field(maybe a text box?) populate with True/False or Yes/No or similar. It is not critical that this information is retained within the DB, it's purpose is to just let the biologist who is sampling the fish know at the moment of sampling what the situation is.

    I'm not worth anything at VBA code, would there be a simple way to do this within an expression, a source of VBA script that would be easy to cannibalize and modify or am I out of luck?

    I'm not even sure where to start with this so any suggestions of ways I can proceed on my own would be great.

    Thanks a ton for the help!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If I understand what you mean:

    1. Let's suppose that you have a table containing the RFID codes from your hatchery (say: Tbl_RFID with a column RFID_Code)
    2. You can create a table with would contain the "foreign" RFDI codes (say: Tbl_RFID_Ext) OR you can add a boolean column to the existing Tbl_RFID table (External: Yes/No)
    3. When a fish is scanned, the scanning process yields it's RFDI code (say: RFID_Code)
    4. To determine whether this RFID code is from your hatchery or not, you can use:

    a) With 2 tables (Tbl_RFID and Tbl_RFID_Ext):
    Code:
    If DCount("*", "Tbl_RFID", "RFID = " & RFID_Code) = 0 Then
        ' "External" RFID Code.
    Else
        ' "Internal RFID Code.
    End If
    If you want to store the "external" RFID Code in Tbl_RFID_Ext you can use
    Code:
    If DCount("*", "Tbl_RFIDExt", "RFID = " & RFID_Code) = 0 Then
        CurrentDb.Execute "INSERT INTO Tbl_RFIDExt (RFID_Code) VALUES (" & RFID_Code & ");",dbFailOnError
    Else
        '  RFID_Code already exists in Tbl_RFIDExt.
    End If
    Note: If RFID_Code is of type Text, use:
    Code:
    If DCount("*", "Tbl_RFID", "RFID = '" & RFID_Code & "'") = 0 Then
        ' "External" RFID Code.
    Else
        ' "Internal RFID Code.
    End If
    And:
    Code:
    If DCount("*", "Tbl_RFID_Ext", "RFID = '" & RFID_Code & "'") = 0 Then
        CurrentDb.Execute "INSERT INTO Tbl_RFID_Ext (RFID_Code) VALUES ('" & RFID_Code & "');",dbFailOnError
    Else
        '  RFID_Code already exists in Tbl_RFIDExt.
    End If
    b) With a single table with a colum (External: Yes/No):
    Code:
    If DCount("*", "Tbl_RFID", "RFID = " & RFID_Code & " AND External = True) = 0 Then
        ' "External" RFID Code.
    Else
        ' "Internal RFID Code.
    End If
    If you want to store the "external" RFID Code in Tbl_RFID you can use
    Code:
    If DCount("*", "Tbl_RFID", "RFID = " & RFID_Code & " AND External = True) = 0 Then
        CurrentDb.Execute "INSERT INTO Tbl_RFIDExt (RFID_Code, External) VALUES (" & RFID_Code & ", True);",dbFailOnError
    Else
        '  RFID_Code already exists in Tbl_RFID.
    End If
    Note: The same remarks applies if RFID_Code is of type Text (see above).
    Have a nice day!

  3. #3
    Join Date
    Dec 2015
    Posts
    5
    I have one table that contains all sampling info length, weight, RFID tag. This information is all entered in one form as we are sampling the fish. My RFID reader will automatically populate the unique RFID tag in text form into a text box into my Form. As of now I have an additional table that contains all known RFID tags that were released from my hatchery. I have no way of anticipating all RFID tags from outside sources so I will be unable to premake a list of those tags.

    I would like to have either a message box appear (which seems to be the more complex option) or a text box within the form(located next to the text box containing the RFID value) that notify me as soon as the RFID text box is populated. I think the issue with what you suggested is that it is all after the fact populating a column within the table after the record has been submitted. Apologies that this is not a more straight forward explanation.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    It does not change anything.

    DCOUNT() and more generally Domain functions can use the contents of a data set and use a criteria build from local value(s). If, in the code module of a form, you want to test immediately after the RFID code is entered in a textbox (say: Text_RFID), replace in the examples above:
    Code:
    If DCount("*", "Tbl_RFID", "RFID = " & Me.TextRFID.Value) = 0 Then
    etc...

    The only problem would be to detect when the value of Text_RFID is changed (and from that to determine when to call the code). The AfterUpdate event is the best candidate but I'm not sure that this event will be raised because of how the RFID code is entered (through code end not at the keyboard). You'll have to test until you found the right event. The documentation of the scanner (if available) could help you too.
    Have a nice day!

  5. #5
    Join Date
    Dec 2015
    Posts
    5
    I appreciate the help and explanation. I'll play around with it and report back.

  6. #6
    Join Date
    Dec 2015
    Posts
    5
    I got the code working, but like you predicted the issue is now getting the code to run after the data is entered. Thanks for the help. The odds of me blundering through that on my own were close to non existent.
    Last edited by steelhead; 01-04-16 at 18:18.

  7. #7
    Join Date
    Dec 2015
    Posts
    5
    Do you have any other ideas on how to trigger the Dcount to run. I've tried inserting my code into both "Before" and "after Update Events, the lost focus, and "enter" and "exit" as well as messing around with "On Dirty".

    My methodology for trying these events is to move my working code into different Private subs for the the events and deleting it from preceding information.

    I ended up making two tag tables for to separate our two types of tags(WNFH S1 and WNFH S2) and "strPITtag" is the column header for my list of RFID tags. I'm 95% certain my code works. Any time I manually enter a code in the text box when using the AfterUpdate Event it will cause the correct message box to appear, however like you predicted none of these will populate the message box if the RFID code is populated using our code reader and VB Script. Any other pointers? It seemed like OnDirty can sometimes be used to force an Event to run, would something like that be a possibility.


    Here is my code:
    If DCount("*", "[tblWNFHS1InBasin]", "strPITtag = " & "forms![frmBioIntoWNFH]![strPITtag]") = 1 Then
    MsgBox ("WNFH S1")
    ElseIf DCount("*", "[tblWNFHS2InBasin]", "strPITtag = " & "forms![frmBioIntoWNFH]![strPITtag]") = 1 Then
    MsgBox ("WNFH S2")
    End If

    Thanks a ton, this is literally the last thing between me and completion of this DB!

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The dirty event will only work if the form is bound (from Access help: "This event applies only to bound forms, not an unbound form or report.") Moreover it would not be triggrered if a value is entered by code (from Access help: Modifying a record within a form by using a macro or Visual Basic doesn't trigger this event." ).

    One possible solution would consist in using the LostFocus event. This would be possible if the string entered in the textbox is followed by the vbNewline codes (Cr+Lf or chr(13)+chr(10)). I know nothing about the system you use for acquiring the RFID tags however if it works a little bit like a codebar reader, it's possibly programmable so it terminates it's sequence with such a separator. If it's the case, check the property EnterKeyBehavior of the textbox which should be set to False.

    If not, you can poll the textbox withing regular intervals (using the Form Timer event) to determine when what's in the textbox is a valid RFID code (I assert that such a code must have a defined structure, e.g. it's length) then, when this condition of completeness or conformity is encountered, it can call the analysis procedure that uses the DCount() functions. This would not be easy and you would have to experiment with the TimerInverval property of the form but it's doable.
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •