Filter and update access based on VBA variant (array)

[poduk]

Hi

Hoping someone on here can help me out with my problem.

I have posted this on the excel page but I think it maybe more linked to access now.

I have two variants (arrays) in my coding

heading() ' linked to the headings
vRecord() ' link to the value of the cell\record

I am struggling with the filter at the moment. If i use hard code, like below, it filters the database based on the LastName of jones.

recordset.Filter = "LastName = 'Jones' "

I have tried a few different styles of code

rs.Filter = "heading(1)='" & vRecord(0) & "'"

Fails with "Run time error '3001'"
and

rs.Filter = 'heading(2)=vRecord(2)'

fails with "syntax error"

any idea?

Thanks

Phil

 

[Micron]

Some things you can do:
- next time, post the error message so that we don't have to go and figure out what the message is for the 10,000 error numbers (this one is Application-defined or object-defined error?)
- try to be consistent when posting your tests; e.g. don't change too many things at once, such as quotes and array item numbers

Did you ensure that none of the array elements were null by checking what heading(2) is for example?
If the array element was incorrect, I think the message would be 'Subscript out of range' - not likely the issue.
By 'heading' I guess you mean that's a valid field name for the recordset. The message you got indicates there is something wrong with how you're using an application object, property or method OR a field or user defined object reference is invalid - something along those lines.

 

[xenou]

Try:
rs.Filter = heading(1) & " = " & "'" & vRecord(0) & "'"
or
rs.Filter = heading(2) & " = " & "'" & vRecord(2) & "'"


Also view your results:
Debug.Print heading(1) & " = " & "'" & vRecord(0) & "'"
Debug.Print heading(2) & " = " & "'" & vRecord(2) & "'"


Depending on what you are trying to accomplish maybe some other way is better, since this code is obviously awkward when you are building raw strings with so many pieces/parts - but not sure.

 

[xenou]

Another idea :

myFilter = "header = 'value'"
myFilter = replace(myFilter,"value",vRecord(0),1)
myFilter = replace(myFilter,"heading",heading(1),1)
rs.Filter = myFilter

Note: working from back to front so as to minimize the possibility of catching a match on the words header or value in the actual replacement strings. That is, replacing the value first, then the header. And reminder that replace() is case sensitive on the matching.

 

[poduk]

Hi

I will try these later and report back the results.

Thanks

Phil

 

[poduk]

Another idea :

myFilter = "header = 'value'"
myFilter = replace(myFilter,"value",vRecord(0),1)
myFilter = replace(myFilter,"heading",heading(1),1)
rs.Filter = myFilter

Note: working from back to front so as to minimize the possibility of catching a match on the words header or value in the actual replacement strings. That is, replacing the value first, then the header. And reminder that replace() is case sensitive on the matching.

This worked, thank you.