Solved Allow Record Deletions but Not Blank Field in Combo Box

[Weekleyba]

I have a sub form linked to a parent form. The sub form is a continuous form. It’s record source is a junction table. There is only a combo box in the sub form. Besides having the ability to select a record from the drop down, I want the user to be able to delete a record (after a warning message box) but not have the ability to leave a blank value in the combo box.
How do I do this?

 

[GPGeorge]

Requery the combo box after deleting the record.

 

[Weekleyba]

The combo box is bound to the ID field but the ID is not shown in the combo box. So when a user deletes the shown field (ContractNumber) it leaves a record with an ID but no ContractNumber. I want to prevent that.
Again, I want to be able to delete the record (no problem there) but no allow the user to delete the ContractNumber.

 

[jdraw]

@Weeklyba
Can you please tell us the details of your record and the table involved?
I find the statement below confusing. If you delete the record, you delete the whole record..
"I want to be able to delete the record (no problem there) but no allow the user to delete the ContractNumber"

 

[GPGeorge]

Ah, slightly different situation then.

The combo box allows the user to delete the contract number from the "ContractNumber" field in the underlying table. That means it is BOUND to that field, not to the corresponding ID field. Why?
As Jack asked, please give us a full picture here.

 

[Weekleyba]

You’re right, I’m not giving the whole picture. I don’t have my computer with me so I’ll respond tomorrow with many more details.

 

[Weekleyba]

Attached is Sample DB1 of how I set this up and what the problem is.
Below shows the sample db screen shots. Note the Contract Number 75H33333333, and the Record Source for the SF_Contract is TJ_ProjectContract.
I want them to be able to delete the whole record, i.e. ProjContID 4 in the TJ_ProjectContract but I don't want them to be able to delete just the ContractID in SF_Contract. The row source for combo box is Q_ContractCombchooose.
Right now, you can click in the cboContractNumber and delete it. This does not delete the entire record but only the ContractID 3.

This shows the deleting of the ContractID 3 but not the entire record.

With this set up, I'm want the ability to delete the entire record in TJ_ProjectContract but not let the user simply delete the ContractID.

I was thinking of something like below but I'm falling short.

Private Sub cboContractNumber_Exit(Cancel As Integer)
Dim myMsg As Integer

If Nz(Me.cboContractNumber.Text, "") = "" Then
myMsg = MsgBox("You cannot have a BLANK field", vbOKCancel)
Cancel = True
Me.cboContractNumber.Requery
If myMsg = 1 Then
Cancel = -1
Else
Me.cboContractNumber.SetFocus
End If
End If

End Sub

 

[Weekleyba]

Maybe I got it...?
I placed this code in the BeforeUpdate event of the cboContractNumber.
What do you guys think of this solution? It seems to work.

Private Sub cboContractNumber_BeforeUpdate(Cancel As Integer)

Dim myMsg As Integer
    'True = -1
    If Nz(Me.cboContractNumber.Text, "") = "" Then
        myMsg = MsgBox("You cannot hava a BLANK field", vbOKCancel)
        Cancel = True
        Me.cboContractNumber.Undo
        If myMsg = 1 Then
            Cancel = -1
        Else
            Me.cboContractNumber.SetFocus
        End If
    End If
            
End Sub

 

[Weekleyba]

Thanks Pat. I’ll make that change.
When you say, it’s more appropriate to have the code in the form vs the combo box, is that more coding technique or can you run into trouble if it’s in the combo box?

 

[Weekleyba]

That makes perfect sense and is a great tip!
Thanks Pat.