[SimonB]

I have a question involving a VBA procedure on Access forms. I'm doing some pro-bono work for a charity, and have gotten in over my head. I am new to VBA in the last two weeks, so please respond to this in a pretty basic level. Thanks.

I have a form called frmDonations with a subform. Behind the master form is the tblDonations with data on things related to donations received (Payee, CheckNo, CheckDate, etc.). Behind the subform is tblLineItems with data on what the payment was for, possibly several things in some instances. tblLineItems is linked to tblDonations with a many-to-one relationship using PaymentID as the common field. It's very similar to the structure of an invoice: for each invoice there are multiple lineitems in the sale. I just break down the transaction at the back end, with the receipt of the donation payment.

I actually have two versions of this scenario, one for complex transactions (involving multiple lineitems with the lineitem data being manually entered), and one for simple transactions (the norm, with only one lineitem involving the same data each time). The complex scenario is easy and is working fine. Now I want to set up a separate simple scenario in which the subform table's data is populated automatically, preferably with no visible presence on the master form. Ideally there would be no subform at all in the simple scenario.

I have tried writing code to run in the master form to write to the subform controls, but VB doesn't seem to like that. Similarly I have tried to write code in the subform to read data from the master form, and some constants, but I can't get that code to trigger (what event would you base it on?) Complicating things seems to be that I have several fields in the master that are required, so the writing to the subform data can't occur until all the fields in the master have been entered.

At its heart, I think my problem lies in my unfamiliarity with how to write to the child table. I don't know how to trigger the generation of a new record in that table that will recognize the link to the master table. That happens automatically when you enter data manuall into the subform. How do I mirror that action with VB code?

I am using Access 2003.

Hopefully I have given you enough to go on here. Thanks in advance for your help.

[OBP]

Simon, I am sorry I missed this Thread, do you still need the help?

[SimonB]

OBP, Thank you. I was about to give up. Getting desperate for a solution here. Yes, I could use some help.
Simon

[OBP]

Simon, can you post a zipped "Blank" copy of the database, that would help a lot.
But basically if you want to write "Standard" data to a subform, you do not even need the subform, you can write it directly to the Subform's Table using a VBA Recordset.

[SimonB]

Tony,

My DB is split between a back end (with 37 tables) and front end (everything else). How do I strip the data out of the DB so I can post it? The front end is 26MB alone. Will that size be a problem for posting?

Re. a VBA recordset: I haven't gotten into that yet. My VBA is pretty basic (no pun intended), but I suspected that might be a path to the solution.

Thanks in advance for your help. Simon

[OBP]

Simon, it sounds as if you haven't Compacted and Repaired the Front end for a while.
You can just create a Blank Database and Import the relevant Backend tables/queries and the Front End Queries/Forms and zip it up and Post that.

[SimonB]

Tony, actually, I have been compacting all along. Maybe I'm doing something wrong. It IS very big. Just zipped the blank version, with front and back ends, and it's 4.4MB. The upload max is 500KB, so it failed. Can I e-mail it to you?

When and if you get it, here are sme thoughts to help you:

I'm open to any and all pointers and suggestions, wherever you want to look in it. Two specific questions I have that may lead me to solutions on the rest of my issues:

1) I have defined relationships in both front and back ends. Is that necessary? If not, where should they be defined? What if they are not done the same way in both places by mistake.

2) The forms I need help with first are frm(64c) and frm(64s). Starting with 64c: This form allows for multiple line items to be entered in the subform for each receipt in the master, but I want to be able to compare the total of the Amounts entered in the subform to the Receipt Gross Amount in the master and change the color of the green controls in the subform to red if not all the Gross Amount has been allocated to lines in the subform. Ideally I want to prevent saving this receipt unless the two figures are not the same. frm(64s) is similar, except it is designed for the 90% of situations where there is only ever one lineitem for each receipt, thus avoiding the need for manual entry into a subform. Ideally I would just get rid of the subform, and have VBA write the single record in the LineItem table with standard values where required.

I have many other forms with glitches in them that I suspect can be resolved when I understand how to fix these frm(64) issues.

Let me know if you have any questions.

Thanks again. Simon

[OBP]

Simon, I have Private mailed you my email address.
Is it essential to "split" the Database?
I am not sure about the Convention of Relationships when you split the database as I have never done so.
Did you use the Database "Splitter"?
or split them yourself?

I have checked my Access 200 Bible and it doesn't mention where the relationships should reside, only it is easy to use the Splitter because it does it all for you.

[SimonB]

I am working off-line with OBP on the solution to this and some other issues I have been struggling with. OBP has been incredibly helpful.