Force requirement based on yes/no (1 Viewer)

[Crabman1985]

Hi

I have two fields in my Table
Inspected - Yes/No - Which is defaulted to No
Date Inspected - Date/Time

I want to make the Date inspected a required input if the Inspected is yes

 

[Gasman]

If Inspected = True And IsNull([Date Inspected] Then....

Best not to have spaces in field/control names.
Put that in the Form's BeforeUpdate event along with any other validation.

 

[KitaYama]

I would get ride of Inspected yes/no field.
If Date_Inspected field has a date, it's inspected.
If Date_Inspected field is null, it's not inspected.

 

[bob fitz]

I would get ride of Inspected yes/no field.
If Date_Inspected field has a date, it's inspected.
If Date_Inspected field is null, it's not inspected.

I agree. The Yes/No field serves no purpose.

 

[Minty]

I am reading this the other way around - the field should probably be call InspectionRequired

If it's ticked then there should be an InspectedDate present.

In which case @Gasman is on the right path.

 

[KitaYama]

I am reading this the other way around - the field should probably be call InspectionRequired

If it's ticked then there should be an InspectedDate present.

You may be correct, but since the field is named Inspected, I guess it's a database that is used in a manufacturing system.
If it's so, then InspectionRecuired is decided in product design phase and goes to tblProducts. A product needs to be inspected always or never. It doesn't change on each order.
Then with each order, inspection date and the person in charge of inspection is registered, which goes to tblOrderDetails or tblProcesses.

I can't think of a situation where InspectionRequired and inspectionDate goes to the same table.
But, who knows. We have to wait until the OP responds.

 

[jdraw]

Crabman1985,

Readers need more info to understand your requirement in context. Perhaps you could provide an example or 2 to clarify your need.

 

[MajP]

If you decide to go this route, you ALWAYS make your date validation in the before update event of the form. This is ALWAYS the correct place to validate data.

Private Sub Form_BeforeUpdate(Cancel As Integer)
   ' Check inspected
    If me.inspected and not isdate(me.InspectionDate) Then
       ' Alert the user.
       MsgBox "You must supply and inspection date."
       ' Cancel the update.
      Cancel = True
    End If
End Sub

Perform simple data validation checks when editing a record in a form

Office VBA reference topic

learn.microsoft.com

You can also alert the user in the Inspected after update event.
Msgbox "You selected 'Inspected', make sure to provide an inspection date before leaving this record.", Vbinformation, "Date Required"

 

[Crabman1985]

@Gasman This was just a typo here, There's no spaced in database. All spaces replaced with _

The field is to flag incomplete inspections on every project
The Yes/No Field seems (after reading through) that it serves no purpose as the date field will confirm if inspection was done or not. Will be using the date to do this query
I've removed it from the table

 

[Gasman]

@Gasman This was just a typo here, There's no spaced in database. All spaces replaced with _

The field is to flag incomplete inspections on every project
The Yes/No Field seems (after reading through) that it serves no purpose as the date field will confirm if inspection was done or not. Will be using the date to do this query
I've removed it from the table

So a unit could be part inspected?, but you cannot then put a date as to when that was carried out, as you would believe it had been fully inspected?

 

[Crabman1985]

So a unit could be part inspected?, but you cannot then put a date as to when that was carried out, as you would believe it had been fully inspected?

No
A project cannot be inspected partially.
the date only option is working for me