Retain information entered in form

[bo8482]

Dear all

I have searched high and low for a solution to my problem but alas haven't seen anything that helps (or else I'm searching along the wrong lines!)

Essentially, I have a form with the following data entry fields:
Client Number *
Client Name*
Posting Date*
Schedule Number*
Debtor Name
Invoice Number
Amount
Notes

Basically, I have schedules produced daily by clients that have multiple debtors on them that have to be input into the database.

The problem is that for each new debtor for one client, I have to re-enter the * fields above. Ideally, I'd like a button on the form which retains the * fields so that I don't have to reenter them for each new debtor.

I'd also like a seperate button that would start a totally new record when I'm entering debtors from a different client.

I hope that all makes sense. If anyone could offer any direction on how to work a solution, that would be much appreciated.

Many thanks

Brian

 

[Mr. B]

Brian,

To accomplish what you want, you will first have to redesign your database. Your issue is not just with your form. The issue is with the actual structure of your database.

You need one table that will hold the client data and another table that will hold the debtor info and perhaps even another table that will hold invoice and amount information. (Please understand that this proposed design is only from the info you provided and that in reallity, there could even be more changes needed.)

Do some reading on "normalization" before you go any further.

Once you have your database structure down and normalized correctly, then you can design the user interface (forms, etc.) as they need to be used.

 

[bo8482]

Hi Mr B

Thanks for getting back to me.

Thought I'd just clarify my setup.

I have a table called client details where I input client number, name, address etc etc.

I then have a seperate table called 'debtors' which is where I store debtors that are related to clients (i.e. a client may have 20 debtors). Info for this table is collected via the form described above.

The client number in this form is a combobox that looks up the client name and returns that value into the box below simply so that the data-entry person can confirm the client that they are entering info for. It's not duplicating any data.

The subsequent fields on the form are necessary pieces of info that I need and are input to my 'debtor' table (i.e. date of posting, schedule date, debtor name etc). The debtor table is linked to the client table by way of the client number being unique.

The problem is that there may be 10 or so debtors for one client on any particular day, and thus it is annoying for people inputting the data via the form above to have re-enter client number, date of posting and schedule date for every new debtor that a client has on one schedule (only the debtor name and the amount will change for any one client per schedule).

Does that clarify the problem? The data can't really be collected in any other way (or at least I don't see how....)

Thanks
Brian

 

[DCrake]

It seems to me you may need to use unbound forms to display the header information.Or use a mainform/subform layout and only chang ethe mainform when need be.

David

 

[Mr. B]

As I stated in my first response, you need a third table.

You need your Client table with only info about the client. You need your Debtor table with only info directly about the debtor (not each transaction). Then you need a table where each transaction is logged. This table will have a Primary Key field along with other data strickly related to the transaction. It will also have a Foreign Key field for the Client, from the clients Table and a Foreign Key field for the Debtor, from the debtors table.

For data entry you can have your users simply select a Client from a list, thus providing the FK to that field in the transactions table. Then select a Debtor from a list, thus providing the FK to that field in the tranactions table. The the only info that would be required would be the specific info about the transaction.

 

[bo8482]

Hi David

That sounds like it should work...annoyed I didn't think of it myself! Will give it a go and let you know how I get on.

Many thanks

Brian

 

[Mr. B]

David's idea for the form is a very good approach for data entry. It is not a solution to the actual problem which is "normalization".

I have been doing database development for years and (I hate to have to admint it) but just the other day, I started a project that I did not spend the time up front to ge the design down correctly and I continued to have real problems until I stopped long enought to really think it through, back up and redesign the data structure and then design the user interface. Everything became much simpler and easier when I got the structure right.

Please understand that you certainly have the priviledge of doing your project anyway you choose, but you did ask how to correct a problem.

Good luck with your project.

 

[bo8482]

Hi Mr B

Yes, I recognise the importance of data normalisation in a database - I'm not confident that my database is designed correctly but unfortunately it is now so big and has so many tables/forms/queries that trying to redesign it at this stage would be just too big a job. Indeed, I wouldn't even be sure how else to redesign my tables to address this problem...

David's solution (hopefully) will work a treat in the meantime though

Many thanks for your input,

Brian