Single Record Report, How To... 2

[wreded]

i have a table of records that get changed periodically. As each record is changed i need a print out of that individual record. It's easy enough to get a report of all records, i just need the one record that's been changed. i've thought of copying the individual record to a temp file, but don't know how to do that in Access. i have the report format i just need to know how to get the one record i need.
Thanks,
Dave

 

[KenReay]

Hi

How are you going to identify the record(s) which have cahnged, may I suggest you include a date/ time column in your table (say datLastChanged), include the column on the form you use to update the table (make it visible = false if you wish), in the bforeupdate event of the form used to update the table, put

datLastChanged = Now() ' if you want exact date/time changed

or

datLastChanged = Date() ' if date changed is enough

Now all you need is a query with criteria of from date / to date and a report based on that query to be able to print all records changed in a given time span Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Bry12345]

You might want to put a yes/no field which you could use like an archive bit. In the 'After Update' property of the form which modifies the record, you can set the yes/no to 'no', indicating that the updated record hasn't been printed. (Obviously, you need to set this field to 'yes' as a default.)

Your printing routine would then print all records set to 'no' and after a sucessful print, set all the records just printed to 'yes'. - - - -

Bryan

 

[wreded]

Thanks! i'll try both approaches. The way i'm used to dealing with this situation is via dBase and record numbers. You know, store a record's record number to a variable and then print for just that variable. Ideally i have a form with a button, when the record is updated, press the button and the record prints. That way You don't forget to print the thing before moving on to the next one.
Thanks,
Dave

 

[Bry12345]

You may just want to hide the button that prints that record and then make it visible in the 'After Update' event. Printing the current record from a form is simple. Post back if you need the code for that. - - - -

Bryan

 

[wreded]

i would certainly enjoy the code! i'm pretty much a jack of all trades here (and master of darned few) and always enjoy learning new things.
Thanks!
Dave

 

[Bry12345]

1 - Start by writing a report containing whatever it is you want to print.

2 - Place a Command Button on your form that will print the report after the record is updated.

3 - This goes in the OnClick event of that Command Button that will print a report containing the current record:

- - -
Private Sub cmdPrintRecord_Click()

Dim strReportName As String
Dim strCriteria As String

strReportName = "rptYourReport"
strCriteria = "[FieldIdentifyingTheRecord]='" & Me![FieldIdentifyingTheRecord] & "'"
DoCmd.OpenReport strReportName, acViewPreview, , strCriteria

End Sub
- - -

In this case, 'FieldIdentifyingTheRecord' is used to tell Access what record to print. It should probably be the Primary Key, to insure you're printing the proper record.

This assumes that whatever field you are using for the link criteria will define as a string. If you are using an autonumber field, for example (which is 'Long') the syntax is slightly different.

Once you get this working, you may want to do something to trap the AfterUpdate event of your form, so that every time you update a record, you are presented a message box that asks if you want to print the report.

Hope this helps.
- - - -

Bryan

 

[Malkyx]

Bryan,

Thanks for that code... I'm pretty much a newbie when it comes to Access, and I'm setting up a database with a single form/table.

I'm using an autonumber field as the primary key, and you mentioned that the syntax of your code would be slightly different...

Could you show the correct code? Thanks


Andy.

 

[jonquil]

I am trying to do the same thing, except I have 2 subforms on my form (from 2 other tables)...

I had figured out what Bryan said to do, but the problem is, I'm getting a report with one parent record and all the child records for that parent. I just want to report the one child that is active in the form.

Is there a way to expand the stCriteria to include these additional drill down records on my report?

 

[wreded]

Bryan,
Thanks alot! It worked splendidly for me. All errors reported are mine (i changed field names and it didn't take everywhere).
i'll be coming back for more!
Thanks again!
Dave

 

[Bry12345]

You're welcome, guys.

Malkyx, give me until tomorrow for the criteria for the autonumber field . . it's in my code bank at work!

Jon . .gotta think about your question.

B. - - - -

Bryan

 

[Bry12345]

Mal . .this code assumes that your key field is an autonumber called 'ID' :

varCriteria = "[ID]=" & Me![ID] & "" - - - -

Bryan

 

[Bry12345]

Jonquil . . you're going to have to build a query that selects what you want from the parent and ONLY the record you want from each child. You'll probably have to use a function like MAX or TOP to get the child record you want, if the child record you want can be selected in that fashion. - - - -

Bryan

 

[Malkyx]

Thanks Bryan!

Worked beautifully!