Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2008
    Location
    Denver, CO
    Posts
    54

    Exclamation Unanswered: pulling rows into their own columns and dynamically changing the caption of lbl

    I think I'm nearly through all my project woes. My final question (man do I hope this is actually my final question) is about rows, columns, and dynamically changing captions of a label. Here's the story:

    I've got a table (tblMain) which holds the names and seat numbers of lists of people for various events. I'd like to display this list on a graphical seat chart. The problem is I can't figure out a reasonable way to query for the name of a person with a given CaseNum for a given Seat without performing queries for each seat. I usually have about 25-30 seats displayed at once and so I'd rather find a way to adjust this using code.

    Here's what I was thinking: each graphical seating chart form has a rectangle which represents the seat, a label which contains the seat number for that seat, and a lable for first and last name for each seat. I also have a text control on the form which is set to not-visible which contains the CaseNum. I can query for tblMain.FirstName, tblMain.LastName, tblMain.Seat where tblMain.CaseNum='Me.txtCaseNum' and tblMain.Seat = 5 (assuming this is for the labels in the seat five box) and then set Me.lblSeat5.caption = rs!FirstName. The trouble is this requires that method of writing a new vba script every time.

    Thinking there might be a way to use it, I standardized the names for the controls on the form for each seat displayed. For every seat there is a box (boxSeat#) a lable for the seat num (lblSeat#), and labels for the first and last of those adjusted (lblFirstName and lblLastName). if we are talking about Seat 47, the chart would have boxSeat47, lblSeat47, lblFirstName47, and lblLastName47. I can query to return a value and assign that value to the query... though I'd be happy to make other changes... to tighten this process more. Looking forward to feedback. Thanks!

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    since your seat numbers vary and you don't really want to design-mode the form each time, you need an offset.

    imagine you have 40 boxes on the form
    design-time number them 0 to 39 - this remains fixed.

    imagine you pull seats 120 to 160 ...you need to map LATER: duh! that should of course read 120 to 159
    120 -> 0
    121 -> 1
    122 -> 2
    ..ie the offset is the lowest seat number.
    if your recordset is sorted by seat number the offset is !seat for the first record in the recordset.

    something like this DAO code:
    [AIRCODE]
    dim recs as dao.recordset
    dim strSQL as string
    dim intOffset as integer
    strSQL = "SELECT whatever, you, are, doing, now ORDER BY Seat
    set recs = currentdb.openrecordset(strSQL)
    with recs
    if .bof and .eof then
    'do something - you have zero records
    else
    intOffset = recs!seat
    do while not .eof
    me.controls("lblSeat" & (!seat - intOffset)).caption = !Seat
    me.controls("lblFirstName" & (!seat - intOffset)).caption = !FirstName
    ' etc etc etc
    .movenext
    loop
    endif
    end with
    set recs = nothing
    [/AIRCODE]

    you could "disappear" unwanted seats - maybe something like:
    ...clear the seat caption before you start
    [AIRCODE]
    for i = 0 to 39
    me.controls("lblSeat" & i).caption = "0" 'assuming you dont have a seat zero
    next
    [/AIRCODE]
    ...and hide seats zero when you are done
    [AIRCODE]
    for i = 0 to 39
    if me.controls("lblSeat" & i).caption = "0" then
    me.controls("lblSeat" & i).visible = False
    else
    me.controls("lblSeat" & i).visible = true
    endif
    next
    [/AIRCODE]




    izy
    Last edited by izyrider; 01-14-08 at 13:49.
    currently using SS 2008R2

  3. #3
    Join Date
    Jan 2008
    Location
    Denver, CO
    Posts
    54
    Magnificent! Thanks so much... that solved the problem for me. Fantastic. I've always wanted to know how to do that. Thanks!

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    you are welcome

    there is an untidiness in the aircode: since intOffset = recs!seat is inside the With recs, it would be tidier as intOffset = !seat and it might even save a millisecond or two.

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Jan 2008
    Location
    Denver, CO
    Posts
    54
    Excellent! Thanks. I'll do that.

Posting Permissions

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