query differences in access 2000 & 2003 using vba

[sugoi_kat]

Hi,

I'm just wondering if there is any differences in writing queries in vba in different versions of access. i'm developing a search function in access 2000, but it doesn't seem to be working. however, the same code in access 2003 works fine.

Here's an extract of the code when the search button is clicked (I'm getting table information, attribute/field information and condition they're specifying from the user. The result is then displayed in a subform):

Private Sub cmdFind_Click()
Dim searchSQL As String

Me.cmbTable.SetFocus
searchSQL = "select * from ENGINEERS INNER JOIN TEL_CABLING ON ENGINEERS.EngID = TEL_CABLING.EngID where " & Me.cmbTable.Text & "."
Debug.Print searchSQL
Me.cmbAttribute.SetFocus
searchSQL = searchSQL & Me.cmbAttribute.Text
Debug.Print searchSQL
Me.txtCondition.SetFocus
searchSQL = searchSQL & Me.txtCondition.Text
Debug.Print searchSQL

Me.subfrmTest.Form.RecordSource = searchSQL
Me.subfrmTest.Form.Requery

End Sub

The part in red is the part that is highlighted whenever i run it in access 2000. The error message is: " You entered an experession that has an invalid reference to the property RecordSource "

Can anyone tell my why it's like that and how i can fix it to work for access 2000?

Thanks I've also enclosed a picture of the form, so you can get a feel of what it looks like.

Kathy

 

[wazz]

btw: no text in red.

i guess you mean: Me.subfrmTest.Form.RecordSource = searchSQL

 

[sugoi_kat]

yeah that part:

Me.subfrmTest.Form.RecordSource = searchSQL

is it written differently for access 2000 & 2003?

Sorry about the double post, i forgot to delete it when I thought it was better to post this topic in the VBA section. I've already deleted the other thread in the query section.

 

[yeatmanj]

Isn't it simply Me.subfrmTest.recordsource without the .Form.

 

[sugoi_kat]

I tried it without the .Form part but that gave me an error message saying: "Method or Data Member Not Found" so I think it's meant to be there

 

[yeatmanj]

In 2003 I have been using Forms!mainform!subform.Form.RecordSource. In 2000 I used Forms!mainform!subform.recordsource. I find that I get more confused when I try to use the Me. short form because I can never remember . or !.

I think that in your example it should be:

(2000) Me!subfrmTest.RecordSource = searchSQL

and

(2003) Me!subfrmTest.Form.RecordSource = searchSQL

 

[sugoi_kat]

yeatmanj,

i tried replacing it with what you have suggested for Access 2000, but it still comes up with the error message: "You entered an experession that has an invalid reference to the property RecordSource". Error code is "2455"