using code with a report.

[sha7jpm]

I am having a headache over this!

I have some code which is based on query field, and then populates an unbound txtbox in a report..

I have the code in the onOpen event of the report..

so the report is based on the query.. it opens.. runs the code..grabs the field and populates the new field..

the problem I am having is that

1. I cannot get the code to grab the field..
2. or populate the new field within the report.

the issues I have is that I am unsure of the code needed to grab the data... do I go back to the query or can I grab it from the report.. ( I am quite unsure of the syntax needed for this.. I keep geting stuck around querydef and me!....)

and is it because of the nature of the report that you cannot use it as a source for the code...

I am fine with code within forms but seem to invite probelms when I start to use it for reports and queries...

bah!

cheers

John.

 

[Fizzio]

Where are you trying to grab the data from? Is it not possible to include the data you are trying to grab into the report's recordsource?

 

[sha7jpm]

Private Sub Report_Open(Cancel As Integer)

Dim intcount As Integer, varFile As Variant, objFileFind As Object


hi Fizzio, many thanks for replying.. it is linked to the help you gave me recently with the searching in windows explorer from access..

as you can see below.. I have tried to link to the report field "projectscode" but it falls over with "argument not optional"
the unbound txtbox below "jimbo" is where I hope to place the resulting count of files.

I was hoping to grab the data from the form like you suggest.. but am clearly going wrong somewhere!...


Set objFileFind = Application.FileSearch 'FindFiles

'Find the Files
With objFileFind
.NewSearch
.FileName = Reports.Item.RecordSource.ProjectsCode & "*.sav" 'or whatever you want to find
.LookIn = "c:\temp"
.SearchSubFolders = False 'if you have subfolders to search also
.Execute

'Count the files found
intcount = 0
For Each varFile In .FoundFiles
intcount = intcount + 1
Me.Jimbo = intcount
Next varFile
End With

End Sub

 

[Fizzio]

What pouplates the report. You seem to be trying to get something from the report but I cannot fathom out quite what
Are you tring to grab a particular filename that is stored in the report?
You could populate a lisbox or combo with all the values the FileFind code throws up then select the file from there but it depends on why you need the value of the file.

 

[sha7jpm]

apologies...

I should have put that in my last post.

the report is based on a query...

on the query is a field (projectscode) I am using this to tell the code where and what to look for....

ie.. findfile projectscode & ".sav"
look in "h:\" & projectscode

so for each row on the report.. the code will take that field.. use it in the directory path it searches in and return the number of files it finds..

once it has the number of files (intcount)
I have an unbound txtbox (jimbo) on the report for each record that is populated..


hope this helps.. I really appreciate your guidance on this..

ta

John

 

[Fizzio]

If I'm not mistaken if the report has a number of rows, you will probably need to use the on_format or on_print events of the report to capture the data for each projectscode.

I'm not sure if you can grab the field from the report before it is formatted/printed to populate your variable in your code but you could do it with a recordset that you could loop through.

Can anyone else clarify the grabbing data from a report bit?