Form I can enter multiple values into. (1 Viewer)

[AdamHughes]

Hello,

I am making a database and I am currently doing my forms. I have been using wizards and so far everything I have wanted to be able to do has been achievable, except for where I am now! I am making a 'purchases' form. What I want to be able to do is make multiple purchases in the one form. Let me try and give you an example to further explain myself.

Lets say Mr John Smith wants to purchase 1 black pen, 1 30cm ruler, 2 rubbers and a compass.

I want it so that rather than have to add each one of the purchases one by one, I want to be able to add them all in the same time and be able to go back to my purchases table and see:

Mr John Smith - 1 black pen
Mr John Smith - 1 30cm Ruler
Mr John Smith - 3 rubbers
etc

Hopefully you understand what I mean?

Can anyone assist me?

Most appreciated!
Adam

 

[spacepro]

Adam,

Firstly welcome to the forum.

You can have a main form which displays your customer details and a sub form that is linked with your customer form and the default view is set to continuous view.

For this to work your tables need to be designed with a 1 to many relationship on the Customer table to the orders table.

For more info take a look at the Northwind database or post back if you get stuck.

Andy

 

[AdamHughes]

Hi again,

Thank you for your warm welcome.

Also, thank you for your assistance with my problem. I have done as you have asked but when I try and add more records of data (a purchase) it gives me this error:

'The changes you requested to the table were not successful becasue they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.'

How do I get around the problem?

In my database I have three tables:

Purchases -

OrderID (primary key), Date, item, MOS, Quantity

Staff -
MOS (member of staff) (primary key), Title, Surname, Primary Department

Stock -
ID (primary key), Item, Cost, Quantity.

I'm guessing that information may be helpful to resolve my problem!

I appreciate the help!
Thanks!
Adam

 

[spacepro]

Adam,

See attached sample.

Look at the table structure and relationships. Note I have including the CustomerID field in both tables and set a one to many relationship.

I simply used the form wizard to create the form that opens up by selecting all fields from both tables.

This should point you in the right direction.

Any problems post back.

Andy

 

[AdamHughes]

Hello again,

Once again for your help so far.

I looked at the database, and the relationships are how they should be in my database. It actually works as well, but only to an extent. It only works with whatever record shows first.

How do I make it so I can simply change the name at the top and it go to their purchases. If I change it now it shows the same error as above. I can use the Record: |< < 1 > >| arrows at the bottom of the form, but I'd prefer that I could just change a name. Is this possible?

 

[spacepro]

Adam,

The way the sample is done is to show you how a relational database works.

The form displayed is for data input purposes, if you want to see all the purchases for a member of staff then use create a query/report/Form to pull the data you want to view.

As an example if you open the tblCustomers Table and click the + sign next to the Autonumber (CustomerID) field , it shows all the purchases/orders for that customer.

You can have one form for data entry and another form to search/view all purchases for a particular customer by using expressions and criteria to filter the data you want.

I have attached a further sample showing you how to filter the form by viewing all purchases for each customer. This is just a basic example. There is VBA Code on the Afterupdate event of the combo box on the form frmSearch.



Andy

 

[AdamHughes]

Hi Andy,

This is helpful, however I am not sure how to set up a filter to do this for my database. Could you explain how, please?

Totally off the subject, but is it possible to set up a macro or something else so that I can print off several queries without having to individually print each one?

Thanks!
Adam

 

[spacepro]

This is helpful, however I am not sure how to set up a filter to do this for my database. Could you explain how, please?

This entirely depends on your table structure and the data you want. Do a search on the forum and post specific problems that require help that you are unable to resolve.

Data is entered into a table, queries pull specific data you want. This is also called filtering. Depnds on what you want to do which determines the route you take. Have a look in Access Help for query and filter records.

To see how the sample works, open the search form and press ALT and F11, this will display the Visual Basic Editor (VBA) and you will see the the code , which is simply a open form command with a filter.

Totally off the subject, but is it possible to set up a macro or something else so that I can print off several queries without having to individually print each one?

Yes, you could do this. Do a search on the forum for print query , printout command, etc etc.

Do a search and if you need specific help post a new thread and members here are very willing to help.

Good Luck and I hope I have answered some of your questions.

Andy

 

[AdamHughes]

Andy,

Thank you for your help and efforts. They have been appreciated and you have helped me a lot, thanks!

Adam.