Newbie needs help filling out forms automatically ASAP

[bfdeal31]

Hi,
I'm designing a billing/invoice database at my internship and am at a standstill and quite frustrated. I've looked in "Access Bibles" and online help sites but am still having trouble.

I'm trying to create a form that will eventually allow me to add new entries with minimal effort to prevent data entry errors, etc. I'll try to provide as much detail as possible and I appreciate any help that can be provided.

My database consists of 3 tables with the fields as follows:
Employee_Lease: Lease #, Employee Last Name, Employee First Name, ID (Auto assigned by Access)

Invoice: Invoice #, Amount, Month, Month Entered, ID (Auto assigned by Access), and two lookup fields--Lookup to Relo_Counseler, Lookup to Employee_Lease

Relo_Counseler: Relo Counseler Last Name, Relo Counseler First Name, ID (Auto assigned by Access)

Basically each Lease can have multiple invoices attached to it. I'm trying to design a form in order to minimize data entry from paper invoices which is prone to mistakes. I want to have a form where I can enter a lease # and invoice # into a new record and if the lease # already exists, then the Employee's name (First & Last), Relo Counseler's name (First & Last), Amount and current month automatically enter into the form's fields. Basically if the lease # exists, a new entry/record is created which is essentially a copy of that lease #'s most recent entry. Once the new entry/record appears, I can manipulate it to reflect any differences in the new invoice.
However, if the lease # is new, and the relo counseler's name, employee's name, amount, are not already in the system, they can manually be entered into the form.

I can explain further elaborate if need be. I'd appreciate any help as soon as possible. I'm new at this, and not even sure if I should use text boxes or combo boxes for the form, so the more detailed information the better. Thank you.

 

[vangogh228]

If you have a lease number, do you really need the Autonumber ID field in the Employee_Lease table?


Whenever you have a one-to-many relationship, you can write a query to do what you are asking. Assuming that the one-to-many between the tables is the Lease Number... write the query to include that field from the MANY table and then include ALL the fields EXCEPT that field from the ONE table. Base your form on that query and... presto!... all the fields found on the ONE side pop in when you fill in the Lease Number. By then adding the rest of the fields (from the MANY side table) to your form, you can make new entries without having to fill in all the fields.

Hope this helps.

Tom

 

[bfdeal31]

Tom, thanks for replying. I see what you're saying about having the lease number being the primary key. I tried changing the one to many relationship to be through the lease number, but the problem is that the lease number is not completely unique...it should be, and is for the most part but about a dozen entries have no lease number (it was not available to be entered) and so those are probably what is causing the problem. I believe that is why it automatically created the auto number.
Is there still a way I can solve my problem?
I appreciate any help.

Stephen

 

[vangogh228]

Stephen: you should have my reply by now. Good luck!! Tom

 

[bfdeal31]

I'm still having trouble. Please help. File attached.

I'm still having trouble. I've been trying your suggestions Tom, but I still can't seem to get the forms to fill in automatically. I haven't done anything this complex with Access before.

I've attached a smaller, modified version of the file (i removed all my attempts at forms and most queries) and would appreciate it if someone could take a look at it and see if they could get a working form where I can enter the lease and invoice numbers into a new record and if the lease exists have all the other information pop in from the most recent entry of the same lease number...or it'd be great if atleast the employee's and relo counseler's names would pop in. I'd really appreciate it.

I'm hoping this might be a piece of cake to someone out there who can take a couple minutes and help me out with my file.
Once I have this smaller version working, I can hopefully duplicate it in my bigger DB. This is all I've been working on for the past few days and don't know what else to do. Thanks.

Steve

 

[vangogh228]

Steve:

Take a look at the form in this file and let me know how you do with it.

Tom

 

[bfdeal31]

More info to hopefully clarify

I'll explain the logic behind my project; first of all, the company I'm interning at has leases to different businesses for furniture,etc. For the past two years, each month, the paper billing invoices get entered into Excel. They now want to go enter the data directly into an Access DB. That's where I come in.

But now about my tables. Each Lease has one Employee attached to it as the point of contact for that lease. The Lease Numbers should be unique, except that some of the entries had no Lease Numbers and therefore appear blank in my table (but perhaps, should be given numbers like 999001, 999002,etc that won't be used for real invoices).

Each Lease has multiple invoices associated with it (usually one each month, sometimes more, sometimes none). Besides an Invoice Number (which sometimes repeat), each invoice also has an Amount, and two dates--the date (month) the invoice is for (billing period) and the date the invoice was actually entered (eg. an invoice for the august billing period can be entered in september). Also, each invoice is associated with a Relocation Counseler, who is basically the person within our company who oversees that particular invoice. The relo couseler's oversee multiple invoices--often times many of a lease's invoices are associated with just one relo counseler.

Here's the instructions I've been given:
"Process invoices entering as little as possible from each paper lease. Enter the lease # and invoice # into an Access form. If the lease # already exists in our database, the end user’s name, relocation counselor’s name, dollar amount and the current month will automatically enter into the form field. The ability to change the dollar amount and month to allow for past due bills as well as add-ons, pick ups or exchanges will exist within the form field. If the lease is new and the relo counselor’s name, end user’s name and total dollar amount charged is not currently in our database, we can manually enter these items into the form page."

Does that make sense? I really appreciate your help. Thanks.

 

[bfdeal31]

New Lease Entries

Tom, that file you have created has worked great. I've used it as a model on which I've based my DB. The form has to have preexisting leases and I am now trying to add a form (instead of having the user go through tables) in order to add new leases. However, what fields/tables would I pull together to create a form to enter new leases along with their Relo Counselers and Employee Names? I've been working with it and have created a semi-usable form, however, once the information has been entered it cannot be edited. It would be kind of nice of the information could be edited, in case the wrong name is entered, etc. I was working with combo boxes so that new employees/counselers can be added in case they don't already exist. Does this make sense? Any thoughts would be appreciated. Thanks.

Steve

 

[vangogh228]

Steve:

Generally, you would have a table that holds all the information necessary for a new lease. If that is the case, simply base a form on that table for input. Why you are being locked out of editing is unknown at this point.

If the data you need to establish a lease is held over more than one table, go through the form wizard and choose the various tables you need. From each table, select the fields needed to complete the entry. It's not quite as simple as that, but it is a start.

One area you might want to study is basing forms (and queries) on multiple tables. As you encounter other issues, like establishing combo boxes based on the current data such as employee names, etc, you'll want to address those separately here in the forums.

Tom