How do I update my combo box

[rrasch]

We have a small computer room with 4 computers.
An attendant logs-in and out people. I wrote
the database that anyone of a 110 people using
the room could be the attendant. We've been using
this program since November 11, 2011. It’s working
fine and is very user friendly.

On the First tab it has 2 buttons(for each computer)
to activate a countdown timer for (1hr). One drop-
down combobox showing room# and name. The second


combobox is for usage-description (google, email,
resume, games... etc)

The Second Tab shows a datasheet of the stuff
we used to write on paper. { Timein-Timeout, Room#,
name,shift. Guy on duty....} Everything seemed fine.

But I've come to realize that if someone new moves-in...
I'm Screwed!

(my program's table structure is right) but I can't figure
out; how to make only the (new guys name) with the
room number.... Showup in my combo box.
as new guestnames are created the combobox must be update
with new names assigned to room.

As part of my code I've got... Form_Rooms Listed in ComboBox

Private Sub Room406()
' I made a 110 subs like this one which is called from another sub
' rooms are numbered disorderedly.....
Me!Labe1Room406.Caption = DLookup("RoomNumber", "Guests", "[myIndex]=" & 37)
theLastDate = DLookup("LastDate", "Guests", "[myIndex]=" & 37)
Me!FirstName406.Caption = Forms!Tab!Main.Form!RoomCombo01.Column(1, 37)
'myActivationDate=30days is public
If (myActivationDate < DateDiff("d", theLastDate, Now())) Then
Forms![Rooms_Listed_in Combo-Box]![Labe1Room406].ForeColor = colorRed1
Forms![Rooms_Listed_in Combo-Box]![Labe1FirstName406].ForeColor = colorRed1
Else
Forms![Rooms_Listed_in Combo-Box]![Labe1Room406].ForeColor = colorBlue2
Forms![Rooms_Listed_in Combo-Box]![Labe1FirstName406].ForeColor = colorBlue2
End If
End Sub

Guest **** GuestName ****Check_in
-------- **** ----------- **** ----------
GuestID **** GuestNameID **** CheckIn_ID
RoomNumber **** FirstName **** GuestNameId
ResPassword **** Initials ****CheckinDateTime
myIndex **** GuestID ****CheckoutDateTime
LastDate **** Computer# **** UsageDescription

Combobox Rowsource setting
SELECT [Room ComboBox Query].RoomNumber, [Room ComboBox Query].FirstName, [Room ComboBox Query].GuestID, [Room ComboBox Query].GuestNameID FROM [Room ComboBox Query] ORDER BY [RoomNumber], [FirstName];
is there an easy way to adjust the setting?

[June7]

Not following, can you provide project for analysis? Copy, remove confidential data, run Compact & Repair, zip if large, attach to post.

[rrasch]

thanks for writing june.... my program is 1.4 mbs

I have the mdb on my yahoo Zumbodrive but I think it has to be a mailed link

http://www.zumodrive.com/share/f1RMMjIxNz

I found a work around solution; but it creates a problem if it's not used accurately...

I would still rather have a select distinct working than what I' made now.

[June7]

If you still need something fixed I need more info. What is the process that is giving you an issue? If you add a new guest while the Computer Room form is open, need to requery all the guestname comboboxes.

What is the work around solution? You want a 'select distinct' instead of what? What code should I look at?