Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2009
    Posts
    49

    Unanswered: Assigning a label to each Record

    Hi guys,

    I have a database scheduler that when you select a module, date or room the form will show a label at the correct position on a timeline and captions etc.. I know how to change position etc, but I am having trouble assigning each record with a different label. I have assigned the following coding to a button just to make it easier for testing.
    There are 10 labels each named "Session1"... "Session10" at the moment I am just trying to get the labels to appear. So this coding is meant to make all 10labels appear on the form.

    Private Sub Command111_Click()
    Dim objsession As Object
    Dim strsession As String
    Dim x As Integer

    x = 1
    Do While x < 11
    strsession = "Session" & x
    Set objsession = [Forms]![Summary]! & strsession
    objsession.Visible = True
    x = x + 1
    Loop

    End Sub

    To get just a singule Label to appear it is:

    Set objsession = [Forms]![Summary]![Session1]
    objsession.visible = True

    After this code is worked out Ill be adapting the code to a record from a query, but baby steps first

    Thanks
    Phil

    Edit: Sorry forgot to say that this is VB Access
    Last edited by pb190; 11-30-09 at 07:23. Reason: Forgot to add it's VBA

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Moved to Access, you'll get a better sampling of VBA-savvy folk there.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Unfortunately, you cannot create an array of controls in VBA, contrarily to what can be done in VB. Here is a possibility similar to the one you use, but it's not really dynamic: Forms: Control Arrays in Access
    Have a nice day!

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Can you possibly create a routine to make a temp type table with a field identifying "Session1", "Session2", etc... for each label.

    This would make it much eaiser without having to setup any kind of array or other special type coding. You just setup the temp table and then run 1 or more queries to append what type of label it is to that table and then open the label report.

    This is usually what I do when I need to make different types of labels in 1 report stemming from 1 table.

    But maybe I'm misunderstanding.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Nov 2009
    Posts
    49
    Hi,

    Thanks for your replies,

    I am trying to incorporate both Sinn's and pkstormy's suggestions in:

    When the form opens it deletes everything in the temptable called Summary.

    The copying and pasting an SQL statement from a query into the coding this adds all the relevant fields and data to the temptable. I can't see how the situation has changed its just from a different angle, so i think i need a tad more help for my brain to pick it up. I thought that if I could create a field that displayed "Session#" for each record then maybe I could work off that, but then it is still the same situation.

    Private Sub Form_Load()
    Dim SQL As String
    Dim SesNum As String
    Dim Session As Variant
    Dim db As DAO.Database
    Dim rs As DAO.Recordset

    Set db = CurrentDb
    Set rs = db.OpenRecordset("Summary", dbOpenDynaset)

    CurrentDb.Execute "DELETE from Summary"
    Room.Value = "Operating Theatre"
    Frame74 = "3"
    WeekNo.Value = "11"
    ModuleCode.Value = "MPLS1ANA"
    Weekday = "Wednesday"

    SQL = "INSERT INTO Summary ( [Week No], Weekday, DateofBooking, [Start Time], [End Time], [Module Code], Room, [Booked By], Description ) " & _
    "SELECT Bookings.[Week No], Bookings.Weekday, Bookings.DateofBooking, Bookings.[Start Time], Bookings.[End Time], Bookings.[Module Code], Bookings.Room, Bookings.[Booked By], Bookings.Description " & _
    "FROM Rooms INNER JOIN (Modules INNER JOIN Bookings ON Modules.[Module Code] = Bookings.[Module Code]) ON Rooms.Room = Bookings.Room " & _
    "WHERE (((Bookings.[Week No])=[Forms]![Summary]![WeekNo]) AND ((Bookings.Weekday)=[Forms]![Summary]![Weekday]) AND ((Bookings.[Module Code])=[Forms]![Summary]![ModuleCode]) AND ((Bookings.Room)=[Forms]![Summary]![Room]));"

    DoCmd****nSQL SQL

    End Sub

    So I think I have to explain my database a bit more...

    The Summary Form is the main form where the user can select Room, Date e.t.c. and if there are any Session Bookings that fit that criteria then the sessions will appear on a grid system ranging from 08:00 till 18:00. If they wish to modify any of the Sessions on the grid system then they can select the label and press another button to modify the Session's details. The problem is that if there is more than one Session on the same day and room then how can I have separate labels for each of the records...

    So far I have 10 labels named "Session1" - "Session10" as this was easier than having to create a label every single time. The first record will use "Session1" (second "Session2" and so on) and the label is modified to start and end at the right time and the caption is to change to show the description of the session. (this last bit is easy, it's the assigning the labels to each record).

    I did try this to select all the labels, but there was a problem with the "Set" line:

    Dim objsession As Object
    Dim strsession As String
    Dim x As Integer

    For x = 1 To 10
    strsession = "Session" & CStr(x)
    Set objsession = strsession
    objsession.Visible = True
    x = x + 1
    Next

    Would this be easier if the labels weren't actually labels e.g. textboxes or buttons? Brain is starting to hurt, which means this post is going to be confusing, sorry.

    Phil

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    If you're referring to labels on a form, here's some code I use to make the labels visible (note that the labels are named: Lbl1, Lbl2, Lbl3, etc...)

    For X = 1 to 10
    me.controls("Lbl" & X).visible = true
    next x

    Hope that helps because I don't quite understand your post.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Nov 2009
    Posts
    49
    Hi,

    Yes that was very helpful, with that bit I managed to finish all the rest of it on its own. Thanks guys for helping.

    Phil

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    might be a nice idea to post your code back here so that others may benefit.. that's how a forum like this works, we help each other to the best of our abiities
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Nov 2009
    Posts
    49
    Whoops sorry,

    got carried away with the massive leap forward

    By the thread you would have gathered that I was trying to make each session that a room is booked for is displayed on a timeline. When the date or module code or room is changed then the timeline should change.

    To do this (from some helpful advice) I made a new table called "Summary" and used an append query to add records that fit the correct criteria (Room, Date and Module) from the Overall Bookings table to the Summary Table.

    As there wasn't going to be many sessions in a day I created just 10 labels hopefully enough to cover all eventualities. Each label displays the Name of the session and the module.

    This is the code:

    Public Sub Bookings()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim cap As String
    Dim capmod As String
    Dim x As Integer
    Dim y As Integer
    Dim Left As String
    Dim start As Date
    Dim starttime As Integer
    Dim width As Date

    DoCmd.SetWarnings (False)
    CurrentDb.Execute "DELETE from Summary"

    SQL = "INSERT INTO Summary (SessionName, [Week No], Weekday, DateofBooking, [Start Time], [End Time], [Module Code], Room, [Booked By], Description ) " & _
    "SELECT Bookings.SessionName, Bookings.[Week No], Bookings.Weekday, Bookings.DateofBooking, Bookings.[Start Time], Bookings.[End Time], Bookings.[Module Code], Bookings.Room, Bookings.[Booked By], Bookings.Description " & _
    "FROM Rooms INNER JOIN (Modules INNER JOIN Bookings ON Modules.[Module Code] = Bookings.[Module Code]) ON Rooms.Room = Bookings.Room " & _
    "WHERE (((Bookings.[Week No])=[Forms]![Summary]![WeekNo]) AND ((Bookings.Weekday)=[Forms]![Summary]![Weekday]) AND ((Bookings.[Module Code])=[Forms]![Summary]![ModuleCode]) AND ((Bookings.Room)=[Forms]![Summary]![Room]));"

    DoCmd****nSQL SQL

    For y = 1 To 10
    Me.Controls("Session" & y).Visible = False
    Me.Controls("Session" & y).Left = "0"
    Next y

    Set db = CurrentDb
    Set rs = db.OpenRecordset("summary", dbOpenDynaset)

    Do While Not rs.EOF
    If rs.RecordCount = 0 Then
    Exit Do
    End If
    x = 1
    cap = rs![SessionName]
    capmod = rs![Module Code]
    start = (((Hour(rs![Start Time]) - 8) * 60) + Minute(rs![Start Time])) / 30
    starttime = 1000 + (435 * start)
    width = (((((Hour(rs![End Time]) - 8) * 60) + Minute(rs![End Time])) / 30) - start) * 435
    Me.Controls("Session" & x).Visible = True
    Me.Controls("Session" & x).Caption = cap & vbCrLf & capmod
    Me.Controls("Session" & x).Left = starttime
    Me.Controls("Session" & x).width = width
    x = x + 1
    rs.MoveNext
    Loop
    DoCmd.SetWarnings (True)
    End Sub

    Explanation:

    DoCmd.SetWarnings (False)

    This removes that really annoying message that says "You are about to append # records to..." you know the rest. This also works with the others like Delete and Update "are you sure?" msgboxes. It is a good idea to put the messages back afterwards so at the end of my code I have used the same line of code but exchanged "False" for "True".

    CurrentDb.Execute "DELETE from Summary"

    This deleted all records from my Summary Table (this is the tempTable). So that when the criteria is changed (Room date e.t.c.) the previous records are not kept.

    SQL = "INSERT INTO .......

    This SQL statement is the append query, the easiest way to make your own is to use Access to do it and then just like Design view there is an SQL View, so I copied and paste that straight into the coding. It is also easier to define this whole SQL statement as a string, so i just used "Dim SQL as string".

    DoCmd****nSQL SQL

    Runs the SQL statement what more can I say

    For y = 1 To 10
    Me.Controls("Session" & y).Visible = False
    Me.Controls("Session" & y).Left = "0"
    Next y


    This piece of coding is to restore the Labels back to their starting position, they are all invisible until needed and the .Left is to move the labels back to the same spot. I used a For... Next Loop so that this piece of coding moves all the labels back instead of having to have separate lines for each label. Therefore Me.Controls("Session" & y).visible = False also means Session1.visible = False with the 1 increasing to 2 then 3 until Session10.visible = False.

    Set db = CurrentDb
    Set rs = db.OpenRecordset("summary", dbOpenDynaset)


    This coding is to open a recordset, in this case to open my table called Summary (this is the temptable).

    Do While Not rs.EOF
    If rs.RecordCount = 0 Then
    Exit Do
    End If


    This is using a Do While... Loop system, and this first bit is saying that if there are no records in rs (Summary temptable) then exit the looping system.
    If there are records then the next bit of coding is run:

    x = 1
    cap = rs![SessionName]
    capmod = rs![Module Code]
    start = (((Hour(rs![Start Time]) - 8) * 60) + Minute(rs![Start Time])) / 30
    starttime = 1000 + (435 * start)
    width = (((((Hour(rs![End Time]) - 8) * 60) + Minute(rs![End Time])) / 30) - start) * 435
    Me.Controls("Session" & x).Visible = True
    Me.Controls("Session" & x).Caption = cap & vbCrLf & capmod
    Me.Controls("Session" & x).Left = starttime
    Me.Controls("Session" & x).width = width
    x = x + 1
    rs.MoveNext
    Loop


    I used another integer (x) for this next bit so I didn't get confused with previous coding that uses a 'y'.

    start = (((Hour(rs![Start Time]) - 8) * 60) + Minute(rs![Start Time])) / 30

    This horrible bit of bracket mess is relatively easy to explain and horrible to look at. Lets say that the start time is 09:00, no time can be booked before 08:00 and this is the start of the time line on my form. Hour() returns the number of hours in the field, so in this case it would equal 9. Minus the 8 hours as nothing can be booked before then so we are down to 1, as we are using minutes in this equation it would be easier to convert the hours into minutes (* 60), therefore we have start = 60 + .... Minute() works in the same way as Hour(). Ignore the 30 its just to fit my database.

    starttime = 1000 + (435 * start)

    The numbers of 1000 and 435 are in twips, this is how access measures distance. I used 1000 to get to the start of the grid, although I know this wasn't necessary, then each 30min segment is 435 twips wide, so in the example above starting at 09:00... start = 2 and starttime = 1870. I used some trial and error to figure out how many twips are there, but in design view 1 square of the grid system is 500 twips.

    width = (((((Hour(rs![End Time]) - 8) * 60) + Minute(rs![End Time])) / 30) - start) * 435

    Same as above

    Me.Controls("Session" & x).Visible = True
    Me.Controls("Session" & x).Caption = cap & vbCrLf & capmod
    Me.Controls("Session" & x).Left = starttime
    Me.Controls("Session" & x).width = width


    Adapting the line from above (where I used the 'y' as integer), I made each box visible, moved the box to the correct start position for that record, the right width and labelled each with the Name of the session and the Module.
    Caption is the writing on the label. vbCrLf forces a new line, and so the Name of the Session and the Modules are sitting on top of another.

    x = x + 1
    rs.MoveNext
    Loop


    the next part is to start the method over again but with the next record, by increasing x by 1, then moving down a record in the recordset and the loop to send the program back to 'Do While'.

    I hope you find this useful

    Phil

Posting Permissions

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