Solved Allow Record Deletions but Not Blank Field in Combo Box (1 Viewer)

Weekleyba

Registered User.
Local time
Today, 05:10
Joined
Oct 10, 2013
Messages
586
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

Grover Park George
Local time
Today, 03:10
Joined
Nov 25, 2004
Messages
1,775
Requery the combo box after deleting the record.
 

Weekleyba

Registered User.
Local time
Today, 05:10
Joined
Oct 10, 2013
Messages
586
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

Super Moderator
Staff member
Local time
Today, 06:10
Joined
Jan 23, 2006
Messages
15,361
@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

Grover Park George
Local time
Today, 03:10
Joined
Nov 25, 2004
Messages
1,775
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

Registered User.
Local time
Today, 05:10
Joined
Oct 10, 2013
Messages
586
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:10
Joined
Feb 19, 2002
Messages
42,970
The form's BeforeUpdate event is the last event that runs before a record gets saved. This is the event you would use for the majority of your validation code. If there is some error, then you would prevent the record from being saved by cancelling the update. Think of this event as the flapper at the end of a funnel. If the flapper is open, the record passes through and gets saved. If there is an error, you close the flapper tight and nothing gets through:)

If Me.DOB > Date() Then
Msgbox "DOB may not be > today's date.",vbOKOnly
Cancel = True
Me.DOB.SetFocus
Exit Sub
End If


This leaves the record dirty and allows the user to go back and fix the problem. You can add a Cancel button which removed all changes by using
Me.Undo
And that makes the record not dirty so the form can be closed without raising an error.

OR, you can teach your users how to use the exc key to back out changes. One Esc backs out the most recent change, Esc, Esc, backs out all changes.
 

Weekleyba

Registered User.
Local time
Today, 05:10
Joined
Oct 10, 2013
Messages
586
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.

1655817625447.png



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



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
 

Attachments

  • Sample DB1.zip
    41.5 KB · Views: 103

Weekleyba

Registered User.
Local time
Today, 05:10
Joined
Oct 10, 2013
Messages
586
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.

Code:
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
 

Attachments

  • Sample DB2.zip
    54.1 KB · Views: 115

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:10
Joined
Feb 19, 2002
Messages
42,970
It is more appropriate to put the code into the Form's BeforeUpdate event.
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)

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

You are not giving the user a choice so there is no point in making the msgbox a question.

I never use the .Undo unless I am never going to allow the user to change the record. If he can change it but the change is incorrect, the user needs to see his error.
 

Weekleyba

Registered User.
Local time
Today, 05:10
Joined
Oct 10, 2013
Messages
586
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?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:10
Joined
Feb 19, 2002
Messages
42,970
In this particular case, you have only ONE control on the form so putting the code there won't cause a problem. However, if you were to add a second field, then, it would be possible for the user to type something directly into the second field and save the record. The contract combo's BeforeUpdate event would not run, so there would be no indication of any error. However, by using the form's BeforeUpdate event, as long as the record has been dirtied somehow, the validation will happen.

So, the recommendation is a defensive programming tip rather than a problem in this very specific situation. If this type of code is always placed in the form's BeforeUpdate event, you will never have a problem and you will never save an invalid record. However, if you leave the code where it is, you won't get burned this time but next time you need to do the same thing you will get burned if you have two fields instead of just one. There are reasons I might use a control's BeforeUpdate event but NEVER to check for null. Null checks must happen in the Form's BeforeUpdate event because if a control never gains the focus or never gets modified, the control events will never fire.
 

Weekleyba

Registered User.
Local time
Today, 05:10
Joined
Oct 10, 2013
Messages
586
That makes perfect sense and is a great tip!
Thanks Pat.
 

Users who are viewing this thread

Top Bottom