Controls on subform not active/enabled

[Williams485]

Hello Access forum,



I have two subforms that appear to not have Data Entry modes enabled, as I cannot add to any of the form controls. Oddly, however, the appropriate foreign keys appear in the text boxes I've added to test the functionality of the forms.

The parent form (frmChecksDataEntry) is linked to a table called CHECKS and the two subforms (frmCMScores & frmGMScores) are linked to tables CM_SCORES & GM_SCORES respectively. Having set up table relationships and checked the Parent/Child link fields, I'm reasonably sure the forms are set up correctly in that respect. I've even added a couple of text boxes to check that the appropriate foreign keys are being sent to the forms (as mentioned). That said, I still can't add data (the controls are check boxes). I've also checked that 'Allow Additions' is set to "Yes" for each of the three forms.

Can anyone point me in the right direction?

Thanks in advance

[June7]

If you want to provide db for analysis, follow instructions at bottom of my post.

[Williams485]

Thanks, June7. See attachment.

QC Db for Access forum.zip

I'm a relative noob with Access/databases in general so if you spot any bad practices in my file I'd be grateful for any tips you care to give!

[Williams485]

I should add that I'm using Access 2013 (despite what it did say on my user profile)

[Williams485]

I found the issue: it was much simpler than I thought. The only editable controls on the form were set to triplestate=true, but this wasn't consistent with my table (I think). Either way, I set the triplestate property to 'No' and the form controls now work.

thanks for reading!

[June7]

Looking at Relationships settings. Why is User_ID in both Checks and Leads? Why is Lead_ID_FK in CM_Scores and GM_Scores but Leads is not linked to these tables? Checks shows a 1-to-1 relationship to CM_Scores and GM_Scores. Is this correct?

Multiple similar name fields (CS1, CS2, etc) as well as lots of Yes/No fields for same type of data indicates a non-normalized data structure.

Forms are already set for DataEntry Yes so no need for the code to set this property. Also, the DoCmd.OpenForm method is opening the form to Add mode. If you do want to use this form to view existing records, the code in its Load event would change record data. So first verify it is a new record.

If Me.NewRecord Then
Me.LEAD_ID_FK.DefaultValue = OpenArgs
Me.cbUserID.DefaultValue = Environ("username")
End If

What is this db for? Why would fields for Title, Address, Email, etc. be Yes/No fields?

This is very odd. I can edit the Yes/No fields directly in CM_Scores and GM_Scores tables but cannot through each form, even when DataEntry is set to No and opened as stand alone to existing records. I created a new form bound to CM_Scores and I am able to edit records through it. I replaced the old form with my new one and the form/subform works. "Very Odd" often indicates corruption and rebuilding may be the only solution. I can't find why the subforms don't work so delete and rebuild.

EDIT: I see you found the cause. I have never used triple state but I think it is only useful for UNBOUND checkboxes. So not corruption and just something I would not have thought to look for.

[Williams485]

Thanks for your response, June7. I appreciate the time you have taken in reviewing my database and providing helpful comments.

In summary, my database is to manage a quality control process for checking sales leads generated by a sales floor. Each lead that goes into the LEADS table, is subsequently checked (CHECKS table) against a series of binary criteria ("is the CONTACT confirmed?", "is the EMAIL phonetically checked?", etc)--hence the yes/no data types; some of the criteria relate to information checked on a call monitoring matrix (CM_SCORES) and others relate to a CRM admin matrix (GM_SCORES). Each check will have a maximum of 1 CM check and 1 GM check, hence the 1-to-1 relationship. This considered, do you still think that I have a normalisation issue; if so, how can I get around it and improve my structure?

Poor naming of fields resulted in the USER_ID fields in the CHECKS and LEADS tables; they are now CHECKED_BY and GENERATED_BY, respectively. I've also reviewed my table structures in relation to the extraneous LEAD_ID fields in the CM_CHECKS and GM_CHECKS and have removed these.

I take your point about the OnLoad event and will change this because I probably will use this form to amend data at some point.

Thanks again

[June7]

Is filtering records based on the Yes/No fields important? Have you tested filtering on the Yes/No fields? Suppose you want records where any of the CS fields are checked or where none of them are checked? What are these 10 fields for? Trying combining this criteria with parameter on another Yes/No field.

It is a balancing act between normalization and ease of data entry/edit. A common philosophy is 'normalize until it works, denormalize until it hurts'.