Check to see if fields in form are set to null

[kjehel]

Hi.

I'm developing an ordering interface in Access 2003 using forms with rows (approx 50) describing per article including two text boxes - one for number of ordered articles and one for number of orders delivered. This interface works as intended.

The problem is that sometimes, people forget to set either "ordered" or "delivered" to a number, or at least to zero ("0"). If they don't set it to a number, the data table will contain null and this leads to a number of problems in my reporting tools.

What I'd like is a way of checking/validating:If either "ordered" or "delivered" is filled out with a number, the other (either "ordered" or "delivered") can't be null, and the person should be unable to save the complete order. This functionality needs to be done on a per order line basis, because not all orders include all types of articles. That is, if both "ordered" and "delivered" is null, this is ok, and will not be saved as an order record in the database.

What is a good and efficient way of achieving this?

Thanks a lot!

 

[RaviWildcat]

Hi there - if they were excel fields and you were checking for null fields, you could probably try something like =IF((A1=""),IF((B1=""),"both are null",""),"")

 

[SydneyGeek]

This sort of validation needs to be done at Form level, in the BeforeUpdate event.
Mapping it out, you could use something like...

Select Case True
 Case IsNull(Me.Ordered) And IsNull(Me.Delivered)
  'OK
 Case Me.Ordered>=0 And Me.Delivered>=0
  'OK
 Case Else
  'Violates: Message and cancellation
  MsgBox "If either Ordered or Delivered if filled out, the other field must be too"
  Cancel = True
End Select

Denis

 

[kjehel]

Denis, thanks a lot.

Will I need to do this validation per field? You wrote at form level, and I'm just checking my understanding, that I would need to iterate over all fields in the form? What would be the best way to do this with regard to your example?

I've attached a screenshot with some basic translation to english. There you'll see that there's four tabs, and each tab has multiple items with corresponding Ordered and Delivered fields.

Again thanks!

/Kjetil

This sort of validation needs to be done at Form level, in the BeforeUpdate event.
Mapping it out, you could use something like...

Select Case True
 Case IsNull(Me.Ordered) And IsNull(Me.Delivered)
  'OK
 Case Me.Ordered>=0 And Me.Delivered>=0
  'OK
 Case Else
  'Violates: Message and cancellation
  MsgBox "If either Ordered or Delivered if filled out, the other field must be too"
  Cancel = True
End Select

Denis

 

[SydneyGeek]

It looks like you have gone for a flat structure, where every line item is at the same level as the order.
That causes a number of problems because you end up with multiple duplicated fields, and any analysis is going to cause you all sorts of issues.

The order items should be in a subform, based on a table that is related to both Orders and Items --

tblOrders
OrderNo (Primary Key)
OrderDate
ClientID (linked to your Clients table)
... and other fields directly related to the order

tblProducts
ProductID (Primary key)
ProductDescription
ListPrice
ReorderLevel
...etc

these 2 tables are linked to tblOrderDetails
DetailID (Primary key)
OrderNo (joined to tblOrders)
ProductID (joined to tblProducts)
Ordered (date field?)
Delivered (date field?)

By creating a multi-line form based on this table, and making it a subform of Orders, you make it possible to use the validation I described because there is only 1 of each field in the form. If you want to show all the options it's possible to populate the subform with the Product items and give yourself an interface similar to the one you showed.

Denis

 

[kjehel]

I agree, but the challenge is that I've inherited the program from someone else, and there's a lot of forms, queries, etc that needs to be changed and updated if I were to convert to multi-line. I simply do not have the resources to do this. When this is the case, is it still feasable to achieve some sort of validation?

Kjetil

 

[SydneyGeek]

As long as the naming convention for the fields is consistent it should be possible to loop through them. You won't want to write out dozens of pairs.

Denis