none
Form controls does not list any control in form from Access database... RRS feed

  • Question

  • Hi!

    I am trying to automate the extraction of data from an ACCESS database using powershell.

    To do this I need to populate 2 date fields in a form (defining the date range of entries to extract from the data base) and run a query that uses the date fields of the form as parameters.

    I tested that on a dummy DB listing the US state capitals and it works fine but when I run it against our production DB it does not work...

    I am using the following code:

    $dueDate = "12/01/2015"

    $accessDB.DoCmd.OpenForm("myForm")
    $oLinkMyForm = $accessDB.Forms.item("myForm")
    $oLinkMyForm.Controls.Item("begin_Date").value = $dueDate
    $oLinkMyForm.Controls.Item("end_Date").value = $dueDate

    I get the following error:

     

    You cannot call a method on a null-valued expression.
    At XXX.ps1:52 char:34
    + $oLinkMyForm.Controls.Item <<<< ("begin_date").value = $dueDate
        + CategoryInfo          : InvalidOperation: (Item:String) [], RuntimeException
        + FullyQualifiedErrorId : InvokeMethodOnNull

    You cannot call a method on a null-valued expression.
    At XXX.ps1:53 char:34
    + $oLinkMyForm.Controls.Item <<<< ("end_date").value = $dueDate
        + CategoryInfo          : InvalidOperation: (Item:String) [], RuntimeException
        + FullyQualifiedErrorId : InvokeMethodOnNull

    If I try to list all controls from this form, nothing is displayed and $oLinkMyForm.Controls.count does not return any value, not even 0...

    I am lost why using the same steps works on my dummy DB, but not on the real one...

    Please help!


    Thursday, December 24, 2015 7:25 PM

Answers

  • Is the form "myForm" a bound form (meaning it has a recordsource and can display records)?  If so, does the recordsource actually return any records?  If not, is the database or form read-only, or the recordsource non-updatable for any reason?

    It's just a thought, but if the form doesn't return any records and no records can be added on the form, then any controls in the form's detail section will have no values and can't be assigned values.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Tuesday, December 29, 2015 9:40 PM
  • Hi Melzinar,

    To narrow down this issue, I also suggest that you make the Access application visible to see whether the issue is relative the suggestion of Dirk.

    If you still have the issue, you can try to share a demo database to help us narrow down this issue. You can share the data base via OneDrive and please remove the sensitive information before you uploading.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, December 30, 2015 7:32 AM
    Moderator

All replies

  • I do not know PowerShell language but try changing to this --

         $dueDate = #12/01/2015#

        or this --

    $dueDate = "#12/01/2015#"


    Build a little, test a little

    Thursday, December 24, 2015 9:27 PM
  • Hi Melzinar,

    How did you open the database? Please make sure that the database was opened successfully before you automate the operation for that database.

    For example, here is a code sample that open the specific database and open one form for the database:

    $Acc = New-Object –com Access.Application
    
    $Acc.Visible=$true
    $Acc.OpenCurrentDataBase("C:\Database1.accdb")
    $Acc.DoCmd.OpenForm("Form1")
    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, December 25, 2015 5:24 AM
    Moderator
  • Is the form "myForm" a bound form (meaning it has a recordsource and can display records)?  If so, does the recordsource actually return any records?  If not, is the database or form read-only, or the recordsource non-updatable for any reason?

    It's just a thought, but if the form doesn't return any records and no records can be added on the form, then any controls in the form's detail section will have no values and can't be assigned values.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Tuesday, December 29, 2015 9:40 PM
  • Hi Melzinar,

    To narrow down this issue, I also suggest that you make the Access application visible to see whether the issue is relative the suggestion of Dirk.

    If you still have the issue, you can try to share a demo database to help us narrow down this issue. You can share the data base via OneDrive and please remove the sensitive information before you uploading.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, December 30, 2015 7:32 AM
    Moderator