Required field if enabled.

[Melaz29]

After some suggestions, I have a form with combo boxes, the combo boxes are set to enabled "no", but become enabled by an after update command on another field.

The after update command works perfectly, however once enabled I need to ensure data is selected, as opposed to leaving blank.

Any ideas on the best/easiest way?

 

[CEH]

something like this might do...
If Me.Combo.enabled = Yes And IsNull(FieldToBeRequired) Then
MsgBox "Required", vbCritical + vbOKOnly + vbDefaultButton1, "Missing Data"
Me.FieldToBeRequired.SetFocus
Cancel = True
End If

 

[Banana]

You also can set fields to be required in table design view. Access then will do this without any additional coding for you.

 

[Melaz29]

Thanks heaps...

Two things not a huge deal, but what is the reason for the

Cancel = true

And

It doesnt seem to be setting the focus properly.

 

[Melaz29]

Thanks banana, but if I set it to required it kept asking for data even if the field was diasbled.

On the same topic, where should I code it, before update doesnt seem to work.

I used on lost focus but my concern is that users may not click in the box at all.

 

[Banana]

If you look at the sub you will see it has Cancel as integer:

Private Sub SomeForm_BeforeUpdate (Cancel As Integer)

When set to true, the event is cancelled. Not all events has that. BeforeUpdate can be cancelled, whereas Close event can't be cancelled.

As for not setting focus properly, did you get a error or did the focus not change? Two most probable things I can think of is 1) the name for combobox is same as the field name for the table bound to form, confusing Access or 2) the combobox is on subform, which would require you to first focus to the subform then on the combobox.

Sorry- didn't realize you may not always need that data when it's not enabled.

 

[Melaz29]

I seem to have the set focus working ok, however I am still struggling with whre to put the code to ensure that the form cannot be closed or updated without the options being complete.

I have a print function with a me.refresh command, if I place the code in before update, it warns about the errors however still proceeds after clicking ok.

 

[Banana]

Ok.

You cannot have a refresh in before update. You see, Refresh and any function that would save a records would cause an infinite loop inside a before update. In other words, you're trying to saving a record then validating data in before update event, which says to save record, which needs to be validated, and so ad infinitium.

I'm not sure what print function is about, but does it really have to be in before update? What does it do?

Also, it's usually helpful to mention what kind of error you got; just saying you got an error isn't very descriptive. A error number and description would help things.

 

[Melaz29]

Sorry, not getting an error....the problem is that the message box appears and when I hit ok it just continues on.

In short

I have two fields that if enabled require data (problem resolved by code)

The problem is if a user tries to exit the form or initiate a report via a command button, the first line of the print command is me.refresh. The error message appears stating that the data is missing from the fields, however clicking ok on the error simply bypasses the error and prints the report.

What I need is to stop any actions like exiting the record, print a report etc....until the fields have data.

 

[Banana]

Okay- and what do you have in your code to check if there is any data in the enabled combobox?

 

[Melaz29]

Sorry if this is bunk but I am a self taught VBA user of about a month!

Here is the code:

Private Sub Vehicle_Make_LostFocus()
If Me.Vehicle_Make.Enabled = True And IsNull(Vehicle_Make) Then
MsgBox "Vehicle Make Required - please select from list", vbCritical + vbOKOnly + vbDefaultButton1, "Error"
Vehicle_Make.SetFocus
End If
End Sub




Also you are right with the combo box name and field name being the same is there a work around for to ensure that after clicking ok on the error it returns to the field?

 

[Banana]

Why in LostFocus?

Note that there is no Cancel; you cannot cancel this event.

Private Sub Vehicle_Make_LostFocus()

As previously mentioned, this should go into form's Before_Update event.

Private Sub SomeForm_BeforeUpdate(Cancel As Integer)

If Me.Vehicle_Make.Enabled = True And IsNull(Vehicle_Make) Then
      MsgBox "Vehicle Make Required - please select from list", vbCritical + vbOKOnly + vbDefaultButton1, "Error"
      Vehicle_Make.SetFocus
      Cancel = True
End If
End Sub

As for identical names; in combobox's property sheet, click on Other. There's a field for Name. Change to something like cboVehicle_Make. The code above should then change to refer to the Combobox, not the field.

HTH.

 

[Melaz29]

i placed it in the lost focus so as the users left the field that would get an error.

Ok I have placed it in before update on the form, it stops me exiting the form but if I use the print report commands (as I said they have a me.refresh command included)....the errors appear but the report prints after OK.

When trying to print how can I get the same response as trying to exit?

 

[Banana]

Okay I'm trying to wrap my head around this one.

You say that if it's in before event, and user choose Print from File or Print Report button, you get the message as below:

"Vehicle Make Required - please select from list"

then it prints okay?

I think the code should be something like

Private Sub SomeForm_BeforeUpdate(Cancel As Integer)

If Me.Vehicle_Make.Enabled = True And IsNull(Vehicle_Make) Then
      MsgBox "Vehicle Make Required - please select from list", vbCritical + vbOKOnly + vbDefaultButton1, "Error"
      Vehicle_Make.SetFocus
      Cancel = True
      Exit Sub
End If

'Print your report here

End Sub

or like this:

Private Sub SomeForm_BeforeUpdate(Cancel As Integer)

If Me.Vehicle_Make.Enabled = True And IsNull(Vehicle_Make) Then
      MsgBox "Vehicle Make Required - please select from list", vbCritical + vbOKOnly + vbDefaultButton1, "Error"
      Vehicle_Make.SetFocus
      Cancel = True
Else
     'Print your report here
End If
End Sub

Is that what you have? I'm sorry if I'm not understanding the whole picture.

 

[Melaz29]

It is ok I am an idiot....the report was missing the data so it wasnt updating at all....which is correct........the code will not let you exit so they will have to enter data.

Thanks heaps for your help!

 

[Banana]

No problem.

We all have our Doh! moment.

Glad you solved it.