Passing a public variable to a SQL string 1

[Syerston]

The variable strReturnId at the end of this piece of code is created as a public variable in a module . However, when the SQL is run in the row source of a combo box it keeps coming back asking for the parameter instead of passing the value directly.

SELECT DISTINCTROW [NewCouncil].[Address_No], [NewCouncil].[Streetname], [NewCouncil].[TWN], [NewCouncil].[PostCode], [NewCouncil].[Ward_ID], [NewCouncil].[CID], [NewCouncil].[OldCouncil_Id] FROM NewCouncil WHERE ((([NewCouncil].[StreetName]) Like [forms]![frmMain]![txtStreet] & "*") And (([NewCouncil].[Address_No]) Like [forms]![frmMain]![txtNo] & "*") And (([NewCouncil].[OldCouncil_Id])=strReturnId));

Can anyone please help!

 

[cmmrfrds]

One way to do it is to create a public function in the standard module to return the id.

Public Function ReturnstrReturnId() as integer
ReturnstrReturnId = strReturnId
End Function

Then insert the function in your string like

[OldCouncil_Id]) = ReturnstrReturnId()

 

[Syerston]

My goodness that was quick. I only posted the thread about five minutes ago.

It worked a treat.

Many Thanks