• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 244
  • Last Modified:

Set a field's value in the BeforeUpdate event

I want to verify that a combo box has a value and, if it doesn't, warn the user and change it to the combo box's default value. So far I have this code:

Private Sub cboSortBy_BeforeUpdate(Cancel As Integer)
    If IsNull(cboSortBy) Then
        MsgBox "You must select a value", vbCritical, "Invalid Entry"
        Cancel = True
    End If
End Sub

I've tried to set the value by adding a line as follows, but it gives an error:

Private Sub cboSortBy_BeforeUpdate(Cancel As Integer)
    If IsNull(cboSortBy) Then
        MsgBox "You must select a value", vbCritical, "Invalid Entry"
       cboSortBy.Value = cboSortBy.DefaultValue
        Cancel = True
    End If
End Sub

Is there a way to set the value in this event?

Thanks in advance.
0
jrmcanada2
Asked:
jrmcanada2
  • 8
  • 4
  • 3
  • +3
1 Solution
 
Kelvin SparksCommented:
Why?  if you have set a default value for the combo correctly, by leaving it as NULL should make access put the default value into it - that's what defaults are for!

Kelvin
0
 
jrmcanada2Author Commented:
It does start with the default value in it, but the user can delete the entry (leaving the field blank). If they do that, I want it to put the default value back.
0
 
Kelvin SparksCommented:
I'm not sure that you can read it via VBA.

cboSortBy.DefaultValue is used to set a default value

ie

cboSortBy.DefaultVale = "ABC"

What you need to do is to stop them going anyfurther

I'd add and after update event

with
IF isnull(me.cboSortBy) then
       me.cboSortBy.SetFocus
End if

or similar to keep them there until they behave - also set the field's Required property to Yes that will force a value before saving.


Kelvin
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
jrmcanada2Author Commented:
There's actually no underlying field. It's a "Sort by" box in a dialog form that launches a report.

Right now, the code works fine in that it prevents the user from leaving the box blank. But if they do delete the entry it won't let them close the form until they select a valid entry. We can live with that if we have to, but I'd prefer not to. That's why I was hoping there was a way to put a value back in that field in the BeforeUpdate event.

Anyway, why I want to do it isn't hugely important. I'm just wondering if there's a way to assign a value to a field in the field's BeforeUpdate event.
0
 
Kelvin SparksCommented:
If you know the default value and it isn't a changing target just use
Me.cboSortBy = "ABC"

in the place where you had your default attempt.


Kelvin
0
 
Gustav BrockCIOCommented:
Let the application do the work silently for the user:

Private Sub cboSortBy_AfterUpdate()
    If IsNull(cboSortBy.Value) Then
        cboSortBy.Value = cboSortBy.DefaultValue
    End If
End Sub

/gustav
0
 
Dale FyeCommented:
Gustav has the right method.

You cannot change the value of a control in the BeforeUpdate event of the control, you can simply Cancel the update and display a message, so if you need to change the value, then use the AfterUpdate event of that control.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
You can do an .undo to put the prior value back.

Jim.
0
 
PatHartmanCommented:
In this particular case, using the control's BeforeUpdate event isn't a problem because the control has a default value.  But, if the control didn't have a default, the code would not trap empty values properly.

The correct event to use to trap for nulls is the FORM's BeforeUpdate event.  The only time control events work to trap nulls is if the user actually placed focus in the control and did something to trigger the control event you used to trap the error.  The Form's BeforeUpdate event works even if the user never entered the control being validated.
    If IsNull(Me.cboSortBy) Then
         MsgBox "You must select a value", vbCritical, "Invalid Entry"
        Me.cboSortBy = Me.cboSortBy.DefaultValue
        Me.cboSortBy.SetFocus
         Cancel = True
        Exit Sub
     End If

Open in new window


PS - it is better to set the default at the table level.  Then it doesn't matter if the user deletes the value in the form.  You also need to set the field as required in the table definition.
0
 
jrmcanada2Author Commented:
Hi Jim,

