Results 1 to 4 of 4
  1. #1
    tlkng1 is offline Novice
    Windows Vista Access 2007
    Join Date
    Nov 2014
    Posts
    12

    Opening Outside Form Using Command Button: VBA

    Good afternoon all : another newbie here.

    Due to security restrictions on our work systems, macros are not allowed and are automatically disabled for any application.

    I am in the process of creating a tracking database to track IT type helpdesk tickets. I have essentially two specific forms going at the moment; one is the Accounts form which shows the users' information and a second form that is specific for tickets. What I am trying to do is use a button on the Accounts form to open up a new Ticket form so a new ticket can be created/tracked. While I can get it to work, at home anyway, via the macro, I can't seem to find the proper coding needed to switch the macro to code so it works on my work system.

    The Accounts form and the Tickets form are linked by unique IDs. The Tickets form contains the ID, a drop down, multi-select option to identify the type of ticket needed (password reset, account unlock etc) and a date field. The idea is that when the command button is clicked to open up a new ticket form, the ID automatically transfers over.

    I tried to do the conversion from within Access, but, the "convert Macros to Visual Basic" selection is grayed out.

    I've tried the DoCmd.OpenForm option without luck. While I was getting an error prior, it is now simply not doing anything.



    Ideas?

    Thanks

    TK

  2. #2
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Is it greyed out because you have the macro in design view and not merely selected in the nav pane?
    Or are we talking about an embedded macro, in which case I believe you must have the form in design view?
    I believe the ability to convert is not being prevented by your IT dept. but by what you are doing.
    Not being a big user of macros, I thought this was controlled by the macro settings in Options > Trust Center > Trust Center Settings > Macro Settings. I didn't realize it could be prevented outside of Access.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    Very few experienced developers use embedded Macros, because they're simply not as flexible as code, and because you can't go into one or two windows and see what's going on, you have to open each and every Macro.

    You also need to know that the native 'Convert Macro to code' function doesn't really do a very good job, much of the time, and understanding what needs to be changed, with the code thus produced, to get it working, requires a knowledge of VBA...thus it's probably best to use code in the beginning, rather than using Macros for development and then having to convert it to code for distribution.

    To be honest, the situation you're describing, here, is a classic one-to-many relationship...one Account-to-many Tickets, and the easiest way to do this would be to use a Main Form/Subform construct, with the Accounts Table being the basis for the Main Form and the Tickets Table being the same for the Subform.

    The two would be linked by the ID field, and it would automatically be part of the Subform Record, without anything else being done.

    Linq ;0)>
    Last edited by Missinglinq; 06-14-2017 at 01:17 PM.
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    tlkng1 is offline Novice
    Windows Vista Access 2007
    Join Date
    Nov 2014
    Posts
    12
    I did figure out the code sequence to open the Tickets Form and add a new record, but, as indicated, I am also looking at using the subform idea in order to display all of the tickets the User has requested.

    For info purposes for readers, the code to open the form in Add mode was:

    Public Sub Tickets_Click()
    DoCmd.OpenForm "Tickets", , , , acFormAdd
    End Sub

    While I am at a very basic level here, further intent is to be able to view all tickets a user has requested form within their accounts record, which, as previously mentioned will be a subform, as well as create reports that can pull the monthly tickets created, by type and date, for each associated User ID.

    I still have some research to do on how to auto-populate fields; for example, using the Ticket Request command button on the user's Account form to open the Ticket form and having it auto-populate the UserID from the Account page.

    I appreciate the help

    TK

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

Similar Threads

  1. Replies: 1
    Last Post: 05-06-2015, 01:23 PM
  2. Replies: 6
    Last Post: 07-20-2014, 03:59 PM
  3. Replies: 5
    Last Post: 05-10-2014, 12:25 PM
  4. Replies: 10
    Last Post: 02-12-2013, 05:04 PM
  5. Opening new Tab with command button
    By JFo in forum Access
    Replies: 2
    Last Post: 09-12-2011, 01:45 AM

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