Replies

It occurred to me as I was posting the other database, that you may have a report more like this example here, where you want all of the data from a particular table and you haven’t got any grouping levels at all.

In that case, I believe the only way to deal with the problem is through code and conditional formatting of the labels for the second column. Essentially the label would be visible if there was data in the cloumn and not visible if there wasn’t. Unfortunately, that is beyond my ability to help. Perhaps someone else can.

Peter

Here is a little example. To see the snaking without adding in extra data, go into page setup and make the bottom page margin 5 or 6 inches. This is a quick, really stripped down version of a report I am currently using, so table and query design is more complicated than it might otherwise be as I didn’t want to start from scratch.

I would suggest you look first in sorting and grouping and have it set the way I have it in the envelope header. Also notice the macro in the page footer to reset the page number if you want that.

If you still can’t get it to work, post yours and I’ll look at it or one of those nice people who really know what they are doing will.

Peter

Create a new header for your detail information (Under View Menu, sorting and grouping, whatever your detail section is set Group header to Yes). Put your column headings in that header instead of the page header. In the new header, set the Force New Page property to “before section”.

You will only need column headings for the first row and they should take up no more space than the width of one column. They will repeat only if the column snakes.

In the page setup dialogue, have 2 columns and set your column layout to down, then across. I also always set the printer for this type of report to “Use specific printer” then any columns or particularly small margin settings aren’t lost.

I think I got all of the pertinent information. If it doesn’t work, let me know and I can upload a copy of a report I did recently that does just this and you can check the properties. The important bits are to get that stuff out of the page header, have the before section property set and to have the columns set properly.

Peter N

Worked like a charm! Thank you more than you can imagine.

I was trying to insert my SQL inside the loop, but now that I’ve seen it work I understand why it can go where it goes.

Peter

OK. Now I’m stumped. The code I posted above works fine and I can get the following append query (from a plain ordinary query not code) to work fine after I run the code to grab the Donation details and dump them in:

INSERT INTO tblDonationDetails ( DonationID, EnvelopeNumber, Amount, Comment )
SELECT tblDonations.DonationID, PARDetails.EnvelopeNumber, PARDetails.Amount, PARDetails.Comment
FROM tblDonations INNER JOIN PARDetails ON tblDonations.PARID = PARDetails.DonationID
WHERE (((tblDonations.ContributionDate)=#5/15/2003#));

Here are the two things I can’t figure out.

1) I have no idea how to insert the SQL into my code. In addition, I have no idea where to insert it in my code either. Do I need to instantiate yet a third recordset? I played around with it for a couple of hours this evening but got nowhere. I have never worked with a querydef object and Access being Access I can’t find the exact syntax I need to set that bit up. So some help about exact syntax and where to insert it in the procedure a few posts back in this thread would be greatly appreciated.

2) I need to replace the actual date in the WHERE clause with the DepositDate as it is entered in my unbound text box on my entry form. In other words, I need to grab the info as a parameter the way you do with any other query by form type data. I have no idea how to revise that syntax either.

