dlookup and filter question

[Guest]

I want to be able to use a combo box to set the criteria for a filter. My
code works, except if you search for the same name twice in a row (even after
showing all the records again) it changes the data values (from "jaimie" to
"anne" or some such). I don't understand why, so if anyone has any clue
(code below) I'd greatly appreciate it!

Sub Go_Click()
Dim Names, namefilter As String 'declare variables
Dim frm As Form

'lookup value for "name" in from viewlessonsbystudent3 and store as names
Names = DLookup("[Name]", "viewbystudent", "[name]='" &
Forms!viewlessonsbystudent3!Name & "'")

'build criteria for filtering for a given name and store as variable
namefilter
namefilter = BuildCriteria("Name", dbText, Names)

'open form
DoCmd.OpenForm "viewbystudent4"

'Return Form object variable set to "viewlessonsbystudent3".
Set frm = Forms!viewbystudent4

'filter records for that user selected (stored in names variable)
frm.filter = namefilter
FilterOn = True

'close form
DoCmd.Close acForm, "viewlessonsbystudent3"

End Sub

 

[John Vinson]

I want to be able to use a combo box to set the criteria for a filter. My
code works, except if you search for the same name twice in a row (even after
showing all the records again) it changes the data values (from "jaimie" to
"anne" or some such). I don't understand why, so if anyone has any clue
(code below) I'd greatly appreciate it!

I'm almost certain that what's happening is that you're using a BOUND
combo box for the search criterion. It's not the DLookUp, it's the
combo box itself.

A combo has two funtctions: a) to select a row from its Rowsource
query and b) to store the selected row into its Control Source. If
you're just using the combo box for searching then you don't WANT to
store the value into the current record - you just want to use it in
your code to find some *other* record.

Change the Control Source property of this combo box from [Name] to
nothing at all and it won't keep storing the data.

John W. Vinson[MVP]

 

[Guest]

That would be why the instructions for the combo box left it unbound....

Thanks very much!

I want to be able to use a combo box to set the criteria for a filter. My
code works, except if you search for the same name twice in a row (even after
showing all the records again) it changes the data values (from "jaimie" to
"anne" or some such). I don't understand why, so if anyone has any clue
(code below) I'd greatly appreciate it!

I'm almost certain that what's happening is that you're using a BOUND
combo box for the search criterion. It's not the DLookUp, it's the
combo box itself.

A combo has two funtctions: a) to select a row from its Rowsource
query and b) to store the selected row into its Control Source. If
you're just using the combo box for searching then you don't WANT to
store the value into the current record - you just want to use it in
your code to find some *other* record.

Change the Control Source property of this combo box from [Name] to
nothing at all and it won't keep storing the data.

John W. Vinson[MVP]