I have a lookup and want only the available items to be those not already selected. How filter?

[Duncan Pucher]

Attached is my database. The primary table is called Resident and the lookup table is RoomNumbers. I want users to only be able to select from rooms not already occupied. I don't want double-booking of rooms. How can I do this?

[June7]

Join lookup table to the data table.

You have Number in Room_Numbers designated as primary key yet you are saving Room into Resident table. Relationship is set with link on Number (autonumber) and Room_Num (text) fields. I am surprised the Relationships builder even allowed this because this errors in query. If you want to save Room then set this as PK.

Why is Number in Physicians a text field? Why is it not autonumber? You are saving the physician name instead of Number. This is repeating the physicians name in data. Same issue for Shift and Diabetic_Status. You are not using the key fields.

Need to fix relationships before proceeding.

Advise NOT to set lookup fields in table especially if alias is involved, which yours don't at this time but would if you modify relationships. http://access.mvps.org/Access/lookupfields.htm

[davegri]

CarlaRoomNumberlookup davegri-v01.zip
Have a look at this. It will not allow duplicate room number assignments.
As others also suggest, removed lookup fields in tables, renamed primary and foreign keys; all keys now numeric.
Changed diabetic field in table to Yes/No with checkbox on form. Diabetic table not used at all.

[orange]

You can review the Room Reservation sample in the Code Repository for ideas.
Good luck

[Duncan Pucher]

Dave,

Your revision to the DB was a big help. I changed the relationships but was wondering if you could look at it again for two items. The room numbers still allow for double-booking of a room. Also, I added a new table for health care staff to review the care of a resident. Care Reviews may happen many times for the same resident. Could you look at how I set the relationship up and how my form (rough) and report (rough) look?

Thank you,

[Duncan Pucher]

Thanks for your help. It needs revision.

[Duncan Pucher]

Thanks, I appreciate the ideas.

[June7]

A form should do data entry/edit for only one table. CareReviewstblSubform should not have Residenttbl in its RecordSource. Residenttbl info is on the main form.

This SQL lists rooms not yet assigned:

SELECT RoomNumberstbl.RoomNumber_PK, RoomNumberstbl.Room
FROM RoomNumberstbl LEFT JOIN Residenttbl ON RoomNumberstbl.RoomNumber_PK = Residenttbl.RoomNum_FK
WHERE (((Residenttbl.RoomNum_FK) Is Null));

[davegri]

The changes I made to avoid duplicate room numbers didn't make it to your latest DB. I've included it again in this DB.
The roomnum_FK is no longer a table lookup. The field is a foreign key having the value of the primary key in the RoomNumberstbl.
It is also indexed, no duplicates.
The comboboxes on the various forms that allow room number changes have an error trap in the afterUpdate events to inform users that the room is not vacant if an attempt is made to assigned it twice.

June's SQL for unmatched rooms won't work for combobox rowsource because as soon as the room is selected, it's no longer unmatched, and not available for the current resident assignment. I originally tried that first and was confounded why it wouldn't work. That's why I went to the indexed no duplicates route.

As far as reviews, the setup is OK, but the subform is pretty wide. If you don't want to scroll that far to the right, you could make the subform single record to compact the view, but that would require vertical scrolling from one review record to the next, so choose lesser evil.
Carla v7 06-14-19 -davegri-v02.zip

[June7]

The issue of alias value not able to display for existing record when RowSource is filtered is well known. This is because the key is saved, not the room number. This would be simplified if room number were saved (which is probably the route I would take in this case). However, one solution for the RowSource is:

SELECT RoomNumberstbl.RoomNumber_PK, RoomNumberstbl.Room FROM RoomNumberstbl LEFT JOIN Residenttbl ON RoomNumberstbl.RoomNumber_PK = Residenttbl.RoomNum_FK WHERE (((Residenttbl.RoomNum_FK) Is Null)) Or (((RoomNumberstbl.RoomNumber_PK)=Forms!ResidentCar eReviewsmainfrm!RoomNum_FK));

Then code in form Current event:

Me.RoomNum_FK.Requery

I discovered 2 records with room 106A.

Advise giving controls names different from field, such as tbxFName, cbxRoom. Also, advise not to use spaces nor punctuation/special characters in naming convention for anything. Fields in CareReviewstbl have both. I would put qualifiers like tbl, qry at beginning of names.

[Duncan Pucher]

June,

I see. Thanks for the information.

[Duncan Pucher]

Dave,

Thanks again for help. The room lookup works great!

[Duncan Pucher]

After looking at the room number lookup procedure I noticed I needed something else. If a resident becomes inactive I don't want it to show an error message associated with the on event procedure when selecting a room for an active resident. The residents who are inactive could be in a room and it would show an error but the room is actually vacant. Could the room be cleared upon selecting inactive? or could the procedure only select active residents?

[davegri]

Code for ActivesndinactiveResidentsfrm

Option Compare Database
Option Explicit

Private Sub chkActiveInactive_AfterUpdate()
    If Me.chkActiveInactive = True Then
            Me.RoomNumID = Null
    End If
End Sub

Private Sub RoomNumID_AfterUpdate()
    On Error GoTo RoomNumID_AfterUpdate_Error
    If Me.chkActiveInactive = True Then
        MsgBox "Resident is inactive status, cannot assign room.", vbOKOnly + vbInformation, "   I N A C T I V E   R E S I D E N T   "
        Me.RoomNumID = Null
        Exit Sub
    Else
        Me.Requery
    End If
RoomNumID_AfterUpdate_EXIT:
    Exit Sub
RoomNumID_AfterUpdate_Error:
    Select Case Err
        Case 3022
            MsgBox "Room already assigned. Choose another room.", vbOKOnly + vbInformation, "   R O O M   N O T   V A C A N T   "
            Me.RoomNumID = Null
        Case Else
            MsgBox Err.Number & ", " & Err.Description & " in RoomNumID_AfterUpdate of VBA Document Form_ActiveandInactiveResidentfrm"
    End Select
    Resume RoomNumID_AfterUpdate_EXIT
End Sub

Your forms are going to be hard to maintain because you can update the same resident data on several forms. For example, you can change room assignments on 4 different forms. This is not good practice. With that arrangement, the code above needs to be on all 4 forms. More complicated, you don't have consistent names for the textboxes and comboboxes, requiring adjustments to the code above depending on the form.
Suggest having one form to edit resident data, then with other forms having to show resident data, have controls locked so changes cannot be made. See the example below. The LastName, FirstName, RoomNumber can be locked, as those items should not be allowed to change on that form.

[Duncan Pucher]

Dave,

Thanks for the code for Code for ActivesndinactiveResidentsfrm. I am not sure where the code goes. Does it replace the code under [Event procedure] for Room? I tried to copy the code over the existing code but it errored. Can you help me update the attached code?

Thanks for the comments about having duplicate fields on forms. I was able to consolidate the forms so I have one form for care items and one main report with a subreport. Since I consolidated I do not have a way for the user to get to inactive records. Is there a recommended way for users to query old records and reactivate them?

It looks like I'm getting closer to what will work.

Thank you,

I posted another question on the reports section of the forum since sorting isn't working.