Report based on current record in a form

  • Thread starter Thread starter Robin Hickman
  • Start date Start date
R

Robin Hickman

This has got to be a common and simple thing to do, but I can't seem to
find it in any of my books.

I am working on an Access 97 database that I use to track my clients.
There is a main table ("Clients") that stores all basic information such
as name, address, etc. Linked to that table in a one to many
relationship is a table ("Actions") that stores each step in the process
with my clients as it is scheduled and completed, with dates and notes,
etc. Each client may have any number of these steps attached to their
record.

I have a main form that shows each customers information and which has a
subform that shows each step associated with that client. What I want
is a button on the main form that I can click to preview a report that
contains the current client's name and address, etc. followed by each
record associated with that client in the Actions table. I have
designed a report that will do that for all the clients in the database,
but I don't know how to make it only show the one I am looking at when I
click the button.

Thanks in advance, this newsgroup has been of tremendous help in the past.

Robin
 
Hello Robin,

You will need to add a Where Condition to the OpenReport method so that the
report shows only the information for the customer on your form. Something
like the following:

Dim strCriteria As String

' This works when your matching field is text
strCriteria = "[CustID] = " & Chr(34) & Me!CustID & Chr(34)
DoCmd.OpenReport "MyReport", acViewNormal, , strCriteria

' This works when your matching field is a number
strCriteria = "[CustID] = " & Me!CustID
DoCmd.OpenReport "MyReport", acViewNormal, , strCriteria

hth,
 
Thanks! Worked great! I knew it was something simple, I just wasn't
finding it.

Robin

Cheryl said:
Hello Robin,

You will need to add a Where Condition to the OpenReport method so that the
report shows only the information for the customer on your form. Something
like the following:

Dim strCriteria As String

' This works when your matching field is text
strCriteria = "[CustID] = " & Chr(34) & Me!CustID & Chr(34)
DoCmd.OpenReport "MyReport", acViewNormal, , strCriteria

' This works when your matching field is a number
strCriteria = "[CustID] = " & Me!CustID
DoCmd.OpenReport "MyReport", acViewNormal, , strCriteria

hth,

--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX


This has got to be a common and simple thing to do, but I can't seem to
find it in any of my books.

I am working on an Access 97 database that I use to track my clients.
There is a main table ("Clients") that stores all basic information such
as name, address, etc. Linked to that table in a one to many
relationship is a table ("Actions") that stores each step in the process
with my clients as it is scheduled and completed, with dates and notes,
etc. Each client may have any number of these steps attached to their
record.

I have a main form that shows each customers information and which has a
subform that shows each step associated with that client. What I want
is a button on the main form that I can click to preview a report that
contains the current client's name and address, etc. followed by each
record associated with that client in the Actions table. I have
designed a report that will do that for all the clients in the database,
but I don't know how to make it only show the one I am looking at when I
click the button.

Thanks in advance, this newsgroup has been of tremendous help in the past.

Robin
Click to expand...
Click to expand...
 
Back
Top