Transfer data from form to report

  • Thread starter Thread starter Dennis
  • Start date Start date
D

Dennis

I am trying to transfer data from a form, press a button
and open a report that has a select distinct query and
paste the data in the "CDC_NBR" field/criteria window
which requests the user to enter that ID number. The
following is the code I have but I get an error message
that says

"Syntax error (missing operator) in query
expression '(Patients Current RX Query.CDC_NBR='B-12345)'.

The B-12345 is the value I wish to paste into the reports
Select Distinct query [CDC_NBR] field.

Private Sub View_current_Rx_Button_Click()
On Error GoTo Err_View_current_Rx_Button_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Patient's Current RX Report"
stLinkCriteria = "Patient's Current Rx Query.CDC_NBR="
& "'" & Me.CDC_NBR
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria

Exit_View_current_Rx_Button_Click:
Exit Sub

Err_View_current_Rx_Button_Click:
MsgBox Err.Description
Resume Exit_View_current_Rx_Button_Click


End Sub

What might be the problem?

Thanks,

Dennis
 
Hi Dennis,

If I understood correctly, I believe you want

stLinkCriteria = "[CDC_NBR]='" & Me.CDC_NBR & "'"

This will open the report filtering only for
records where [CDC_NBR] ='B-12345'
if that is the value of CDC_NBR on the
current form.

I might change the name of the textbox that
is bound to CDC_NBR on the form to something like

txtCDC_NBR

then use

stLinkCriteria = "[CDC_NBR]='" & Me!txtCDC_NBR & "'"

But then, I could be misunderstanding completely
what you are doing.

Good luck,

Gary Walter
 
Gary,

It works perfectly. Thank you for you knowledge and your
willingness to share it with others!

Dennis

-----Original Message-----
Hi Dennis,

If I understood correctly, I believe you want

stLinkCriteria = "[CDC_NBR]='" & Me.CDC_NBR & "'"

This will open the report filtering only for
records where [CDC_NBR] ='B-12345'
if that is the value of CDC_NBR on the
current form.

I might change the name of the textbox that
is bound to CDC_NBR on the form to something like

txtCDC_NBR

then use

stLinkCriteria = "[CDC_NBR]='" & Me!txtCDC_NBR & "'"

But then, I could be misunderstanding completely
what you are doing.

Good luck,

Gary Walter
Dennis said:
I am trying to transfer data from a form, press a button
and open a report that has a select distinct query and
paste the data in the "CDC_NBR" field/criteria window
which requests the user to enter that ID number. The
following is the code I have but I get an error message
that says

"Syntax error (missing operator) in query
expression '(Patients Current RX Query.CDC_NBR='B- 12345)'.

The B-12345 is the value I wish to paste into the reports
Select Distinct query [CDC_NBR] field.

Private Sub View_current_Rx_Button_Click()
On Error GoTo Err_View_current_Rx_Button_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Patient's Current RX Report"
stLinkCriteria = "Patient's Current Rx Query.CDC_NBR="
& "'" & Me.CDC_NBR
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria

Exit_View_current_Rx_Button_Click:
Exit Sub

Err_View_current_Rx_Button_Click:
MsgBox Err.Description
Resume Exit_View_current_Rx_Button_Click


End Sub

What might be the problem?

Thanks,

Dennis
Click to expand...


.
Click to expand...
 
Back
Top