vba assistance required

I am trying to check an event time in a dataset before allowing a user to book a time slot, so as not to double book.

Can anybody tell me how to access a field in a table in Access97/VBA - or even how to access the table for that matter.

I think I need to set a dataset variable and assign the required table to that variable. I tried this but I don't know how to assign a specific dataset to the variable.

The tables can be updated, retrieved, deleted, have fields added to etc.. by using SQL.

So, write a module function to handle the action:

dim db as database
dim rs as recordset
dim sql as string
set db = CurrentDb()
set rs = db.OpenRecordset("Select slot from table where slot = '" & slot & "';")

if rs.eof then isOkay = true else isOkay = false

Something like that?

hmm...I tried that but to no avail.

I am wondering if the following is along the right lines??
---
Private Sub settextboxes(i As Integer)

Dim dbs As Database
Dim qdf As QueryDef
Dim fld As Field
Dim QryStart, QryFinish, xstepval, xmytime, xtmpstart, xtmpfinish, stepval, mytime, tmpstart, tmpfinish As Date
Dim xmytimestr, xmytimestr2, mytimestr, mytimestr2 As String

Set dbs = CurrentDb
Set qdf = dbs.QueryDefs!qryVehiclesBooked

For Each fld In qdf.Fields
If fld.Name = "StartTime" Then
QryStart = fld.Value
End If
Next fld

If StartString <> "00:00:00" Then
If FinishString <> "00:00:00" Then
If Hearse.Value = -1 Then
tmpstart = CDate(StartString)
tmpfinish = CDate(FinishString)
stepval = CDate("00:30:00")

If tdf.Fields(StartTime) = StartString Then
End If
If QryStart >= StartString Then
For mytime = tmpstart To tmpfinish Step stepval
mytimestr = CStr(mytime)
mytimestr2 = Mid(mytimestr, 1, 2)
mytimestr2 = mytimestr2 + Mid(mytimestr, 4, 2)
If ((mytime >= tmpstart) And (mytime < tmpfinish)) Then
Me.Form.Controls("txt" + CStr(i) + CStr(mytimestr2)).BackColor = RGB(0, 0, 0)
End If
Next mytime
End If
End If
End If
End Sub

Can't really see what you're doing there. Seems a very complicated way of checking for double booking a time slot though.

Sorry...this is the relevant stuff...

Private Sub settextboxes(i As Integer)

Dim dbs As Database
Dim qdf As QueryDef
Dim QryStart, QryFinish As Date

Set dbs = CurrentDb
Set qdf = dbs.QueryDefs!qryVehiclesBooked

QryStart = qdf.fields(StartTime)
QryStart = qdf.fields(FinishTime)

If not QryStart >= StartString Then
blah blah blah

the problem is assigning a value from the query to a variable

I've never done that querydef stuff, always used recordsets to obtain the data.

Surely you could just recreate the query in the module and run tests against that?

Where are you creating the QueryDef object? I found 2 examples you could pull apart and apply to your code. Both examples can be found at Microsoft Support




Function ParamSPT (NewGroup As String)

Dim MyDb As Database, MyQ As QueryDef
Set MyDb = CurrentDB()

' Create a temporary QueryDef object that is not saved.
Set MyQ = MyDb.CreateQueryDef("")

' Type a connect string using the appropriate values for your
' server.
MyQ.connect = "ODBC;DSN=dsn1;UID=sa;PWD=;DATABASE=test"

' Set ReturnsRecords to false in order to use the Execute method.
MyQ.returnsrecords = False

' Set the SQL property and concatenate the variables.
MyQ.sql = "sp_addgroup" & " " & NewGroup

Debug.Print MyQ.sql
MyQ.Execute
MyQ.Close
MyDb.Close

End Function

==================

Function ParamSPT2(MyParam As String)

Dim MyDb As Database, MyQry As QueryDef, MyRS As Recordset
Set MyDb = CurrentDB()
Set MyQry = MyDb.CreateQueryDef("")

' Type a connect string using the appropriate values for your
' server.
MyQry.connect = "ODBC;DSN=user1;UID=user1;PWD=user1;DATABASE=TEST"

' Set the SQL property and concatenate the variables.
MyQry.SQL = "sp_server_info " & MyParam

MyQry.ReturnsRecords = True
Set MyRS = MyQry.OpenRecordset()
MyRS.MoveFirst

Debug.Print MyRS!attribute_id, MyRS!attribute_name, _
MyRS!attribute_value

MyQry.Close
MyRS.Close
MyDb.Close

End Function