Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2008
    Location
    Denver, CO
    Posts
    54

    Question Unanswered: struggling with an update query and how to use one in a loop

    I'd like to run an update query looped until a select query returns no records but I'm struggling with three things. First, I'm having syntax errors in my update query. Second, I'm concerned about CurrentDb v. DbEngine and compatibility issues between 2007, 2003, and xp. And Third, how to call the select query and the update query and use the loop.

    So... Here's where I'm at with all of that. With the help of others list members in a thread here (thanks again!) I was able to get my SQL statements under control to determine the value of the seatnumber I'd like to move a person into and the value of the seatnumber where the person I would like to move is sitting. I'd like to keep seats 1-13 full. In order to fill them I need to find the person sitting in the lowest seatnumber above 13 and move them into the lowest seat number in the 1-13 range. I imagine this as a loop which tests the first sql statement and if it returns a value then it runs an update query but if it doesn't return a value it stops. I'm just not sure how to do that. The SQL statements I've got so far are as follows:

    Code:
    SELECT MIN(Seat) AS min_seat_from_Presumptive
    FROM tblSeat
    WHERE Seat BETWEEN 1 AND 13
    AND NOT EXISTS
    (SELECT NULL FROM tblMain
    WHERE RefNum = '205CBV609'
    AND tblSeat.seat = tblMain.Seat);
    which returns the lowest seat number in the 1-13 range with a refnum of 205CBV609 that is currently empty and

    Code:
    SELECT MIN(Seat) AS min_seat_from_Rest
    FROM tblSeat
    WHERE Seat BETWEEN 14 AND 200
    AND EXISTS
    (SELECT NULL FROM tblMain
    WHERE RefNum = '205CV609'
    AND tblSeat.Seat = tblMain.Seat);
    which returns the lowest seat number in the 14-200 range with refnum of 205CBV609 that is currently occupied. What I thought I could do was:

    Code:
    UPDATE tblMain
    SET tblMain.Seat = (SELECT MIN(Seat)
    FROM tblSeat
    WHERE Seat BETWEEN 14 AND 200
    AND EXISTS
    (SELECT NULL
    FROM tblMain
    WHERE RefNum = '205CBV609'
    AND tblSeat.Seat = tblMain.Seat))
    WHERE (((tblMain.Seat)=(SELECT MIN(Seat)
    FROM tblSeat
    WHERE Seat BETWEEN 1 AND 13
    AND NOT EXISTS
    (SELECT NULL
    FROM tblMain
    WHERE RefNum = '205CV609'
    AND tblSeat.seat = tblMain.Seat))));
    but that gives me the error:

    "Operation must use an updateable query."

    Since my update query doesn't work (I guess it was wishful thinking that it could be that simple *pout*) I'm struggling to write the loop.

    As for those other two issues, even if I get the update query working, I don't know how the code for the loop would look. I'm using Office 2007 on an XP machine to write my code and when I went to the help on CurrentDb to try to figuer this out it said something about CurrentDb working in Office 12... which lead me to believe it might not work in previous versions (in my office I would want it to work for 2003 and XP as well as 2007). Even if it does work with previous versions I'm not sure how to call the queries in the loop or even if While-Wend is the right structure. Your help is greatly appreciated!

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi again

    Ok - problems.
    1) JET SQL does not allow you to have aggregations in an update query anywhere. Even if you reference a saved query with an aggregation it will come up with this error. I... ahem... forgot about this earlier - JET SQL is a little unusual in this respect.
    Solutions: Store the results of the queries in a table. Store the results of the queries in variables (since they are effectively scalars). Use the evil Domain Aggregate functions (check out DMIN in help.If you look at the syntax it is sort of like a query without the SELECT, FROM or WHERE clause key words).

    2) Although loops are also evil to a set based programmer I think it is probably the best way for this. Unless a SQL guru comes up with a novel solution, I can't think of a solution that would work with JET. Since there will be a maximum of 13 loops it would not be the worst thing in the world.

    3) I haven't used Access 2007 - we will need some input from someone with experience. I'll have a quick google.

    In any event we need to solve problem 1) before tackling 2 & 3

    HTH

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ah - hang on. I've reread your compatibility worry. CurrentDB is old as the hills - that will work fine in XP and 2003.

    I'm afraid my only advice really can be suck it and see. I would, however, strongly advise that you work with the lowest version of Access (inlcuding service packs) that you are going to distribute to, & test regularly. My copy of Access was always the very last in the organisation to get an update\ service pack. This is especially critical if you are using mdes - and you should be

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ooh - a thought. If we do store these results in a table we could use TOP and ORDER BY, autonumber fields.... three queries to perform however many updates are required, no loops.

  5. #5
    Join Date
    Jan 2008
    Location
    Denver, CO
    Posts
    54
    Hi again. Thanks for the response. I'm so excited because there are only two steps left in my project that need completion (when I started I had 6 pages of to-dos) and so as soon as the issue we're now talking about is overcome I'll be left with one step and then I'm done.

    So... in terms of the number of seats in the lower range and the upper range, the total number of seats will never exceed 200 and the number in the lower range will never exceed 30. I'm open to the table idea though I must admit I have no idea how to do that. I imagine it will involve either creating a table that stores the info while its being used and is cleaned out when done or something like that but since I've never done anything like that I'm only guessing.

    Looking back on this project I've learned so much in such a short time. When I started, a couple of weeks ago, I didn't even understand the notion of relational dbs. I had all my data in one table and had one big form to manipulate it and all of it was stored in one file. Afterall... not the normal task for a law clerk working in district court... but I wanted a new challenge for my free time and so I dove right in.

    Now I have my data distributed across 12 tables and the tables are stored in a seperate file from the gui which merely links to the tables. I have modules (though I'm still getting the hang of that) and I've written code which imports data, manipulates data, inserts, updates, deletes and queries data, and presents it to the user in a host of different ways. I'm getting so close to the end of the project I can taste it.

    But I digress... It sounds like using tables is the way to go (three queries sounds particularly attractive). Where do I start? Should I creat a table or should I be using vba to do that (I've always been curious about the ability to create tables using code... wonder if this is a time to do that)?

    A couple of notes... first, as for me working on 2007... unfortunately I don't seem to have any control over the computer and the software the folks here give me. I told them about my project and they told me that because they are upgrading the entire court system here in my home state they didn't want me doing anything on outdated software. I told them that didn't sound logical (as you described, programming on older systems will be compatible with new-backward-compatible versions of the software but the opposite isn't necessarily true) but they didn't listen... what can I say... it's the government. They mean well.

    Second, and maybe we should reserve this question for later... as it could be its own thread... but that MDE thing. If I understand correctly that is a compiled version of the access file, right? The idea being that it will make the app easier to distribute it as it will be smaller, faster, and won't have code for a user to mess with... but in access 2007 I tried creating the MDE and I couldn't figure out how. When I click create MDE it brings up a save dialog and then when I click the save button it give me an error:

    This error is usually associated with compiling a large database into an MDE file.* Because of the method used to compile the database, a considerable number of TableID references are created for each table.* The Access database engine can only create a maximum of 2048 open TableIDs at one time.* Exporting a database as an MDE potentially can exceed this limit if the database has a large number of objects (table, macro, form, report, etc).

    There is no accurate method to estimate the number of TableIDs the Access database engine uses during the process of compiling a database as an MDE.* However, each VBA module and each form uses one TableID, as a result, if the database has 500 forms, and each form's HasModule property is set to Yes, as many as 1,000 TableIDs are used.
    Like I said... that sounds to me like a completely seperate subject so maybe we should table that discussion for now. Anyhow... Thanks for all your help. I look forward to taking the next step on this topic.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Two options for your tables.
    1) Create them and drop them on the fly. For this you have another two options - sql (http://msdn.microsoft.com/library/de.../acfundsql.asp
    http://msdn.microsoft.com/library/de...l/acintsql.asp
    http://msdn.microsoft.com/library/de...l/acadvsql.asp)
    or code - I would use the TableDef object and append to the tabledefs collection for that.
    2) Permanent tables that you populate, use, delete, reseed.

    Tables would be something like:
    row_no - autonumber
    seat_number - integer

    One table for available seats, one for populated seats. Your queries change to something like:
    Code:
    INSERT INTO available_seats (seat_number)
    SELECT TOP 13 Seat AS available_seats
    FROM tblSeat
    WHERE Seat BETWEEN 1 AND 13
    AND NOT EXISTS
    (SELECT NULL FROM tblMain
    WHERE RefNum = '205CBV609'
    AND tblSeat.seat = tblMain.Seat);
    Reseeding:
    http://www.dbforums.com/showthread.php?t=1607477

    See how you get on. See as well if you figure out the entire solution from the above

  7. #7
    Join Date
    Jan 2008
    Location
    Denver, CO
    Posts
    54
    Alright... so first... thank you so much for all your help. This is fantastic! I'm nearly there. Here's what I did. I created two tables (tblAvailable and tblPopulated). tblAvailable merely holds the lowest seat number in the lower range seats. tblPopulated holds the lowest seat number and ID number for the person sitting in the upper range. The structure of the two are as follows:

    tblAvailable
    .Available, Number, Holds the seat number to be transfered to

    tblPopulated
    .Populated, Number, PrimaryKey, Holds the Seat of this person
    .Juror, Number, Holds the ID_Main of the person to transfer

    I'm going to toss all the code I use in this section so you can see all that I'm doing here (in case you think I need to do something differently). Before I do that, I should probably give a bit of explination. First, jurors are identified by one of two numbers: SeatNum or SummNum. The SummNum is the summons number from the summons they received that told them they needed to come in. The SeatNum is a seat number randomly generated by the state's computer when the juror checks in. There are four different types of cases (which up until now I was calling RefNum but in my code is CaseNum): reg, complex, DP, and civ. For reg and civ there are only two stages (1-Pre and 3-Group). For complex there is a third stage (2-Ind) but all that goes on here is excusing and nobody has a seat num. For DP there is also a third stage (2-Ind) and here there is excusal and there is qualifying. If qualified, they are assigned a seat.

    In Stage 1-Pre all that happens is excusing. At this stage, nobody has a seat number. I haven't worked on stage 2-Ind yet but it will look a lot like the change to stage 3-group for complex and will do nothing for the change to stage 3-group for DP (because everyone already has a seat). For reg and civ moving from stage 1-pre to stage 3-group will assign all those jurors who have not yet been excused a seat number. If the juror was originally being identified by the SeatNum, I just assign the SeatNum to the Seat for that Juror. I haven't yet implemented it, but if the juror was being identified by SummNum, I take the lowest ID_Main for jurors on a particular CaseNum who are not excused and assign that juror seat 1 and then take the min juror without a seat and assign the (max (seat) + 1) until all thost are seated.

    If the juror was originally IDed by seatnum I look for gaps (this starts around strSQL3 and is where you came in *GRIN*) and I fill those gaps. Oh... one more thing... every time I do anything with a juror, I create a log entry for that juror. So... on to the code. Note that this only works for the first gap but does nothing for the following gaps. I don't have a loop and I think I might need one to make this work the way I've done it but that could be me not understanding the method you suggested. Anyhow... let me know what you think. Thanks!

    Code:
    Private Sub btnChangeToGroup_Click()
        Dim strSQL, stSQLHolder, strSQL1, strSQL2, strSQL3, strSQL4, strSQL5, strSQL6, strSQL7, strSQL8, _
            strSQL9, strSQL10, stPassMeToUniversals As String
        Dim lngStatusChangeVal, lngEventVal As Long
        Dim db As Database
        Dim rs As Recordset
        Set db = DBEngine(0)(0)
        
        stPassMeToUniversals = "CaseNum = '" & Me.txtCaseNum & "'"
        
        Select Case Me.cmbType
            Case 1, 2, 4 ' crim-reg, crim-complex, and civil
                lngStatusChangeVal = 3
            Case 3 ' DP
                lngStatusChangeVal = 4
        End Select
        
        If lngStatusChangeVal = 3 Then
            lngEventVal = 4 'Event would be to be seated
        Else
            lngEventVal = 5 'Event would be to be qualified
        End If
        
        If Me.chkIDBy = 0 Then
            ' SQL statement that will enter an event for each juror who is still around and is being seated or qualified
            ' and is using original seatnum as their first seat
            strSQL = "INSERT INTO tblMainEvent ( Juror, Event, NewSeat, OldStatus, Status, Stage, EventLog ) " & vbCrLf _
                & "SELECT tblMain.ID_Main, " & lngEventVal & ", tblMain.SeatNum, tblMain.Status, " & lngStatusChangeVal & ", " & Me.cmbStage & ", Now() " & vbCrLf _
                & "FROM tblMain LEFT JOIN tblMainEvent ON tblMain.ID_Main = tblMainEvent.Juror " & vbCrLf _
                & "WHERE (((tblMain.ID_Main) In (SELECT tblMain.ID_Main FROM tblTrial LEFT JOIN " & vbCrLf _
                & "tblMain ON tblTrial.CaseNum = tblMain.CaseNum " & vbCrLf _
                & "WHERE (((tblMain.Status)<>4) AND ((tblMain.CaseNum)='" & Me.txtCaseNum & "')))));"
            
            db.Execute strSQL, dbFailOnError
            
            ' SQL statement that will update the seat and status for each juror in tblMain
            strSQL1 = "UPDATE tblMain SET tblMain.Seat = tblMain.SeatNum, tblMain.Status = " & lngStatusChangeVal & vbCrLf _
                & "WHERE (((tblMain.Status)<>4) AND ((tblMain.CaseNum)='" & Me.txtCaseNum & "'));"
           
            db.Execute strSQL1, dbFailOnError
    
            ' SQL statement that updates the status of this trial in tblTrial
            strSQL2 = "UPDATE tblTrial SET tblTrial.Stage = 3" & vbCrLf _
                & "WHERE (((tblTrial.CaseNum)='" & Me.txtCaseNum & "'));"
            
            db.Execute strSQL2, dbFailOnError
            
            ' SQL statement that checks to see if the presumptive is full
            strSQL3 = "SELECT Min(Seat) AS min_seat_from_Presumptive " _
                & "FROM tblSeat " _
                & "WHERE Seat Between 1 And " & (12 + Me.txtAlternates) _
                & "AND Not Exists " _
                & "(SELECT NULL FROM tblMain " _
                & "WHERE (CaseNum = '" & Me.txtCaseNum & "') AND (tblSeat.seat = tblMain.Seat));"
            
            Set rs = db.OpenRecordset(strSQL3)
            If rs.RecordCount > 0 Then
                
                ' SQL statement that looks at the presumptive and moves the lowest empty seat number to tblAvailable
                strSQL4 = "INSERT INTO tblAvailable ( Available ) " _
                    & "SELECT Min(Seat) AS min_seat_from_Presumptive " _
                    & "FROM tblSeat " _
                    & "WHERE Seat Between 1 And " & (12 + Me.txtAlternates) _
                    & " AND Not Exists " _
                    & "(SELECT NULL FROM tblMain " _
                    & "WHERE (CaseNum = '" & Me.txtCaseNum & "') AND (tblSeat.seat = tblMain.Seat));"
                db.Execute strSQL4
                
                ' SQL statement that looks at the rest of the panel and moves the lowest occupied seat number to tblPopulated
                strSQL5 = "INSERT INTO tblPopulated ( Populated ) " _
                    & "SELECT Min(Seat) AS min_seat_from_Rest " _
                    & "FROM tblSeat " _
                    & "WHERE Seat Between " & (13 + Me.txtAlternates) & " AND 200 " _
                    & "AND Exists " _
                    & "(SELECT NULL FROM tblMain " _
                    & "WHERE (CaseNum = '" & Me.txtCaseNum & "') AND (tblSeat.seat = tblMain.Seat));"
                
                db.Execute strSQL5
                
                ' SQL statement that looks at tblMain and copies the ID_Main to tblPopulated.Juror where the CaseNum is right
                ' and the tblMain.Seat = tblPopulated.Populated
                strSQL6 = "UPDATE tblPopulated, tblMain " _
                    & "SET tblPopulated.Juror = tblMain.ID_Main " _
                    & "WHERE ((tblMain.Seat=tblPopulated.Populated) " _
                    & "AND (tblMain.CaseNum='" & Me.txtCaseNum & "'));"
                
                db.Execute strSQL6
                
                'SQL statement that creates the event entry for this change
                strSQL7 = "INSERT INTO tblMainEvent ( Juror, Event, OldSeat, NewSeat, OldStatus, Status, Stage, EventLog ) " _
                    & "SELECT tblPopulated.Juror, 5, tblPopulated.Populated, tblAvailable.Available, 3, 3, 3, Now() " _
                    & "FROM tblAvailable, tblPopulated;"
    
                db.Execute strSQL7
                
                ' SQL statement that looks at tblAvailable and copies the juror from tblPopulated into the seat in tblMain
                ' that matches the seat in tblAvailable
                
                strSQL8 = "UPDATE tblMain, tblAvailable, tblPopulated " _
                    & "SET tblMain.Seat = tblAvailable.Available " _
                    & "WHERE (tblPopulated.Juror=tblMain.ID_Main);"
                
                db.Execute strSQL8
                
                ' SQL statements that cleans out the two tables (available and populated) and gets ready to run it again
                strSQL9 = "DELETE tblAvailable.* FROM tblAvailable;"
                strSQL10 = "DELETE tblPopulated.* FROM tblPopulated;"
                
                db.Execute strSQL9
                db.Execute strSQL10
            Else
                MsgBox "Jury Box 12 and alternate seat(s) are already filled"
            End If
            
            Call OpenFormAndClosePrevious("frmstagechanger", stPassMeToUniversals, stPassMeToUniversals)
        Else
            ToolUnderConstruction
        End If
    End Sub
    Alright... so... now that I've shared all of that, I'm thinking that's way more than is fair to ask you to respond to. Given that, perhaps we can talk about this in the abstract.

    I guess I want to know your thoughts on my creating the two tables and using them the way I did. I'm also interested in your thoughts on using a loop... now that I've set all this out this way... I imagine you were thinking I should have copied all the empty seats from the lower range to available and enough seated jurors from the upper range to match the recordcount from tblAvailable and then do my updates accordingly but, and I can't believe I'm going to say this about myself, I think I'm feeling linear today... can't think about the big picture... wow... I think I should be taken out back and put down... I've lost my usefulness in this world. Anyhow... I'm excited to hear your thoughts. Thanks again for everything!

  8. #8
    Join Date
    Jan 2008
    Location
    Denver, CO
    Posts
    54
    one more quick thing about the loop question...

    I tried changing that If then else to a:

    Code:
    While rs.RecordCount > 0
    
    ...
    
    Wend
    moving that else to just after the While-Wend and though it looped through my SQL statements and successfully repeated the process for each of the empties in the lower range, it never stoped... it just kept looping and never went to that msgbox I had in the Else above. Not sure why that would be... but then again... I've never used a loop before (can you believe that! I'm shocked myself). Anyhow... Thanks for all your help.

  9. #9
    Join Date
    Jan 2008
    Location
    Denver, CO
    Posts
    54
    Still having trouble but I think I had the while-wend portion incorrectly constructed. I've tried changing it but I think I've still got it wrong. The change is:

    Code:
            Do
                Do While rs.RecordCount > 0
                    
                    ' SQL statement that looks at the presumptive and moves the lowest empty seat number to tblAvailable
                    strSQL4 = "INSERT INTO tblAvailable ( Available ) " _
                        & "SELECT Min(Seat) AS min_seat_from_Presumptive " _
                        & "FROM tblSeat " _
                        & "WHERE Seat Between 1 And " & (12 + Me.txtAlternates) _
                        & " AND Not Exists " _
                        & "(SELECT NULL FROM tblMain " _
                        & "WHERE (CaseNum = '" & Me.txtCaseNum & "') AND (tblSeat.seat = tblMain.Seat));"
                    db.Execute strSQL4
                    
                    ' SQL statement that looks at the rest of the panel and moves the lowest occupied seat number to tblPopulated
                    strSQL5 = "INSERT INTO tblPopulated ( Populated ) " _
                        & "SELECT Min(Seat) AS min_seat_from_Rest " _
                        & "FROM tblSeat " _
                        & "WHERE Seat Between " & (13 + Me.txtAlternates) & " AND 200 " _
                        & "AND Exists " _
                        & "(SELECT NULL FROM tblMain " _
                        & "WHERE (CaseNum = '" & Me.txtCaseNum & "') AND (tblSeat.seat = tblMain.Seat));"
                    
                    db.Execute strSQL5
                    
                    ' SQL statement that looks at tblMain and copies the ID_Main to tblPopulated.Juror where the CaseNum is right
                    ' and the tblMain.Seat = tblPopulated.Populated
                    strSQL6 = "UPDATE tblPopulated, tblMain " _
                        & "SET tblPopulated.Juror = tblMain.ID_Main " _
                        & "WHERE ((tblMain.Seat=tblPopulated.Populated) " _
                        & "AND (tblMain.CaseNum='" & Me.txtCaseNum & "'));"
                    
                    db.Execute strSQL6
                    
                    'SQL statement that creates the event entry for this change
                    strSQL7 = "INSERT INTO tblMainEvent ( Juror, Event, OldSeat, NewSeat, OldStatus, Status, Stage, EventLog ) " _
                        & "SELECT tblPopulated.Juror, 5, tblPopulated.Populated, tblAvailable.Available, 3, 3, 3, Now() " _
                        & "FROM tblAvailable, tblPopulated;"
        
                    db.Execute strSQL7
                    
                    ' SQL statement that looks at tblAvailable and copies the juror from tblPopulated into the seat in tblMain
                    ' that matches the seat in tblAvailable
                    
                    strSQL8 = "UPDATE tblMain, tblAvailable, tblPopulated " _
                        & "SET tblMain.Seat = tblAvailable.Available " _
                        & "WHERE (tblPopulated.Juror=tblMain.ID_Main);"
                    
                    db.Execute strSQL8
                    
                    ' SQL statements that cleans out the two tables (available and populated) and gets ready to run it again
                    strSQL9 = "DELETE tblAvailable.* FROM tblAvailable;"
                    strSQL10 = "DELETE tblPopulated.* FROM tblPopulated;"
                    
                    db.Execute strSQL9
                    db.Execute strSQL10
                Exit Do
            Loop
        Loop Until rs.RecordCount = 0
    Anyhow... that's where I am. So close I can taste it! Thanks!

  10. #10
    Join Date
    Jan 2008
    Location
    Denver, CO
    Posts
    54
    Wait! Hold everything... I think I finally understand the method of moving all the empties to tblAvailable and moving all the replacements to tblPopulated and then doing this all at once... don't take the time to answer just yet... I think I may have figured this out. I'll report back in a bit.

  11. #11
    Join Date
    Jan 2008
    Location
    Denver, CO
    Posts
    54
    I think I pulled it off! Check this out:

    Code:
    Private Sub btnChangeToGroup_Click()
        Dim lngStatusChangeVal, lngEventVal As Long
    
        Select Case Me.cmbType
            Case 1, 2, 4 ' crim-reg, crim-complex, and civil
                lngStatusChangeVal = 3
            Case 3 ' DP
                lngStatusChangeVal = 4
        End Select
        
        If lngStatusChangeVal = 3 Then
            lngEventVal = 4 'Event would be to be seated
        Else
            lngEventVal = 5 'Event would be to be qualified
        End If
        Call RegCompCivToGroup(Me.txtCaseNum, Me.IDBy, lngStatusChangeVal, lngEventVal, Me.cmbStage, Me.txtAlternates)
    End Sub
    Then I created a module with my seat changer scripts. The first is RegCompCivToGroup which looks like this:

    Code:
    Sub RegCompCivToGroup(MyCaseNum As String, IDJurorsBy, lngStatusChangeVal, lngEventVal, lngStageValue, lngAltsForCase As Long)
        Dim strSQL, strSQL1, strSQL2, stPassMeToUniversals As String
        Dim db As Database
        Dim rs As Recordset
        
        stPassMeToUniversals = "CaseNum = '" & MyCaseNum & "'"
    
        
        Set db = DBEngine(0)(0)
        
        If IDJurorsBy = 0 Then ' Jurors are being identified by Original SeatNumber
            ' SQL statement that will enter an event for each juror who is still around and is being seated or qualified
            ' and is using original seatnum as their first seat
            strSQL = "INSERT INTO tblMainEvent ( Juror, Event, NewSeat, OldStatus, Status, Stage, EventLog ) " & vbCrLf _
                & "SELECT tblMain.ID_Main, " & lngEventVal & ", tblMain.SeatNum, tblMain.Status, " _
                & lngStatusChangeVal & ", " & lngStageValue & ", Now() " & vbCrLf _
                & "FROM tblMain LEFT JOIN tblMainEvent ON tblMain.ID_Main = tblMainEvent.Juror " & vbCrLf _
                & "WHERE (((tblMain.ID_Main) In (SELECT tblMain.ID_Main FROM tblTrial LEFT JOIN " & vbCrLf _
                & "tblMain ON tblTrial.CaseNum = tblMain.CaseNum " & vbCrLf _
                & "WHERE (((tblMain.Status)<>4) AND ((tblMain.CaseNum)='" & MyCaseNum & "')))));"
            
            db.Execute strSQL, dbFailOnError
            
            ' SQL statement that will update the seat and status for each juror in tblMain
            strSQL1 = "UPDATE tblMain SET tblMain.Seat = tblMain.SeatNum, tblMain.Status = " & lngStatusChangeVal & vbCrLf _
                & "WHERE (((tblMain.Status)<>4) AND ((tblMain.CaseNum)='" & MyCaseNum & "'));"
           
            db.Execute strSQL1, dbFailOnError
    
            ' SQL statement that updates the status of this trial in tblTrial
            strSQL2 = "UPDATE tblTrial SET tblTrial.Stage = 3" & vbCrLf _
                & "WHERE (((tblTrial.CaseNum)='" & MyCaseNum & "'));"
            
            db.Execute strSQL2, dbFailOnError
            
            Call GapFiller(MyCaseNum, lngAltsForCase)
    
            Call OpenFormAndClosePrevious("frmstagechanger", stPassMeToUniversals, stPassMeToUniversals)
        Else
            ToolUnderConstruction
        End If
    End Sub
    Then I created GapFiller which I'll be able to use for future gap filling... not just when I make that initial switch. Every time I excuse a juror from that lower range once in the Group stage I can call GapFiller and it will fill my gaps in the lower range. Check it out:

    Code:
    Sub GapFiller(MyCaseNum As String, lngAltsForCase As Long)
    
        Dim db As Database
        Dim rs As Recordset
        Dim strSQL3, strSQL4, strSQL5, strSQL6, strSQL7, strSQL8, strSQL9, strSQL10, strSQL11 As String
        Dim lngInAvail As Long
        
        Set db = DBEngine(0)(0)
    
        ' SQL statement that checks to see if the Jury 12 + alts are filled
        strSQL3 = "SELECT Min(Seat) AS min_seat_from_Presumptive " _
            & "FROM tblSeat " _
            & "WHERE Seat Between 1 And " & (12 + lngAltsForCase) _
            & " AND Not Exists " _
            & "(SELECT NULL FROM tblMain " _
            & "WHERE (CaseNum = '" & MyCaseNum & "') AND (tblSeat.seat = tblMain.Seat));"
        
            Set rs = db.OpenRecordset(strSQL3)
            If rs.RecordCount > 0 Then
                ' SQL statement that looks at the presumptive and moves the empty seat numbers to tblAvailable
                strSQL4 = "INSERT INTO tblAvailable ( Available ) " _
                    & "SELECT TOP " & (12 + lngAltsForCase) & " Seat AS available_seats " _
                    & "FROM tblSeat " _
                    & "WHERE Seat BETWEEN 1 AND " & (12 + lngAltsForCase) _
                    & " AND NOT EXISTS " _
                    & "(SELECT NULL FROM tblMain " _
                    & "WHERE CaseNum = '" & MyCaseNum & "' " _
                    & "AND tblSeat.Seat = tblMain.Seat);"
                
                db.Execute strSQL4
                
                ' Check how many records are now in tblAvailable
                strSQL5 = "SELECT Count(1) FROM tblAvailable;"
                Set rs = db.OpenRecordset(strSQL5)
                lngInAvail = rs.Fields(0)
                
                ' SQL statement that looks at the rest of the panel and moves the lowest occupied
                ' seat numbers to tblPopulated but only moved enough to cover the available seats
                strSQL6 = "INSERT INTO tblPopulated ( Populated ) " _
                    & "SELECT TOP " & lngInAvail & " Seat AS populated_seats " _
                    & "FROM tblSeat " _
                    & " WHERE Seat Between " & (13 + lngAltsForCase) & " AND 200 " _
                    & " AND Exists " _
                    & "(SELECT NULL FROM tblMain " _
                    & "WHERE (CaseNum = '" & MyCaseNum & "') AND (tblSeat.Seat = tblMain.Seat));"
                    
                db.Execute strSQL6
                
                ' SQL statement that looks at tblMain and copies the ID_Main to tblPopulated.Juror where the CaseNum is right
                ' and the tblMain.Seat = tblPopulated.Populated
                strSQL7 = "UPDATE tblPopulated, tblMain " _
                    & "SET tblPopulated.Juror = tblMain.ID_Main " _
                    & "WHERE ((tblMain.Seat=tblPopulated.Populated) " _
                    & "AND (tblMain.CaseNum='" & MyCaseNum & "'));"
                
                db.Execute strSQL7
                
                'SQL statement that creates the event entry for this change
                strSQL8 = "INSERT INTO tblMainEvent ( Juror, Event, OldSeat, NewSeat, OldStatus, Status, Stage, EventLog ) " _
                    & "SELECT tblPopulated.Juror, 5, tblPopulated.Populated, tblAvailable.Available, 3, 3, 3, Now() " _
                    & "FROM tblAvailable, tblPopulated;"
    
                db.Execute strSQL8
                
                ' SQL statement that looks at tblAvailable and copies the juror from tblPopulated into the seat in tblMain
                ' that matches the seat in tblAvailable
                
                strSQL9 = "UPDATE tblMain, tblAvailable, tblPopulated " _
                    & "SET tblMain.Seat = tblAvailable.Available " _
                    & "WHERE (tblPopulated.Juror=tblMain.ID_Main);"
                
                db.Execute strSQL9
                
                ' SQL statements that cleans out the two tables (available and populated) and gets ready to run it again
                strSQL10 = "DELETE tblAvailable.* FROM tblAvailable;"
                strSQL11 = "DELETE tblPopulated.* FROM tblPopulated;"
                
                db.Execute strSQL10
                db.Execute strSQL11
        Else
            MsgBox "Jury Box 12 and alternate seat(s) are already filled"
        End If
    End Sub
    Looks like it's all good! Thanks so much for your help. I'm feeling useful again and my code works... a double plus... not to mention... sushi for dinner... Life is good!

Posting Permissions

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