I've tried this but it doesn't put the old value back:

Private Sub cboSortBy_BeforeUpdate(Cancel As Integer)
    If IsNull(cboSortBy) Then
        MsgBox "You must select a value", vbCritical, "Invalid Entry"
        cboSortBy.Undo
        Cancel = True
    End If
End Sub

James
0
 
jrmcanada2Author Commented:
Hi Pat,

This is actually just a dialog form that launches a report. So there is no underlying table where I can set the default and I don't think the form's BeforeUpdate event fires.

James
0
 
jrmcanada2Author Commented:
Hi Gustav,

I like the AfterUpdate event ... it allows me to put the value back in. But my problem then is that I can't have the focus set back on the control. I've tried this but it still leaves the focus on the next control.

Private Sub cboSortBy_AfterUpdate()
    If IsNull(cboSortBy.Value) Then
        MsgBox "You must select a value", vbCritical, "Invalid Entry"
        cboSortBy.Value = cboSortBy.DefaultValue
        cboSortBy.SetFocus
    End If
End Sub
0
 
PatHartmanCommented:
If you are putting the default value back, why even bother with the message and moving focus,  why not just move on?
0
 
jrmcanada2Author Commented:
Because I don't know if they actually want the default value. The only reason I wanted to put it back is that right now, if they have deleted the value of the field and then decide to close the form, it won't let them - it keeps popping up the message saying they have to select something.

I realize that this is a VERY minor concern, but I posted the question here because I thought there might be an easy way around it that I didn't know about. Then I could tidy up this last tiny loose end.

But there doesn't seem to be a simple way around it, so I'll just leave it as it was. In the rare instance they decide to close the form at this point, they'll just have to live with populating the field first.

Thanks, everyone, for the suggestions.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
James,

<<I've tried this but it doesn't put the old value back:>>

  Just to be clear, it doesn't put the default value back, but the last valid entry in the control.

  To me, that's a little more consistent in terms of the interface than putting back the default value.  Say a user types:

"A"

 and that's accepted.   Now their keying head down and make a mistake wiping out the "A" (being on the wrong control).   .Undo put's the "A" back.

  If this is the first edit, then they will end up with the default value.  If it's an edit beyond that, they will end up with the last accepted value.

Jim.
0
 
jrmcanada2Author Commented:
Hi again Jim,

I agree ... the last valid entry would be better. But my problem is that when I try that code, it doesn't put anything back. It leaves the field blank.

Perhaps it would work if this were a form with an underlying table, but there is no underlying table. It's a dialog box to enter parameters to run a report.

Anyway, sorry to use up so much of your time. Thanks again!

James
0
 
PatHartmanCommented:
If the control isn't bound, the value isn't saved anywhere so what is the harm in simply allowing the form to close?  It makes sense to force a value so that the report will not fail so I would put back the default but  I probably wouldn't go beyond that.  If there are options for the report, perhaps displaying them in the header would eliminate any confusion.  I have a form dedicated to running reports and each report may have one or more criteria that can be used.  I build a string and put it in the report header so when the report is printed, they can see that - CM = Mary Jones AND Status = Open AND VA = True.  It helps the users greatly to know what they are looking at.
0
 
Kelvin SparksCommented:
Use the idea above of using the after update, but then use the cboSortBy.SetFocus to return the user to that control


Kelvin
0
 
Gustav BrockCIOCommented:
>  but it still leaves the focus on the next control.

That's a known issue. A trick is to move to a (dummy) control, then back:

Private Sub cboSortBy_AfterUpdate()
     If IsNull(cboSortBy.Value) Then
         cboSortBy.Value = cboSortBy.DefaultValue
         Me!DummyFocus.SetFocus
         cboSortBy.SetFocus
         MsgBox "You must select a value", vbCritical, "Invalid Entry"
     End If
 End Sub

/gustav
0
 
jrmcanada2Author Commented:
That works perfectly! Thank you!
0
 
Gustav BrockCIOCommented:
You are welcome!

/gustav
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 8
  • 4
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now