Return the results of a SQL query in textbox

[virgilio]

I'm trying to display all of the results of a query in a textbox on my form. The form that this query is happening on is based on a different table than the data that I'm displaying, so I think I need to run a SQL query.

The previous maintainer of this DB used the following SQL string to make the query:

Source = "SELECT DATE_ENTERED, USER_ID, COMMENT_STR FROM LOT_COMMENTS WHERE LOT_NUMBER = '" & LOT_NUMBER & "' ORDER BY DATE_ENTERED DESC;"

The table that is being queried, LOT_COMMENTS, is organized like this:

Table LOT_COMMENTS =
record_id | LOT_NUMBER | USER_ID | LOT_COMMENTS  | DATE_ENTERED
550         | 66666666   | 109     | First Comment    | 12/14/2019
551         | 66666666   | 210     | Second Comment| 12/14/2019
552         | 66666666   | 311     | Third Comment   | 12/14/2019

and the results are something like:

12/14/2019 109 First Comment
12/14/2019 210 Second comment
12/14/2019 311 Third comment

I have tried putting the SQL string into a function:

function getComments()
   dim Source as string
   Source = "SELECT DATE_ENTERED, USER_ID, COMMENT_STR FROM LOT_COMMENTS WHERE LOT_NUMBER = '" & LOT_NUMBER & "' ORDER BY DATE_ENTERED DESC;"Source = "SELECT DATE_ENTERED, USER_ID, COMMENT_STR FROM LOT_COMMENTS WHERE LOT_NUMBER = '" & LOT_NUMBER & "' ORDER BY DATE_ENTERED DESC;"

   getComments = Source
   end function

but VBA doesn't like this, when I use the function as the data source(ie, Control Source =getComments() ), I get the full SQL string in the comments instead of the desired return value.

What am I missing?

[June7]

The SQL statement is just a string, it is not data. SQL statement must be used to open a recordset object or as form/report RecordSource or combo/list box RowSource.

So your options are:

1. subform

2. listbox or combobox

3. VBA that loops through Recordset and builds a string from data and populates textbox with that string

Looks like you duplicated the Source = … line in posted code.

[virgilio]

woops that duplicate was a typo.

I'm pretty sure that I can generate a subform that does this in no time, but what would the VBA look like?

[June7]

Create a subform with VBA? I would not do that.

Create a form with design tools.

Do these two sets of data have a relationship?

If you want to provide db for analysis, follow instructions at bottom of my post.

[ArviLaanemets]

Main form source must have fields User_ID and Lot_Number (or at least User_ID, and an unbound control which determines lot number).

Then you create a form with your comments query as source, activate your main query in design mode, and drag comments form into main form. This creates a subform control in your main form. In subfrom properties, LinkMasterFields property must be like 'USER_ID;LOT_NUMBER' or 'USER_ID;LotNumberControl' , LinkChildFields property must be like 'USER_ID;LOT_NUMBER'. Links may be created automatically - when not, then set them manually.

In case you have for any combination of USER_ID and LOT_NUMBER maximally a single comment only, then you may consider to base your main table on left join query instead, like

SELECT ymt.SomeField1, ymt.SomeField2,...,ct.Comment FROM YourMainTable ymt LEFT JOIN CommentsTable ct ON ct.USER_ID = ymt.SER_ID AND ct.LOT_NUMBER = ymt.LOT_NUMBER

Now you don't need subform. But you have to check, is the query editable or not - I can't say it on fly.

[virgilio]

Thank you all for your help, I went with a combobox in a subform because it was the easiest for me to implement. I'll revisit this thread later when I improve this app if I need help.