Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    generic code (Access 2000)

    I have the folllowing code that repeats in each object

    Dim strDocName As String
    strDocName = "frmMain"
    Dim Department As Control
    Set Department = Forms![frmMain]![Department]
    If IsOpen(strDocName) = True Then
    If Department = 1 Then
    do someting
    ElseIf Department = 2 Then
    do something
    ElseIf Department = 3 Then
    do something
    ElseIf Department = 4 Then
    do someting
    ElseIf Department = 5 Then
    do something
    ElseIf Department = 6 Then
    do something
    End If

    And here is an example:

    Dim strDocName As String
    strDocName = "frmMain"
    Dim Department As Control
    Set Department = Forms![frmMain]![Department]
    If IsOpen(strDocName) = True Then
    If Department = 1 Then
    base = Ste
    ElseIf Department = 2 Then
    base = Va
    ElseIf Department = 3 Then
    base = Bl
    ElseIf Department = 4 Then
    base = Ha
    ElseIf Department = 5 Then
    base = Pl
    ElseIf Department = 6 Then
    base = Ta
    End If
    Me.RecordSource = base


    I want to rationalize my coding and if possible not to repat one and the same condition many times.
    I will be very grateful if i could simplify my coding with somebody's help.
    What i repeat in many forms and reports is the condition in my main form called frmMain. In this form i have
    an option control called Department. This is a key condition in my database becasue many functions and objects depend
    on the choice in my main form.Of course they are performed only if the main form is opened.
    Is it possible that i do not describe each time these conditions and do not enumerate each time the different options,
    but write them only once.
    I imagine the code something like that:

    base = ste (Forms![frmMain]Department]
    base = Va((Forms![frmMain]Department])

    It didnt work for me so i am asking for help, and but may be somebody might give me a better advice.So in short i want to refer to the

    options
    in my main control without enumerating them each time
    Thanks in advance

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

    Re: generic code (Access 2000)

    I see that your code sets a string variable for "frmMain", but then you go ahead and hard code a reference to Forms!frmMain into the code, which doesn't make much sense. I'm also getting lost in stuff like

    base = ste (Forms![frmMain]Department]
    base = Va((Forms![frmMain]Department])

    What the heck are Ste and Va? Are these functions?

    What you appear to want to do is to look at your frmMain and check the Department option group to see which department to use. Then, based on the selected department, you want to specify a particular recordsource for your form.

    Unfortunately, without having a clue as to what those "Base = Va"-type lines mean, it's hard to know what to do with this code. There are a couple of ways you could clean up and simplify it, however.

    First off, you cant set a reference to control on a form that isn't open, so you need to put the line that sets Department after the line that tests for the open form.

    Second, you can use the strDocName variable in the Set Department line like this:

    Set Department = forms(strDocName)!Department

    If, in fact, things like Va and Ste are functions (if they are, it's better to give functions meaningful names that describe what they do) and you want to pass them the control as an argument, then all you need to do is exactly that:

    Base = Va Department

    The control object carries the information about the form it's on, so you don't need the entire reference.
    Charlotte

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

    Re: generic code (Access 2000)

    One more thought. If things like Va and Ste are functions that return a SQL string, you don't need separate functions. The simplest approach is to have a single function that builds the SQL string and simply pass it the department control. Let that function determine what should be in the string based on the value of Department. That way, all you need in your calling code is something like this:

    Dim strDocName As String
    Dim ctlDepartment as Control

    strDocName = "frmMain"
    If IsOpen(strDocName) Then
    Set ctlDepartment = forms(strDocName)!Department
    Me.Recordsource = GetRecsource(ctlDepartment)
    Else
    'handle the condition where frmMain isn't open
    End If

    Then in GetRecsource, you would put the code that says if Department is 1, etc., do whatever. And you would return the result to the calling routine.
    Charlotte

  4. #4
    2 Star Lounger
    Join Date
    Nov 2001
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: generic code (Access 2000)

    Excellent. Thats what i meant and your suggestions are very useful

Posting Permissions

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