using vba to loop an insert query until all records in query are d

[ArielZusya]

I've got a table (tblMain) which stores the seat to which each person in the
table is assigned for a given event. I'm working on writing code that will
take the person sitting in the lowest seat number about a certain range and
move that person into the lowest seat number currently empty below that
range. I've figured out the SQL to accomplish the locating of the lowest
occupied seat above the range and the lowest unoccupied seat below the range
and I've figured out the SQL to move the person sitting in the the seat found
above the range into the seat found below the range. What I don't know is
how to loop this until all seats below the range are filled. I'm sure it is
something relatively simple, I just don't know how to do it. I feel like it
needs to be something like:

While the result of the query for the min of the unoccupied seat less than a
particular value is not null, insert the min of the occupied seat above that
value. I just have never used a loop... though I'm excited to learn how.
Your help will be greatly appreciated! Thanks!

 

[Steve Sanford]

This is only a start the code does not take into account events or number of
tables.

The code reassigns seat at one table, but is wouldn't be hard to add in
selecting the table at a specific event.


' this is my button name
' Sub A_Test_Click()
'--------------code begin----------------
On Error GoTo Err_mybad
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rt As DAO.Recordset

Dim strSQLS As String
Dim strSQLT As String

Dim i As Integer
Dim SeatsToFill As Integer

'change this to increase or decrease the
'number of seats at a table
Const SeatsPerTable As Integer = 5

Set db = CurrentDb

' select seat numbers 1 - 5
strSQLS = "SELECT tblAction.Seat"
strSQLS = strSQLS & " FROM tblaction"
strSQLS = strSQLS & " WHERE tblaction.Seat <= " & SeatsPerTable
strSQLS = strSQLS & " ORDER BY tblaction.Seat;"

'select seats >= 6
strSQLT = "SELECT tblAction.Seat"
strSQLT = strSQLT & " FROM tblaction"
strSQLT = strSQLT & " WHERE tblaction.Seat > " & SeatsPerTable
strSQLT = strSQLT & " ORDER BY tblaction.Seat;"

Set rs = db.OpenRecordset(strSQLS)
rs.MoveLast

SeatsToFill = SeatsPerTable - rs.RecordCount

Set rt = db.OpenRecordset(strSQLT)
rt.MoveLast
rt.MoveFirst

If rt.RecordCount < SeatsToFill Then
'exit sub
End If

If SeatsToFill > rt.RecordCount Then
MsgBox "Not enough people to fill in seats 1 - " & SeatsPerTable

Else

If SeatsToFill > 0 Then
rs.MoveFirst
For i = 1 To SeatsPerTable
If i <> rs!Seat Then
rt.Edit
rt!Seat = i
rt.Update

rt.MoveNext
Else
rs.MoveNext
End If

Next i

End If
End If

Exit_mybad:
rs.Close
rt.Close

Set db = Nothing
Set rs = Nothing
Set rt = Nothing

Exit Sub

Err_mybad:

'error 3021 = No Current record
If Err.Number = 3021 Then
Resume Next
Else
MsgBox Err.Description
Resume Exit_mybad

End If
'--------------code begin----------------
' this is where END SUB would be



HTH

 

[ArielZusya]

Wow! Thanks so much for your help. I've got some questions. First,
CurrentDb... is that only available in 2007? I'm working in access 2007 but
I'm trying to be conscious of the folks around here still using older
versions. Second, if CurrentDb isn't available in access XP or 2003 can I
use DBEngine(0)(0).Execute or is there a better way? Finally, and this is
only tangentially related but I wanted to know... that rt.RecordCount <
SeatsToFill... I tried returning the value of a query which would return
either one number or no numbers... if it returned one number I could output
the number without error by setting the caption of a label to the result but
if it returned no numbers I would get an error... RecordCount would get me
around that, huh? In other words, instead of just returning the result of
the query I could test to see if the RecordCount of the result of the query
was > 0 and if so return the value and if not return "". Do I have that
right? Thanks so much for your help I was starting to think I had been
forgotten. *GRIN*

 

[Steve Sanford]

Didn't forget about you. Looking at your tables.

I have A2K and A2K2. "CurrentDb" should ve available inA2K7. In the IDE,
open the object browser and search for "CurrentDb".

 

[ArielZusya]

Actually I was worried that it might not have existed in previous versions
(the help said something about it working for Office 12 whereas in other
spots for other functions in the help it says something about functions
working for all versions or it says nothing... so I was worried) but then I
did a bit of googling and found references to CurrentDB as far back as A97 so
I'm good there. As for the rest of the questions Problems Answered! Thanks
so much for your help!