#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2001
    Location
    North Yorkshire (U.K.)
    Posts
    64
    Rep Power
    14

    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.
  2. #2
  3. Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Oct 2001
    Location
    New Zealand
    Posts
    1,774
    Rep Power
    25
    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?
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2001
    Location
    North Yorkshire (U.K.)
    Posts
    64
    Rep Power
    14
    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
  6. #4
  7. Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Oct 2001
    Location
    New Zealand
    Posts
    1,774
    Rep Power
    25
    Can't really see what you're doing there. Seems a very complicated way of checking for double booking a time slot though.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2001
    Location
    North Yorkshire (U.K.)
    Posts
    64
    Rep Power
    14
    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
  10. #6
  11. Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Oct 2001
    Location
    New Zealand
    Posts
    1,774
    Rep Power
    25
    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?
  12. #7
  13. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2002
    Location
    NJ, USA
    Posts
    91
    Rep Power
    13
    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


IMN logo majestic logo threadwatch logo seochat tools logo