Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

findfirst using text criteria 1

Status
Not open for further replies.

errolf

IS-IT--Management
Aug 21, 2000
51
US
I am trying to fill an unbound text box on a report. The Code field in my Stock table is text as I am using alphanumeric codes. I am using the following function
Function FillRep()
Dim rs As Recordset
Dim db As Database
Set db = CurrentDb
Set rs = db.OpenRecordset("STOCK", dbOpenDynaset)
rs.MoveFirst
rs.FindFirst "[P_NO] =" & "'Reports![Fill Report]![P_NO]'"
Reports![Fill Report]![Price] = rs![PRICE_1]
'FillRep = rs![P_NO]
End Function
The problem I have is that I only get the same value in every line of the [Price] text box which is the first record in the Stock table. I can get it to work If I change my text field to numeric and use rs.FindFirst "[P_NO] =" & Reports![Fill Report]![P_NO] but alas I am unable to use a text field in my Stock table because of some stock code constraints
 
Code:
Function FillRep()

    'I am trying to fill an unbound text box on a report.
    'The Code field in my Stock table is text as I am
    'using alphanumeric codes.

    Dim rs As Recordset
    Dim db As Database

    Set db = CurrentDb
    Set rs = db.OpenRecordset("STOCK", dbOpenDynaset)

'_________________________________________________________________________________
'    rs.MoveFirst           'This is unnecessary, as FindFirst starts at Beginning
'_________________________________________________________________________________


    rs.FindFirst "[P_NO] =" & "'Reports![Fill Report]![P_NO]'"

    Reports![Fill Report]![Price] = rs![PRICE_1]
    'FillRep = rs![P_NO]

    'The problem I have is that I only get the same value in every line
    'of the [Price] text box which is the first record in the Stock table.
    'I can get it to work If  I change my text field to numeric and use
    '
    'rs.FindFirst "[P_NO] =" & Reports![Fill Report]![P_NO]
    '
    'but alas I am unable to use a text field in my Stock table
    'because of some stock code constraints
'___________________________________________________________________________________
    'Response:
    'You get thte same value (first record value) because when FindFirst
    'Doesn't "find" th ecriteria, it restores the record pointer to the
    'Starting point.  The MoveFirst statement places the record pointer
    'at the first record, --- you get this value on the failure of FindFirst.

    'You should ALWAYS test the results of any of the FindMethods or Seek.
    'The tersting is different in various versions of Ms. Access, so you need
    'to look up the sample in your help (earlier versions there was a "NoMatch"
    'function, but I understand that Ms. Access 2K doesn't use it?

    'AS to the Actual Find statement, the Problem is not clear from your description,
    'however I suspect that the reports...[p_no] has been set to a numeric
    'representation (Format Property?).  If this is the case, then
    
    'rs.FindFirst "[P_NO] =" & "'Trim(Str(Reports![Fill Report]![P_NO]))'"

    'may work better

End Function

MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over
 
I have a report and I use the the following function to create an index
Option Compare Database
Option Explicit


Dim db As Database
Dim toctable As Recordset

Function InitToc()
' Called from the OnOpen property of the report.
' Opens the database and the table for the report.
Dim qd As QueryDef
Set db = CurrentDb()
Set qd = db.CreateQueryDef("", "Delete * From [Table Of Contents]")
qd.Execute
qd.Close
' Opens the table.
Set toctable = db.OpenRecordset("Table Of Contents", dbOpenTable)
toctable.Index = "Description"

End Function

Function UpdateToc(tocentry As String, Rpt As Report)

' Called from the OnPrint property of the section containing
' the Table Of Contents entry. Updates the Table Of Contents
' entry.

toctable.Seek ">", tocentry

If toctable.NoMatch Then
toctable.AddNew
toctable!Description = tocentry
toctable![page number] = Rpt.Page
toctable.UPDATE
End If

End Function


The problem that I have is that if the product heading spans more than one page I get multiple entries on the table created eg:
Description Page Number

Fertilisers Retail 2
Fertilisers Retail 3
Fertilisers Retail 4
Frost Protection & Anti Transpirants 5
Frost Protection & Anti Transpirants 6
Fungicides And Bactericides (Commercial) 6
Fungicides And Bactericides (Commercial) 7
What I need isa function to concatenate the pages like:-
Description Page Number
Fertilisers Retail 2,3,4
Frost Protection & Anti Transpirants 5,6
Fungicides And Bactericides (Commercial) 6,7
Can anyone help with this problem
Errolf

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top