Subform not showing when there is no data

[Seth Schrock]

I have an Access 2010 database that is based on MS SQL Server through ODBC. There are a some times when I will open a form that has a subform and I will go to a record where there is no data in the related table for the current record and the subform will not be there. The box for the control is there, but the subform is not. This doesn't always occur, which makes it tough for me to figure out what is the common denominator that makes it work once and then not another time. I have never had trouble with this when using Access as the backend so I'm assuming that there is something with either MS SQL Server or just ODBC connections in general that is causing this.

One example of this happening consistently is when I add a user, the subform is only visible if they are a certain type of user. When first creating the user, I have the subform set to visible = False. In the EmployeeType combobox's AfterUpdate event I check to see if the type selected was the type that needs this subform and if it is, then I make the subform visible. When this happens, only the subform control become visible, but not the form (which is empty this this is a new employee being entered). I have to go to another employee's record and then come back to the one I just created and then I see the sub form itself and can add the data needed in it. This happens every time. There is another situation with a subform on a different form whose Visible property never changes, but occasionally it isn't visible. So far this only occurs when there is no data.

Is there something that I can do to make these subforms always be visible even if there is no data?

[nico5038]

Hmm, I've run into trouble when the linked table doesn't have a unique index in MS SQL. So first check or the subform's table has such a unique index.
Next make sure the dataentry property for the subform is set.

Personally I often INSERT a childrow into the subform's table (with the mainform's ID) to allow easy update by the user, however this requires a DELETE when the user cancel the INSERT of the mainform. In your case the selection of the Type can trigger such an INSERT.

Nic;o)

[Seth Schrock]

Both tables (for the parent and subform) have primary keys set.

When you say, "make sure the dataentry property for the subform is set" I assume you mean set to no (which it is). Otherwise, existing data isn't shown.

What data do you put in the subform automatically? I picture this causing confusion for my users when they create a record in the main form and then there is already a record in the subform.

[nico5038]

Dataentry itself should be "No", but you need to allow addition of records ofcourse.

I picture triggering an INSERT when the Type is selected. This also requires a DELETE when there's a change of Type, but effectively the addition becomes an Update of an existing record and this approach saved me a lot of time looking for why records don't appear.

Possibly I would "split" the mainform and the subform in having a mainform with a datasheet subform with all Employees and besides that different action buttons like:
Add TypeA
Add TypeB, etc.
Update
Delete
Print
etc.
Thus when clicking the [Employee Add TypeA] button you can show the proper form with or without the subform.

Having such a mainform with employees will allow the user to use the right-click popup menu to filter the employees and easily find one(s) that needs to be updated. And this approach is rather common in many applications.

Nic;o)

[Seth Schrock]

What do you INSERT? Triggering it from the Type AfterUpdate event sounds like a good idea, but I don't want to insert wrong data and then trust that the user will leave the record with the subform correct since this is the form that grants access to certain areas of the database.

[nico5038]

I insert a default record into the child table.
When closing the form the user has two options:
1) Cancel, which will require to delete the record.
2) Save, what will trigger the code to validate the record and when OK nothing has to be done as the data will be stored automatically.
Additionally you'll need to check or the selected value from the combobox does need a child record and by default (when some values do have children and some not) I would drop all rows from the child table holding the mainform's unique identifier.

Just curious, what's the function of this record that the user needs to leave correct?

Nic;o)

[Seth Schrock]

I guess I could create a record that didn't do anything that if it is left in the subform wouldn't change anything.

The main form is the employee form and one of the fields is the employee type. If the type is Loan Processor, then I have the subform appear which lists the loan types that the selected processor can do. Thus, I wouldn't want a record inserted that listed a real loan type since the loan processor might not be able to do that loan type and then I would be counting on the user entering the employee to either change or delete the record to make sure that the permissions were set correctly. Besides, if the data isn't correct, then what good is it since you can't trust it?

[nico5038]

Hmm, I guess there are multiple loan types that can be selected for an employee and in a normalized datastructure you would have one row for each loantype and an indicator "Valid Yes/No".

I would have created a tblLoanType with just the LoanType and ValidYN fields (Set to "No") and use these to insert all loantypes for such a "Loan Processor" employee.
Then the user gets a filles subform and is able to check the appropriate loan types. When the employee type is changed you can delete these records or set all ValidYN fields to False.

In the processing you can filter the loantype records for a ValidYN field to be true for further processing and ignore the "No" records...

Getting the idea ?

Nic;o)

[twinnyfo]

Seth,

Did you try Nico's suggestion in post 4 above, to AllowAdditions set to Yes on the subform? I have come across your problem previously, and that seems to do the trick.

Regards,
twinnyfo

[Seth Schrock]

@Twinny All the settings are correct to allow me to edit, add and delete records. My problem is not in editing or adding records, but in having the subform appear inside the subform control.

@Nico I think that I understand what you are doing. What I could do is just insert every loan type into the join table and then the subform would have a set of records. The user would then only have to check a checkbox to give them permission to use that loan type. It would create more headaches in that if I would add a loan type later on, it would make it so that I would have to code an append query to insert the new loan type for each loan processor into the joining table (between the loan type and employee table). I'll have to think about it before I implement it, but I think that I could do that. Seems complicated to have to do all that just to make a subform show up.

[nico5038]

An easy help for the insert you can use a query like:

INSERT INTO tblLoanLink ( PersNr, LoanType, ValidYN )
SELECT 123 AS PersNr, tblLoantype.LoanType, tblLoantype.ValidYN, *
FROM tblLoantype;

By executing this from code (CurrentDB.Exec ute command) you can replace the 123 with the needed ID.

Nic;o)

[feanorn]

I have had the same problem: Primary key present in table, recordset type set to dynaset, allow additions enabled on the form, and it just went blank when no data present (updates worked fine on existing data). What solved it for me, was to include the primary key column in the select statement of the record source. It is not enough to have it in the table. This part seems to be missing from the internet sources i could discover,