Filling a user form in MS Access using Powershell

Question

I have a form in MS Access that I have to fill very often. I would like to automate filling the from. As I am just a user, I don't have access to the database itself, it's password protected. I would like just to fill the fields in the form, without submitting it or creating a new record in the database. So what I need is: form opens, the fields are already filled and I manually click Submit button.

I attach the screen of a part of a form to show what I mean.

screen of the form

I know how the form is called in the base - frm_MainManu. What is more I am in general new to PowerShell, so maybe what I have now (and is below) is completely wrong (or maybe not?).

What I have now is:

$FormFile = "FileWithForm"

$oAccess = New-Object -com Access.Application
$oAccess.Visible=$true
$oAccess.OpenCurrentDataBase($FormFile)
$oAccess.DoCmd.OpenForm('frm_MainMenu')
$AccForm = $oAccess.Forms.Item("frm_MainMenu")
$AccForm.Controls.Item("Reference_3").value = "Refvalue"

After executing the code I get an following error:

You cannot call a method on a null-valued expression.
At line:12 char:1

+ $AccForm.Controls.Item("Reference_3").value = "Refvalue"
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull
  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

The file with the form opens, as I understand there is a problem with filling the fields? How do I do it?

Thank you!

@June7 I tried removing it from both lines that contain it and unfortunately it didn't work — Fiilli
– Fiilli, Commented Nov 28, 2022 at 9:21

mazoula · Accepted Answer · 2022-11-28 09:43:14Z

I don't use powershell, so take that into consideration but after some testing it looks like once you get into the database you must use the Docmd object to do everything. For instance:


$Fullpath = "C:\folder1\folder2\Desktop\anotherfolder\mydbname.accdb"
 $oAccess = New-Object -Com Access.Application
$oAccess.Visible=$true
$oAccess.OpenCurrentDataBase($Fullpath)
$oAccess.DoCmd.OpenForm('frm_MainMenu')

'From here continue using DoCmd to set the control


$oAccess.DoCmd.SetProperty("Reference_3",10,"Refvalue")

it looks like they haven't dotted the i's and crossed the t's for instance PowerShell didn't recognize the acPropertyEnum so I had to use 10. see https://learn.microsoft.com/en-us/office/vba/api/access.docmd.setproperty

I had a database lying around that was protected by a password form that loaded automatically but powershell could access the database. The password form came up but Powershell just ignored it.

inportant note: you will need the unique names for each form and control in access as the . operator wasn't working inside power shell. Usually that is just the controls name but for instance the unique name for a form will usually be Form_theformsname. To find an objects unique name in Access use the object browser which can be found by going to the visual basic window and hitting F2.

Collectives™ on Stack Overflow

Filling a user form in MS Access using Powershell

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related