Child Form does not use Where clause for OpenForm Add

[mkenyon2]

I have a form, AssetDetails with a button (cmdEquip) that opens another form, EquipmentDetail.


I have an if statement on the button that says if the AssetID is found in EquipmentDetails (table) then OpenForm EquipmentDetail, WHERE [Asset] = [ID], Edit, Dialog.
Else, OpenForm EquipmentDetail, WHERE [Asset] = [ID], Add, Dialog
The edit one works, filtering as expected, the add method gives me a blank form but Asset has not been assigned to the filter.

Any ideas why? How can I have the OpenForm Add use the filter to apply the value to that field?

[TG_W]

If I am reading right, the record does not exist in the other table, so you would need to set the value since you are adding a new record, thus you are getting a blank since it is filtering as you have asked it to. Try setting [ID] = [Asset] after you open the form.

[mkenyon2]

After I open the form? Would that be a new macro in the EquipmentDetail form? Or the original macro, just after OpenForm?

[TG_W]

I'm not sure about Macros. I prefer to only use VBA, but you can probably insert that after the OpenForm call in the macro.

If you would like to change it all to VBA, you can use the code below.

In the AssetDetails form, open EquipmentDetail with -

DoCmd.OpenForm "EquipmentDetail", , , "[Asset]='" & Me!ID & "'"

This will open the form and filter on the Asset. You might need to swap Asset and ID as I am not sure which is where. It will open filtered on what you want, but if you allow additions on the EquipmentDetail form, then it will open to a new record. If the record does not exist, then you can set an on load VBA in EquipmentDetail.

If IsNull(Me.Asset) Then
Me.Asset = Mid(Me.Filter, 7, 5)
End If

That will populate the Asset field (if that is the field on EquipmentDetail) based on the filter set in place by the button. You will need to adjust the starting point (first number) if the field is ID, not Asset. You will also need to adjust how many characters the code will pull (second number). **NOTE** This code is solely dependent on the Asset/ID being the same number of characters.

Hope this helps some.

[mkenyon2]

I would LOVE to use VBA, but this is an Access Web Database. It isn't allowed.