Form to Output Table

[dwright]

Yes I can. It'll take some time because as I said - a few hats to wear today but I will get it up before the end of the day.

[dwright]

Ok it took me less time than I thought. I gave it a priority - Other jobs can wait. I have been stressing over this for too long. The basic tables are there I just changed the data. There's some test receipts in there. Everything else is the same. Thank You!

[jzwp11]

The code is actually working fine if the form is not in data entry mode (form opens to enter a new record). You just need to set the Data Entry property of the form from yes to no. I've made the adjustment in the attached DB

[dwright]

What a wonderful start to my day.

So is there a way to not make it open with a receipt open - have them select before it shows anything?

I have someone here who is going to help me create 2 front ends - the one with the void form is going to only be available to one person but they will also be able to enter & report & modify supporting tables (budget lines & employees), the others will be able to enter & report. Do I have to do anything special to that form in advance to prepare the security? We want to make it based on their window log on & they should be able to enter into the database without locking each other out.

Believe it or not I have taken Access level 1 & 2 (twice - actually 1 - 2 day seminars) but I am not having Access level 3 until March. Maybe I can actually answer some of these questions on my own then. We'll see.

[jzwp11]

I would not recommend having two different front ends, but rather a way for your users to login and tied to their login you would have permissions that allow some users to do one thing while others are allowed to do others. Having to maintain two separate front ends is just extra work.

So is there a way to not make it open with a receipt open - have them select before it shows anything?

Yes, there are a number of ways. One fairly easy way is to use an unbound form that has the combo box similar to what I have already walked you through, but in the after update event of the combo, use the selection to filter your the void form as it opens.

[dwright]

Ok, I will finish up this form, do my reports and do 1 front end. Hopefully we can get the security to work after that.

[jzwp11]

OK. Please let me know if you have any additional questions regarding the form we have been discussing.

[dwright]

You'll probably be sorry you said that.

When you say create an unbound form - does that mean I'll have to create a whole new form? Can I change this one & how do I make it unbound?

To have the filter as it's open is that going to be different code in the visual basic from what I have?

[jzwp11]

When you say create an unbound form - does that mean I'll have to create a whole new form?

Yes, but a simple one with a combo box and a command button. Unbound means that the form is not tied to a table or query. You would use the command button to filter and open your existing void form to the receipt selected in the combo box. Since your receipt and void forms are essentially the same, we can probably get rid of one and use the unbound form (and its associated code) to manipulate the receipt form when it opens or is filtered.

I've attached your earlier database with an example unbound form that does the manipulation as I explain above. I used the command button wizard for each with some slight code additions.

[dwright]

I like the concept of the main form going to enter or void. I'm not sure that I want to use the same form because I don't want the other employees with access to void. But it's about the permissions right? If on the main form they don't have permission to go to the void form then on the receipt form you can make it so they can't check void? We definitely don't need edit. Because legally it's either a receipt - or it's voided. That's the way they want it. Is it easier to give permissions to a form, or to a field in a form?

[dwright]

I just received another issue that I wasn't aware of. It looks like they would like to have the possibility to enter multiple budget lines on one receipt. How would this work? If the receipt number is an auto number, indexed, no duplicates how can they have multiple budget lines per receipt?

[jzwp11]

Is it easier to give permissions to a form, or to a field in a form?

You can show or hide controls on a form based on the user's permissions. What I have done in the past is to actually hide the buttons on the menu form (like the one I provided) so people who don't have permission never even see the buttons! If they don't see the buttons, they can never get to the form.

It looks like they would like to have the possibility to enter multiple budget lines on one receipt.

This describes a one(receipt)-to-many(budget line) relationship which requires a separate but related table

tblReceiptBudgetLines
-pkReceiptBudgetLineID primary key, autonumber
-fkReceiptID foreign key to the primary key of your receipt table
-budgetline (if you have a table that the user can select from, then this would be a foreign key to that table)


Before you go further, I would recommend that you sit down with those requesting this database of you and have them lay out all of their requirements now so that you can work on the table structure for the application. Once the table structure is set up, then and only then would I worry about forms. The table structure is critical to a successful application.

[dwright]

This has gotten a lot more complicated than I originally thought. This is the vision from beginning to end (I did go talk to them). Security - 1 admin who has rights to void form & supporting tables. The security based on their windows logon. A receipt entry form, auto number, select multiple budget lines and it looks like if they have multiple budget lines they'll have to have multiple payment information for them. She did throw in there - 'anyway we can put the county seal on the receipt'. Reports - which I think I may be alright. Other than the 2 forms already mentioned we would need forms to update the supporting tables which now includes - 2 budget tables. We would also have to figure out a way for them to archive. They would like to be able to have 2 years worth of data search able & being county government they would probably have to be able to go back to see other data if necessary.

[jzwp11]

It sounds like you have your work cut out for you, but it doesn't sound too complicated. I have read about using a user's Windows ID in an Access application but have never used it myself.

They would like to be able to have 2 years worth of data search able & being county government they would probably have to be able to go back to see other data if necessary.

This is not a problem. You would keep all of the data in the database and just filter it to the two most recent years (relative to the current date). You would just add some criteria to restrict the date range. You'll have to get more info on what types of searches and what fields need to be searched, but all of that should wait until your table structure is completed.

She did throw in there - 'anyway we can put the county seal on the receipt'.

I have seen others use logos in their databases, so if you can get the seal in a jpg file you should be able to insert the image into a form/report.

A receipt entry form, auto number, select multiple budget lines and it looks like if they have multiple budget lines they'll have to have multiple payment information for them.

Based on the earlier database you posted, your current structure will not support the above, so you will have to redesign your tables which will impact any of your current forms, reports and queries.

The above implies that there are multiple budget lines for a receipt.
It also sounds like there are multiple payments for a receipt. Can those payments be applied to specific budget lines or are they applied to the total receipt?

Is the receipt applicable to just 1 payee?
If multiple payments are received, I assumed that any employee can receive the payment, correct?

[dwright]

I do have my work cut out for me & I am not exactly sure where to start.

As far as the security goes the person who is set to help me made it sound like I could create the information & then go from there so I am hoping that is true.

I already intended to have date parameters built in for the reporting. I was playing with the transaction date Between [Beginning Date] And [Ending Date].

As far as the picture, I found one & was playing with putting that in the receipt form shouldn't be an issue.

Back to my receipt & table structure. The person taking the receipt needs to be able to apply the payment to multiple budget lines (if necessary). My confusion lies in how they can select multiple budget lines (how to set that up) and then how it relates back to my table that has the receipt information. Receipt #1 could realistically have - 1 auto receipt number, 1 payor, 1 employee taking payment, 2 budget lines, 2 descriptions, 1 comment, 2 payment types, 1 payment detail, 2 payments which equal one total amt. The total I am guessing can be for the receipt only because the amounts would relate back to the table/budgetline. (somehow). As I have said before - I am on take 3 or 4 of this database so if you have some great ideas on how I can set this up to support such a form I am more than willing to try anything. I just appreciate all the help & feed back.

I think I answered your questions.

I should be an expert by the time I'm done right?