Open Blank Data Entry Form and Populate Fields Based on Current Form

[Tlo]

Hello All. I'm working in Access 2016. I have a form that contains several hundred records of different customers, and a numerical CustomerID as the primary key for each. I have a button that opens a form that the user can use to add notes to this customer's account. The form's properties are data entry = yes since the form needs to be blank so the user can add notes.

What I'm attempting to do but haven't figured out yet is pass the CustomerID (and thus display the CustomerName) on the notes form. I can easily add a combo box on the notes form and have the user select the Customer, but I don't want the user clicking the button to "Add Notes" next to "Customer A" and then mistakenly select "Customer B" from the combo box on the notes page. Below is my current code. NOTE: in our business Property and Customer are interchangeable; I just used customer in the description above because it seems to be a more common term. Also the docmd.requery is for another purpose in the form and unrelated to opening the form.



Private Sub Command232_Click()


Dim Where As String


Where = "([PropertyID] = " & Me.PropertyID & ")"


DoCmd.OpenForm "frmRenewalActivityNotes", , , Where, , acDialog
DoCmd.Requery


End Sub

When I click the button the form opens blank, and the PropertyID box on the form says "(New)". I have several buttons in my DB that open forms to specific records and display data for those records, but this is the first time I've tried to open a form and set one of the values to a unique value.

Any help is much appreciated.

[ItsMe]

I would start by changing the name of your variable.

  Dim strWhere As String


  strWhere = "([PropertyID] = " & Me.PropertyID & ")"

And then you can use your variable in your criteria parameter.

DoCmd.OpenForm "frmRenewalActivityNotes", , , strWhere, , acDialog

Aside from that, I am not sure what your question is and or what is not working.

Your code is set up to open a form and apply the Form's Filter property, using strWhere as the criteria. Is this working as expected?

[Tlo]

What I Want: There are three controls on "frmRenewalActivityNotes." They are PropertyID, ActivityDate, and ActivityNotes. I want the form to open with PropertyID equal to the value of variable "Where" (strWhere in your suggestion), and ActivityDate and ActivityNotes to be blank.

What Is Currently Happening: "frmRenewalActivityNotes" is opening with all three fields blank.

Note that when I go into Design View of "frmRenewalActivityNotes" and open the properties tab, Form -> Data -> Filter field is set properly (i.e. to the value of "Where" (strWhere). See image attached.

[ItsMe]

The following line of code is affecting the Form's filter property.

DoCmd.OpenForm "frmRenewalActivityNotes", , , strWhere, , acDialog

If you execute that code, the form will open in Form View. If you Save the form, you will save the Filter property that the code assigned. I do not believe you need to switch to Design View prior to saving.

Your screenshot indicates the filter property was saved after your code was executed. I suggest deleting the text in the Field for the Filter property.

Having said that, subsequent execution of the Docmd code that includes the Filter argument will override a previously saved Filter assignment.

As for ActivityDate and ActivityNotes to be blank, you can make your String a little longer. Maybe something like ...

strWhere = "[PropertyID] = " & Me.PropertyID & " AND [ActivityDate] Is Null AND ([ActivityNotes] Is Null OR [ActivityNotes] = '')"

[ssanfu]

The form's properties are data entry = yes

You are opening the form in DATA ENTRY mode, yet you are trying to set a filter/criteria by using the WHERE argument in the command.
Because the form is in DATA ENTRY mode, there are no records to filter!!

You need to use the "OpenArgs" argument in the command to open the form. Then there must be code in the form being opened to do something with the "OpenArg" value. But you can't use the "Form open" event. It is too soon. So I used the "Activate" event. This works because the form is in "DATA ENTRY" mode.

See the demo...


Or maybe I misunderstood what you wanted......

[ItsMe]

@Steve
Yeah, if you look at the screen shot, the same form with the Filter property assigned is Data Entry = Yes.
I had it confused where I thought there was more than one form involved.

[Tlo]

@Steve, that is exactly what I was looking for. Thank you for taking the time to put together a demo for me, much appreciated. One note though - not sure why this is, maybe because I'm using Access 2016, but when I assign the value of openargs to the newly declared variable in the form that is opened I had to fuss with the syntax a while to get it to work. Ended up on lngCustomerID = Me.OpenArgs. Your code showed "lngCustomerID = Forms!frmCustomers.OpenArgs". Not complaining at all, just though I'd post for informative purposes only. (Or maybe there's a reason for that and I'm not well versed enough to know it.)

Also I had to use the Load event to get the code to execute. In the Activate event nothing was executed (I had a msgbox in the code that was not popping up). I'm not sure but have a feeling that's because all of my forms open in dialog mode.

Either way thanks for the help and putting in the time.

[ssanfu]

I am using A2010 and HELP showed an example of:
strEmployeeName = Forms!Employees.OpenArgs

I tried using the Form "Open" event but the code wouldn't execute properly. Maybe I should have tried the Load event.

Anyway, glad you figured it out.