Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2010
    Posts
    4

    Unanswered: VBA CreateControl Button

    I've been stalking the forums here for a couple weeks now. I've found lots of great stuff that has been tremendously helpful.

    I have a problem now that I just can't find the solution to. First, a little background. I have a form that I am creating dynamically, including creating all the controls. The reason I went this way is because I don't know before runtime what controls are needed. It could be 5, it could be 50. The application allows the user to define their own data input fields. Creating hidden controls and showing them when needed was not an option. I have all of that working perfectly, with the exception of command buttons. One of the custom fields a user can create is a "file upload" field. Basically, it's a text box with a command button that allows them to browse their hard drive and select a file. I can't figure out how to code the event procedure for the button though. What I would like would be something like:

    Code:
     ' Create browse button
    Set ctl = CreateControl("frmCollectData", acCommandButton, acDetail, "", "", iCtlLeft + ctl.Width + 50, iCtlTop)
    With ctl
         .Name = "cmdBrowse"
         .Caption = "Browse..."
         .Height = iCtlHeight
         .Width = 1000
         .FontSize = iFontSize
         .OnClick = "= FileBrowse("""cmdBrowse""")"
    End With
    How do I code the .OnClick event to call a function or sub????? I've tried calling a macro that then calls "Run Code", but I have not been successful with that.

    Any help would be greatly appreciated!

  2. #2
    Join Date
    Apr 2010
    Posts
    4
    I think I answered my own question.

    It appears you can't call a sub, but you can call a function.

    Code:
    ' Create browse button
    Set ctl = CreateControl("frmCollectData", acCommandButton, acDetail, "", "", iCtlLeft + ctl.Width + 50, iCtlTop)
    With ctl
         .Name = "cmdBrowse" & rs![intStepID]
         .Caption = "Browse..."
         .Height = iCtlHeight
         .Width = 1000
         .FontSize = iFontSize
         .OnClick = "= FileBrowse(" & rs![intStepID] & ")"
    End With
    The above code works as long as "FileBrowse" is a function, not a sub.

    Good to know. I'm sure I'll be back though.

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Not sure I'd go the route of creating buttons/controls on the form within code. I may hide/unhide controls/buttons on the form but I usually frown upon actually trying to code in the creation of controls/buttons. This usually becomes problematic (if not for you, for the next developer who inherits your design.)

    Personally, I'd stick with the making visible true/false of controls and not worry about the actual creation of controls/buttons in code. I've never had the need to do this in all the coding I've ever done over the last 25+ years. It just seems to over-complicate the design and the objective is always to make things as simple as possible. Although it may 'seem' neat and 'seem' like a necessity, it's basically just a 'work-around' for something which could otherwise easily be done.

    I've never, ever found the need to create controls/buttons/etc... in code. Regardless of the situation! But as the saying goes, to each his own (and hopefully nobody else ever has to troubleshoot your design.) Keep it simple dude.
    Last edited by pkstormy; 04-26-10 at 16:43.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Apr 2010
    Posts
    4
    pkstormy,

    I won't disagree with that, except that just won't work in my case. The fields that are needed on the form are stored in the database because they are defined by the user. Basically a user can create a set of steps, or procedures. They can define the field type (text, numeric, date/time, etc) for each step. There is no way to know ahead of time what controls need to be placed on the form.

    I have everything working, and I think the code is quite clean, although I'm a web developer, not an access developer. This is also a temporary "add-on" to a fully-functional web-application.

    Thanks!

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Yes Cardinco. I've seen this kind of requirement before where the user needs to create their 'fields' of data on the fly. Have you considered a table which is 'generic' in nature and allows users to create 'fields' in an actual field called something like 'UserFieldName'? I've done something like this where I've made the form design 'generic' so to speak. It'll work if done correctly.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    It's actually a table with fields such as: UserFieldName, DataFieldType, etc...
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Apr 2010
    Posts
    4
    That's essentially what I have, and what I intended to do. I'm sure if I was Access savvy enough it could work that way. I have no idea how to bind a form to a table/query like that (create specific control types based on a db field). Not to mention validation based on control types. It seemed far more complicated to me than to create the controls at runtime.

  8. #8
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Yes. If at all possible, avoid creating controls on the form at run-time. This can really complicate things all around. But you'll need to know a bit of vba coding to make it all work which is rather difficult to describe in a post. Ideally, you'd design a form which utilizes a combination of listboxes or comboboxes to select the 'table' holding the fieldnames and then possibly the designated form name in the table to open to enter the data. But again, it's something you would've needed to have done before to know how to make it work nicely.

    Hint: You can manipulate the recordsource of a form to work with how this technique is done but can get tricky with coding.

    Perhaps someday I'll design an example and post it in the code bank to see how it's done.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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