BTW, I took Pat’s advice about the PARID being added as a field in tblDonations and that works beautifully. It’s this (&^%^&
SQL/DAO/VBA syntax that has me tied in knots!

Peter

That seems to make sense. I will try it tomorrow. Do I need to add a Where clause to my SQL to limit the Select to the current batch? Otherwise wouldn’t I be picking up every set of batches I ever posted? So, the WHERE clause would be something along the lines of “WHERE tblDonations.DepositDate = strDepositDate” or something with correct syntax (I haven’t actually thought that far.

Peter

Here is a copy so you can look at it. PAR and PARDetails are for storage of information which gets posted monthly to tblDonations and tblDonationDetails. The form – Form1(clever name, huh?) is for the convenience of the poster who inserts the date she wants and a description and hits OK. The data is then copied from PAR tables into Donation tables to be recorded. My next step is to get the detail information. Right now the only part that works is copying the data from the main table and setting up the new AutoID (DonationID)

Hopefully this explains it.

Peter

I’m halfway there. This is the code I came up with today. It seems to work quite nicely on the first part of the task which is creating the Donations table entries. I’m not sure where to go from here in terms of where to put the the SQL Insert Into statement to add in the DonationDetail information from the PARDetails table and what the syntax would be. I have never worked with QueryDefs before. If there I things I should fix up or change in the code here, I would appreciate your comments.
*******************************
Private Sub OK_Click()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim strAcct As String
Dim strDepDate As Variant
Dim strDepDesc As Variant

Set db = CurrentDb()
Set strDepDate = Forms!Form1!DepositDate
Set strDepDesc = Forms!Form1!DepositDescription

Set rst2 = db.OpenRecordset(“PAR”, dbOpenDynaset)
Do While Not rst2.EOF
With rst2
strAcct = rst2.Fields(“Account”).Value

Set rst = db.OpenRecordset(“tblDonations”, dbOpenDynaset)
With rst
.AddNew
![Account] = strAcct
![ContributionDate] = strDepDate
![DepositDescription] = strDepDesc
.Update
End With
strAcct = “”
.MoveNext
End With
Loop
End Sub
************************

Everything was nicely indented before. I don’t know how to preserve formatting in a post like this. There is a bit of naming housekeeping to do as well. Form1 will get a more useful name. And I started off with strDepDesc and strDepDate as a string and a date respectively and access only seemed to like them declared as variants. I suppose something to do with them coming from unbound fields on an unbound form?

Peter

The table structure for Donations and DonationDetails is as listed in the first post in this thread. Pat was asking about the PAR table structure (which is for storage of fixed information only) which I am not sure about. Should the PAR table structures mirror exactly the Donations and DonationDetails including DonationID as the linking field and primary key?

As to the whole issue of Accounts, the church in question stores its records of donations in a somewhat unusual way in that when a donation is divided into two parts, they physically store two separate envelopes as primary evidence of the donation. So if you put one envelope on the plate with $50 in it and half goes to the current account and half goes to the building fund, they will create a second envelope and change the amount on each to $25. (Don’t ask, that’s just the way they do it!)

Consequently, when posting of the collection plate offering is done into the Donations form, the main form has the Donation ID, Date, Deposit Description (which is used only as a sort of comment field as needed) and the Account. The details then record all of the envelope numbers and the amounts that were posted to that account on that day. The join field is DonationID (autonumber on main, number on sub).

The PAR information, therefore needs to be set up in a similar manner in order to fit the existing table structure. I am quite happy to set up the PAR tables in whatever manner is necessary. In my original design I was leaving out the Date and Deposit Description as that information needs to change with each posting and it seems to make more sense to grab that info from the form that will run the procedure.

Peter

Charlotte, this is exactly what I need. I will use DAO as I find it slightly less inscrutable than ADO. I think I have most of the pieces of the puzzle to give this a try. Three further questions for now.
1) Do I also need to Dim db as DAO.Database and Set db = CurrentDb()
2) What would the syntax look like for referring to rst!DonationID
3) Given that there will be more than two (I can’t tell you off hand as I have to get the real data) records in the parent table (one for each account to be posted to) your basic recipe doesn’t tell me how to move on to the second record. Wouldn’t I need to do the whole thing over again for each record in PAR? In which case, if not a Loop, then what?

Many thanks for your help so far.

Peter

>>What field relates PAR and PARDetails?

That needs to be determined by how I can make the code work. I started off with a dummy DonationID field that would only function to relate the two fields. I also played around with adding Account into the PARDetails as for a reference table, that also wold work.

Peter

In PAR the one constant is Account and in PARDetails are EnvelopeNumber, Amount, Comment.

I have been waffling with PAR whether to use update to take the info from the unbound form and insert it into DepositDate and DepositDescription fields and then append the records or whether it is possible to append Account only and grab the other stuff from the unbound form directly. The PARDetails table will remain as listed above.

The only other question is the whole issue of depositID as that is the Parent/Child field between table and subtable.

Quite frankly, I don’t really care how I accomplish it. I’m just interested in doing it simply and efficiently !!
The more research I do, the more I realize there are about 6 different ways to do it (if not 60!) and the problem with VBA is that you need to be consistent whichever way you start.

Peter

My VBA skills are limited (mainly tweaking others’ code to do what I need). Could you talk me through the basics? Is this something that can be done in one procedure with something like a loop or does this need to be done in two steps?

(As to Pat’s question: The static information will be stored in the PAR pair of tables and copied from there into the Donations pair of tables)

Peter

For the part of the database you refer to I actually have a table and a sub-table With category the main table and specialties the detail table rather like the typical Orders/Order Details a la Northwinds.

I wasn’t completely clear on my dilemma, though. This has to do with unique info at theClient level that will only be recorded for the directory. For example, Organizations give Founding dates, contact names, charitable status, etc and Individuals can record degrees and diplomas and Both types have the opportunity for Additional Information that will be recorded in a memo field – up to 40 words for free and up to an additional 100 words for a fee. All of the above are only recorded in the directory and are not part of the regular record keeping of the Arts Council.

With that additional information, then, should I just dump this stuff into the main clients table or is there a reasonable case for a sub table that has a one-to-one relationship.

In addition, if I use the main table, what is the best way to filter these directory listings? Just use a check box called “directory”? Certainly both safe and explicit as opposed to doing a query on one of the other fields that uses “not null” as a parameter.

If you think I should go the one-to-one route, how do I get it to hook in easily to the main table, which I have always found a bit of a hassle when adding this type of table to existing records.

Peter

Problem Solved!

It seems at one point I had set the main form’s cycle property to Current Record. On fooling around with this code in Northwind and changing the cycle property I discovered that the OnCurrent event stopped working there as well. By restoring cycle to All Records, the problem went away.

So, the question then becomes: If you want the cycle property to remain Current Record, is there something you can do to force the focus to the first field in the subform as you cycle around and around the current record? At this point it is academic, but I would be interested to know if there is an answer.

Peter