Databases Reference
In-Depth Information
Dim lcRecords As Long
On Error Resume Next
db.QueryDefs.Delete "temp1"
On Error GoTo 0
sSQL1 = "SELECT Room FROM Assignment" & _
" WHERE (Name = [Enter Name])"
sSQL2 = "SELECT Room FROM Assignment" & _
" WHERE (Name = '_default') AND ([Enter Name] NOT IN (SELECT Name FROM
Assignment))"
sSQL3 = sSQL1 & " UNION " & sSQL2
Set qry1 = db.CreateQueryDef("temp1", sSQL3)
sName = InputBox("Enter name")
qry1.Parameters(0) = sName
' To see the results
''DoCmd.OpenQuery qry1.Name
Set rs = qry1.OpenRecordset
' Populate and get recordcount
rs.MoveLast
lcRecords = rs.RecordCount
' Random record
Randomize Timer
' lRandom is between 0 and lcRecords-1
lRandom = Int(lcRecords * Rnd)
rs.MoveFirst
rs.Move lRandom
MsgBox "Room for " & sName & " is " & rs!Room
End Sub
19.5 Time to Completion I
Here is a simple time-to-completion problem. Table 19-8 shows the status of widget
production for your company. At various stages in the production process, the workers
enter a record into the table indicating the remaining time to completion for the widget.
Table 19-8. Wi
d
gets table: Time to completion for widgets
WidgetID
TimeToCompletion
1
5
1
3
1
2
1
1
