problem in an Access adp file

Question

problem in an Access adp file

mattbristow

SSC Veteran

Points: 240
More actions
April 11, 2005 at 5:48 am

#65848

I have an access database which I have created a form that when a user fills it out and submits it will search a field in a given table, the form uses the following query:
SELECT Tbl_Centre.CentreID, Tbl_Centre.CentreName, Tbl_Centre.CentreNo, Tbl_Centre.CentreAdd1, Tbl_Centre.CentreAdd2, Tbl_Centre.CentreTown, Tbl_Centre.CentreCounty, Tbl_Centre.CentrePCode1, Tbl_Centre.CentrePCode2
FROM Tbl_Centre
WHERE (((Tbl_Centre.CentreTown)=[Forms]![Frm_SearchCentre]![SearchCentre]));
This works fine, however I have to upsize the database to SQL Server and when I do so it throws an error.
the SQL once upsized looks like this:
SELECT     CentreID, CentreName, CentreNo, CentreAdd1, CentreAdd2, CentreTown, CentreCounty, CentrePCode1, CentrePCode2
FROM         dbo.Tbl_Centre
WHERE     (CentreTown = @Forms_Frm_SearchCentre_SearchCentre)
the error I get is:
Microsoft Access can't find the object'dbo.QryFrmSearchCentre
But it is clearly there, anyone have any ideas?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

kbrown@pccsinc.org Old Hand Points: 366 More actions · Answer 1

mattbristow,

I'm not sure from where you are calling "QryFrmSearchCentre", but if it is used as the RecordSource for a form, you might try dropping the "dbo". I've had a similar problem.

Forms have two properties RecordSource and RecordSourceQualifier that I sometimes have problems with in binding a form to a Stored Procedure. If the RecordSource property is dbo.QryFrmSearchCentre then, you might try changing the RecordSource property to just QryFrmSearchCentre and set the RecordSourceQualifier property to dbo.

Hope that helps.

Kyle.

Chris Mackin-166947 SSChasing Mays Points: 653 More actions · Answer 2

Take your SQL string and set that as the recordsource. So something like:

dim strSQL as string

strSQl = "SELECT CentreID, CentreName, CentreNo, CentreAdd1, CentreAdd2, CentreTown, CentreCounty, CentrePCode1, CentrePCode2

FROM dbo.Tbl_Centre

WHERE (CentreTown = " & @Forms_Frm_SearchCentre_SearchCentre)

(assuming @Forms_Frm_SearchCentre_SearchCentre evaluates to a numeric value, otherwise use the ' qualifier around it)

Then have:

Me.Recordsource = strSQL

Me.Requery

The issue is that once this value is passed to SQL Server, the server cannot reac values from your forms so you need to evaluate the variable in the front end and pass the complete SQL string to SQL Server.

-Mackin