Form reference in MS Access query

[MUKESHY12390]

Hi All,

I'm trying to build query by using form reference but it's not working.

Can anyone point me toward right direction.

SELECT *FROM tblFilm in [forms]![form1]![txtPath]

'------ this below one works perfectly -------

SELECT *FROM tblFilm in 'D:\Training stuff\BU\2copy.mdb'

Any help would be appreciated.

Thanks
Mukesh Y

 

[welshgasman]

You would need to concatenate the form control value with the sql string

strSQL = "SELECT * FROM tblFilm in '" &  [forms]![form1]![txtPath]  & "'"

 

[MUKESHY12390]

thanks for your quick reply.


I need to put this in query ( ms Access query design view) how I can do that what part I have to remove?

strSQL = "SELECT * FROM tblFilm in '" & [forms]![form1]![txtPath] & "'"

 

[xenou]

In the query property sheet you enter this as the Source Database:
[forms]![form1]![txtPath]

In my opinion just as easy to use SQL View, in which case use the query as described above:
strSQL = "SELECT * FROM tblFilm in '" & [forms]![form1]![txtPath] & "'"

If you do either one, you can see the result switching back and forth from SQL view and Design View.

 

[MUKESHY12390]

In the query property sheet you enter this as the Source Database:
[forms]![form1]![txtPath] --------------------------------I have tried but this not work.


I'm looking for SQL view only.

could you tell me what would be query in SQL view , I have tried many time but couldn't pass.


Thanks for reply.

 

[xenou]

It is just as described above (although I do notice one missing double quote in the above posts, which is fixed below):
SELECT * FROM tblFilm in "'" & [forms]![form1]![txtPath] & "'"

This will only work if Form1 is open and has a valid path to an existing database with the expected table in it.

 

[xenou]

apologies no it doesn't work with the Forms!Form!txtBox syntax - or rather the database accepts the syntax but fails at runtime.

 

[MUKESHY12390]

so is there any solution? Non VBA solution.

 

[xenou]

I doubt it. It's pretty straightforward so either it works or it doesn't

(well, of course you could just add tblFilms as a linked table in the database but I assume you have a reason you are not doing that already).

 

[MUKESHY12390]

thanks for reply.

I'm working on macro someone built in past not allowed to changed it.

I have strong feeling there is solution to incorporate form reference in this scenario, but don't know how to do it now

I will share if I get anything on this.


thanks again

Mukesh Y