Variable populates in Access 2003, not in 2007

Topic

New Reply

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 https://www.askwoody.com/forums/topic/variable-populates-in-access-2003-not-in-2007/ = ” & 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.

Reply | Quote

Replies

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.
i.e.
Me.FilterOn = True
Me.Filter = “label_name='” & global_groupname & “‘”

Reply | Quote

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!

Reply | Quote