How are field values in a recordset referenced?

[Jackie]

How are field values in a recordset referenced?

Below is code that opens two recordsets.

A report is run based on a value in the first recordset matching a value in the 2nd record set. When I execute this code a prompt is displayed asking for the rstPM.[Program Manager]and [rstFindProj].[Program Manager] values. I have tried using the ! instead of the . to distinguish the field, but this did not work.

Please note, that I am not finished with this code. I intend to add logic to use the output to command (instead of the OpenReport command) to save the report to the harddrive. I use the OpenReport command for debugging purposes.

Thank you in advance for your help.


Private Sub cmd_PM_ProjNum_Click()

Dim dbMLV As Database
Dim rstPM As Recordset
Dim rstFindProj As Recordset
Dim strFileName As String
Dim strPMName As String

Set dbMLV = CurrentDb
Set rstPM = dbMLV.OpenRecordset("qry_PM_Proj_Num&quot
Set rstFindProj = dbMLV.OpenRecordset("qry_find_program_mgr&quot

If rstPM.RecordCount > 0 Then
Do While Not rstPM.EOF
DoCmd.OpenReport "PM_Proj_Num", acPreview, "", "[rstPM].[Program Manager]= [rstFindProj].[Program Manager]"
rstPM.MoveNext
Loop
MsgBox "Project Manager by Project Number Reports Completed."

End If
End

End Sub

 

[SgtJarrow]

Although not completely sure....what I think you are looking for is:

DoCmd.OpenReport "PM_Proj_Num", acPreview, "", "rstPM.Fields("Program Manager&quot = rstFindProj.Fields("Program Manager&quot"

Please remember to give helpful posts the stars they deserve! This makes the post more visible to others in need!

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

 

[SgtJarrow]

As I hit send I realized, this still will not work for you beacuse of all the double quotes....try:

DoCmd.OpenReport "PM_Proj_Num", acPreview, "", "rstPM.Fields(" & """Program Manager""" & &quot = rstFindProj.Fields(" & """Program Manager""" & &quot" Please remember to give helpful posts the stars they deserve! This makes the post more visible to others in need!

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

 

[Jackie]

This Syntax provies an "Undefined Function rstPM.Fields error.

I put brackets around [Program Manager] to no avail.

I am using Access 97.

Any ideas would be appreciated.

 

[HenriM]

Just a thought - presumably you know the Field Number of "Program Manager", say it is field number 3 in your record.

[rstPM.Field(3)] will return the value of that field. I know because I do it all the time! Admittedly I have not tried it with queries but it works fine with tables. The only problem I can think of is that the Field No may differ between the taable and the query.

If you want to check this, suggest you put a breakpoint in your code, run the program and, in the Debug Window - Immediate type:

[Dim X as Integer]
[For X=0 to rstPM.Fields.Count-1:? X;rstPM.Fields(X).FieldName:Next] (put this all on one line).

In its inimitable way, Access will give you drop-down options on each "." so you should not go far wrong.

If this does not work with queries, why not get the Pragram Manager from the Table , [SELECT * From [TableName] where..... ] and put it to a variable, say "ProgMan" and put that in your OpenREport command instead of the rstPM Recordset?

 

[Jackie]

This is the line of code:

DoCmd.OpenReport "PM_Proj_Num", acPreview, "", "[rstPM.Field(3)] = rstFindProj.Fields(" & """Program Manager""" & &quot"

It produces the error "Invalid Bracketing of Name: [rstPM.Field(3)] "


I tried the syntax of Fields instead of Field and still got an error.

Then I modified the code as follows:
DoCmd.OpenReport "PM_Proj_Num", acPreview, "", "rstPM.Field(3) = rstFindProj.Field(3)" and got the same message.

It seems that this is a syntax problem.

Should it be FIELD or FIELDS?

-jackie

 

[borg2002]

It should be FIELDS.

Kind regards
Borg

 

[Jackie]

I ended up using a string variable as shown below. Thank you for your help.


Private Sub cmd_PM_ProjNum_Click()

Dim dbMLV As Database
Dim rstPM As Recordset
Dim rstFindProj As Recordset

Dim strFindPM As String


Set dbMLV = CurrentDb
Set rstPM = dbMLV.OpenRecordset("qry_PM_Proj_Num&quot
Set rstFindProj = dbMLV.OpenRecordset("qry_find_program_mgr&quot


If rstFindProj.RecordCount > 0 Then
Do While Not rstFindProj.EOF
'Identify the program manager for which to run the report
strFindPM = rstFindProj.Fields("PM&quot

'Run the report for the specific PM
DoCmd.OpenReport "PM_Proj_Num", acPreview, "", "[qry_PM_Proj_Num].[PM] = """ & strFindPM & """"

'Send the report to a file
'DoCmd.OutputTo acReport, "PM_Proj_Num", "RichTextFormat(*.rtf)"

'Advance to the next Program Manager for which to run the report
rstFindProj.MoveNext
Loop

'Tell the user the reports are finished
MsgBox "Project Manager by Project Number Reports Completed."

End If