Help with Access 2007 validation for a field in a subform based on a query

I have a subform that is part of a larger form in which both contain a PROJECT_ID field. In the main form, the PROJECT_ID field is a key. In the subform, users have the option of a assigning a new representative to the project or making changes to the current project representatives in the subform. A project can have multiple representatives开发者_Go百科, however only one can be an active primary. My issue is I’m having trouble writing the validation for the primary flag field (ADV_FLAG) because it’s essentially based on a query. I’m a bit of a novice when it comes to VBA, but I think it’s probably the best solution for my issue. Any suggestions or samples of similar code on how to go about solving this issue. Below is currently what I have in the sub_form’s BeforeUpdate Event Procedure.

Private Sub Form_BeforeUpdate(Cancel As Integer)

'Validation for more than one Active Primary on Project

‘Append to the table T_Error_Catch the project_ID and an error_flag of YES 'where a project has more than one active primary.

DoCmd.SetWarnings False
DoCmd.OpenQuery "Q_Append_Errors_MultiplePrimaries", acViewNormal, acEdit

‘Throw an dialog box error to user to indicate this project already has one active primary

If ERR_FLAG = "Yes" And ADV_FLAG.Value <> "Secondary" Then

   MsgBox "Project already has an Active Primary.", vbExclamation
   ADV_FLAG.SetFocus
   Cancel = True

End If

‘Truncate the T_Error_Catch table once the record has been corrected and there is again only one active primary

If ERR_FLAG = "Yes" And ADV_FLAG.Value = "Secondary" Then

   DoCmd.SetWarnings False
   DoCmd.OpenQuery "Clear T_Error_Catch", acViewNormal, acEdit

End If

End Sub

You're using form before update to validate ADV_FLAG values in existing records. I think you need to also address new records ... to prevent the user from adding a new representative as primary for a PROJECT_ID which already has a primary representative assigned. You could add a procedure for form before insert to deal with that.

However, rather than waiting until the user has completed all the fields before validating ADV_FLAG, do the validating in the after update event of the ADV_FLAG control. The after update procedure would handle both record update and insert.

But perhaps even easier to implement would be a command button on the subform which makes the current representative the sole primary for the current PROJECT_ID. The button's click event could use code such as this:

    Dim strSql As String
    Dim db As DAO.Database

On Error GoTo ErrorHandler

    strSql = "UPDATE YourTable SET ADV_FLAG = 'Secondary' WHERE PROJECT_ID = " & _
        Me.txtPROJECT_ID & ";"
    Debug.Print strSql
    Set db = CurrentDb
    db.Execute strSql, dbFailOnError
    Me.txtADV_FLAG = "Primary"

ExitHere:
    On Error GoTo 0
    Debug.Print "RecordsAffected: " & db.RecordsAffected
    Set db = Nothing
    Exit Sub

ErrorHandler:
    'your error handler code here '

I assumed your subform has a text box control named txtPROJECT_ID which is bound to the PROJECT_ID field, and another named txtADV_FLG bound to the ADV_FLAG field. Change those names as required to match your data controls.

With this approach, there's not really a need for the user to directly edit values in txtADV_FLG (since the command button will make any changes needed). So in the property sheet for txtADV_FLG you can set Enabled = No, and optionally Locked = Yes.

The Debug.Print lines are to help you troubleshoot problems. They will print information to the Immediate Window. (You can go to the Immediate Window with the Ctrl+g keyboard shortcut.) After you have the code running correctly, you could disable or remove the Debug.Print statements. Or leave them as is ... you won't suffer any significant performance hit.

Notice I used db.Execute strSql, dbFailOnError instead of DoCmd.SetWarnings False and DoCmd.OpenQuery. I never SetWarnings off. If you do it, you must remember to SetWarnings back on again afterward. Your code didn't include DoCmd.SetWarnings True. So without SetWarnings on, you risk suppressing important information. Don't do that!

My eyes glazed over trying to follow @HansUp's solution, which I'm sure is correct. I'll instead offer an answer that uses the schema design to obviate any need to write much in the way of code.

I've had to do this many times -- you have a N:1 table but you want one of the records to be designated as PRIMARY.

First, you set up your N:1 table.

Then you add a field to the main table (the 1 side) and have that store the PK value of the record in the N table that you want as your main record.

For instance, say you have tblInventory and tblImage, which has an ImageID PK and an InventoryID FK.

To set one of those as the main image, you'd add a MainImageID field to tblInventory, and edit it with a combo box that lists the images from tblImage that are joined to that InventoryID in tblImage. You'd have to requery the combo box in the Inventory form's OnCurrent event, of course.

An example UI is here:

In that implementation, the list of images has a checkbox for TOP, but it's not editable (and that's a bad UI, since it's not clear from looking that it can't be changed there), but users figure it out fairly quickly. It's certainly not necessary that the check be displayed there, or that the same control be used to indicate the TOP item.