Buttons, fill fields, calculations and look ups

[BRV]

Hello everyone. I'm new to the forum and just starting out in access, so please bare with me.



I'm creating a database to store vehicle inventory. I'd like to add the functionality of a button that will automatically calculate our sales prices.

We use a "tiered markup" system, whereby the higher the cost of the item, the less markup we make off of it. We have 3 different markups that we use for each item. For this, I think I should use a seperate table. The table designates 3 different markups for different price categories (one for less than $$$, one for between $$$ and $$$$, etc.)

So far, my thoughts are for the user to enter the cost and click the button. I was thinking about using on onClick expression and a dlookup to draw in the correct markup and calculate it all in the expression. Unfortunately, this is proving to be not only functional, but I believe also overly complicated.

The markup table would need to compare the cost and draw out 3 sets of numbers (the "mark up"). At this point, 3 different fields would be filled on the current record (adding each of the markups to the cost) - Net price, low retail & mid retail (the 3 different mark up levels).

This all seems confusing to me, so I hope that it's clear enough for you folks to understand.

Currently, I'm having a tough time figuring out if I should be looking at queries, macros, or VB code. I'm really looking to be pointed in the right direction. I'll research and ask questions as necessary, so I'd really like suggestions on the best way to accomplish this. Thanks in advance!

[RuralGuy]

I would do it all with code in the AfterUpdate event of the Cost control.

[BRV]

I would do it all with code in the AfterUpdate event of the Cost control.

Thanks for your advice. I looked into the "AfterUpdate" event. Unfortunately I really need this to be manually implemented on the form as it won't be used every time. Would the onclick on a button be a problem?

I'll start looking more into using VBcode. It's been many years, so I'm basically starting from scratch there. Could you elaborate on some of the functions that I'll likely be using so that I have somewhere to start?

[RuralGuy]

Using a button will not be a problem. As for the code needed that would depend on your table structures and the other requirements. Unfortunately that is about as specific as I can get without more specific details of your application. Why would it not be *every* time a value is entered into the cost control?

[June7]

The markup factor only needs to be saved if is possible it could be changed in the future and you want it to remain unchanged for that sale record forever. Otherwise, can use constants to calculate the adjusted price, select the appropriate constant with conditional expression.

The only problem with a button is that user has to remember to click it. Need to build code to deal with this user error. Should they or should they not have clicked the button? What criteria would determine the answer? If you can determine criteria then that could just as easily be built into the AfterUpdate event and user need not be bothered with another click.

I try to limit buttons to actions the user must initiate in order to proceed, such as Open form or Close form.

[BRV]

June 7, Thank you for your input. It is possible that it would need to be changed in the future. I didn't mention that there will also be another table that it will need to reference which will add in freight & prep costs, which will definitely need to be easy to change.

Thought more about the button. It really just depends on whether its new or preowned inventory. Since New/Used is one of the fields in the record, I could probably just use a if/then statement to calculate only on new units. I'll talk to the users and see what they prefer.

It seems that I'll be using elseif statements to accomplish what I need. How do I access the table data in VB?

[BRV]

So I've been looking through some tutorials and figured I'd see if I'm on track here.

My plan is to design this as a "standalone" program that can be installed on the individual terminals with the data residing on a Windows 2003 Server.

So far I'm leaning towards using an ODBC data source. Does this seem right? If not, what would you suggest & why?

I should mention that there will be 1-3 users accessing and possibly amending data on it at any given time.

[June7]

I don't use any ODBC data source for data I have to maintain. Choice depends on your requirements. Access serves us just fine. Access does have a 2gb file size limit. Must run Compact&Repair periodically, especially after design changes as this causes the file size to grow.

I have one project that is a split design. Both parts are Access files. I use UNC pathing to link to the backend. Has about 10 users.

I have another project that is not split and has about 4 users. We find that they can all open the same file and edit data as long as not same record and the edits are reflected through all sessions. However, there is no code (macro or VBA) in this simple project. I was surprised at how well it works. They are happy with it.

From VBA, access data in table with domain aggregate functions (DLookup, DMax, etc) or recordsets. Use sql SELECT statements to open a virtual table of records.

[BRV]

June7, Thank you so much. You've been very helpful!

I found a good article on split databases and I'm looking into that now. When you're basically starting from scratch, it's hard to find the right terms to research to learn what you need to do.

I'm also going to look into UNC pathing.

Thanks again!

[June7]

This thread discusses my split db http://forums.aspfree.com/microsoft-...tc-324677.html