Search code examples
excelvbams-accessms-access-2016

Input box in MS Access 2016 Reports

I am new to MS Access. I have created a report which pulls data from excel and displays on the MS Access form output. I have added a text box for which in Properties->Control Source I added "PO#" as in fig 1.

enter image description here

So the user enters the value in the dialog box when he tries to open the report as below in fig 2. I need to add a default value in the dialog box in the format "LDXXXX" where XXXX is the financial year. I have the logic to generate the financial year as below:

If month <= 8 then
  Current year
else
  current year + 1

I don't know where to implement it. The dialog box should have the above format as default value when the user tries to open the form and it should populate in the text-box from fig 1. I know there are three events that can be accessed when we click on the text and go to "build events"-Macro, code or expression builder but how to implement this for the dialog box?

enter image description here

Solution

  • If PO# doesn't exist in the source data, it probably shouldn't be referenced as a control source. Does the user need to change the LDXXXX default, or did you implement this to ensure the value is being populated? If the latter, you could probably just use something like this as the control source of the texbox:

    ="LD" & IIf(Month(Date())<8,Year(Date()),Year(Date())+1)

    And if the user needs to change it, keep the txtbox called 'PO#' and add code to the load event of the report.

    Private Sub Report_Load()
Me.[PO#] = InputBox("Fiscal Year:", "Fiscal year", "LD" & IIf(Month(Date) < 8, Year(Date), Year(Date) + 1))
End Sub