Calculating Transport Costs through Access 2007 Form

[Qpido]

Dear Forum Members,

I'm working for a manufacturing company and we have an on-going problem with the consistent arduousness of trying to find the best price for our transporting costs.

We have 2 suppliers both with their own prices, switching between these each time takes a lot of time and can lead to mistakes sometimes.

I want to create a form in access that contains the data of which countries we ship to (and have data for from the transport companies), the weight you want to ship it at, the postal codes that you want to ship it to and then it should display both prices.

I have assembled all the data in the database with the prices of each and such, but I am getting in a huge mess not knowing where to start and how to link everything.

I was rethinking the concept and might want to use access in combination with excel sheets that have all the data, because excel is easier to edit.

Qpido

 

[Qpido]

Ok I managed to get a little further, not being able to sort the data at all.

I filled in the textboxes for an idea what it will look like for my senior. Nothing actually works yet.

http://img819.imageshack.us/img819/5773/previewformconcept0.jpg

Should I upload the 2007 database so you can view it?

Jerome

 

[Montez659]

Couple of questions: what exactly are you needing to sort? Why is there multiple of the same value listed in the weight listbox?

It looks like your format is good. The piece of the puzzle that we haven't worked on yet is getting the info behind the listbox entries into variables. That is going to be done in VBA by something called DLookup (you can easily google this and get an idea of how it works, but sometimes the syntax can be confusing, even for me!).

 

[Qpido]

Dear Montez,

I can see your confusion, but this was supposed to be just an example. There should only be single values per column.

In the most left column, you choose the Country. Each country has different amounts of weight we ship to (Poland 0-25000, Belgium 0-4000, France 0-5000kg etc.), When you choose Belgium, it will only show 0-4000, each per t/m50kg, t/m100kg, etc. And per country there are a bunch of Postal Codes you can choose (each having their own price tag PER weight).

So once you have chosen Country, Postal Code and Weight, the idea is to have Bosman Price and Rotra Price load into the far right 2 columns/text boxes. I have given each Postal Code 2 codes, One Rotra Zone code and One Bosman Zone code, these are then linked to the bosman price table and rotra price table.

The values I have filled in are not actual results, I filled them in manually to show my senior what it would potentially look like as a structure.

Hope to have informed you enough and let me know if you want me to upload the database for you to have a better understanding of what I am trying to do.

Jerome

 

[Montez659]

I wish I could take a look at the db, but I am on a work computer and won't be able to d/l it.

Since you have what looks like a pretty good basic layout down, what is the next piece that you need to work on? Have you figured out how to change the values in the weight list box based on the Country? I believe what you are trying to accomplish is something called cascading comboboxes (even though you are using listboxes ). Check out this link, which contains another link for that piece of the puzzle.
http://mymsaccessblog.blogspot.com/2008/03/how-to-synchronize-two-combo-boxes-on.html
This will probably help you to sychronize all three listboxes so that the weight and Postal Codes will be narrowed down based on country choice.

 

[Qpido]

Hey Montez,

I was playing around with this thing and just got the weirdest results.

It kept changing my table column data entries to empty fields and random numbers. I removed all relationships and just tried fresh and still it just started changing all my data and not giving any results at all.

The database is here on my SkyDrive, you could look at it whenever possible:
http://cid-9af56772efadafa1.office.live.com/self.aspx/Silicon Development Files/TestingBelgie.accdb

Thanks

Jerome

 

[Montez659]

Will try to take a look at that later, but let me ask you - are the controls that you are using on the form bound to a table? If they are bound, they will change if you change them. You can populate the listboxes without them being bound and that would make sure none of the table values would change.

When you created the listboxes you used the wizard right? I think they probably are bound then. I know there surely is a way to make it unbound but it is Friday morning after 4 hours of sleep and the coffee hadn't kicked in yet. Try creating another listbox but then canceling out of the wizard. Then specify the Row Source Type as Table/Query and Row Source you can click on the Query builder and select the table and values that you want.

 

[Montez659]

Ok, don't know why I didn't think of this before, but probably making sure that the Control Source proerty is blank should sever the binding.