String take rows from query to populate a cell

[crmrpage]

I am attempting to make a table which contains a list of room numbers and a yes/no column populate a cell in another table or textbox IF the second cell is true.

example

RoomNum | Clean
110-1 | 1
111-1 | 0
112-1 | 1
113-2 | 1
114-1 | 0
114-2 | 1

Next table

Clean Rooms 110-1,112-1,113-2,114-2

[twinnyfo]

crmrpage,

Welcome to Bytes!

First, just about any expert on this forum will tell you about the same thing concerning your request. We would all advise against creating or updating a second table based upon the results of your first table.

What you are looking for is a list of clean rooms, yes? Then, to get that information, you simply create a query that looks at your first table, and returns only those records which have the Clean field set to "True".

It would look something similar to this:

SELECT RoomNum 
FROM tblRooms 
WHERE Clean;

This would produce the following results:

110-1
112-1
113-2
114-2

Hope that hepps!

[crmrpage]

That is part of the issue, it must populate a single textbox or somehow be written in a string for a printable report. The query part I'm okay with, it is putting it as a string to show
110-1,112-1,113-2,114-2 in a single text box.
I am attempting to not make the user input the data in an additional way for the printable report.
Similar to how it appears in the notes2 section of the report. Unfortunately that one I have it where they have to enter each one individualy

I only need it that way for the purpose of displaying in a report

[twinnyfo]

You can do that easily through a function. How comfortable are you working with VBA? It is really an easy function to create....

[twinnyfo]

I had a few minutes to share this:

Public Function ConcatQuery(strSQL As String) As String
On Error GoTo EH
    Dim db          As DAO.Database
    Dim rst         As DAO.Recordset
    Dim strResult   As String
 
    strResult = ""
    Set db = CurrentDb()
    Set rst = db.OpenRecordset(strSQL)
    With rst
        If Not (.BOF And .EOF) Then
            If .Fields.Count > 1 Then
                MsgBox "Only one field allowed!"
            Else
                .MoveFirst
                Do While Not .EOF
                    If strResult = "" Then
                        strResult = .Fields(0)
                    Else
                        strResult = strResult & _
                            ", " & .Fields(0)
                    End If
                    .MoveNext
                Loop
            End If
        End If
        .Close
    End With
    db.Close
    Set rst = Nothing
    Set db = Nothing
 
    ConcatQuery = strResult
 
    Exit Function
EH:
    MsgBox "There was an error Concatenating!" & vbCrLf & vbCrLf & _
        "Error: " & Err.Number & vbCrLf & _
        "Description: " & Err.Description & vbCrLf & vbCrLf & _
        "Please contact your Database Administrator.", vbCritical, "WARNING!"
    Exit Function
End Function

You can use it in your query like this:

ConcatQuery("SELECT RoomNum FROM tblRooms WHERE Clean;") AS CleanRooms

Hope this hepps!

[crmrpage]

Great, Thanks! I'll give this a shot and let you know.

[crmrpage]

I'm struggling to get this to work. I created and compiled the module and entered the second part into the text box but it keeps popping up a message box requesting the information.
The text box also keeps saying that the expression contains Invalid Syntax

[crmrpage]

To better explain I added a my table in a screen shot. I need for the list of [BedNum] to be placed into a textbox for every patient that has a blood glucose checked, one for drips, one for foley, one for central, one for restraints and last one for isolation. The image shown here is how I want it to look but it is currently set up where the user has to input the data twice.

[twinnyfo]

I'm struggling to get this to work. I created and compiled the module and entered the second part into the text box but it keeps popping up a message box requesting the information.

I do not understand what you mean when you say you've "entered the second part into the text box".

Please explain.

This code works perfectly on my machine when I send it a properly formatted SQL string.

[crmrpage]

Where was I supposed to place this part?

ConcatQuery("SELECT RoomNum FROM tblRooms WHERE Clean;") AS CleanRooms

[twinnyfo]

This is where you have made it a bit difficult for us....

Based on Post #8 (and previous posts), it "appears" that you have a report based on the information that you want to list. That report must have an underlying table or query as its RecordSource.

Since we don't have that RecordSource, we can't tell you exactly where to put it.

Additionally, based upon your initial post, you wanted to list Rooms that were "Clean." However, since that initial post, I have not seen any images or any indicators on any report that shows anything about "Clean" rooms; so, I can't point to anywhere on any of your images to say, "Put it there!"

Please--I'm not trying to be a jerk. I just can't advise you without additional information. Based upon what I see so far, you don't appear to be a complete beginner. But, you have to help us out with more details.

[NeoPa]

If you check post #5 you'll see that it's to go in your query.

It's actually in SQL format so add it into the SQL of the query. Alternatively, if adding it in the QBE (Query By Example) window it would look like :

CleanRooms: ConcatQuery("SELECT RoomNum FROM tblRooms WHERE Clean;")

[crmrpage]

I used the clean part as an example, the other is identical in that they are just yes/no
I placed an image showing how my table is setup (7). The only part that I need for this issue is the BEDNUM and the yes/no fields such as clean or bloodglucose. The user puts a check for each patient that has each of those listed such as FOLEY, DRIPS, BLOOD GLUCOSE, RESTRAINTS, CENTRAL, ISOLATION etc.

What I need is to have a text box in my report that displays all rooms with a check mark in FOLEY for example in a single string as displayed in the image attached(8).

I'm not a beginner, I just have been out of the game for 15+ years. I once made a living off of making MS ACCESS databases and have used Access since the early 90's while in the military but had to switch to teaching Math and Computer Science in 2002 for the last 15 years and now I work at a VA hospital as a Clerk and am trying to improve their assignment sheet system for nurses so I am a little rusty

[crmrpage]

I got it to work! YAY :) Thank you NeoPa for pointing out my blindness. Totally overlooked that part!

[crmrpage]

Worked great then when closed and reopened it gave a

Not sure why

[twinnyfo]

Did you forget to save the Module that had the Function or forget to set it at Public instead of Private?

[crmrpage]

It is public if you are referring to here?

And it is saved

[PhilOfWalton]

crmrpage

You need to create a Standard Module, NOT a Class Module. Past in Twinnyfo's code and when you save it, call the module anything you like OTHER THAN "ConcatQuery". Access sometimes gets confused if a Module Name & a Function Name are the same.

Personally, I tend to call it ModQueries, and any functions used in Queries goes into this module. Sim

[crmrpage]

That was it thank you!

[twinnyfo]

@All - My guess is the naming convention you used for the Module. That will always cause a problem with Name resolution.

You should also always get into a habit of a proper naming convention; e.g., Modules begin with "mod" or "bas", Forms beign with "frm", Tables with "tbl". You get the picture. This allows you--and other users who are troubleshooting your work, to quickly understand what refers to what.

[NeoPa]

It's funny how, by getting a couple of things wrong during this process, you've learned far more than you would have if you'd got everything right first off.

I'm very pleased to see you got it all working in the end. Well done :-)