Results 1 to 7 of 7

VBA question

  1. #1
    smicklo is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    11

    Question VBA question

    Hi Experts



    I am learning Access db for my job and have a few questions for all about VBA programming. I have written several macros and programs in VBA for both Word and Excel files.

    I am wondering if I can do the same for Access db?

    If so, is it the same idea as with Excel? Create buttons, pack the buttons with code to complete tasks?

    Do the fields get called int eh same way as Excel ( I would presume they would as the VBA code is the same. Just not sure of the way Access is built).

    Am I correct to believe that Access is (in its most basic form) a set of spreadsheets that can be queried more easily?

    Thanks in advance for the answers.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    6,447
    yes and no. The objects are different.
    listboxes and combo boxes are loaded by simply binding it to a table/query. No vb needed.

    Access does not use references like excel....cells(r,c)
    you can do pretty much everything with queries and macros.
    sometimes some odd things need pure vba.
    Use forms to edit data, queries to move data.(or view)

  3. #3
    smicklo is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    11
    Thanks you for the info.

    I will briefly explain what I am looking to try and accomplish, and if you can let me know if its possible- please:

    We have an Access DB. What I am wanting to do is to take a number, enter it into a "macro" have it read the access line, display some data from 4 fields, verify it, then with 1 click change 3 fields to show destroyed / depleted / returned and fill in a field with a date completed. Currently, we query for the number, then tab through the DB line data to the fields, and manually change the fields, past a copied text in other fields, and type in a final field.

    Much of this I know how to do in Excel, but is this doable in Access? Or would there be a much cleaner and quicker way?

    Thanks again

  4. #4
    Join Date
    Apr 2017
    Posts
    610
    Quote Originally Posted by smicklo View Post
    Currently, we query for the number, then tab through the DB line data to the fields, and manually change the fields, past a copied text in other fields, and type in a final field.
    It looks like you enter data into table directly. A bad idea in general. Create a form, with table as source - it allows much more control over entered/edited data, allows easily to link info from other tables, and allows to use built-in automation.

    What I am wanting to do is to take a number, enter it into a "macro" have it read the access line, display some data from 4 fields, verify it, then with 1 click change 3 fields to show destroyed / depleted / returned and fill in a field with a date completed.
    This looks suspiciously like saving calculations into table. In Access, it is best to save all data needed for calculation into table, and not to save the calculated value. In forms, you can use a query as source, and make calculations in query (you can then display the calculated field in control on form), or you calculate the value in unbound control on form. And same for reports.

    Tables are for saving data. And only for this!

  5. #5
    keviny04 is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Apr 2015
    Posts
    87
    Quote Originally Posted by smicklo View Post
    Hi Experts

    I am learning Access db for my job and have a few questions for all about VBA programming. I have written several macros and programs in VBA for both Word and Excel files.

    I am wondering if I can do the same for Access db?

    If so, is it the same idea as with Excel? Create buttons, pack the buttons with code to complete tasks?

    Do the fields get called int eh same way as Excel ( I would presume they would as the VBA code is the same. Just not sure of the way Access is built).

    Am I correct to believe that Access is (in its most basic form) a set of spreadsheets that can be queried more easily?

    Thanks in advance for the answers.

    Excel is basically a free-form data entry tool for charts and things or anything for which you need "flexible" data entry. But Access is much more structured and has a lot more rules for what kind of data you can have. That is not a disadvantage because in some circumstances you do need to have more rules and restrictions with your data.

    Even though all Office programs share the same VBA language (even Outlook, Powerpoint, etc. do), Access has different "object models" from those of Excel (and Word, Outlook, etc.). Data are referenced in manners required by those object models. In Excel, you can reference any cell with =Range(cell, column) or some such. But in Access, you first need to open up a "data set," which is often just a SUBSET of all the data your have. Then you retrieve a "record," then reference a field in that record. It's much more structured. Everything in Access is much more RIGID and structured than Excel. So don't go there if you want "flexibility" with your data.

  6. #6
    smicklo is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    11
    We do enter the data through a form. To edit the data once we have completed our work yes, I query for the record and then tab through and manually change 4 fields (no calculations done).

    My hope was to create a macro that would search for the record number, allow the user to verify its correct, then on a click, change 4 fields to show completed (to avoid tabbing through records.)

    If I am reading your post correctly, you are recommending using a form to find the record number, then update the fields through this form. And that makes sense. As an Excel-man, my mind automatically goes to a marco for quick repetitive tasks. Will train to use forms instead.

    thanks so much for the assistance.

  7. #7
    smicklo is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    11
    Thanks so much for the explanation. What your saying does make sense, and yes, seems that if it can be done with a form, to use it that way. As a macro seems more involved in getting the data set (subset) , and then retrieving the record........

    Just use a form to do what you want.

    Thanks again!!

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

Similar Threads

  1. Replies: 4
    Last Post: 08-25-2012, 06:19 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
  •  
Tech Forums: Microsoft Office Forums