Positive Test Flagging & Reminders

[yrstrulysa]

I am developing a Microsoft Access-based system to manage virus/bacterial retesting for plant blocks. The system tracks testing schedules, flags positive tests, and automates retest reminders using VBA and a subform to display test records.

​

 

[yrstrulysa]

Please see:

 

[plog]

Ok? How can we help you?

 

[yrstrulysa]

I am developing a Microsoft Access-based system to manage virus and bacterial retesting for plant blocks. The system tracks testing schedules, flags positive tests, and automates retest reminders using VBA and a subform to display test records.

Files (Upload function does not work): [Follow the link attached for files]

What I Have Done So Far

1. Created a Table for Retesting (TblBlockRetesting).
2. Subform: QfrmBlockPath_subform - Link & Functionality
   • This subform is embedded in the Blocks Administration Form (frmBlocks Admin).
   • It is linked to the main form using:
     • Master Field: Nr
     • Child Field: Nr, Block

Testing Reminder - How It Works

• Checks if a block has tested positive (TestResult = "A/EC").
• Looks at the TestDate column to determine if the test was conducted 5+ years ago.
• Inserts overdue tests into TblBlockRetesting if they are not already recorded.
• Triggers a pop-up reminder listing overdue blocks.

I tried using a VBA script, but it is currently calling a previous macro, and I do not know how to remove it.

 

[plog]

We are now up to 3 posts where you give us a synopsis of your project and I think I finally see a sentence hidden in there that has to do with an issue (but no specific question though):

I tried using a VBA script, but it is currently calling a previous macro, and I do not know how to remove it.

The most common way to call a macro from VBA is via the DoCmd.RunMacro method:

DoCmd.RunMacro method (Access)

Office VBA reference topic

learn.microsoft.com

So, my suggestion is to search (Ctrl + f) your VBA for either the name of the macro that is no longer in use or 'DoCmd.RunMacro'.

 

[arnelgp]

cross posted here:

Positive Test Flagging & Reminders

