Accessing a Form field from Microsoft Access via SQL

[DIVINELIVING]

So far so good with ‘basic’ connections to my Microsoft Access database and SQL statements, but I could use some help in referencing an active form field from Microsoft Access. The queries/SQL returns the correct values within a query in Microsoft Access but not when done within MS, not at least how I have them setup…

I know that the connection to my database works because this script works:

//Connect to Datasource
Let>str=zg_live
DBConnect>str,dbH

//Perform SELECT query
Let>SQL=SELECT [Zg Inventory].ID FROM [Zg Inventory] WHERE [Zg Inventory].InventoryItemID='030-u'

DBQuery>dbh,SQL,CUSTOMERS,numrecs,numfields
messagemodal>customers_1_1

.....

but if the SQL variable is this:


Let>SQL=SELECT [Zg Inventory].ID FROM [Zg Inventory] WHERE [Zg Inventory].ID=[forms]![f_zg live]![ID]

It does not work and I get the error… too few parameters. Expected 1

The only difference in these two SQL statements ( the one above that works and the one that does not), is after the equal sign, where I am referring to the current form (which is open and, as mentioned above this query works in Access)

I also tried it with periods instead of exclamation! points.. and that also works in MS Access, but not in Macro Scheduler –

=[forms].[f_zg live].[ID]

So I tried calling a query as suggested by Marcus in one of the forums and that did not work either.

(q_live_form_ID – is the name of the query that holds the above SQL)

Let>SQL=SELECT q_live_form_ID.ID FROM q_live_form_ID

Also tried it like this ( even though there is only one field)

Let>SQL=SELECT * FROM q_live_form_ID

And it did not work either.

So it sure sounds like I am misconfiguring the call of Microsoft Access query within SQl (albeit it works in MS Access..)

Hope this makes sense. By having this working, I won’t have to attempt to access these fields directly on the form ( which for whatever reason Microsoft, does not allow the controls to be visible) and I will be able to use/have access/control these values on the form for all kinds of uses.

Thanks,


PS.. link I was referring to above: http://www.mjtnet.com/forum/mdb-file-da ... hlight=vba

[Marcus Tettmar]

You just can't do that. Your SQL only makes sense in the context of being inside Access where Access understands your where clause is based on the current form field value.

Take that outside of Access and it is meaningless. Even to the Access ADO/ODBC driver which you are using to connect. It does not understand what you mean, and even if it did there is no context.

So you'll need to find that form value some other way.

I guess the form is asking for info from the user? So one option is to ask the user directly from the script instead using either the Input command or a custom dialog.

If it all must happen inside Access then why are you doing the SQL in Macro Scheduler anyway. But you could control Access via VBScript or wrote some Access macros internally with VBA and call those from Macro Scheduler.

Difficult to advise what would be best without understanding your exact requirements, context and scenario.

[DIVINELIVING]

Thanks Marcus!

There are several scenarios.

1. A user uses a hand held barcode scanner to scan in a barcode - which is the ID that searches for an item. I'd like to capture that ID at some point and paste it into another program ( a photo program called NKRemote) and put it in their comments field as that is the name of the picture.

Currently the user needs to select the ID field from the Access form , copy it, and then go to NKremote and paste it there. It is a cumbersome process...

Since Microsoft Access does not allow to control fields, as always I was attempting to avoid press/send commands if possible... as I first need to use the mouse to click on the field... I will end up doing that if no other method works..

I like your idea of creating a macro in Access and then simply calling that macro from MS.. that will work as a charm, no VBA needed ( I have seen your other posts on how to that). thanks for that idea. The macro will put that field into an access temp table and then from within MS with SQL I will select that field. a round about way, but easily done.

If there is a quicker way of accessing that field, even better...

what would the VBscript code be?

I scoured the web and see that I can use VBscript to access a form control.. but not sure how to use it inside VB Script.. http://ss64.com/access/syntax-references.html

Not sure if there is a way of capturing that ID when it is scanned with the barcode reader.. that would be ideal... as we would have that value as soon as it is captured...

And for that matter, can MS know when the barcode reader has found a barcode? Then we could trigger another series of macros... and rather than the barcode reader entering the text into the select field, we could intercept and have more control onto where that value is inserted.

That would make the process of inventory counts so much more streamlined as we have thousands of items...

The second scenario is similar to the first but without the barcode scanner, for which the Microsoft Access macro would be a solution... and/or he vb script reading the form record

[DIVINELIVING]

I tried to open the Microsoft Access Macro, but I really need to be able to open the macro from within an already open database, since the form needs to be open... the challenge is that running macros from the command line opens a new instance of Microsoft Access which defeats the purpose. The alternative is to open the macro from VBscript... Does anyone know how to do that in MS?

Here is command line code that 'works'.. but opens in a new instance.. which is perfect for opening macros that are not dependent on an one instance...

ExecuteFile>"C:\Program Files (x86)\Microsoft Office\Office14\msaccess.exe" "\\server\inventory\inventory.accdb" /X m_ID,

[Marcus Tettmar]

You could convert the Access VBA macro to VBScript entirely and run it all inside Macro Scheduler:

http://www.mjtnet.com/blog/2008/04/28/c ... -vbscript/

Or just use something like this:

Code: Select all

VBSTART
Sub RunMacro(macroname)
  set oaccess = createobject("access.application")
  oaccess.opencurrentdatabase "C:\MDB_file_Path\MDB_File.mdb"
  oaccess.docmd.runmacro macroname
End Sub
VBEND

VBRun>RunMacro,yourmacro

Trouble is this needs to start the Access db. That may be an issue if it is already running when the macro scheduler macro starts. But if possible you could have macro scheduler open access using create object earlier in the script and then later it is able to call the RunMacro function.

A less technical and fairly simple solution if all the above seems too tricky is to do something like this:

Is it possible to assign a hot key to an access macro, or create a toolbar button for it? It's certainly possible to create an access form with a button that runs a macro. So do one of those things and then macro scheduler just has to send a shortcut key sequence or accelerator keystroke or mouse click .... Simple.

[DIVINELIVING]

Thanks Marcus,

I was thinking the same.. the macro hotkey in Access that will put the form value into a temp table which MS can easily read.. all called from MS... easiest solution to implement and as you say 'simple' .. awesome!