Form that can edit selected record or create new record depending on selection in a Split Form (2 Viewers)


Local time
Tomorrow, 05:47
Dec 7, 2021
Good afternoon,

The end goal of my Access is to have users use it in standalone (runtime).

As such, it is not ideal for them to edit the tables of records directly. Also, the tables may not be readable as some fields are foreign keys (autonumbers instead of corresonding value, eg analyst_id instead of analyst name, method_id instead of method name, etc).

In my Main Menu form, I have a button "Assign Analysts to routine Activities" which opens up the Task_Detail_Interface form when clicked on.

The Task_Detail_Interface form is a split form which shows a table (from a query which links the foreign keys to corresponding values, etc) in datasheet view. I added a leftmost column with the "Assigned" hyperlink. Clicking on it will open a macro that opens a form, Task_Detail_Add_v3, which allows the user to edit the record. Other features like cascading comboxes are present in Task_Detail_Add_v3 that "filters" the options available (Date and method affects instrument, all of them affect which analysts appear in the combo box etc)

I notice that if I click on the "Assign" hyperlink on the bottom-most row, it will generate an syntax error. I guess it is expected since the bottommost row is not an actual record and thus does not have a record ID for Task_Detail_Add_v3 to open.

My current workaround is that i have another form similar to Task_Detail_Add_v3 which is for the creation of new records. Definitely not ideal.

What can I do so that if I click on "Assign" hyperlink on the bottommost row, it will (ideally) open Task_Detail_Add_v3 but to create a new record.

Moving beyond the above issue, would it be better if the user can edit record on the datasheet directly (without the form) but for each field, there is a dropdown box that filters the options based on the selection of previous/fields (on the left of the current field). Any example or guide that I can refer to?

Thanks again!


  • Test Planner 2022-01-20 CT.accdb
    2.8 MB · Views: 221


Local time
Today, 13:47
Mar 9, 2014
Macro could use If THEN ELSE action to test if Task_Detail_ID is Null and act appropriately.

If IsNull([Task_Detail_ID]) Then
'open form in Add data mode
. . .
'open form filtered to record
. . .
End If

Cascading comboboxes should work on Split form as well. You already have example in your own form so what do you need?

What is "better" is subjective. Use whichever you prefer.
Last edited:

Users who are viewing this thread

Top Bottom