This is the ASP code (minus the HTML). It connects via ODBC into the Access file:
Code:
<%Set Conn = Server.CreateObject("ADODB.Connection")
Conn.open "AMSEAT"
Set RS = Server.CreateObject("ADODB.Recordset")
Set RS2 = Server.CreateObject("ADODB.Recordset")
Dim actnum
Dim seats
Dim SQL
Dim SQL2
Dim endmark
Dim finish
Dim used
actnum = 0
SQL = "SELECT DACTNO, VIP, date,COUNT(DACTNO) AS SEATS From [F6-DINNER] GROUP BY VIP,date,DACTNO; "
RS.Open SQL, Conn,3,3
RS.MoveLast
RS.MoveFirst
'RESPONSE.WRITE SQL
Do While Not RS.EOF
seats = CInt(RS("SEATS"))
'RESPONSE.WRITE RS("DACTNO")
SQL2 = "select * from mseat where avail >= " & seats & " order by table"
RS2.Open SQL2, Conn,3,3
RS2.MoveLast
RS2.MoveFirst
Afinish = RS2("USED")
endmark = seats
RS2("AVAIL") = RS2("AVAIL") - seats
RS2("USED")= RS2("USED") + seats
RS2.Update
do while endmark > 0
endmark = endmark - 1
Afinish = Afinish + 1
If Afinish = 1 Then
RS2("SEAT1") = RS("DACTNO")
'RS2.Update
End If
If Afinish = 2 Then
RS2("SEAT2") = RS("DACTNO")
'RS2.Update
End If
If Afinish = 3 Then
RS2("SEAT3") = RS("DACTNO")
'RS2.Update
End If
If Afinish = 4 Then
RS2("SEAT4") = RS("DACTNO")
'RS2.Update
End If
If Afinish = 5 Then
RS2("SEAT5") = RS("dactno")
'RS2.Update
End If
If Afinish = 6 Then
RS2("SEAT6") = RS("DACTNO")
'RS2.Update
End If
If Afinish = 7 Then
RS2("SEAT7") = RS("dactno")
'RS2.Update
End If
If Afinish = 8 Then
RS2("SEAT8") = RS("DACTNO")
'RS2.Update
End If
If Afinish = 9 Then
RS2("SEAT9") = RS("dactno")
'RS2.Update
End If
If Afinish = 10 Then
RS2("SEAT10") = RS("dactno")
'RS2.Update
End If
If Afinish = 11 Then
RS2("SEAT11") = RS("DACTNO")
'RS2.Update
End If
RS2.Update
loop
RS2.Close
RS.MoveNext
Loop
RS.Close
RESPONSE.WRITE ("Seating Complete!")
%>
This is about my third attempt to get it into VBA inside the Access file that the ASP connects to:
Code:
Private Sub Seating()
'recordset and connection variables
Dim db As Database
Dim objRS As Recordset
Dim objRS2 As Recordset
Dim strSQL As String
Dim srtSQL2 As String
'Dim Cnxn As ADODB.Connection
'Dim strCnxn As String
'connection
'Set Cnxn = CREATEOBJECT("ADODB.Connection")
'strCnxn = "AMSEAT"
'Cnxn.Open strCnxn
Set db = CurrentDb
Set objRS = CREATEOBJECT("Recordset")
Set objRS2 = CREATEOBJECT("Recordset")
'other variables
Dim Msg1 As String
Dim actnum As Integer
Dim seats As Integer
Dim endmark As Integer
Dim finish As Integer
Dim used As Integer
'set actnum = 0
'strSQL = "SELECT DACTNO, VIP, date,COUNT(DACTNO) AS SEATS From [F6-DINNER] GROUP BY VIP,date,DACTNO;"
objRS.Open "SELECT DACTNO, VIP, date,COUNT(DACTNO) AS SEATS From [F6-DINNER] GROUP BY VIP,date,DACTNO", db, adOpenDynamic, adLockPessimistic
'objRS.Refresh
objRS.MoveLast
objRS.MoveFirst
Do While Not objRS.EOF
seats = CInt(objRS("SEATS"))
'strSQL2 = "select * from mseat where avail >= " & seats & " order by table;"
objRS2.Open "select * from mseat where avail >= " & seats & " order by table", db, adOpenDynamic, adLockPessimistic
'objRS2.Refresh
objRS2.MoveLast
objRS2.MoveFirst
Afinish = objRS2("USED")
endmark = seats
objRS2("AVAIL") = objRS2("AVAIL") - seats
objRS2("USED") = objRS2("USED") + seats
objRS2.Update
Do While endmark > 0
endmark = endmark - 1
Afinish = Afinish + 1
If Afinish = 1 Then
objRS2("SEAT1") = objRS("DACTNO")
End If
If Afinish = 2 Then
objRS2("SEAT2") = objRS("DACTNO")
End If
If Afinish = 3 Then
objRS2("SEAT3") = objRS("DACTNO")
End If
If Afinish = 4 Then
objRS2("SEAT4") = objRS("DACTNO")
End If
If Afinish = 5 Then
objRS2("SEAT5") = objRS("DACTNO")
End If
If Afinish = 6 Then
objRS2("SEAT6") = objRS("DACTNO")
End If
If Afinish = 7 Then
objRS2("SEAT7") = objRS("DACTNO")
End If
If Afinish = 8 Then
objRS2("SEAT8") = objRS("DACTNO")
End If
If Afinish = 9 Then
objRS2("SEAT9") = objRS("DACTNO")
End If
If Afinish = 10 Then
objRS2("SEAT10") = objRS("DACTNO")
End If
If Afinish = 11 Then
objRS2("SEAT11") = objRS("DACTNO")
End If
objRS2.Update
Loop
objRS2.Close
objRS.MoveNext
Loop
objRS.Close
Msg1 = MsgBox("Seating Complete!", vbOKOnly)
End Sub