Form help

[SwanseaAccess]

Evening all,

I'm quite new to access and have taken on the quite daunting task of implementing a system to efficiently add sales orders, manufacturing orders and schedule production for a small-ish manufacturing company.

My starting point has been playing around with setting up a table for sales orders. I think I've added all the fields I need and now I am attempting to set up a form which will essentially act as an interface for the users (who have very limited computer skills).

I basically want a form where the users can either input an existing order number, retrieve the data for that order, amend certain fields and save it as a new order or just create a new order from scratch.

I've read a few books, browsed a few websites and played around with buttons and not got very far!

Some of the books and articles I've read are either too basic or too advanced for my needs and I can't seem to find a good source of guidance. Any help or pointers would be appreciated (i.e. a good website resource or book to buy).

I think I may need to learn some VBA programming to carry out this task but I wanted some expert opinion before I dig a hole so deep I can't get out!

Thanks!

 

[YNWA]

I think you will need more than 1 table. What fields do you have in this table?

I would think you would also have reference tables for things like products, delivery method etc...

Would it feasible to have a tables for customers, table for sales (linked to customers) then a few other tables with products etc... in?

Once you have your tables set up, create a query of them. The create a form with the fields you need based on the queries.

You can easily create a basic form, it only gets tricky once you get on a roll.

 

[YNWA]

Also you could have a form with a search function that types the orderID in, pulls that record and it can be edited, saved, deleted etc.. no problem. I have a search tool I use for something similar.

Good resource? This site, its amazing!

Oh and I am not an expert so I could be wrong

 

[SwanseaAccess]

Thanks YNWA,

Sorry forgot to mention I'll be using multiple tables. In my mind I'm thinking I'll have a main customer orders table (with order number, delivery date, quantity required, etc), then reference tables for customers (name, address, id, etc) and currency, etc. The next phase of my project will be to add in works orders and some form of scheduling but I want to crack the customer orders part first.

I've added the basic tables with a few rows of data and used the wizard to make a form and amended the layout a bit. Where I've become a bit stuck is that I want the users to use the form for data input.

I'm hoping to give them two options - copy existing or new. Copy existing will allow them to retrieve an existing orders details, amend a few fields then add the revised info as a new order with a new order reference by clicking a button. New will just be a blank input form where they enter the details then click add to add the order. And also an option to amend existing perhaps?

I don't know if this is possible but this is what I'm hoping for!

 

[SwanseaAccess]

Sorry YNWA, just saw your second post! The search tool sounds pretty much what I'm looking for. Do you have a link to some instructions to create something similar?

Is the YNWA because you're a Liverpool fan? What's happening at Anfield this season??!

 

[gemma-the-husky]

i think what YNWA meant was that one table isnt enough for orders

you need an order table AND an ordered items table

ie each order can have more than 1 item.

---------
you do need to consider everything you want the system to do - eg - do you want it to store selling prices for all your customers, so that when they order a product, it brings up the selling price. If so, do you want to store a price history. Stuff like that. As it will probably affect the system design.

 

[SwanseaAccess]

Thanks Dave.

I think an ordered item table is probably unneccessary - we only manufacture a couple of products and that will be identified in the order number (The order number is a letter followed by a five-digit code with the letter designating the product type).

Where a customer order has more than one requirement I'm thinking this will be stored in the table as A12345-1, -2, etc. With maybe the -1 as a seperate field?

It's still really early days though and at the moment, I'm just playing around and trying to get a structure I'm happy with.

Where I feel I really need help at the moment is designing a user interface. I want a screen where the user can easily input new orders or amend existing orders.

 

[vbaInet]

Where I feel I really need help at the moment is designing a user interface. I want a screen where the user can easily input new orders or amend existing orders.

Use the form wizard, see whether it meets your basic needs. If you want more functionality and are unsure about a specific thing, just pop a quick question.

Off topic: If the Swansea you mention is in Wales then, ahhh... Wind Street

 

[SwanseaAccess]

I have played around with the form wizard and managed to create a form I’d be happy using for data input.
In my mind I need the following:

1) A menu screen. The first screen users see when they open access. In here I will create buttons with VBA macros running when users click the button.

2) The first button will be “Add new order.” Clicking this will bring up a message box (perhaps?) with two options – “create new” and “copy or edit existing”.

3) Depending which option they choose will determine which form they will be taken to.

4) Choosing “create new” will bring up a blank form which the user can enter data into. There will be another button within the form to “Add order.” This will add the order to the orders table, allocating the next available sequential number as the order number

5) Choosing “copy or edit existing” brings up a different form. User will be prompted to enter an order number (Msgbox again?), once entered this populates the form and users can then edit this data and then select from two buttons – add new or amend order. Add new will take the details in the form and save as the next sequential order number, amend will adjust the existing records in the table.

I don’t know if any of that is possible, I suspect it is but I’m not sure whether it’s extremely complex to achieve. Where I struggle is writing the VBA code to carry out these instructions, I’m a complete beginner when it comes to writing VBA code. It’s something I am keen to learn as it can only help with projects in the future.

I’ve been able to create the buttons in the design view and understand that I need to edit the “On click” option but when I get into the event procedure I draw a blank.

All help and advice is gratefully received!

Off topic: Thanks for the response vbaInet, the Swansea in my username is a reference to my hometown in Wales and I’ve spent many a night on Wind Street!

 

[vbaInet]

All your requirements are literally that of a fully fledged database. You would need to do some reading to get yourself more acquainted with the workings of Access and VBA.

1) Can be achieved using a switchboard manager that comes with Access. Look through the menus.
2) Button wizard
3) You can ask this at a later stage
4) Can be done with one line of code of but you can ask this at a later stage
5) You will struggle with this as a beginner.

My advice would be to get your tables right before considering designing the user interface and take things one step at a time. Read up on normalization.

Off topic: I was there for a while. Loved the beaches

 

[SwanseaAccess]

Thanks vbaInet,

Have started looking at the switchboard manager - this seems exactly what I need.

Will then have a better look at the button wizard and see where I get with that.

Do you think it's best to get the tables right first?

I was thinking it was best to develop some provisional tables first (which I've done), then develop and test the user interface.

Once I know the interface works as I want with my provisional tables, I can then tweak the tables for fields I may have forgotten about and add some real data.

Off topic: The beaches are great. Swansea's got a bad name as a City but places like Llangennith, Rhossilli, etc are absolutely beautiful and great for surfing!

 

[vbaInet]

No. 4 can also be done via the button wizard too.

That's correct. Always best to get your tables right before delving into UI design. You will find it much easier to tweak your UI to suit your tables design and much harder the other way round. This is where the Theory and Practice of Database Design section of the forum comes in handy.

Off topic: I've only been to Rhossili and another smaller beach (which I can't remember the name of), but they were both very nice. Did a bit of surfing but I'm not any good. Obviously been to the main beach (on the stretch of Oystermouth road)