Entries on Subform disappear

[mightyatom13]

I have a subform that allows you to make selections from a combo box. The combo box, in turn, is drawn from a query that is filtered by date for upcoming events (ie. nothing previous to the current date is in the combo box). When you select an event, it only shows the second column (the name of the event rather than its numeric ID) as this has the info I need. After the date has passed, though, and I am reviewing records, all the boxes are blank as they are no longer in the qry the combo box draws from. The actual entry for the box is a numeric ID, and that is visible in the underlying table. Is there any way to get the box to look up the info from a table seperate from the query that the combo draws from? I don't have room on the form for a seperate box for the name of the event

I hope this makes sense and that someone has an idea.

Jeff Cooper

 

[GingerR]

sure. do this:

in design view, make your combo box transparent (no color in the color bucket).
create a new text box. make it's control source look up the event title:

=DLookUp("EventTitle","EventList","EventID=" & [cboEventID])

tweak as necessary, substituting your object names for these:
Table holding events = "EventList"
Field in table EventList that holds the Event Title: "EventTitle"
Field in table EventList that holds the EventID: "EventID"
combo box name: cboEventID

do NOT make this text box invisible/transparent.
now size it to be exactly the size of the combo box, less the little drop-down arrow. place it directly over the combo box. now that data will appear.

you'll notice now that if you proceed and enter a new record by choosing something from the combo box, previous entries won't show up because now the combo box, which is underneath, has focus, and the text box with the words in it does not have focus. to correct this, put this code into the OnChange event of the combo box:

Me.txtBoxName.SetFocus

tweak and sub in your new text box name for "txtBoxName"

see how that works...

g