Solved DoCmd.OpenForm with where condition does not filter as expected. (1 Viewer)

[5hadow]

Private Sub btnNewDR_Click()
    DoCmd.OpenForm "frmdocrevdetail", , , "fldDocID =" & me.fldDocID, acFormAdd
End Sub

My main form is "frmWIDetail"
In it, I have a sub-form "frmDocReview"
Also on the main form I have a button "btnNewDR"

My plan is to keep a record of reviews (Sub-form "frmDocReview"), connected to main form via union table.
When I press button, I want to add a new record that is in acFormAdd mode, but it's filtered to current record on main form.

Everything is good except when I press the button it opens my form in add mode and fldDocID is set to "0" instead of what ever it is on the main form.

How do I add a new record, but also have the where condition point to a record?

Edit: More info

As per image above, DocID is 0. I want it to be set to what ever the main form is on.
But I also want the form to be in add mode.

 

[5hadow]

Should mention:

When I try to save the record on an opened form, it says "You cannot add or change a record because a related record is required in table "tblWIUnion"

 

[theDBguy]

Hi. For your first issue, when you open a form to a new record, the entire form is blank. This allows you to enter any value in DocID. The new record will then stay or disappear from the form based on the filter you applied.

To automatically add the current DocID, you can either pass it to the opening form using the OpenArgs argument or pull it from the main form using a form reference.

As for your second issue, we would probably need to see your form in action to offer any suggestion. Unless, you are saying you are leaving the DocID blank when saving the new record. In which case, doing either of the above approach should help fix it.

 

[5hadow]

Hi. For your first issue, when you open a form to a new record, the entire form is blank. This allows you to enter any value in DocID. The new record will then stay or disappear from the form based on the filter you applied.

To automatically add the current DocID, you can either pass it to the opening form using the OpenArgs argument or pull it from the main form using a form reference.

As for your second issue, we would probably need to see your form in action to offer any suggestion. Unless, you are saying you are leaving the DocID blank when saving the new record. In which case, doing either of the above approach should help fix it.

Here's what I did:

Private Sub btnNewDR_Click()
    Dim DocID As Long
    DocID = Form!frmDocReview!fldDocID
    DoCmd.OpenForm "frmdocrevdetail", , , , acFormAdd, , OpenArgs:=DocID
End Sub

I know it works because I tested with msgbox. DocID gets the correct number.

Here's the error I get:

DocID feild is still set to 0 as soon as I start inputting things into the form.

Here's tblWIUnion

and relationships:

Any ideas?

 

[theDBguy]

Any ideas?

Yes. I guess I only told you half of what you needed to do. In the Open event of your form, you can then use the value in the OpenArgs property to assign a default value for DocID (at least, that's the approach I would take). For example:

Me.DocID.DefaultValue = """" & Me.OpenArgs & """"

Hope that helps...

 

[bastanu]

You probably don't need to use the OpenArgs, just set the default value in design view (of the frmdocrevdetail form) to
Forms!frmWIDetail!frmDocReview.Form.fldDocID

Now for the issue at hand; you are attempting to create a new record in tblDocReview with the same DocID. We assume the fldDocID on the frmDocReview subform comes from the union table. Is the form bound to just the table or to a query that includes the union table?

Cheers,

 

[Pat Hartman]

Do not use the open event of a form to populate the foreign key. That only populates it for a single record, the first. Use the form's BeforeInsert event. That event will run ONCE for each new record. So, if the user adds multiple records, all will be properly linked.

PS - open the child table in design view and remove the zero from the default property. You ALWAYS want the default for Foreign Keys to be null. If the FK is required, set the FK field to be required. If the FK is optional that set the required property to no. An example of an optional FK would be the AssignedTo field in a database that manages computer equipment or cars. When you buy them they go into an unassigned pool. Eventually, the FK is filled in and they get assigned to a specific person.