Need Help filtering multiple tables in a form

[at51178]

Hey guys

I have a question

I have a table called "DepInfo"
in the table I have
8 fields named
PartTimer
FirstName
LastName
Position
TeamLeader
WorkNumber
EmployeeID
Tnumbers

I created a form with all these fields in them.
What I would like to do is on the On Open event of the form is to have access look at all the fields and return any fields that have a null value in them.

I know what you may be thinking set the field to required field but the problem is that when I have new users I may not receive all their information at the same time.

Let me know if this can be done thanks

 

[Kherozen]

Dim myControl as Control

Select Case myControl.ControlType
Case acTextBox, acComboBox, acListBox, acOptionGroup, acCheckBox
If IsNull(myControl) Then
myControl.setfocus ' (if by Returning you meant set focus to)
End Select
Next myControl

that should do the work

 

[Kherozen]

heh, no that shouldn't work



Dim myControl as Control

For Each myControl in Me.Controls '<---- that was missing
Select Case myControl.ControlType
Case acTextBox, acComboBox, acListBox, acOptionGroup, acCheckBox
If IsNull(myControl) Then
myControl.setfocus ' (if by Returning you meant set focus to)
End Select
Next myControl

' hum and i'm ain't sure that what I just wrote is right
' probably a Exit For IsNull(myControl) is more
'appropriate..since I think what my code is doing
' will just set focus and continu to loop through the
' controls ... sorry I wrote the code too fast ...

 

[Yarbz]

Greetings!
The easiest way I can think of is to run your form off a Query.
Create a new query based on the table desired and in the different fields place in the first Criteria row &quot;Is Null&quot; go down one line and over one field and place in &quot;Is Null&quot; go down one and over one etc until all fields are covered.
Create a form based on that Query (It can look just like the other form but named different)

All records with one or more blank field will show up and can be filled in as desired.

If you wish to see all records add a new line in the criteria and put &quot;Is Not Null&quot; in all fields on the same line.

Hope this helps.
Yarbz

 

[at51178]

First off thanks for responding I appreciate it

In response to kherozen I am not looking to have the field setfocus on a blank field what I am looking for is to return only those records that have a blank record

So if my database looks like this
Parttimer|FName|LName|posit|teamleader|work#|EmpID|T#'S
|1|Fulltimer|John |Smith|Agent|Adam |1234 |5555 |T1255
|2|<Blank> |Joe |Smith|Agent|Adam |1234 |5556 |T1256

So in this example I have record one where all the fields are filled in and record two where everything is filled in except the Fulltime status I would like to have the form only return the second row in this example or any rows where the field is blank.

In response to Yarbz your method would work only if there was one field that I was looking for but in this case I am looking for all the fields to see if any of them are blank

 

[Kherozen]

try something like that
Dim stLinkCriteria as string
Dim stDocName as string
Dim myControl as control

stDocName = &quot;yourform&quot;
for each myControl in me.Controls
stLinkCriteria = &quot;If(&quot;
Select Case myControl.ControlType
Case acTextBox, acComboBox, acListBox, acOptionGroup, acCheckBox

If Not IsNull(myControl) Then
stLinkCriteria = stLinkCriteria & &quot;False&quot; & &quot; OR &quot;
Else
stLinkCriteria = stLinkCriteria & &quot;True&quot; & &quot; OR &quot;
End If
End Select
Next myControl
'the idea is that if all the field are filled it will
' return false
dim lenght as integer
lenght = len(stLinkCriteria)
stLinkCriteria = Left(stLinkCriteria, lenght - 4) 'so it
'is not terminated by a &quot; OR &quot;
stLinkCriteria = stLinkCriteria & &quot&quot;
DoCmd.OpenForm stDocName,,,stLinkCtriteria

 

[at51178]

Sorry that didn't work I get a variable not defined and the last line stlinkcriteria is highlighted in blue

Oh ya I am using access 2000 if that helps

 

[Kherozen]

DoCmd.OpenForm stDocName,,,stLinkCtriteria

should be

DoCmd.OpenForm stDocName,,,stLinkCriteria