Problem updating a Function

[cantona]

I have a report displaying applications by people to particular courses. The report contains the following text box to display offer criteria. By Offer criteria I mean a simple sentence stating ‘for this course you must have passed this previous course’ etc..

=GetOffer([txtPersonCode].[Value],[txtOfferCode].[Value])

txtPersonCode is a text box with a control source of [Person_code], txtOfferCode is a text box with a control source of [Offer_code]. The problem im having with the report is that the text box is displaying offer criteria for all the applications that a person may have for that year. I only want it to display the current record.

The Get Offer() function uses the following code;

Public Function GetOffer(nPerson As Long, sOffer As String) As String
Dim rs As DAO.Recordset
Dim sOfferString As String

'Get the offer elements for this offer code
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT [OfferElements].* " & _
"FROM [OfferElements] " & _
"WHERE [OfferElements].[OFFER_CODE] = '" & sOffer & "' " & _
"AND per_person_code = " & nPerson & ";")
With rs
.MoveFirst
sOfferString = ""
Do While Not .EOF
If ![OFFER_CODE] = 33 Then
sOfferString = sOfferString & Trim(!COMMENTS) & " "
Else
sOfferString = sOfferString & Trim(!OFFER_DESCRIPTION) & " "
End If
.MoveNext
Loop
End With
GetOffer = Trim(sOfferString)

End Function

As it is not a report I have created myself I’m finding it difficult to work out what the function is doing and what I need to change. It would appear that it is only using the person code and offer code field for a person. How would I amend the text box above and the function above to include a field called AU_Number. (number field) This is a unique number so should only print the offer criteria for one record. How would I amend the query above to include the Au_number field? The field exists with the OfferElements table. Then, would I need to amend the text box as well?

 

[Remou]

In outline, you can change the recordset:

Set rs = db.OpenRecordset("SELECT [OfferElements].* " & _
  "FROM [OfferElements] " & _
  "WHERE [OfferElements].[AUCODE] = '" & sAUCode & "' "

Assuming that AUCode is a text field and that you feed in AUCode as sAUCode, however, there are a few points to consider, for example, is the function you posted used elsewhere? Would simply creating a new report be more suitable in that a function should not be necessary when only one record is being returned?

 

[cantona]

Hi Remou,

Thanks for your help, managed to get it working with what you suggested. I simply replaced the person code line, with the AU_Number and the report works fine.