Results 1 to 15 of 15
  1. #1
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    VBA & Access (XP)

    Hi

    Just a quick question. Can I create an Access Database with Forms, etc, and then add a VBA aspect to it? I.E., does the whole front-end of a Database have to be made with VBA if you're using VBA, or can I create 4 or 5 Access forms and then have a button that opens up a form created using VBA?

    Thanks in advance. [img]/forums/images/smilies/smile.gif[/img]

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA & Access (XP)

    I don't understand what you mean by "... have to be made with VBA". <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

    In general, you only use VBA where you need it, not every form or report needs VBA.

  3. #3
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: VBA & Access (XP)

    What I meant was . . .

    Is VBA just another element that can be used in Access. So, I would have Tables, Queries, Forms, Reports, Macros, Modules . . . and VBA.

    Or . . . If I used VBA, does the *whole* Front-End need to be done in VBA. So, I would have Tables, Queries, and VBA.

    I assume from your answer that I *can* create Forms, and then when I need to create a Form that can't be built using Access' Built-in Form function, I can fire up VBA and create a VBA Form that will have greater functionality than the standard Forms.

    Thanks for answering so quickly! [img]/forums/images/smilies/smile.gif[/img]

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA & Access (XP)

    VBA is not another element beside Tables, Queries, Forms, Reports, Macros, Modules and Pages.

    Except in very special circumstances, you don't create forms or reports using VBA. Either, you create a form in design view, or you use one of the wizards to create the form, and then fiddle with it to adapt it to your needs. At any point during this, you can add VBA, to handle events such as clicking a command button. It is also possible to design a form without any code at first, and add it later on when you find that you need it.

    If you need code that is to be used in several objects, you place the VBA code in a separate module in the Modules tab. If you need it in one form or report only, you put the VBA code in the form module or report module.

  5. #5
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: VBA & Access (XP)

    Yeah

    What I want is an Interface whereby people can select objects from a Number of Available Mailing Lists and send them to a 'Selected' Mailing Lists window, similar to the Address Book in Outlook.

    I've attached an example.

    I assume this can be done in VBA? <img src=/S/confused.gif border=0 alt=confused width=15 height=20>
    Attached Images Attached Images

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA & Access (XP)

    Yes, you would use VBA code in the On Click event of the four command buttons to make them do what their captions promise.

    I have attached an extremely simplistic example. It leaves a lot to be done, but it demonstrates the basic idea of using code to add and remove items from list boxes.
    Attached Files Attached Files

  7. #7
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: VBA & Access (XP)

    OK, that was brilliant, but now I'm thinking that I've misunderstood what VBA is/was. I thought it was a visual way to design elements (such as the Outlook ADD/REMOVE interface) that couldn't be done in Access alone, but now I'm thinking that it's just VB Code that's used behind the scenes of MSO programs.

    I.E., are things like 'OnClick, DoCmd: Maximize' actually VBA? If I buy a VBA book, am I just getting code examples and the syntax I need to use 'behind the scenes' as opposed to a program that provides visual GUIs that subsequently generate code?

    I think this is why my post was so confusing. I think I've misunderstood what VBA is and does. I did a VB class (not VBA) when I was learning Access, and this taught us how to create Forms that had their source in Access Databases. I thought VBA was just a specific Application designed to extend the funcionality of MSO.

    HELP? <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA & Access (XP)

    Indeed, VBA is not a visual design interface or something like that. Part of that is done by various Wizards in Access - they can help you create interface elements, and some Wizards will generate code for you.

    Instead, VBA is a programming language; its general form is the language used by VB6, and each Office application adds its own objects, methods, properties and events to the language. So if you are programming in Access, you can use the generally available language elements such as If ... Then .... or the Left, Mid and Right functions, and additionally, there are elements specific to Access, such as the DoCmd object with its methods such as OpenForm.

    You description that VBA works behind the scenes hits the nail on the head. You use it to get things done while the user works with forms etcetera, not to design forms.

    If you study a general VBA book, you will only learn the general elements; if you want to learn about the Access-specific elements, you need to get an Access VBA book. Another good way to learn the specifics is to study sample database, for example the Northwind database that comes with Access. Micosoft also has several interesting sample databases for download in Access 2000 format. See here and here.

  9. #9
    Lounger
    Join Date
    May 2002
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA & Access (XP)

    That code was terrific, but can I ask another question? This database would be used by multiple people, therefore, if 2 users were creating a mail merge at the same time, the checkboxes would be checking and unchecking like crazy as each user chose the lists they wanted to use. I presume it would be possible to use a Query instead so that each time a user opens a form, the data he or she selects would be unique to that person for that session--and, maybe even until the user physically 'clears all' the data back from the Selected List Box to the Available List Box?

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA & Access (XP)

    Using a query won't help - changing data in a query changes the data in the underlying table(s), so the confusion would remain. If you want this to be multi-user, it must be done differently.

    One possibility is to set the Row Source Type of the list boxes to List of Values, and to populate the "Available" list box in the On Load event of the form. The Add and Remove command buttons would use AddItem to add an item to the target list box, and RemoveItem to remove the item from the source list box. Note: AddItem and RemoveItem were introduced in Access 2002; they are not available in earlier versions.

    Another possibility is to create temporary tables for available and selected items; the tables should have unique names so that each user would use his/her own copy. If you use a backend/frontend design where each user has a local copy of the frontend, you can create the temporary tables in the frontend.

  11. #11
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: VBA & Access (XP)

    Oh right, I must have been confusing it with the way an Expression can be built that populates at runtime. I was extending that theory to include creating a Field that Populated at runtime. Sorry.

    I looked up the Help in Access, and it provided this example

    Function AddItemToEnd(ctrlListBox As ListBox, _
    ByVal strItem As String)
    ctrlListBox.AddItem Item:=strItem
    End Function

    This example adds an item to the beginning of the list in a combo box control. For the function to work, you must pass it a ComboBox object representing a combo box control on a form and a String value representing the text of the item to be added.

    Function AddItemToBeginning(ctrlComboBox As ComboBox, _
    ByVal strItem As String)
    ctrlComboBox.AddItem Item:=strItem, Index:=0
    End Function

    As you see, it says that it needs a ComboBox to work. Is that just an example? Will a List Box work just fine too?

    I've just ordered myself a SAMS Teach Yourself Access VBA in 24 Hours, so it'll help me understand the code, b/c at the moment, half of this isn't clear to me.

    I hope this'll help!

    As always, thanks for your reply, Hans.

    Have a great weekend.

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA & Access (XP)

    You can use list boxes as well, and if you put the code in the form module itself, it can be simpler. Say that your list boxes are named lbxAvailable and lbxSelected. Code to move the selected item in lbxAvailable to lbxSelected could look like this:

    Private Sub cmdAdd_Click()
    If Me.lbxAvailable.ListIndex = -1 Then Exit Sub
    Me.lbxSelected.AddItem Me.lbxAvailable
    Me.lbxAvailable.RemoveItem Me.lbxAvailable.ListIndex
    End Sub

    You'll only have to wait 24 hours, after that you'll know everything about VBA! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  13. #13
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: VBA & Access (XP)

    Well, that code certainly does look simple!

    I hope the code that compares the lbxSelected Items against a Query that contains all the Lists is as simple!!!

    Was that a total joke, or do you not have a lot of faith in those SAMS books?

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA & Access (XP)

    My remark was only based on the title of the book, it was not meant to disparage its quality.

  15. #15
    Lounger
    Join Date
    May 2002
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA & Access (XP)

    Oh, good.

    I thought it was a joke, but then I thought that maybe you didn't think much of those SAMS books!

    Anyway, thanks again, I'll be sure to post back! LOL!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •