Results 1 to 10 of 10
  1. #1
    BRV is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2011
    Posts
    29

    Buttons, fill fields, calculations and look ups


    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!

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I would do it all with code in the AfterUpdate event of the Cost control.

  3. #3
    BRV is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2011
    Posts
    29
    Quote Originally Posted by RuralGuy View Post
    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?

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    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?

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,619
    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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    BRV is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2011
    Posts
    29
    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?

  7. #7
    BRV is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2011
    Posts
    29
    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.

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,619
    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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    BRV is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2011
    Posts
    29
    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!

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,619
    This thread discusses my split db http://forums.aspfree.com/microsoft-...tc-324677.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 05-30-2011, 06:03 PM
  2. Replies: 4
    Last Post: 03-27-2011, 01:24 AM
  3. Using a combo box to fill in other fields
    By mccluein in forum Access
    Replies: 1
    Last Post: 03-14-2011, 05:40 PM
  4. How To Fill In Multiple Fields From A Form
    By SamanthaSamuels in forum Access
    Replies: 3
    Last Post: 08-16-2010, 12:13 PM
  5. auto fill certain text fields?
    By darklite in forum Forms
    Replies: 4
    Last Post: 07-12-2010, 02:20 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums