Binding query results to a text box

[Jacob Frankham]

PLEASE HELP!

I have had help in the past with this one but I cannot get it to work !

I have a query called 'sqry_ContractProvisions' which produces 2 fields:
contract and provision
ie
contract NTN001506 provision BET
contract NTN001506 provision LOT
contract NTN001506 provision IAP
contract SCO003201 provision SJFT

I have a report which is grouping by contract in the 'group header', and I
wish to list all of the associated provisions in the 'group footer' ON ONE
LINE SEPARATED BY COMMAS.

ie for the group NTN001506, I wish to see something like this in the group
footer:
BET, LOT, IAP

Hope you can help, I am a complete novice.

Jake

 

[Andrew Smith]

Hi Jacob,

I think this is quite a difficult question for a complete novice! It will
have to be done using code and the code needs to be in the OnPrint event of
the report group footer.

First you should add an empty text box control to the group footer section
of the report. Call it txtProvisionList or something similar. Next enter the
code in the OnPrint event. The code needs to do the following things:

1. Open a recordset containing the provision field for the current contract.
2. Loop through each record and add the provision to a text string.
3. Set the value of the text box to the text string.

The code will be something like this:

'Start of code
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strProvisionList As String

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT Provision FROM sqry_ContractProvisions
WHERE Contract = '" & [Contract] & "'")

With rst
Do Until .EOF
strProvisionList = strProvisionList & !Provision & ", "
.MoveNext
Loop
.Close
End With

'The next line gets rid of the last ", "
strProvisionList = Left(strProvisionList, Len(strProvisionList) - 2)
Me.txtProvisionList = strProvisionist

db.Close
Set db = Nothing
Set rst = Nothing
'End of code

If you are using Access 2000 or later you will need to add a reference to
the DAO object library to use this code (tools - references in the VB editor
window).

Good luck.

Andrew