Outputting records as a string in a textbox

saross

Registered User.
Local time
Today, 06:29
Joined
Mar 4, 2003
Messages
120
Is it possible (I'm sure it is but unsure how) to ouput records from a query into a string that can then be displayed in a text box. Query would ouput between 1 and 20 records and I would want to use one of the fields only which are text. I would like to link them together into a string with a comma between each one... How could I do this?
 
Paste this code into the form code module and call it from a command button
you will need to insert your own field and query names


Sub GetTxt
Dim tempRecordset As DAO.Recordset
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim StrText as string

StrText=vbnullstring

Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("YourQueryName")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set tempRecordset = qdf.OpenRecordset
With tempRecordset

If Not tempRecordset.BOF And Not tempRecordset.EOF Then

tempRecordset.movefirst
do until tempRecordset.Eof =true

StrText= "," & StrText

tempRecordset.movenext
Loop


'get rid of the leading comma
if len(strText) > 1 then
me.[YourTxtboxName]=right(StrText,Len(StrText)-1)
else
me.[YourTxtboxName]=null
end if
end if
tempRecordset.Close
Set tempRecordset = Nothing
Set qdf = Nothing
Set dbs = Nothing
End With
End Sub

Regards
Bjackson
 
THanks for this. I've put it into a function which I've called on the on open/on update actions for the form so the info automatically outputs but it seems to be skipping a lot of the code. I've put a watch on the qdf and it definitely returns the right records but the function seems to skip lines as follows:

Public Function LoadSubCats()
Dim tempRecordset As DAO.Recordset
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim StrText As String

StrText = vbNullString

Set db = CurrentDb
Set qdf = db.QueryDefs("qrySubCat")

-------------------------------
SKIPS FROM HERE
------------------------------------
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm

Set tempRecordset = qdf.OpenRecordset
With tempRecordset
If Not tempRecordset.BOF And Not tempRecordset.EOF Then
tempRecordset.MoveFirst
Do Until tempRecordset.EOF = True
StrText = "," & StrText
tempRecordset.MoveNext
Loop

'get rid of the leading comma
If Len(StrText) > 1 Then
Forms!FRMContractInfo.[txtSubCat] = Right(StrText, Len(StrText) - 1)
Else
Forms!FRMContractInfo.[txtSubCat] = Null
End If
End If

tempRecordset.Close
---------------------------------------
TO HERE
---------------------------------------
Set tempRecordset = Nothing
Set qdf = Nothing
Set db = Nothing
End With

End Function

Have I done something wrong? :confused:
 
I didnt know if your query had any parameters so i assumed it did.If there are no parameters then the code should still return the query and get the value you are after, or you could just change the code to


Dim tempRecordset As DAO.Recordset
Dim db As DAO.Database
Dim StrText As String
StrText = vbNullString
Set db = CurrentDb
Set tempRecordset= db.openrecordset("qrySubCat")

Does it return the string you want ?

Regards
Bjackson
 
My ignorance is embarassing.... but i'm not too sure what the perameters of a query are..?? My query is:

SELECT TBLSubCat.SubCategory
FROM TBLSubCat INNER JOIN (TBLContractInfo INNER JOIN TBLContractSubCat ON TBLContractInfo.ContractID = TBLContractSubCat.ContractID) ON TBLSubCat.SubCatID = TBLContractSubCat.SubCatID
WHERE (((TBLContractInfo.ContractID)=[Forms]![FRMContractInfo]![ContractID]));

Sometimes it will return a number of records and sometimes none. I noticed with the previous piece of code you gave me that if there were more than one record it did output the comma value, but not the text from the field...

I've put in the amended code you suggested but this returns an error message on the set db action since at the moment for many records the value returned by the query is nil...

I've tried to work through the code you originally gave me and understand it but i was totally stumped, a. by the perameters bit, and b. i couldn't see where it drew on the value of the field in question from the query.

:-$
 
the parameter for the query is the form criteria =Forms![FRMContractInfo]![ContractID]

My Mistake for the set db
Set db = CurrentDb()

when you step through your code this part prm.Value = Eval(prm.Name)
should be prm.value=the value of the field on the form
Eval(prm.name) should = the control name, which is Forms ![FRMContractInfo]![ContractID]
hope this helps
Bjackson
 
prm.value doesn't take on any value, though the prm.Name does take on the control name while the current action is on that line.

Once you pass through the line, no value is assigned to either, but when you hover over either it says the following:
prm.Value (and prm.Name) = Object variable or With block variable not set

I have included
Dim prm As DAO.Parameter

in my declarations...
 
You must pass to the next line of code to set the object variable.So When the cursor drops to the next line ,then hover over it.If it has no value then that means the form control has no value and the query will not return the records you seek.

This is another way to try,although a stored query is more effecient

Dim Dbs as Dao.Database
Dim tempRecordset as Dao.Recordset
Dim QueryStr as string
Dim StrText As String

set Db=CurrentDb()

StrText = vbNullString

QueryStr="SELECT TBLSubCat.SubCategory" _
& " FROM TBLSubCat INNER JOIN (TBLContractInfo INNER JOIN " _
& " TBLContractSubCat ON TBLContractInfo.ContractID = " _
& " TBLContractSubCat.ContractID) ON TBLSubCat.SubCatID = " _
& " TBLContractSubCat.SubCatID" _
& " WHERE (((TBLContractInfo.ContractID)=" & [Forms]![FRMContractInfo]![ContractID] & "));"

Set tempRecordset=Db.OpenRecorset(QueryStr)


If Not tempRecordset.BOF And Not tempRecordset.EOF Then

Etc Etc

Regards
Bjackson
 
Arrrrrrgh!!! This thing is driving me insane!! I just wish I could understand the perameters bit clearer...

Anyway, tried your alternative option but my strings are really playing up too!! Both this one and another strQry I'm trying to use won't accept the full length of the string. They cut off halfway through for no reason. I've checked the formatting etc. and I can't see a reason why...

:eek:
 
I should have tested what i gave you better.There was no field being used from the query,thats the reason for only having the commas in the string with no other value.


I ran your function through 20 records from my db using a form control as criteria for the query.It returned a commer delimited string as expected.As to why your string is cut off i cant help you.Maybe its to long.

Public Function LoadSubCats()
Dim tempRecordset As DAO.Recordset
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim StrText As String

StrText = vbNullString

Set db = CurrentDb
Set qdf = db.QueryDefs("qrySubCat")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm

Set tempRecordset = qdf.OpenRecordset
With tempRecordset
If Not tempRecordset.BOF And Not tempRecordset.EOF Then
tempRecordset.MoveFirst
Do Until tempRecordset.EOF = True
StrText = StrText & "," & ![SubCategory]
tempRecordset.MoveNext
Loop

'get rid of the leading comma
If Len(StrText) > 1 Then
Forms!FRMContractInfo.[txtSubCat] = Right(StrText, Len(StrText) - 1)
Else
Forms!FRMContractInfo.[txtSubCat] = Null
End If
End If

tempRecordset.Close
Set tempRecordset = Nothing
Set qdf = Nothing
Set db = Nothing
End With

End Function


Regards
Bjackson
 
That's worked!! Thank you so much... you've been really patient! (And hopefully I've learned loads!) :)
 
your welcome, glad to put back some of the help i have received from these forums.
Bjackson
 

Users who are viewing this thread

Back
Top Bottom