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

 

[xenou]

This appears to work:

SELECT *
FROM tblFilm IN 'Database2.accdb'[[Forms].Form1.txtPath];

Design-wise it corresponds to having Database2.accdb in the Source Database property, and [Forms].Form1.txtPath in the Source Connect Str property. Having a play with it seems to show that the connect str can have the folder path or full file path, works either way. But the source database property needs the Database name.

Although this doesn't look clean since you would need two textboxes really - one for the path, one for the database name. Unless some kind of string parsing comes to the rescue (or other combination works that is not yet discovered by one of use).

 

[MUKESHY12390]

I'm glad you came with another solution,but my situation doesn't allow me to use,

Thanks
Mukesh Y

 

[welshgasman]

I believe you are confusing VBA and SQL syntax?

I used the builder to create the SQL below which works.

SELECT [old Transactions].*, [old Transactions].Category
FROM [old Transactions]
WHERE ((([old Transactions].Category) In ([Forms]![Form1]![txtCriteria])));

Do not include strSQL=
That is to build a string to execute from VBA
However

strSQL="SELECT [old Transactions].*, [old Transactions].Category FROM [old Transactions] WHERE ((([old Transactions].Category) In ([Forms]![Form1]![txtCriteria])));" should still work

Old Transactions is just a table I used to test. As mentioned form has to be open for this to work.?
HTH

 

[MUKESHY12390]

SELECT [old Transactions].*, [old Transactions].Category
FROM [old Transactions]
WHERE ((([old Transactions].Category) In ([Forms]![Form1]![txtCriteria])));

I'm creating select query for another database (MS Access) which saved in shared drive, in above query you have used table which already exist in database.

could you tell me how to give path reference in above query.

this is syntax :

SELECT * FROM MyTable IN "C:\Test.mdb"

I

 

[xenou]

I think the nub here is what to put in the form field (i.e., what is the value we are reading in [Forms]![Form1]![txtCriteria]).
I don't know the answer. I believe that what Mukeshy would *like* to enter is the full path to the remote database (C:\somefolder\somedatabase.accdb). Also I gather that we are limited by note being able to use VBA, but having to use/edit an MSAccess macro instead (which is not my strong suit).

Edit:
note that for clarity to readers of the post, we are talking about an MSAccess remote query, such as:

SELECT [Orders Qry].* FROM [Orders Qry] IN 'C:\msoffice97\Office\Samples\Northwind.mdb';

Description of Remote queries here: http://www.vb123.com.au/200108_gr_remote.htm

 

[welshgasman]

Ok, never done anything like this before, so was intrigued to say the least. Also learnt something.

This worked for me

strSQL = "SELECT * from tblclient in '" & [Forms]![frmTable1]![txtData] & "'"

The form had
F:\Users\Paul\Documents\Commissions.accdb

in the data field

However that is VBA which we are now saying cannot be used.?

All I can think of is to create the sql for the query, save it in the qdf of that query (or make a copy and delete?) and then run the query from the macro?

 

[MUKESHY12390]

I believe you have mistaken.

This is vba code work perfectly.
strSQL = "SELECT * from tblclient in '" & [Forms]![frmTable1]![txtData] & "'"

what I'm trying : build a MS Access query where I can replace "C:\Test.mdb" with form reference like

"SELECT * from tblclient in '" & [Forms]![frmTable1]![txtData] & "'"

above line is not working in ms query.

 

[xenou]

I'm pretty the "working" version employed VBA - so we can't use VBA. I imagine reconstructing the SQL query def and saving also envisions VBA so that's probably off the table too. I'm not very good with macros so not really sure (basically I've never used msaccess macros except in a handful of cases).

 

[welshgasman]

Got me stumped.
Whatever you put for IN it takes literally?
I even tried Tempvars and it did the same thing.?

I think the o/p is going to have to rethink how it will all work.?

Easily doable with VBA, so why cripple yourself using macroes?

 

[xenou]

Well for what its worth MSAccess macros have a different security context so I can imagine that as being one reason for using them. But that's speculation. It's possible in some workplaces you can't even use VBA - though I'd probably go nuts in such a place. Probably as a useful exercise it would be great to build a good, working, and properly functional database using no vba - just to discover and implement all the great builtin features of Access. But who has time for useful exercises