Templates ?

2

25rover

Guest

• Oct 19, 2005

• #1

Hello everyone,

I just surfed in from google

I'm on a bit of an access learning curve and would like a little assistance if possible?

I created an access database for my uncle a year ago to deal with orders and delivery notes etc...

now he wants me to upgrade it a little, but i have no idea how..

What i want to do is:-

the database is quite simple in thought, really all i require is:

• customer form (to add new customers)
• products form (to add new products and change stock levels etc)
• Create order form
• Job sheet form (same data as create order but formated nicer)
• delivery note (again same data as create order but formated nicer)

Currently when creating an order customers and products are entered manually which is very time consuming and prone to human error.

i would love to have a form that you can select customers & products from a dropdown list.

in the future i would like the stock levels to change as orders are raised but won't look into that till i've had a long read of some books

would anyone care to offer some advise? or link to a template?

many thanks
john

 

Oldsoftboss

AWF VIP

Local time

Today, 16:49

Joined

Oct 28, 2001

Messages

2,499

• Oct 19, 2005

• #2

What you are changing to is actually quite complex. You will need to set up your tables differently, having a customer table, a products table and then tables that link the two. Inventory contol is complex due to many varing factors (eg, incorrect orders, incorrect stock counts, lost stock, found stock(!) etc) You will need to set up several tables for your inventory, Stock In (Purchases), Stock Out (Sales), and Stock Adjustments, then the current stock level will = Stock In + Stock Adjusted - Stock Out.

Just a few things to ponder,

Dave

 

2

25rover

Guest

• Oct 19, 2005

• #3

cheers for the reply Dave (dvdforums?)

Agreed the stock part sounds a little complicated right now, will look into that in the future.

What i'd like to do right now is select the customer from a drop down list.

from the selection i would like the data to fill in 5 fields (or cells?) in the form

ie the customer drop down comes form the customers table and writes the following into the order form:

contact name
address
tel
fax
email

I have done this in the past but at the time i had problems with it only writing the data temporarily and was deleted when i moved onto next record

cheers again

 

Oldsoftboss

AWF VIP

Local time

Today, 16:49

Joined

Oct 28, 2001

Messages

2,499

• Oct 19, 2005

• #4

You need to base your form on a query. I first tried ths with the Northwind example, sat there for hours trying to nut it out and eventually gave up. The a simple post changed every thing. Creat a dummy Db and try the following (copied from my code archive)

Auto Fill

Take any two tables related by a one-to-many relationship. Create a query that includes ONLY the related field from the "many" table and ALL of the "one" table's fields EXCEPT the related field.

Run the query, and then make a valid entry into the "many" table's related field, and the rest of the fields should fill in.

If it works... and it should... save the query, then create a form using the query plus whatever other fields you need.

I will whip up an example if you are still not sure.

Dave

 

2

25rover

Guest

• Oct 19, 2005

• #5

Dave if you could do a small example it would be ace!

cheers
John

 

Oldsoftboss

AWF VIP

Local time

Today, 16:49

Joined

Oct 28, 2001

Messages

2,499

• Oct 19, 2005

• #6

This is a VERY cut down version of a Db I did for work. Have a look at the query qryServiceJobs. See how the ClientID field is a combo box. This is added when designing the table.

If you select a client in the ClientID field, the other field will autofill.

It also has NotInList code and code for adding new clients that are not in the client tabl.

HTH

Dave

 

Attachments

• AutoFill.zip

  81.7 KB · Views: 249

2

25rover

Guest

• Oct 19, 2005

• #7

Dave thanks for that

i'm dead impressed, sat here for the past 2 hours trying to figure out exactly how you did it.

got miffed and gave up for lunch

Would you mind hooking up the fields to my combo box for me if i attached the file?

should only be 4 or 5 simple fields

cheers
John

 

Oldsoftboss

AWF VIP

Local time

Today, 16:49

Joined

Oct 28, 2001

Messages

2,499

• Oct 20, 2005

• #8

Sure, but dont give up. Have a look at this sample. I have butchered it a bit to make it easier to understand. Have a look at the query in design mode. You will see the clientID is in the service table, but the client details all come from the customers table. This is a good demo of how relationships work. I have also played with the form, but concentrate on the query, once you understand how it works, the forms will follow.

You mentioned you want me to set up your combo box. Think of it as a glorified way of displaying the clientID

Dave

 

Attachments

• AutoFill.zip

  81.1 KB · Views: 221