Dealing with a filtered form in the event that no records exist for a given foreign key

[Pawtang]

My goal is to create an Attachments pop-up form that can be opened for any given primary record. When I open the Attachments form from the primary record interface, it sets the record id to a tempvar and then opens up a continuous form that lists all attachments with a foreign key equal to the id.

This works fine as long as records exist with that foreign key, but if there are no existing attachments for the id, the form opens to an id of "0". Would it be better to build the form in relation to a query, and then set query constrains based on the TempVar? Or what's the best way to handle this?

Relationships:





Form design/relevant parameters:

[June7]

And this is an issue because ...?

What do you want to happen - don't open form if no data? Code can do data validation, perhaps with DLookup().

[Pawtang]

This is intended to be the only interface for adding/opening attachments for a record, so I need to be able to open this interface for a record that does not yet have any attachments, and add them in the continuous form format.

I originally was designing this as a subform to a parent form that would open to the id, and then link master-child on the id. Is this a better way to handle if I want to be able to add and edit? Halfway through the design I scrapped the parent shell because I thought it was redundant.

[June7]

Be aware that embedding lots of large files in db uses up Access 2GB limit.

Why would you think form/subform arrangement is redundant? This is the simplest way to set up data entry/edit for related tables. Little or no code is needed.

Otherwise, code would validate data and either open form to existing record(s) or to new record row and populate foreign key field.

I've never used TempVars.

[Pawtang]

I am using a file path method rather than attachments for that reason, this will just store file paths to a shared drive and use some code to open them or to browse to the path to build a new connection.

I suppose I thought the form/subform was redundant because I had a parent shell that presented no unique information that wasn't already present in the child form. However I will give this another shot and see if it solves that problem.

[kd2017]

I suppose I thought the form/subform was redundant because I had a parent shell that presented no unique information that wasn't already present in the child form. However I will give this another shot and see if it solves that problem.

I understand it can seem clunky having to use two separate forms to create a single pop up window but this is probably the easiest way to go. I seem to recall having quirky window issues when trying to use a continuous form as a pop up and had to wrap it in a 'shell' form anyway...

[June7]

So it is not really and Attachment type field?

Why is parent info repeated in child form? Don't use query that joins the two tables as RecordSource for child form.

[Pawtang]

So it is not really and Attachment type field?

Why is parent info repeated in child form? Don't use query that joins the two tables as RecordSource for child form.

Not in the standard sense, no.

Problem was that I had both the parent and the child source set to the table of attachment paths. Now the parent uses the primary records table as record source and matches the child's foreign key with its primary key. Works fine.

[June7]

And that's as it's intended to work. Glad you got it working.