Report based on form value
in [General]
|
Prev: Crosstab vs Flex Grid
Next: api
From: gsanderson99 on 20 Apr 2010 10:55 I have a report based on a table. The table contains an ID# which is unique to an individual, but the ID# may appear more than once in the table. I also have a form that displays one ID# and has a command button to print the report. I use the DoCmd.OpenReport command in the OnClick function of the command button, but am having problems with the criteria part. strCriteria = "[ID#] = " & Me.ID where ID# is the field in the table and ID is the field on the form, returns a dialog box asking for the ID#, though the current ID# is displayed in the dialog box. I'm probably missing something simple, can anyone help? Geoff
From: ghetto_banjo on 20 Apr 2010 11:42 are you certain that the report's record source is your table (or a query that selects [ID#])? when Access prompts for a value like that, it usually means that the field name is not found in the report's record source.
From: gsanderson99 on 20 Apr 2010 12:09 On Apr 20, 10:42 am, ghetto_banjo <adam.v...(a)gmail.com> wrote: > are you certain that the report's record source is your table (or a > query that selects [ID#])? when Access prompts for a value like > that, it usually means that the field name is not found in the > report's record source. I confirmed that the report's record source is a table and the ID# is the correct field name. However, I found the problem. The ID field in the form is a string, so I changed the strCriteria to: strCriteria = "[ID#] = " & " ' " & Me.ID & " ' " This worked, except when the ID# does not exist in the table. I added a NoData function to the report, but after displaying the "No Record Found" message box I created, I get an error message that leads me back to the DoCmd, which is: DoCmd.OpenReport, strReport, acViewPreview, , strCriteria The NoData function is: Private Sub Report_NoData(Cancel As Integer) Cancel = MsgBox("No Record Found", vbInformation, Me.Caption) End Sub Any ideas? Geoff
From: Daryl S on 20 Apr 2010 12:10 Geoff - Post your DoCmd.OpenReport statement so we can help you with it. -- Daryl S "gsanderson99(a)yahoo.com" wrote: > I have a report based on a table. The table contains an ID# which is > unique to an individual, but the ID# may appear more than once in the > table. I also have a form that displays one ID# and has a command > button to print the report. I use the DoCmd.OpenReport command in the > OnClick function of the command button, but am having problems with > the criteria part. > > strCriteria = "[ID#] = " & Me.ID > > where ID# is the field in the table and ID is the field on the form, > returns a dialog box asking for the ID#, though the current ID# is > displayed in the dialog box. > > I'm probably missing something simple, can anyone help? > > Geoff > . >
From: gsanderson99 on 20 Apr 2010 13:15
On Apr 20, 11:10 am, Daryl S <Dar...(a)discussions.microsoft.com> wrote: > Geoff - > > Post your DoCmd.OpenReport statement so we can help you with it. > > -- > Daryl S > > > > "gsanderso...(a)yahoo.com" wrote: > > I have a report based on a table. The table contains an ID# which is > > unique to an individual, but the ID# may appear more than once in the > > table. I also have a form that displays one ID# and has a command > > button to print the report. I use the DoCmd.OpenReport command in the > > OnClick function of the command button, but am having problems with > > the criteria part. > > > strCriteria = "[ID#] = " & Me.ID > > > where ID# is the field in the table and ID is the field on the form, > > returns a dialog box asking for the ID#, though the current ID# is > > displayed in the dialog box. > > > I'm probably missing something simple, can anyone help? > > > Geoff > > .- Hide quoted text - > > - Show quoted text - Dim strReport As String Dim strCriteria As String strReport = "rptMember_Complexity_Tool" strCriteria = "[ID#] = " & "'" & Me.txtID & "'" DoCmd.OpenReport strReport, acViewPreview, , strCriteria Geoff |