Open a form to a specific record using a batch file and parameters

[jrdnoland]

I'm trying to open a form in Access via a batch file made in Excel. The batch file needs to pass three parameters to access to load the correct record of the form.

My Batch File looks like the:

Set /A Project_Request=1
Set /A Project_Release=2
if exist ""C:\Program Files (x86)\Microsoft Office\root\Office16\MSACCESS.EXE"" (
"K:\R&D Dept\Development Lab\R&D Test Request System (For testing and training)\DataBase\R&D Project Requests DB.accdb" /x mcrEmail /cmd 1616
goto install
) else if exist ""C:\Program Files (x86)\Microsoft Office\Office14\MSACCESS.EXE"" (
"K:\R&D Dept\Development Lab\R&D Test Request System (For testing and training)\DataBase\R&D Project Requests DB.accdb" /x mcrEmail /cmd 1616
goto install
)

Project: 1616 R1 R2

Batch File To Start Access and open to the submitted request record.

The Function in Access looks like this:

Public Function fOpenEmailForm()    
Dim CounterFieldNumb As Integer
Dim Project_Request As Integer
Dim Project_Release As Integer

CounterFieldNumb = CLng(Command())
Project_Request = 1
Project_Release = 2

    Call modCode.SetBypass(False, c_Main_Drive & c_Main_Folder & c_Main_Database_Folder & c_DBName) 'True setting allows bypass of startup options
    DoCmd.OpenForm "frmTestRequests", , , "[Counter]=" & CounterFieldNumb And "[Project_Request]=" & Project_Request And "[Project_Release]=" & Project_Release
End Function

This creates an error saying Type Mismatch.

I hard coded the Request and Release, what I need to do is send all three parameters to the access function to open the correct record.

The Fields are Counter, Project_Request, and Project_Release
I'm not sure how to use all three in a batch file and have the correct record load.



Thanks,
Jeff

[Welshgasman]

Type mismatch is when you code for a number and use a string an vice versa.
So if you have string variables these need to be surrounded in single quotes (or triple double quotes if the variable could contain a single quote. as in a surname.?

So are project_request and project_release strings?

[jrdnoland]

Type mismatch is when you code for a number and use a string an vice versa.
So if you have string variables these need to be surrounded in single quotes (or triple double quotes if the variable could contain a single quote. as in a surname.?

So are project_request and project_release strings?

No, all three fields are Integers.

[Welshgasman]

Tip.
Put your criteria into a string, then you can debug.print it to see how it has been constructed'

    DoCmd.OpenForm "frmTestRequests", , , "[Counter]=" & CounterFieldNumb  & " And [Project_Request]=" & Project_Request & " And [Project_Release]=" & Project_Release

HTH

[Micron]

I have no concrete idea how you send parameters to Access from a .bat or .cmd file if that's what that is (maybe /cmd switch?)

what I need to do is send all three parameters to the access function

Then your function needs the parameters, which you don't have?
Public Function fOpenEmailForm(Cnt As Integer, ProjReq As Integer, ProjRel As Integer)

[jrdnoland]

Tip.
Put your criteria into a string, then you can debug.print it to see how it has been constructed'

    DoCmd.OpenForm "frmTestRequests", , , "[Counter]=" & CounterFieldNumb  & " And [Project_Request]=" & Project_Request & " And [Project_Release]=" & Project_Release

HTH

But the DoCmd OpenForm is in the access function. I need to know how to pass in all three parameters (Counter, Project_Request, Project_Release) in a Batch file.

I also just realized that my combined Key fields are not quite right. I need Counter to increment once for each set of Project_Releases. The fields would look something like:

Counter Project_Request Project_Release
1616 1 1
1616 1 2
1616 1 3
1616 1 4
1617 1 1

There may be as many Project_Request numbers as necessary but Project_Release numbers will be limited to 4. Counter numbers are also unlimited.

Thanks again!

[Welshgasman]

I would set them as environment variables and then use the Environ() function.?

Else use this method https://stackoverflow.com/questions/...eters/43178693

[Micron]

Or fix the concatenation?

DoCmd.OpenForm "frmTestRequests", , , "[Counter]=" & CounterFieldNumb & " And [Project_Request]=" & Project_Request " And [Project_Release]=" & Project_Release

not

DoCmd.OpenForm "frmTestRequests", , , "[Counter]=" & CounterFieldNumb And "[Project_Request]=" & Project_Request And "[Project_Release]=" & Project_Release

[Welshgasman]

I am only pandering to what you asked?

Just exactly what are you trying to achieve?

I would expect linking the Excel worksheet to Access would be a better method, then access each row as records?

[jrdnoland]

Or fix the concatenation?

DoCmd.OpenForm "frmTestRequests", , , "[Counter]=" & CounterFieldNumb & " And [Project_Request]=" & Project_Request " And [Project_Release]=" & Project_Release

not

DoCmd.OpenForm "frmTestRequests", , , "[Counter]=" & CounterFieldNumb And "[Project_Request]=" & Project_Request And "[Project_Release]=" & Project_Release

Actually, I tried that combination along with many others.

[jrdnoland]

I am only pandering to what you asked?

Just exactly what are you trying to achieve?

I would expect linking the Excel worksheet to Access would be a better method, then access each row as records?

I'm making a batch file in Excel which then allows the DB person to start up Access to that specific record from a hyperlink to the batch file in Outlook. The record is then used in Access to assign persons to work on the "Project". I'm using Access as a front end, the back end is sql server. The Access tables are linked to the Sql Server table(s).

[Welshgasman]

So why not just have the user find the relevant record from within Access?

Seems a very complicated way of doing things?

Post your attempts as the SQL string using that method I mentioned by Debug.Print strCriteria where strCriteria =
"[Counter]=" & CounterFieldNumb & " And [Project_Request]=" & Project_Request " And [Project_Release]=" & Project_Release

If you still have not got the parameters coming in correctly, hard code them for now, just to get the syntax correct.

Micron and I posted the same code, so unsure why it does not work for you?

[jrdnoland]

I would set them as environment variables and then use the Environ() function.?

Else use this method https://stackoverflow.com/questions/...eters/43178693

I actually saw that link, the select case only seemed to follow what I'm currently doing. I could not figure out how to pass the Request and Release numbers along with the Counter number.

[Welshgasman]

I would try the /cmd switch and concatenate them into a string with perhaps the | character, then split the string in the autoexec to populate the 3 global variables.?

Setting as environment variables does see simpler?

This way is even simpler?

https://docs.microsoft.com/en-us/off...urrentdatabase