Shipping Calculations

[boblarson]

Re: Shipping Calcuations

Yes, another table for categories would be appropriate

tblProductCategories
ProductCategoryID - Autonumber (PK)
ProductCategory - Description

and then you store the product category id in with the product in the products table.

You could then, if the shipping was based on CATEGORY and not just item, change the shipping to ProductCategoryID - Long Integer (FK) instead and then pass the category id to the function instead of the product id itself.

 

[Nicolette]

Re: Shipping Calcuations

ok i'm pretty sure I fully understand all this.... Thanks for taking the time to explain it.... Also what did you think of my explination for my frmCreateInvoice from my post on totaling a subform earlier?

I want this DB to be as "correct" as possible I have used and gotten some idea's from microsofts northwind example DB but apparently their example doesn't follow the "best practices" as far a DB design goes.

in general I make seperate forms for adding, editing and deleting because well I like to have a list of current records that i can select from to do the editing and deleting
is this a bad way to do it?

 

[boblarson]

Re: Shipping Calcuations

Sorry, I didn't really digest it all. I know I need to and I just forgot (got a pesky tooth that I have to go in to get a root canal on tomorrow). I'll try to remember to look at it. I need to look at it when I am home and can do some things with it. While at work it is hard to do anything very "deep" as I have to fit things in between tasks I'm doing for my employer.

 

[Nicolette]

Re: Shipping Calcuations

oh that is not a problem I understand! I have a dentist appt tomorrow also so I feel ya... and I understand between tasks since I work on this between 4 girls ages 10 years to 6 months running around on summer vacation...

no you don't NEED to i am just looking for input from experienced DB developers so that I know I'm making this DB the best way possible I don't want y'all to give me answers and do it for me, just to explain and give honest comments and suggestions.

and I know you all don't sit in front of this forum all day everyday... While I might be in a hurry to get this done I never expected it to be done in a week and I started this with only a very basic understanding of Access so I value all the help you all are giving me....

 

[Lightwave]

I found it useful when I started to consider functions as just another variable.
The difference being it is calculated in someway using input variables.

That is why the opening line is just like the dim statement except rather than Dim you use function. Which I guess allows for the use of brackets after the variable name in the syntax.

Function "Getrate"(variables required to calculate) as variabletype

Getrate = (formula using variables)

End Function

As such just like variables you can put the function in a module if you are planning on referring to it from multiple forms or if you want you can put it within the code of an individual form. Oh yes you can call it from a query as well but I hardly ever do that... And just like a variable it can be private or public.

 

[PNGBill]

Re: Shipping Calcuations

in general I make seperate forms for adding, editing and deleting because well I like to have a list of current records that i can select from to do the editing and deleting
is this a bad way to do it?

I previously did this but have changed to one form that shows all data by default and uses filters to display only the data required.
Show all Orders. Click a button and now you see just the Invoiced Orders, or Shipped Orders etc.

Recently finished One Form that replaced 3 menu items, 5 forms and countless queries including a macro and temporary table. A lot of assistance from the forum and some magic wand work but end result is easier to use and takes up less database size.

If you wish to edit an order, then you would click a button on the record which will take you to a form that allows editing.

To Add a record, where ever this may occur, you click a button and open the same form that does the Editing but now, the properties are set to allow a new record to be added. Same form used for two different tasks.

Deleting a record - this is a little different in that do you really want to delete a record or edit it so it now shows, or doesn't show, as being deleted.
To do this, or even completely erase all record of the transaction, you just need a button, with a warning message and when clicked, the task is done. (use vba, action queries and or macros behind the button)
This button can be placed on a record in a continuous form or on a form that just displays one Order Record but no need to have the whole order details displayed in order to delete same.

 

[Nicolette]

Re: Shipping Calcuations

I previously did this but have changed to one form that shows all data by default and uses filters to display only the data required.

so you use a continuious form or data sheet view? How do you filter it?

Recently finished One Form that replaced 3 menu items, 5 forms and countless queries including a macro and temporary table. A lot of assistance from the forum and some magic wand work but end result is easier to use and takes up less database size.

