Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2004
    Posts
    1

    Unanswered: access and vba code HELP!!!

    Hello, i need to find some vba code to fill a listbox with all free rooms that aren't yet occupied (with start and end date). since it has to be in access i tought bout using VBA. I have searched hours and hours but still havent found it. I made it in Visual basic since i can make it in that language but don't know how i can get the databasecontrolers i use with Visual basic into vba code since i'm using 2 tables, 1 with the rooms in Tblkamer and one with the reservations Tblreserveringkamer.

    so with this code i check if the room is available, it checks the first line of the table tblreserveringkamer and sees if the roomnumber is the same check on date if it's not available, the variable OK goes to 1 so later i can check if the room has at least one record where it's not available so it's not added to the list.

    COULD SOMEONE PLEASE HELP TRANSLATING THIS CODE TO ACCESS AND VBA it would be a big big big help

    ty

    'DTARES IS DATABASE CONTROLER FOR TABLE Tblreserveringkamer
    'DTAKAMER IS DATABASE CONTROLER FOR TABLE Tblkamer

    Private Sub cmdcheck_Click()
    Dim kamer As Integer
    Dim reskamer As Integer
    Dim sd As Date
    Dim ed As Date
    Dim ok As Integer
    sd = txtbegin.Text
    ed = txteind.Text
    ok = 0
    lstvrij.Clear
    dtakamer.Refresh
    dtakamer.Recordset.MoveFirst
    While Not (dtakamer.Recordset.EOF)
    kamer = dtakamer.Recordset("kamerid")
    dtares.Refresh
    dtares.Recordset.MoveFirst
    While Not (dtares.Recordset.EOF)
    reskamer = dtares.Recordset("resKamer_KamerID")
    If kamer = reskamer Then
    'CHECKING FREE STARTS HERE
    If ((sd >= dtares.Recordset("reskamer_begindatum") And sd <= dtares.Recordset("reskamer_einddatum")) Or (ed <= dtares.Recordset("reskamer_einddatum") And ed >= dtares.Recordset("reskamer_begindatum")) Or (dtares.Recordset("reskamer_begindatum") >= sd And dtares.Recordset("reskamer_begindatum") <= ed) Or (dtares.Recordset("reskamer_einddatum") <= ed And dtares.Recordset("reskamer_einddatum") >= sd)) Then
    ok = 1
    End If
    'CHECKING FREE ENDS HERE
    End If
    dtares.Recordset.Movenext
    Wend
    If ok = 0 Then
    lstvrij.AddItem kamer
    End If
    dtakamer.Recordset.Movenext
    ok = 0
    Wend
    End Sub

  2. #2
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    I would try something like the following (warning This is from the top of my head, I did not test this code, so use is as an example only)

    dim dbs as database, rsr as recordset, rsk as recordset
    dim lstStr as string
    Dim kamer As Integer
    Dim reskamer As Integer
    Dim sd As Date
    Dim ed As Date
    Dim ok As Integer

    sd = txtbegin
    ed = txteind
    ok = 0
    set dbs = currentdb()
    set rsk = dbs.openrecordset("SELECT * FROM Tblkamer;")

    while not rsk.eof
    kramer = rsk!kramerid
    while not rsr.eof

    'open the reservation table only to the one record you need
    set qdf = createquerydef("","SELECT * FROM Tblreserveringkamer Where reskramer_kramerid = [k_Id];")
    qdf![k_id] = kramer
    set rs = qdf.openrecordset
    if sd >= rsk!reskamer_begindatum and ...

    then
    ok = 1
    end if
    rsr.movenext
    wend
    if ok = 0 then
    lstStr = lstStr & kramer
    end if
    rsr.close
    rsk.movenext
    ok = 0
    wend
    end sub

    It may be easier to qrite a cuple queryies and use lstboxName.requery every once in a while to make sure it is up to date.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •