Invoicing Database - Assistance Required (1 Viewer)

[N31L777]

Hi all.

I was wondering if somebody could assist me with a problem I have with a database I am trying to create as a project for work. I've been asked to make an 'invoicing database' which when different departments purchase items said database acts as a log (information regarding the purchase would need to be entered manually).

Just as a prefix - I'm no expert at Microsoft Access so please don't flame me too much if I've messed up the database design.

The database contains a few tables:-

Table A - Stores all the required fields for the form (e.g. date purchased, date received, quantity, department code, total cost of order)
Table B - Contains department code and total spendable budget

I've created a form with various fields which the user can insert data in to (plus validation) and all of this works great. When a user inserts data it reflects in Table A and therefore this is then used to run queries against. I've also linked together the department codes in the two tables as a relationship. My problem is that I wanted to have a dynamic label or text box in the form which shows the remaining budget to the appropriate department.

E.G - Table B lists Department with the code "123" having "£10000" to spend. If I enter an order with a total value of £500 and select department code "123" from the drop-down menu I'd like the form to subtract the amount remaining in Table B for this department and show a remaining budget on the form. (£10000 - £500 = £9500).

I did try to solve this by using various methods but nothing seemed to work, all I ended up with was a "#NAME in the budget remaining label on my form.

I hope that I have explained this well enough so that it is understandable...

Much appreciated!

Neil

 

[DCrake]

Simple Software Solutions

In your DLookup for the remaining budget you need to emcompass the field name in square brackets [ ]. The reasonbeing the field name has spaces in it. Access thinks that the first word is the field name. It is best practice not to have spaces in field names. The convention I use is as follows:

Tables:
fldYourFieldName

fld: so when using VBA I know I am dealing with a table field
YourFieldName: capitalising each word without spaces still makes it readable but eliminates the use of square brackets.

From Controls

TxtYourFieldName

Txt: so you can identify it as a form control

YourFieldName: so that when passing data between form controls to table contents and vice versa by using the same name it is easy to match thewm up.


CodeMaster:

 

[N31L777]

I've made some changes.

Hi all,

I've made some changes to the database and it now seems to be half working, lol.

The changes I've made include:-

• Creating QryTotalCost which builds a total cost by department
• Creating QryTotalRemain which subtracts the total cost from the budget table.
• Reworked the way the database was laid out.
• Have made autonumber the primary key in the tables.

The main problem I now have is that when a user enters an order it doesn't successfully subtract that amount from the relevant department in the "Department Code Budget" table.

Any suggestions for how I can get the budget for the appropriate department to be subtracted would be great

The new database file is attached.

Thanks in advance

Neil

 

[MStef]

Hello Neil!

Here it is, (attacment).
1) I think you don't need a "tbl Department Code".
2) I made a change in the Relationships.
3) I made a "qryGetTotalValue".
4) In the form, I added a Sub "Total_Value_AfterUpdate".
5) You have to take care of the change a value in the "Dept Code" field.

 

[MStef]

Here it is .

 

[N31L777]

Wow... just wow

Mstef... you're fantastic!

It works beautifully, what more can I say?

I'll give it a running over with some more test data and post back here if I need any further assistance.

Thanks again

Neil