the size of my DB is already huge (at least in my opinion and I don't even have all my stock entered in and it is already at over 600 records) now i realize that is not a lot in comparison to others....

If you wish to edit an order, then you would click a button on the record which will take you to a form that allows editing.

To Add a record, where ever this may occur, you click a button and open the same form that does the Editing but now, the properties are set to allow a new record to be added. Same form used for two different tasks.

Deleting a record - this is a little different in that do you really want to delete a record or edit it so it now shows, or doesn't show, as being deleted.
To do this, or even completely erase all record of the transaction, you just need a button, with a warning message and when clicked, the task is done. (use vba, action queries and or macros behind the button)
This button can be placed on a record in a continuous form or on a form that just displays one Order Record but no need to have the whole order details displayed in order to delete same.

I never figured out how to make one form to do all that. I always thought it was done with the vba coding to determing how that single form is opened

 

[Nicolette]

I'm kinda feeling like I am starting over... I know i'm not but with all the changes I feel I need to make It seems like I might as well....

 

[PNGBill]

Virtually all my forms are either continuous or single - never datasheet.

My reference to filter is for a continuous form.
You view all order pending, shipped or invoiced.
You have the filter and sort order in the command button.

VBA code is used but very simple one or two line stuff that once you have the first one done the rest are just copy and past and edit a little.

600 records - hardly a speck in the world of databases.
Access doesn't even blink until you are in the 100,000's and stories on the forum talk of millions as a normal occurrence.

Having a form be able to Edit an existing record or Add a new record is just one line of code in the button that is clicked.
When you are happy with the form - data is the same for Edit or Add - the forum can quickly help with the command button code to change the form property.
Have a look at your form property and you should see Allow Edit, Allow Additions.
The code just makes changes to these and Wala, you have one form that can do two or more tasks.

A little further down the track you can have parts of the form that are visible in one version and invisible in another, different colours and the list goes on.
You even change the label/heading on the form as it becomes Add New Order or Edit an Existing order. Again, just one line of code (maybe two) but just one step at a time as you have been going so far.
Ask the questions as you go along as, you have been and the responses will be there.

Make changes to a copy database so you maintain business as usual and don't get if a change, upgrade doesn't work out as you hoped or as quick as thought.

If you can keep a copy that only has sample data then you can quickly post this if things get hairy.

 

[gemma-the-husky]

nicolette

not sure if this has been touched on - but you need to be sure about the calculation

you mentioned variant methods of calculating shipping.

so what happens if someone picks things that dont all share the same shipping calculation - or the items cant all be shipped together. this may need a bit of care to get right.

 

[Lightwave]

I'm kinda feeling like I am starting over... I know i'm not but with all the changes I feel I need to make It seems like I might as well....

Nicolette this is probably a sign that you have learnt a lot from getting stuck into the project.

That often means that after a while you start realising all the mistakes that you've accidently designed in. All part of designing and building anything.

and yes I have gone back and built from scratch a few times rather than try and untangle my mess.

 

[Nicolette]

so what happens if someone picks things that dont all share the same shipping calculation - or the items cant all be shipped together. this may need a bit of care to get right.

oh crud I haden't thought about that!

 

[Nicolette]

ok so i have been messing with this for days now and I keep getting a variety of error messages I'm thinking the name of something is wrong but i'm not sure here is the VBA code

Function GetRate(lngProdID As Long, lngQty As Quantity) As Currency
   Dim strSQL As String
   Dim rst As DAO.Recordset
   
   strSQL = "Select ShippingRateBase, ShippingRateAdditional " & _
                     "FROM tblShippingRates " & _
                     "WHERE ProductID = " & lngProdID
   Set rst = CurrentDb.OpenRecordset(strSQL)
 
   GetRate = rst!ShippingRateBase + ((lngQty - 1) * rst!ShippingRateAdditional)
   
   rst.Close
   Set rst = Nothing
End Function

I really don't know what information you need to determine where the problem is so let me know what you need thanks!

 

[Nicolette]

update

I'm now getting this error
User-defined type not defined

I have no idea what that means but i'm trying to figure it out anyone have any suggestions?

 

[Nicolette]

still working on it does anyone have any ideas?

 

[vbaInet]

I haven't read your thread fully but I'm only replying to your current problem. I would imagine it's pointing to the Dim rst as DAO.Recordset line when you click DEBUG?

In the code window click TOOLS -> REFERENCES and add Microsoft DAO x.x Object Library.

 

[Nicolette]

ok it says
"Name conflicts with existing module, project or object library"
There isn't a duplicate of this code anywhere

 

[vbaInet]

Does it display the message when you try to add the reference? Or when you Compile?

Here's a useful link:
http://allenbrowne.com/ser-38.html

 

[Nicolette]

when I try to add the library from the reference window.

thanks I'll take a look

 

[vbaInet]

Have a look at the link first and if not successful then show us a print screen of your references. A blown up version would be nice. Also, the version of Access you're running.