Me.RecordSource Problemo

[Akart]

Hi there,

I have a little problemo.

I want to change the recordsource property of a form using the method descibed below:

This method works;

dim tempfield
tempfield = 1000

me.recordsource = "SELECT DISTINCT Customers_AU.* FROM Customers_AU INNER JOIN Invoices_AU ON Customers_AU.CustomerId = Invoices_AU.CustomerID WHERE (Invoices_AU.InvoiceID > '" & tempfield & "')"

But i want to do the following which returns "Invalid use of me keyword".

dim tempfield
dim sql

tempfield = 1000
sql = "SELECT DISTINCT Customers_AU.* FROM Customers_AU INNER JOIN Invoices_AU ON Customers_AU.CustomerId = Invoices_AU.CustomerID WHERE (Invoices_AU.InvoiceID > '" & tempfield & "')"

Me.RecordSource = sql

Can anyone tell me a way to do this?

Regardos Akartos =)

 

[cmmrfrds]

It should be no problem to equate the record source to a variable. I don't see anything wrong at first glance.

Try making the variable a string instead of a variant.
Dim sql as string

Me.RecordSource = sql

 

[Akart]

Ok,

I fixed this one myself and after doing so realised that noone could have known the answer without knowing more about the way i coded it....

The problem was the code was in a module.... and the me.recordsource does not seem to work from a module.
so i made a button and put this in it.

SearchEngine ' This is the module that sets sql to the "SELECT etc

Me.RecordSource = sql

This worked.

I'll make sure i mention more next time.

 

[cmmrfrds]

ME, in Access is a short cut way to refer to a Form or Report that you are working (vba code) in so it has no definition outside of those bounds.

Me.RecordSource
Forms!myForm.RecordSource

 

[jjlogan]

Access 2000 introduced many new classes and objects. If you are using this version, this is what I recommend trying to make this property change when you are outside the form itself. I would would write a procedure in a standard module that references the AllForm class (contains all forms, open and closed). Then refer to the specific form you want to change and make the property change at that time. Note: to make a property change, the names form must be opened in this procedure in Design mode. See help file under working with AllForms class.

Jeff