I am developing a Microsoft Access-based system to manage virus/bacterial retesting for plant blocks. The system tracks testing schedules, flags positive tests, and automates retest reminders using VBA and a subform to display test records. Files(upload function dont...

www.accessforums.net

 

[yrstrulysa]

Ok? How can we help you?

Thank you, I'm glad you asked. See the link to my files in a text file(size too big to attach).
1. I would like a VBA/Macro that will take the last test date and count 5 years from that date to remind the users when a re-test is due if a test for a plant virus has come out positive.
2. The user will need to capture this re-test, so a table would be needed for this for future reference.
On this form Blocks Administration, I search with "Find record by number", but the data has been shuffled, so as long as you get the subform to display at the bottom, where the test results appear in Form QfrmBlockPath_subform.
Any other logic that I might have missed you are welcome to suggest.

 

[plog]

I've lost interest in helping you and am more annoyed by each subsequent post you make. I'm unsubscribing and hope someone else has the patience to assist you.

 

[Isaac]

@yrstrulysa to be honest with you, in my own personal opinion, forums like this are best geared toward helping you when you have one specific problem - not a bunch of things. Isolate your problem to one thing, explain what you've tried, and explain the problem. Keep it to one, isolated thing, I can't repeat that enough.

I mean it's fine to talk in generalities about a project if you just want to bounce it off people and get some comments, but when you need specific help then you MUST provide specific problem - problem, singular, not problems, plural.

"I need to do _____________"
I thought this code would work ________________
The result is unexpected to me because ___________

 

[The_Doc_Man]

1. I would like a VBA/Macro that will take the last test date and count 5 years from that date to remind the users when a re-test is due if a test for a plant virus has come out positive.

a. See the DateAdd function and understand that you can ask it to generate a date in the future by adding 5 years to any relevant date (such as the stored date of a test).

b. Implicit in this concept is that 5 years from now someone will be running this app (OR that you have entered all the old test records from 5 years ago so you would know when prior tests are ready for re-test.)

2. The user will need to capture this re-test, so a table would be needed for this for future reference.

If a test requires a re-test but the data collected would be the same, NO to the "new table." Use the same test table but include a field that says "Y" (i.e. yes, this was a re-test). And the original test would have that same field as "N" (not a re-test)

I tried using a VBA script, but it is currently calling a previous macro, and I do not know how to remove it.

As previously suggested, search the VBA code (whole project search) for the name "CompactBackEnd" that is shown in the image you screen-captured for us. HOWEVER, if that macro does what I THINK it would do, you don't want it anyway. "Compact&Repair" operations tend to get glitched when unattended. I hate to be the one to break it to you, but even the smoothest databases require manual maintenance at a time when you can assure exclusive access to the back end. Schedule a time for this maintenance and let everyone know you cannot do without it. Then be prepared to kick off your users at that time. There are other posts on the forum dealing with formal maintenance.

cross posted here:

Our member ArnelGP noted that you have cross-posted. You are new so we can forgive a little bit of this. However, when you post in more than one forum and don't notify everyone that you have posted elsewhere, you risk wasting our time. We are all volunteers and hope to get help to as many people as we can. However, when you silently cross-post, it becomes possible for you to get a solution from one forum and thus no longer need help, but the folks in the other forum don't know that and expend time helping someone who no longer needs help. You are, in effect, taking time away from other people who ALSO come here for help. For that reason, unannounced cross-posting is considered impolite. Note also that if you ask for help and don't get it, and as a result DO cross-post, it is not so rude as long as you explain in the notice of cross-posting that you were getting no help. Every major help form has a way for you to copy a hyperlink so that you can identify the cross-posted article to other forums. For us, the post's individual link is on the top line of the post's frame. At the far right you will see a #-sign and a number. Right-click on that number and you can copy the hyperlink.

 

[yrstrulysa]

a. See the DateAdd function and understand that you can ask it to generate a date in the future by adding 5 years to any relevant date (such as the stored date of a test).

b. Implicit in this concept is that 5 years from now someone will be running this app (OR that you have entered all the old test records from 5 years ago so you would know when prior tests are ready for re-test.)
- I meant last test date column, then caclculate 5 years from that. So if Last Test day was 10/12/2012, 5 years later should be 09/12/2017 etc.


If a test requires a re-test but the data collected would be the same, NO to the "new table." Use the same test table but include a field that says "Y" (i.e. yes, this was a re-test). And the original test would have that same field as "N" (not a re-test)
- Will you please have a look at my file which incudes the test table etc.


As previously suggested, search the VBA code (whole project search) for the name "CompactBackEnd" that is shown in the image you screen-captured for us. HOWEVER, if that macro does what I THINK it would do, you don't want it anyway. "Compact&Repair" operations tend to get glitched when unattended. I hate to be the one to break it to you, but even the smoothest databases require manual maintenance at a time when you can assure exclusive access to the back end. Schedule a time for this maintenance and let everyone know you cannot do without it. Then be prepared to kick off your users at that time. There are other posts on the forum dealing with formal maintenance.
- I found it and deleted it. The code just run, it gives no effect. Please assist with the writing of this VBA/Macro.

Option Compare Database

Private Sub Form_Open(Cancel As Integer)Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim sqlQuery As String
    Dim testDateThreshold As Date
    Dim blockNr As Long
    Dim virusType As String
    Dim originalTestDate As Date
    Dim retestDueDate As Date
    Dim retestExists As Variant
    Dim messageText As String
    
    ' Set the date threshold (5 years ago from today)
    testDateThreshold = DateAdd("yyyy", -5, Date)


    ' Open the current database
    Set db = CurrentDb()


    ' SQL query to find blocks that tested positive (A/EC) 5+ years ago
    sqlQuery = "SELECT Nr, Block, TestResult, TestDate FROM TblBlocks " & _
               "WHERE TestResult='A/EC' AND TestDate <= #" & Format(testDateThreshold, "MM/DD/YYYY") & "#"


    Set rs = db.OpenRecordset(sqlQuery, dbOpenDynaset)


    ' Initialize message text
    messageText = "The following blocks require retesting:" & vbCrLf & vbCrLf


    ' Check if records exist
    If Not rs.EOF Then
        While Not rs.EOF
            blockNr = rs!Nr
            virusType = "A/EC" ' Since we are filtering for this specific test result
            originalTestDate = rs!TestDate
            retestDueDate = DateAdd("yyyy", 5, originalTestDate)
            
            ' Check if this block is already in the Retest Schedule table
            retestExists = Nz(DLookup("Nr", "TblBlockRetesting", "Nr=" & blockNr & " AND VirusType='" & virusType & "'"), 0)


            ' If the block is not already scheduled for retesting, insert it
            If retestExists = 0 Then
                db.Execute "INSERT INTO TblBlockRetesting (Nr, BlockName, VirusType, OriginalTestDate, NextDueDate, Retest_Status) " & _
                           "VALUES (" & blockNr & ", '" & rs!Block & "', '" & virusType & "', #" & Format(originalTestDate, "MM/DD/YYYY") & "#, #" & Format(retestDueDate, "MM/DD/YYYY") & "#, 'Due');"
            End If


            ' Add block info to the message pop-up
            messageText = messageText & "Block Nr: " & blockNr & " | Block: " & rs!Block & _
                          " | Due Date: " & retestDueDate & vbCrLf


            ' Move to next record
            rs.MoveNext
        Wend


        ' Show pop-up reminder
        MsgBox messageText, vbExclamation, "Retesting Alert"
    Else
        MsgBox "No blocks require retesting.", vbInformation, "Retesting Check Complete"
    End If


    ' Close resources
    rs.Close
    Set rs = Nothing
    Set db = Nothing
End Sub

Our member ArnelGP noted that you have cross-posted. You are new so we can forgive a little bit of this. However, when you post in more than one forum and don't notify everyone that you have posted elsewhere, you risk wasting our time. We are all volunteers and hope to get help to as many people as we can. However, when you silently cross-post, it becomes possible for you to get a solution from one forum and thus no longer need help, but the folks in the other forum don't know that and expend time helping someone who no longer needs help. You are, in effect, taking time away from other people who ALSO come here for help. For that reason, unannounced cross-posting is considered impolite. Note also that if you ask for help and don't get it, and as a result DO cross-post, it is not so rude as long as you explain in the notice of cross-posting that you were getting no help. Every major help form has a way for you to copy a hyperlink so that you can identify the cross-posted article to other forums. For us, the post's individual link is on the top line of the post's frame. At the far right you will see a #-sign and a number. Right-click on that number and you can copy the hyperlink.

- Understood, my intentions were good. I did not know you cross-check platforms or that it is not allowed.

 

[yrstrulysa]

I've lost interest in helping you and am more annoyed by each subsequent post you make. I'm unsubscribing and hope someone else has the patience to assist you.

Your demeanour comes across as rather off-putting. I never requested your assistance, yet you responded to my post. Kindly keep your negative mood to yourself in future interactions.