How to set RecordSource property on the fly?

[siebel2002]

Hi gurus:

I am getting an error "You Cancelled the previous operation" when I run the following code. It does pull in the value for GetFirmID and GetContacID from the global module, but it still gives the nasty error.. please help!

I have left the Record Source property in the form's design view blank. It runs OK on the query design grid when I input the value of [User Fields 1] & [User Field 2] in the criteria row!

Private Sub Form_Open(Cancel As Integer)
Me.RecordSource = "SELECT * From Contacts Where [User Field 1] = " & GetFirmID & " And [User Field 2] = " & GetContactID
End Sub

Thank you so much

 

[thornmastr]

You are very close. The reason it is not working is that even though the form is opening, it is not yet the current form. While it is opening it cannot even get the focus. What this means to you is that in this instance me is referring to the object that sill has the focus.
Change your code a bit, ie,

Me.recordset should be forms!frmnameyouareopening.recordsource = etc.

Watch the word wrap above. Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[galar]

I hope that thornmastr was right, although I disagree with him.
I'm using the same code you've written in many cases and I don't have problems with it. Ususally, this message points on wrong sql statment. Maybe, you're trying to treat text type field as if it was a number type field?

 

[siebel2002]

Thanks guys! The remedy was in correcting the datatype of FirmID and ContactID to stringtype and adding the necessary single quotes .

Cheers!

 

[thornmastr]

Sorry, i am wrong. I included on the open event of a form, the statement debug.print me.name
which did give the name of the form being opened.

Galar, thanks for raising the issue. Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com