Results 1 to 13 of 13
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    104
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Passing arguments to embed in ctrl names (A2k-SR1)

    Hi,

    I would appreciate help in simplifying the following code so that I can pass it an argument and get it to do what I want rather than having repetitive code.

    The poor code is :

    <font color=red>Sub PopulateStd(Element As String)

    On Error GoTo Sub_Exit

    Select Case Element
    Case "Fe"
    Forms![Assay Sets]!ctlStdSets![Fe] = DLookup("[Fe]", "Standards", "[StandardId] = " & Forms![Assay Sets]!cboFeStdID)
    Case "Fe2"
    Forms![Assay Sets]!ctlStdSets![Fe2] = DLookup("[Fe2]", "Standards", "[StandardId] = " & Forms![Assay Sets]!cboFe2StdID)
    'another 17 case statements here !
    Case . . . . . . . . .

    End Select

    Sub_Exit:

    End Sub</font color=red>

    I have simplified it as follows, but can't figure out how to change the term on the left of the second "=" to make it change as the argument passed changes. As it stands below, the code will do the same as the first Case "Fe" statement above if "Fe" is passed to it, but is incorrect for all other elements as the "Fe" field name is hard coded.

    <font color=red>Sub PopulateStd(Element As String)

    Dim strCbo As String

    strCbo = "Forms![Assay Sets]!cbo" & Element & "StdID"

    Forms![Assay Sets]!ctlStdSets![Fe] = DLookup(Element, "Standards", "[StandardId] = " & strCbo)

    End Sub</font color=red>

    Thanks,

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

    Re: Passing arguments to embed in ctrl names (A2k-SR1)

    From your code, I gather that ctlStdSets is a subform.

    If so, you can use

    Forms![Assay Sets]!ctlStdSets.Controls(Element) = ...

  3. #3
    2 Star Lounger
    Join Date
    May 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    104
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passing arguments to embed in ctrl names (A2k-SR1)

    Excellent - thanks Hans - I knew there would be an elegant solution and 19 Case statements wasn't it !

    BTW I think I have twisted a few arms to send me on a course. From the sort of questions you have seen me posting, do you think I should take the plunge and do an advanced course ?

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Passing arguments to embed in ctrl names (A2k-SR1)

    I'm afraid you may be disappointed in "advanced" Access courses. In most of them I've seen, the "advanced" part is teaching you how to build macros or maybe use the wizards. I have yet to see one that actually addresses things like classes and automation.
    Charlotte

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

    Re: Passing arguments to embed in ctrl names (A2k-SR1)

    I'm with Charlotte - I did take an advanced course several years ago that actually got into the finer points of programming in Access 2 - it was 2+ days a very intense. And I haven't seen anything like it since. We do have a series done here in Denver by a guy called the ToolMan that covers lots of arcane subjects in half day seminars who actually taught that course, but the company went out of business (more or less) and he now works for MS. Bottom line is to very carefully check any advanced course sylabus before you commit to it. It should have lots of VBA content, and lots of more advance topics like working with ADO, security, creating add-ins, doing OLE Automation, using ActiveX controls, that sort of stuff. You might look at some of the advanced topics in "The Access Developer's Handbook" as a set of topics for example. If you do find a good course, please spread the word.
    Wendell

  6. #6
    2 Star Lounger
    Join Date
    May 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    104
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passing arguments to embed in ctrl names (A2k-SR1)

    I was half expecting a response like that.

    I am more than half of the way through the "Access 2000 Bible" and I can see it is not going into enough detail on the sort of topics you listed. There have been fleeting references to DAO and ADO for example, but there is not nearly enough detail. The book has been great for the UI side and queries, etc.

    I certainly will check out the course sylabus carefully, otherwise I will look at getting something like the Developer's Handbook you mentioned. It is just a frustratingly slow process doing it from a book.

    Thanks for the suggestions . . .

  7. #7
    2 Star Lounger
    Join Date
    May 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    104
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passing arguments to embed in ctrl names (A2k-SR1)

    Hi Wendell,

    Is this the book set to which you referred ?

    - Access 2000 and Visual Basic language developer's set by: Ken Getz , Mile Gilbert, Paul Litwin Paperback (boxed set) CD-ROM - 3500pp (15 Jun 2000) Sybex International; ISBN: 0-7821-2785-1

    The above is the 3 volume set with the 3rd volume covering VBA as far as I can tell. There ia also a 2 volume set. The approx prices from a South African bookstore are $130 and $235 whilst for the 2 volume set from Amazon it is $70 + shipping. No need to guess where I would buy it !

    I wonder if there is an XP version available or would the 2000 version be more than adequate ? I don't foresee moving on to Access XP immediately and once I have mastered 2000 I am sure I would be able to cope with incremental changes.

    Any comments ?

    Thanks,

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Passing arguments to embed in ctrl names (A2k-SR1)

    The 3-volume set includes the VB Language Developer's Handbook, which is useful if you use VB or Office automation. For Access itself, the 2-volume set is enough. There is a set of handbooks for 2002 as well, but it is mostly the same. If you are going to work in Access 2000, there are a few things in the 2002 set that would confuse you because of the differences between the two versions.
    Charlotte

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

    Re: Passing arguments to embed in ctrl names (A2k-SR1)

    Those are the books I was referring to, though I've not seen the 3-volume set. There is also a XP/2002 version of the books now shown on Amazon and it looks to be available. The changes in most respects are either incremental or enhanced functionality, and at least the 2000 version is very good about telling you what was new. Another book you might consider is Helen Feddema's Access 2002 Inside Out - it comes at things in a bit more tutorial style, where the Handbook series is more focused on problem solving.

    A general comment about learning from books versus taking courses: At least in my case, I tend to use books in a problem solving mode most of the time, so you end up digging into heavy technical details, but with a narrow scope. The advantage of a course, at least a good one, is that you get a much broader exposure to what Access can and can't do. So I do take a course of some sort every few years just to expand my horizions occasionally. One possibility is some of the online courseware - I have not looked at it, but it might offer you some options you can't get in South Africa.
    Wendell

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

    Re: Passing arguments to embed in ctrl names (A2k-SR1)

    Another book you might find useful is Alison Balter's "Mastering Access 2000" - there are versions for 97 and 2002 as well. I've done some of her course work and liked her style, and her books are filled with real world examples which lots of people find useful.
    Wendell

  11. #11
    2 Star Lounger
    Join Date
    May 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    104
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passing arguments to embed in ctrl names (A2k-SR1)

    Hi Wendell and Charlotte, thanks for your suggestions.

    What about "Professional Access 2000 programming by: Felipe Martins , Ian Blackburn, Robin Dewson, Scott Hanselman, Hope Hatfield" ? I read through all the contents pages at Amazon and it looked perfectly suitable - certainly if the reviews are to be believed. It includes .adp stuff and migrating to SQL and web data pages, some or all of which I may want to implement here when we link our production system to the financial system (AccPac on MS SQL Server).

    The reason I ask is that it is immediately available, is about $75 and I have a work colleague who can bring it back to Zambia this weekend. I had visions of the DHL (or whichever) courier from Amazon getting lost with my book and snail mail isn't an option here either <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Thanks

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

    Re: Passing arguments to embed in ctrl names (A2k-SR1)

    I don't have any first-hand (or second hand) knowledge of it as I've never even looked at it. But I plan to make a foray to a bookstore that I'm sure will have it today, and let you know what I think at first glance. BTW Allison Balter has a complete new book on the topic of migriating to SQL Server, web pages etc. that won't be available until 20 June, but the description of it sounds good. It will undoubtedly cover some of the subject matter in Vol 2 of the Developer's handbook. And sorry about putting you in the country - I knew better too!
    Wendell

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

    Re: Passing arguments to embed in ctrl names (A2k-SR1)

    I did get a chance to peak at it - it is one of the WROX books (they have a very helpful web site by the way at http://www.wrox.com) and it looks to be a pretty decent book. It tends to be more tutorial, which may be exactly what you are looking for. I have used several of their books and have been generally pleased with the approach and the content. Sorry about the delay in responding - had a very late night.
    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
  •