create and print one record viewing

[PattyS]

I have 2 tables and 2 corrisponding forms. I did the access magic and dropped form2 into form 1 to create from 1 with a subform (relationship one to many)
table 1 is EMPLOY INFO w/ BADGENO key
table 2 is SUMMONS w/BADGENO key
The subform (SUMMONS) might have several records associated with one Officer
What I want to print is infromation from EMPLOY INFO and one subform record together.
When officer has a new SUMMONS record entered, then I'd want to print the NEW report w/ the new SUMMONS record.
So, I did not like the Record Wizard results so I tried to create my own by makeing a Query with
both EMPLOY INFO and SUMMONS tables together called PED ENFORCEMENT.
I put a command button on the EMPLOY INFO form to print but it didnot print the records(EMPLOY INFO and SUMMONS) that I was viewing.
Hope you understand what I need, can I do it?

 

[stopher]

There's at least two ways to go about this.

One option is to have a criteria set in your query that takes its value from a field on your form. So if you have a field on your form called BadgeNo then the criterion might be:

[Forms]![Emloy Info]![BadgeNo]

When you run the query it will filter records according to the value in your form (the form has to be open for it to work)

Another way is to use the OpenReport function in VBA (the code behind the button). This assumes you have already created a report based on your query. The line would look something like this (assuming badge number is a number not a string):

DoCmd.OpenReport "myreport", , , "[BadgeNo]=" & Me.BadgeNo

The me.badgeno bit is another way of referring to the field on your form.

hth
Chris

 

[PattyS]

DoCmd.OpenReport "myreport", , , "[BadgeNo]=" & Me.BadgeNo

oK, BUT what if officer jones, badgeno 123, has 5 SUMMONS records? Will all 5 print because they all have badgeno 123 on them.

can't use something like CURRENT RECORD to print JUST the ONE BEING VIEWED?

 

[PattyS]

what if I put the print cmd button on the subform page instead of the EMPLOY INFO page
created a report that has everything on 1 page , name of report is PEDESTRIAN ENFORCEMENT

 

[PattyS]

finally created a workable report called PEDESTRIAN ENFORCEMENT
the report draws from the query I created to put the two tables together
query also called PEDESTRIAN ENFORCEMENT

Now to print JUST the record s I am viewing / record s meaning the OFFICER INFO and the SUMMONS record presently viewed in the subform.

understand that officer jones might have 5 SUMMONS records but there would only be one officer jones, I only want to print the SUMMONS(subform) record presently being viewed in officer jones EMPLOY INFO form, not all 5.

 

[stopher]

finally created a workable report called PEDESTRIAN ENFORCEMENT
the report draws from the query I created to put the two tables together
query also called PEDESTRIAN ENFORCEMENT

Now to print JUST the record s I am viewing / record s meaning the OFFICER INFO and the SUMMONS record presently viewed in the subform.

understand that officer jones might have 5 SUMMONS records but there would only be one officer jones, I only want to print the SUMMONS(subform) record presently being viewed in officer jones EMPLOY INFO form, not all 5.

Ok, so you have a query that joins Employee Infop and Summons info. The query lists all records i.e. all summons for all employees. And you have created a report based on this query.

But you want the report to only show the data for one summons. So use something like this:
DoCmd.OpenReport "myreport", , , "[SummonsID]=" & Forms![main form name]![subform control name].Form![control name]

Obviously you need to replace the names appropriately.

The last bit "control name" is the name of the field in your subform that uniquely identifies your summons record (summonsID maybe?)

hth
Chris

 

[PattyS]

I think I uploaded a copy of my database

 

[PattyS]

DoCmd.OpenReport "myreport", , , "[SummonsID]=" & Forms![main form name]![subform control name].Form![control name]

tried it....no go

I think this might be important......
the only field that make officer jones SUMMONS records unique is the DATE field. So Officer jones has 3 SUMMONS records and each date is different, 08/01/09, 08/10/09 and 08/30/09
I only want to print the 1 record that Iam viewing at the time

 

[stopher]

The following code does what you want. You need to add a button to your form and stick the code in the OnClick event for the button.

Dim strSQL As String
Dim strDocName As String
strDocName = "PEDESTRIAN ENFORCEMENT"
strSQL = "[BADGENO]=" & [Forms]![Employ Info]![SUMMONS].[Form]![BADGENO]
strSQL = strSQL & " AND [DATE]='" & [Forms]![Employ Info]![SUMMONS].[Form]![DATE] & "'"

DoCmd.OpenReport strDocName, acViewPreview, , strSQL

However there are some serious design issues here....

Essentially you have not normalised your database. All the infringements should be listed downward in your table not across. So typically a record would look like:

BadgeNumber___Date___InfringementCode___NoOfInfringements_____Hours etc
123____________1/2/09____39:4-5_______________6_______________4
123____________1/2/09____39:4-15______________3_______________2
etc

All tables should have a field that uniquely identifies the record. The SUMMONS table does have this and so the method of identifying the unique record is made difficult. I would add an autonumber field.

You have many "total" fields. Can these not be calculated from your other data? If so then they should not be stored but calculated as required (using a query).

I would avoid the use of the word DATE and DAY as field names as these are reserved words in Access and could cause you problems at some point.

Your date is in text format. Unless you have a real good reason to do this then date should be in date format. This will make dates much easier to deal with when querying/sorting.

hth
Chris

 

[PattyS]

well you were right, this does work perfectly
thank you
now to tidy up....if I do not want to preview the report before printing it
do I just change acViewPreview to acViewNormal?
and can I add to the print record code

DoCmd.Close .........but I don't want to close the form
DoCmd.SelectObject acForm, "EMPLOY INFO"
Forms![EMPLOY INFO].LASTNAME.SetFocus

this way after you tab to print, the cursor moves to the next blank EMPLOY INFO page instead of another blank SUMMONS page

 

[PattyS]

OR.......
I put the PRINT RECORD BUTTON at the bottom of the EMPLOY INFO
page (below the subform position)
and on the last field of the SUMMONS page (Location2) I add a code
OnEnter? that when the operator presses ENTER after filling in the field
the cursor will move back to the EMPLOY INFO form and the PRINT RECORD BUTTON
as it works now, after you enter thru the last field in the SUMMONS form the cursor moves to a new blank SUMMONS form.