Link command button to select customer????

  • Thread starter Thread starter jwr
  • Start date Start date
J

jwr

The subject of my previous post may not have indicated my concern.

If I have a form open that can contain many customers, how do I link my
command button; i.e. - print invoice, to be for that customer that is
visible on the screen? I would want to print for that customer only,
without having to input customerID, for example.

Thanks
 
jwr said:
The subject of my previous post may not have indicated my concern.

If I have a form open that can contain many customers, how do I link
my command button; i.e. - print invoice, to be for that customer that
is visible on the screen? I would want to print for that customer
only, without having to input customerID, for example.

Thanks
Click to expand...

Your button is likely using the following code statement to print the invoice...

DoCmd.OpenReport "InvoiceReportName"...

That method "OpenReport" includes an optional argument that supplies a WHERE
clause to the report and thus allows you to filter the report that is opened or
printed. In your case you need to include a WHERE argument that filters the
report to the current CustomerID. Something like...

DoCmd.OpenReport "InvoiceReportName",,,"CustomerID = " & Me!CustomerID
 
Ron -

Below is the information from my form. Please look at this and give me some
more guidance. It looks like it should work for me.

I have a form "Orders by Customer". I have command buttons at the bottom
of
the form:

Orders
Payments
Preview Invoice
Print Packing List
Bill of Lading
Authorization to Deliver
Close Form

Below is the event procedure. My preview invoice, payments and packing list
pull the information for the customerID and controlNumber that is visible on
the screen. The others - Bill of Lading and Authorization to Deliver - to
not. What have I done wrong??? Thanks in advance.


Private Sub PreviewInvoice_Click()
On Error GoTo Err_PreviewInvoice_Click
If Forms![Orders by Customer]![Orders by Customer
Subform].Form.RecordsetClone.RecordCount = 0 Then
MsgBox "Enter order information before previewing invoice."
Else
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
DoCmd.OpenReport "Invoice", acPreview, , "[OrderID] = Forms![Orders
by Customer]![Orders by Customer Subform].form![OrderID]"
End If

Exit_PreviewInvoice_Click:
Exit Sub

Err_PreviewInvoice_Click:
If Err <> 2501 Then
MsgBox Err.Description
End If
Resume Exit_PreviewInvoice_Click
End Sub






Private Sub Close_Form_Click()
On Error GoTo Err_Close_Form_Click


DoCmd.Close

Exit_Close_Form_Click:
Exit Sub

Err_Close_Form_Click:
MsgBox Err.Description
Resume Exit_Close_Form_Click

End Sub
Private Sub Command41Pkg_List_Rpt_Click()
On Error GoTo Err_Command41Pkg_List_Rpt_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Packing List"
Me.Dirty = False

stLinkCriteria = "[CustomerID]=" & Me![CustomerID]
DoCmd.OpenReport stDocName, , , stLinkCriteria

Exit_Command41Pkg_List_Rpt_Click:
Exit Sub

Err_Command41Pkg_List_Rpt_Click:
MsgBox Err.Description
Resume Exit_Command41Pkg_List_Rpt_Click

End Sub
Private Sub Command44Auth_to_Deliver_Click()
On Error GoTo Err_Command44Auth_to_Deliver_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Authorization to Deliver Snapshot Form"
Me.Dirty = False
stLinkCriteria = "[CustomerID]=" & Me![CustomerID]

DoCmd.OpenReport stDocName, , , stLinkCriteria

Exit_Command44Auth_to_Deliver_Click:
Exit Sub

Err_Command44Auth_to_Deliver_Click:
MsgBox Err.Description
Resume Exit_Command44Auth_to_Deliver_Click

End Sub
Private Sub Command45_Click()
On Error GoTo Err_Command45_Click

Dim stDocName As String

stDocName = "Bill of lading"
DoCmd.OpenReport stDocName, acNormal

Exit_Command45_Click:
Exit Sub

Err_Command45_Click:
MsgBox Err.Description
Resume Exit_Command45_Click

End Sub
 
jwr said:
Ron -

Below is the information from my form. Please look at this and give
me some more guidance. It looks like it should work for me.

I have a form "Orders by Customer". I have command buttons at the
bottom of
the form:

Orders
Payments
Preview Invoice
Print Packing List
Bill of Lading
Authorization to Deliver
Close Form

Below is the event procedure. My preview invoice, payments and
packing list pull the information for the customerID and
controlNumber that is visible on the screen. The others - Bill of
Lading and Authorization to Deliver - to not. What have I done
wrong??? Thanks in advance.
Click to expand...

