Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2008
    Virginia, USA
    Thanked 0 Times in 0 Posts

    Variable populates in Access 2003, not in 2007

    I have a list box on a form that filters records in the form successfully in Access 2003 and 2007. The afterupdate event reads:

    Me.RecordSource = "Select * from [tbl_Instructions] WHERE [topic] = " & Chr(34) & Me.UniqueTopics.Column(0) & Chr(34)
    sTopic = Me.UniqueTopics.Column(0)

    A report using sTopic to filter records works well, and "msgbox sTopic" successfully produces expected results in 2003. No problem.

    However, using the same appoach in Access 2007 the sTopic variable does not populate using the code above. ???

    For what it's worth, both database call a function to establish sTopic as a public string variable upon opening. And the Access 2007 database retains the value until I run the code above. Please help.

  2. #2
    Star Lounger
    Join Date
    Dec 2009
    Findlay, Ohio
    Thanked 5 Times in 5 Posts
    I see you are using a filter. I found in 2007 that when I use me.filter I also had to force the me.filteron=true in my vba code for access 2007. I didn't need to do that with 2003 but for some reason 2007 wouldn't keep the filteron in the properties screen.
    Me.FilterOn = True
    Me.Filter = "label_name='" & global_groupname & "'"

  3. #3
    New Lounger
    Join Date
    Mar 2013
    Thanked 1 Time in 1 Post

    Some thoughts using limited information

    First off, you do not need to call a function to establish sTopic as a public string variable. Just put this line at the top of any Access VBA module:

    Public sTopic as String

    That takes care of the public nature of the variable. I don't know what your function does to establish the public nature of the string, but that approach is foreign to me. I tend to have a module in every Access database named basGlobals which collects into one place all of my global variables (very few!) and global contstants (lots and lots). I don't like to spread around my global/public declarations.

    Next, put a break point on the assignment line sTopic = Me.UniqueTopics.Column(0) in your Access 2007 code. Check the value of Me.UniqueTopics.Column(0) at that very instant. What do you see? If it looks good, step one line further and then check the value of sTopic. What do you see?

    You might also try using Me.UniqueTopics.Value if you indeed have column(0) as the data column and you are not using a multi-select list box.

    That's all I can offer without any more information. Good luck!

Posting Permissions

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