Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Add code to controls programmatically (2000)

    Is it possible to programmatically add code to a control that has been created using CreateControl? I am trying to create a form that uses a crosstab query as its recordsource. As a result I don't know in advance how many fields to allow for in the detail section. I thought I could get around this by stepping through a recordset based on the same crosstab query and adding each of the controls that I need using CreateControl. The controls all need to respond to the OnDoubleClickEvent but I'm not sure how to do get them to do this.

    The whole thing is theoretical at the moment. I've never used CreateControl before so if anybody knows of any pitfalls I'd be very grateful to hear of them.

    Ian

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

    Re: Add code to controls programmatically (2000)

    Yes, you can write code with code. In the following, I assume that you have a Form object frm that is open in design view and that you have created a Control object ctl.

    This code will open the form module, insert a DlbClick event handler, and insert a single line of code in the event handler.

    ' Declare variables
    Dim mdl As Module
    Dim lngEventLine As Long
    ' Class module associated with form
    Set mdl = frm.Module
    ' Create event handler for button click
    lngEventLine = mdl.CreateEventProc("DblClick", ctl.name)
    ' Delete empty line
    mdl.DeleteLines lngEventLine + 1, 1
    ' Insert event handler body
    mdl.InsertLines lngEventLine + 1, vbTab & "MsgBox ""This is the double click event handler"""
    ' Release object memory
    Set mdl = Nothing

  3. #3
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add code to controls programmatically (2000)

    Thanks for the code sample, some good stuff in there, but I have thought about the problem and I now think that I was asking the wrong question. What I really need to do is alter the crosstab query's column headings property before opening the form. Is this possible?

    The query in question looks at appointments by the rooms that they take place in, with the appointment time as a row heading and the room ID as the column heading. I now realise that I want all the rooms to appear, even if there are no appointments taking place in them. What I don't know in advance is how many rooms there are.

    What I would like to do is read the rooms table, create a string of room IDs then write this to the query's column headings property. Also, does anybody know how to hide a column on a datasheet form? Making the controls invisible doesn't seem to work.

    Thanks again for everybody's help.

    Ian

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

    Re: Add code to controls programmatically (2000)

    If you want *all* of the rooms to appear, it would seem to be a one-time action; perhaps you don't need code for that. If you do want to do it in code, the column headings appear in the SQL of the query in the form

    ... PIVOT fieldname IN ('column1', 'column2', ...)

    So you would have to use the DAO TableDef object, or open the table as a DAO or ADO recordset, and loop through the Fields collection to assemble the IN(...) part. Can you take it from there, or do you need more assistance?

    Personally, I avoid datasheet forms. They leave the end user too much freedom to muck things up. You can create a continuous form that looks much like a datasheet form to the user, but gives you as developer much more control over how it is displayed.

  5. #5
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add code to controls programmatically (2000)

    I agree with your comments about datasheet forms and normally don't use them but the users want to be able to add/change rooms as their business evolves and so I do not know how many controls to add to the form. My solution was to add too many then hide the ones they don't need. This is a bit messy with standard form view as the invisible controls will make the form much wider than it needs to be. Datasheet view seems to accomodate hidden columns more elegantly. Is there a way to hide datasheet columns in the form's Load event.

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

    Re: Add code to controls programmatically (2000)

    The ColumnHidden property of a control on a form determines if it is hidden in datasheet view.

  7. #7
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add code to controls programmatically (2000)

    That worked, thank you.

    Incidentally, it's fast aproaching 11 pm here in the UK (only sad programmers facing deadlines are not drunk or watching gameshows) but still my queries are answered in a matter of minutes. It doesn't seem to matter what time I post I still get swift replies. How do you do it? Is there a chain of you around the globe?

    Ian

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

    Re: Add code to controls programmatically (2000)

    There certainly isn't a chain of me around the globe <img src=/S/grin.gif border=0 alt=grin width=15 height=15>, but yes, there are knowledgeable Loungers in many time zones, so you have a good chance of getting replies 24 hours a day. The weekend, especially Sunday, tends to be quiet, though.

  9. #9
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Add code to controls programmatically (2000)

    Hi Ian,
    Actually, sometimes I think HansV is the chain around the globe - I suspect he programs in his sleep! <img src=/S/grin.gif border=0 alt=grin width=15 height=15> Not only that but his answers are nearly flawless - a real asset to WOPR and the MS Office community in general. <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30> and thanks Hans.
    Wendell

Posting Permissions

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