Display queried value in controls

[Peter Scott]

Hello. I'm afraid I don't program Access often enough to have gotten the
mental model for certain things yet. I've struggled with this one for a
day.

This is a simple event scheduling app: Tables: Rooms, TimeSlots, Sessions,
each keyed on ID. And a table Schedule, containing just: TimeSlotID,
RoomID, SessionID. I'm making a form for Schedule that will control the
scheduling by room, so it is a single form containing a list box bound to
RoomID and a continuous forms subform containing TimeSlotID and SessionID.

Because Schedule is pre-populated with all possible room & timeslot IDs,
TimeSlotID is shown in a locked text box; SessionID is a list box.

This works: I can select a room by its ID, see all the timeslots by their
IDs and change the corresponding sessions by their IDs. But what I want
displayed instead of the IDs are values drawn from the tables those IDs
refer to. So where I see TimeSlotID I want to see TimeSlots.Day &
TimeSlots.StartTime & TimeSlots.EndTime (where TimeSlots.ID = TimeSlotID),
etc, even though the control is reading and writing the numeric ID.

I've sweated over ControlSource for a while trying to put a query in
there but I'm not getting it. Can someone point me in the right direction
please?

 

[swas]

Peter,

Place a combobox on the form with the wizard control enabled. Let it guide
you through the selection of some data from a table with an ID field hidden.
Then look at the properties for the cbo and how they interact. Particularly:

column count (How many columns)

column widths (Note ID width is 0 so not displayed)

list width (Total of column widths)

bound column


You can do all the above at table design level by making the ID field a
lookup control, then anytime the field is placed on a form the columns etc..
.. are set to display the desired field for the ID selected.


swas

 

[Peter Scott]

Thanks, swas. I now understand about columns and binding. My only
remaining issue is a cosmetic one I can live with. The TimeSlotID field
on the subform should not be changeable by the user. I can make it
locked and disabled and either make it a list box of height 1 or a combo
box, but in both cases I cannot stop it displaying with with scroll bars
and in one case is inverted. I wanted it to look like a disabled text
box, but in that case I cannot enter any column information. Would what I
want still be doable, and would it take some VB?

 

[swas]

Peter,

You should be able to make the control a text box and locked (Not the
subform).

But if you can't, you can always add some code to the beforeupdate event for
the control. If you don't want them to edit it, set cancel=true

Then control.undo to restore the previous value if needed (Cancel=true only
stops the update, but the control still has the new value set).

You can put a msgbox in to advise the user if desired, or check if it is a
new record (if me.newrecord then...) to allow different rules here.

I find this sort of stuff (moulding a form to react how you want it) can
take a lot of time, especially if several controls interact.

Malcolm