Solved Data from another form pop up (1 Viewer)

[Valentine]

Ok this is a weird one stay with me. I have a data entry form that captures all the data for everybody. This is saved to a table called Roster. Each section has their own macro button to open a form with their specific peoples data. There are 2 variables that designate when a person leaves the section, Outbound and ETS date fields that are filled out in the data entry form and saved into the roster table. There is a status in the data entry form that has 2 options, Present and Archive. I want a pop up to show when the section form is opened via the macro to ask if a person is still active because either their ETS or Outbound date field has elapsed. I know that is kinda confusing but I will answer any questions to assist in helping me.

 

[Valentine]

So far this is what I have:

Private Sub Form_Load()
   
    Dim dbCurr As DAO.Database
    Dim rsLoss As DAO.Recordset
    Dim lossStr As String

    Set dbCurr = CurrentDb()
    lossStr = "SELECT Roster.[Last Name], Roster.[First Name], Roster.Outbound, Roster.ETS, Roster.Rank " _
            & "FROM Roster " _
            & "WHERE (((Roster.Outbound)<=DateAdd('d',180,Date())) AND ((Roster.Platoon)=1) AND ((Roster.Status) Not Like 'Archive')) OR (((Roster.ETS)<=DateAdd('d',180,Date())) AND ((Roster.Platoon)=1) AND ((Roster.Status) Not Like 'Archive')) " _
            & "ORDER BY Roster.Outbound, Roster.ETS;"
    Set rsLoss = dbCurr.OpenRecordset(lossStr)
    Do While rsLoss.EOF = False
        If rsLoss!(Outboud) Or rsLoss!(ETS) < Date Then
            Response = MsgBox("Has " & rsLoss![Rank] & " " & rsLoss![Last Name] & " " & rsLoss![First Name] & " Left Fort Meade?", vbYesNo + vbCritical + vbDefaultButton2)
            If Response = vbNo Then
                Exit Sub
            Else
                DoCmd.RunSQL "UPDATE Roster " _
                           & "Set [Status] = 'Archive';"
            End If
        End If
    Loop
    Set dbCurr = Nothing
    Set rsLoss = Nothing
End Sub

I am getting an error: "Type-declaration character does not match defined data type" highlighting the first if statement in the do loop.

 

[plog]

  If rsLoss!(Outboud) Or rsLoss!(ETS) < Date Then

Each comparison operator (>, <, =) only works on one thing on the left and one thing on the right. You can't seperate them with AND/OR. So, you need 2 comparisons operators in the above.

Wrong: (A OR B) < C
Right: (A<C) OR (B<C)

 

[plog]

Now before you do that, this line of code is dangerous:

                DoCmd.RunSQL "UPDATE Roster " _
                           & "Set [Status] = 'Archive';"

You have no WHERE clause, which means if that line of code ever runs it will UPDATE every record in Roster, not just one record. My guess is you need a WHERE clause to identify which person you are marking Archived.

 

[Valentine]

Yeah, I noticed the or thing after putting everything up here. Good thing it asked me if I wanted to do the update on 232 records and I was like NO!!!!!! So I have a where clause but its not finding the value in the variable.

                DoCmd.RunSQL "UPDATE Roster " _
                           & "Set [Status] = 'Archive' " _
                           & "WHERE ((Roster.[DoD ID]) = rsLoss![DoD ID]);"

once I run the code if I say yes to the msgbox it asks for what the DoD ID is instead of already knowing.

 

[Valentine]

I figured it out. I didn't have " & rsLoss![DoD ID] & " now it works as I want thank you for the help.

 

[Pat Hartman]

and I was like NO!!!!!!

You did make a back up of the database prior to running an untested action query, right

 

[Valentine]

You did make a back up of the database prior to running an untested action query, right

Yeah I have A LOT of bakups .

In case anyone else would like to do this here is my code that accomplished the task:

Private Sub Form_Load()
    
    Dim dbCurr As DAO.Database
    Dim rsLoss As DAO.Recordset
    Dim lossStr As String

    Set dbCurr = CurrentDb()
    lossStr = "SELECT Roster.[Last Name], Roster.[First Name], Roster.Outbound, Roster.ETS, Roster.Rank, Roster.[DoD ID] " _
            & "FROM Roster " _
            & "WHERE (((Roster.Outbound)<=DateAdd('d',180,Date())) AND ((Roster.Platoon)=2) AND ((Roster.Status) Not Like 'Archive')) OR (((Roster.ETS)<=DateAdd('d',180,Date())) AND ((Roster.Platoon)=2) AND ((Roster.Status) Not Like 'Archive')) " _
            & "ORDER BY Roster.Outbound, Roster.ETS;"
    Set rsLoss = dbCurr.OpenRecordset(lossStr)
    Do While rsLoss.EOF = False
        If rsLoss!Outbound < Date Or rsLoss!ETS < Date Then
            Response = MsgBox("Has " & rsLoss![Rank] & " " & rsLoss![Last Name] & " " & rsLoss![First Name] & " Left Fort Meade?", vbYesNo + vbCritical + vbDefaultButton2)
            If Response = vbYes Then
               DoCmd.RunSQL "UPDATE Roster " _
                          & "Set [Status] = 'Archive' " _
                          & "WHERE ((Roster.[DoD ID]) = " & rsLoss![DoD ID] & ");"
            End If
        End If
        rsLoss.MoveNext
    Loop
    Set dbCurr = Nothing
    Set rsLoss = Nothing
    Me.Refresh
End Sub

I put it in "On Load" event of the form, so when the person clicked the macro to open the form these msgbox would open before the form and then refresh as the form opened so any of the lists would be accurate as to who left.

 

[Pat Hartman]

Thanks for sharing the correct code. Please mark the thread as complete