Checking for fields with data

Topic

New Reply

I’ve setup an option group on the first page of a tab control so users can nominate the types data the application should expect for the remaining tabs. Option 1 successfully enables or disables tab 3. However, if any of the 22 fields or 2 subforms on tab 3 already has data then a message alert needs to appear. There needs to be safe guard just in case someone needs to disable tab 3 but another person has already entered data on tab 3.

Is there an easy way to test 22 fields and 2 subforms for the existance of any data, rather than listing all individually?

Many thanks

Reply | Quote

Replies

I do something (originally stolen from MSAccess help files) commonly on a form I create originally with all the field names. Then, I create the field names in a fixed format (prefaced by “qbf_” for instance). Then I remove the datasource — but the fields are all still named, and drop-down selections are still there, and other things are in place.

You will have to get fancier to look at the field type in building your code, but, basically, to scroll through all the fields in a particular form:

Dim actualFldName As String
Dim frm As Form, ctl As Control, intI As Integer

Set frm = Forms!WhateverYourFormNameIs
For intI = 0 To frm.Count – 1
Set ctl = frm(intI)
If Left$(ctl.Name, 3) = “qbf” Then
If IsNull(ctl) Then
Else
actualFldName = Mid$(ctl.Name, 4)
… and then you build your criteria string using the actualFldName and the value, and whether you will use the “like” keyword or “=” or — I think in your case, you are just looking for if some information exists. Then you close all this out and set your objects to nothing, of course.

Hope this helps!
Pat

Reply | Quote