Your code looked okay to me. What's the problem?

You get an error?

You get all records?

You get the wrong record?

You get no records?
 
I am asked to enter a control number (number we assign to customer) and/or
CustomerID (auto number assigned to each customer). If I do not put
something in the question box, of course, I get nothing.

If I insert a CustomerID (Just tried to print Authorization to Deliver from
command button), The form prints, BUT with one for each customer.

Thanks,JR
 
jwr said:
I am asked to enter a control number (number we assign to customer)
and/or CustomerID (auto number assigned to each customer). If I do
not put something in the question box, of course, I get nothing.

If I insert a CustomerID (Just tried to print Authorization to
Deliver from command button), The form prints, BUT with one for each
customer.
Click to expand...

That suggests that the CustomerID field does not exist in the RecordSource of
your report. Access then assumes it must be a parameter so it prompts you for
it. What you enter is irrelevent since it is not a field in the report.
 
THANK YOU!!!

How can something so obvious be so hard to find??

One more "easy" questions. How do I change a command button from printing
to preview - without deleting command button and redoing?
 
I said that I got it to work. The Authorization to Deliver DOES not. The
Print Bill fo Lading command button is not working.


I have made certain that CustomerID is in my query and form/report that the
command button is addressing.
I am getting report printed for all customers.

Following is code:

Private Sub Command44Auth_to_Deliver_Click()
On Error GoTo Err_Command44Auth_to_Deliver_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Authorization to Deliver Snapshot Form"
Me.Dirty = False
stLinkCriteria = "[CustomerID]=" & Me![CustomerID]

DoCmd.OpenReport stDocName, , , stLinkCriteria

Exit_Command44Auth_to_Deliver_Click:
Exit Sub

Err_Command44Auth_to_Deliver_Click:
MsgBox Err.Description
Resume Exit_Command44Auth_to_Deliver_Click

End Sub
Private Sub Command45Bill_of_Lading_Click()
On Error GoTo Err_Command45Bill_of_Lading_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Bill of lading"
Me.Dirty = False
stLinkCriteria = "[CustomerID]=" & Me![CustomerID]


DoCmd.OpenReport stDocName, acNormal

Exit_Command45Bill_of_Lading_Click:
Exit Sub

Err_Command45Bill_of_Lading_Click:
MsgBox Err.Description
Resume Exit_Command45Bill_of_Lading_Click

End Sub
 
jwr said:
THANK YOU!!!

How can something so obvious be so hard to find??

One more "easy" questions. How do I change a command button from
printing to preview - without deleting command button and redoing?
Click to expand...

Just use acViewPreview as the second argument (right after the report name).
The default is acViewNormal which goes straight to the printer.
 
jwr said:
I said that I got it to work. The Authorization to Deliver DOES not.
The Print Bill fo Lading command button is not working.
Click to expand...

Your code for the Bill of Lading report is not applying any criteria. You
create a variable for the where clause, but you don't use it in the OpenReport
method.
Private Sub Command45Bill_of_Lading_Click()
On Error GoTo Err_Command45Bill_of_Lading_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Bill of lading"
Me.Dirty = False
stLinkCriteria = "[CustomerID]=" & Me![CustomerID]

DoCmd.OpenReport stDocName, acNormal

Exit_Command45Bill_of_Lading_Click:
Exit Sub

Err_Command45Bill_of_Lading_Click:
MsgBox Err.Description
Resume Exit_Command45Bill_of_Lading_Click

End Sub
Click to expand...
 
Thank you.
Rick Brandt said:
jwr said:
I said that I got it to work. The Authorization to Deliver DOES not.
The Print Bill fo Lading command button is not working.
Click to expand...

Your code for the Bill of Lading report is not applying any criteria. You
create a variable for the where clause, but you don't use it in the
OpenReport method.
Private Sub Command45Bill_of_Lading_Click()
On Error GoTo Err_Command45Bill_of_Lading_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Bill of lading"
Me.Dirty = False
stLinkCriteria = "[CustomerID]=" & Me![CustomerID]

DoCmd.OpenReport stDocName, acNormal

Exit_Command45Bill_of_Lading_Click:
Exit Sub

Err_Command45Bill_of_Lading_Click:
MsgBox Err.Description
Resume Exit_Command45Bill_of_Lading_Click

End Sub
Click to expand...
Click to expand...
 
Back
Top