About

Scott Diamond is a long time developer of Access and other database applications (over 20 years). He has been honored by Microsoft with there Most Valued Professional (MVP) designation since 2007.

He has published a book on Access VBA and, besides this blog, has contributed to several Q&A sites under his screen name of Scottgem.

He has also developed several Access apps for users all over the world. If interested in contracting with him for an custom Access app, please contact him at scottgem@diamondassoc.com.

 

Advertisements

13 Comments

  1. Thank you very much for your post about sequential numbering. I am having one challenge. I am using the sequential numbering for an Purchase Order. I have the code behind an “Activate Order” button. My purchase order form has a subform to handle the multiple items on an order. I am able to get the activated number to propagate to only the active line in the subform. Below is my code. Is there a way to get this activated number to propagate to all line in the active order? Thanks again for your expertise.

    Private Sub CTRLPOActivateSave_Click()
    Me.txtPOACTSAVE = Nz(DMax(“[PONum]”, “tblPO”), 0) + 1
    Me.fPODetailssubform.Form.txtPONumsubfrm = Me.txtPOACTSAVE
    End Sub

    • Don’t use the visible PO #. Use an AutoNumber as the PK and corresponding FK. Link the subform on the key fields. Just use the sequential PO # as a visible Numbering system. So you should not have the PO # in the Detail record at all. You can pull it into queries that join the two tables on the key fields.

      Scott

  2. Hi Scott,

    Having looked at the help provided by you, I would appreciate help on VB code for the following.
    Am a novice with VB in access. I have a main form in Access 2010 with options to use other forms for eg. a client, order, product for a project. I need a VB script to access those forms from the main menu and close the main form and open the form that has been selected from the main menu.

    Could you help please!
    VM

    • Hi Veena,

      Have you looked at the built in Switchboard Manager for Access? You don’t say what version of Access you have so accessing it may be different in your version. But searching for Access Switchboard Manager should get you instructions on how to launch it. Its a wizard based menu system.

      While I do answer specific questions pertaining to my blog articles here, part of the purpose of the blog is drum up freelance work. If you are not looking to retain such services, then I would suggest posting in the Access forum at askmehelpdesk.com. I monitor that board as well and will be happy to help (no fee) with specific questions.

      One last point. Access uses VBA (Visual Basic for Applications). VBA is a superset of the VB language containing commands, classes and extensions to manage the features specific to the application. So it is different from VB.

      Scott

    • That’s because you are not storing the price as part of your transaction record. This is one of the very few exceptions to the rule of not repeating data in multiple tables. That exception is for time sensitive values and prices are the prime example. You should be grabbing the price from your product table and storing it in the transaction record. This way, when the price is changed, the transaction records are not dependent on the prices in the Products table.

      Scott

      • The cost price is what is in the product table while the selling price is in the order table… I checked Northwind Data in Access and the list price in the product table can be changed without it affecting past transaction calculation
        I can’t figure out how it was done

        Please help me with a solution like that of Northwind

        Sent from my BlackBerry® smartphone, powered by Easyblaze

      • Again, the selling price should be in the Order details table. If it is, then you should be pulling that price for all reports. Without knowing your table structure. I can’t advise further.

        Also, your question has nothing to do with any of the articles in my blog. I prefer to answer questions here about the tips in my blog articles. If you need further help with this. Please post on either utteraccess.com, answers.microsoft.com (Access topic) or the Access forum at AskMeHelpDesk.com. I monitor all of those and either or or one of the other helpers will try to help. You will need to tell us where the price is changing and show us any SQL queries used or how the forms are setup.

        Scott

  3. Scott, I created a database in Access 2010 and am trying to password protect it with users so I can restrict input and viewing, etc. . I followed your example and actually used your coding, but you mention a password reset form but don’t explain how to create and link it to the enter password form. I’m an idiot. Can you help?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s