Microsoft Access has been driving me mad ever since I started my Advanced Higher Information Systems course work. Basically I'm building a stock control database which records the sales but picks up the description and sale price when a 'Item Code' has been been entered.

So far all I can see to make this work would be using the Event Procedure, 'After Update'. Thats where it all goes downhill because I have no idea on how to use MVB for these sort of things. I tried playing with the Expression Builder but that didn't go well either.


roughed out guide of how the Form looks just to give you more of an idea

Basically what I want is when 'Item Code' is entered it will take the 'Description and 'Sales Price' for that 'Item Code' from the Products table and add them to the fields here thus saving them to the Purchase table. Hope that makes sense ...

Anyone got any ideas or advice on how to implement this ??

Cheers
Keiran

Shouldn't need to use 'after update' event procedure.

You've built your tables; sales, items, and stock codes (or similar).
Have you defined the relationship between a field in these tables? - one of the icons on the menu when looking at tables/quieres/forms (sorry, Access not installed here, working from memory)
Sales to items is a one-to-many relationship using 'Sale ID' field. Items to stock codes is a many-to one-relationship using 'Item Code' field.

This relationship will underly all use of these tables, regardless of the quieries and forms used, and should grab the relevent data from associated records.

Good luck

I'm no guru, but I created and sold a complex stock management database system using Access 2000 when I was at college. I've not touched Access since, but I should be able to jog my memory.

However it's difficult to get a grips with someone's elses database, especially if I don't have it to play with.

P1lot is right though, this is something dead simple and just sorting out your key fields and relationships between the tables should get this working in no time.

I don't have the database on hand forgot to take if off my school account on the last day before study leave. All the data has been normalised to 3NF and all the relationships have been sorted out.

Heres a quick example of the data involoved:

Customer
Customer ID
Name
etc

Products
Item code
Description
Sale Price
Quanitiy

Sale
Sale ID
Sale Date
Paid
Customer ID*

Purchase
Sale ID*
Item Code*
Description
Quanitiy
Sale Price

So basically what I want to happen when the 'Item Code' is entered into the Form for the database to automatically take the 'Description' and 'Sale Price' for that 'Item Code' from the Products table and place them in the text boxes on the form. Then that will add them to my Purchase table. My main problem is getting it from the Products table I really have ran out of ideas on how to get it to work. I should have made an easier design brief for my project

Cheers
Keiran

So you have something like this?
Then you create a form & subform, with the form linked to Sales table, and subform linked to Sales Purchases table.
As soon as you leave the 'Item code' field in the subform, it should grab the Description for you.

edit: tried it myself and found something missing. Need a query linking Products to Sale Purchases. This is the source data for the subform

edit2: hmm, not working for me. Still working on it...

Thanks for taking a look at it

What you've got is roughly right. In the products table you need 'Sale Price'.

Quote :

As soon as you leave the 'Item code' field in the subform, it should grab the Description for you.

Yup once a 'Item Code' has been entered it should grab the 'Description' and 'Sale Price' from the Products table. So some how the SubForm needs to search the Products table for the 'Item Code' entered and take the 'Description' & 'Sale Price' from it. Thats my sticking block :s

Cheers
Keiran

Quote from keiran :

Yup once a 'Item Code' has been entered it should grab the 'Description' and 'Sale Price' from the Products table. So some how the SubForm needs to search the Products table for the 'Item Code' entered and take the 'Description' & 'Sale Price' from it. Thats my sticking block :s

I've got that part working, now I can't enter new orders, grrr

Great thanks Any chance of you mailing me that part you got working or uploading it here?? keiran.smart@gmail.com is my mail.

Now off to my Information Systems exam :o If I get a bit of time after the exam I'll try get upto the computing department and take my database home. If I saw your one I'd easily be able to implement it across.

Thanks again, that means I can get my project moving now
Keiran

Oooh, advanced higher info systems, im doing Higher Info Sys at the mo (exam on thursday) and I hate it with a passion!

How do you find Adv. Higher?

Adv. Higher ain't to bad. You are left on your own to work like most Adv. higher courses so thats pretty good and 50% of the marks are from your two projects The Higher course was made harder this year, last year I only had to normalise to 1NF

Keiran