Find certain Criteria in a form

[Martin Hopkins]

hello,

I have created a log on form where the user enters name, employee number.

Also they select what records they wish to view from two combo boxes. They
select a Wing and a Sqn.

When they select view records, they only see the records for the above
selection.

That is great, but I need soome people to see all records for a Wing. I
cannot get this to work.

The code used behind the open records button is:

stDocName = "FrmPersonnel"

stLinkCriteria = "[Wing_ID]=" & Me![wing_id]
stLinkCriteria = "[Squadron_ID]=" & Me![Squadron_id]
DoCmd.OpenForm stDocName, , , stLinkCriteria

How can I get the restriction in returning certain records if say I want to
see a particular wing and I do not enter any Squadron value?

thanks for any help.

Martin Hopkins

 

[Barry Gilbert]

hello,

I have created a log on form where the user enters name, employee number.

Also they select what records they wish to view from two combo boxes. They
select a Wing and a Sqn.

When they select view records, they only see the records for the above
selection.

That is great, but I need soome people to see all records for a Wing. I
cannot get this to work.

The code used behind the open records button is:

stDocName = "FrmPersonnel"

stLinkCriteria = "[Wing_ID]=" & Me![wing_id]
stLinkCriteria = "[Squadron_ID]=" & Me![Squadron_id]
DoCmd.OpenForm stDocName, , , stLinkCriteria

How can I get the restriction in returning certain records if say I want to
see a particular wing and I do not enter any Squadron value?

First of all, I don't see how this code could work. The second statement
overwrites stLinkCriteria so how does the WindId ever get to the OpenForm
command? I think you need a set of If/Then statements:

If Not IsNull(Me![wing_id]) Then
strLinkCriteria = "[Wing_ID]=" & Me![wing_id]
End If
If Not IsNull( Me![Squadron_id]) Then
If stLinkCriteria <>"" Then
stLinkCriteria = stLinkCriteria & " AND "
End If
stLinkCriteria = stLinkCriteria & "[Squadron_ID]=" & Me![Squadron_id]
End If
DoCmd.OpenForm stDocName, , , stLinkCriteria

Barry

 

[Martin Hopkins]

Barry,

Thanks very much..it worked. I can see how itr works now.

Only one small problem...if the user leaves all selection blank then all
returns are returned.

Can this be prevented eg if field cannot be left null.

I can talk my way into the solution but writing it is still tricky for me.

martin

hello,

I have created a log on form where the user enters name, employee number.

Also they select what records they wish to view from two combo boxes.
They
select a Wing and a Sqn.

When they select view records, they only see the records for the above
selection.

That is great, but I need soome people to see all records for a Wing. I
cannot get this to work.

The code used behind the open records button is:

stDocName = "FrmPersonnel"

stLinkCriteria = "[Wing_ID]=" & Me![wing_id]
stLinkCriteria = "[Squadron_ID]=" & Me![Squadron_id]
DoCmd.OpenForm stDocName, , , stLinkCriteria

How can I get the restriction in returning certain records if say I want
to
see a particular wing and I do not enter any Squadron value?

First of all, I don't see how this code could work. The second statement
overwrites stLinkCriteria so how does the WindId ever get to the OpenForm
command? I think you need a set of If/Then statements:

If Not IsNull(Me![wing_id]) Then
strLinkCriteria = "[Wing_ID]=" & Me![wing_id]
End If
If Not IsNull( Me![Squadron_id]) Then
If stLinkCriteria <>"" Then
stLinkCriteria = stLinkCriteria & " AND "
End If
stLinkCriteria = stLinkCriteria & "[Squadron_ID]=" & Me![Squadron_id]
End If
DoCmd.OpenForm stDocName, , , stLinkCriteria

Barry

 

[Barry Gilbert]

Barry,

Thanks very much..it worked. I can see how itr works now.

Only one small problem...if the user leaves all selection blank then all
returns are returned.

Can this be prevented eg if field cannot be left null.

I can talk my way into the solution but writing it is still tricky for me.

Do you mean that you want to prevent them from leaving out a criteria
selection? If so, you can add a little code like this:

If IsNull(Me![wing_id]) Then
MsgBox "Enter a Wind Id", vbOkOnly+vbInformation, Missing criteria"
Else
strLinkCriteria = "[Wing_ID]=" & Me![wing_id]
End If
If Not IsNull( Me![Squadron_id]) Then
If stLinkCriteria <>"" Then
stLinkCriteria = stLinkCriteria & " AND "
End If
stLinkCriteria = stLinkCriteria & "[Squadron_ID]=" & Me![Squadron_id]
End If
DoCmd.OpenForm stDocName, , , stLinkCriteria

Barry

 

[Martin Hopkins]

Barry,

Thanks again. I put the " before Missing and it worked...

But the Open form command still worked and the user was given all records.

Yes, I would like the user to enter at least 1 of the 2 criteria and if not
when they press the open form button they get the polite repoly Missing
criteria.

I have tried to add to your help but to no avail I must go on a VB course.

Again thanks in advance for help.

Martin

Barry,

Thanks very much..it worked. I can see how itr works now.

Only one small problem...if the user leaves all selection blank then all
returns are returned.

Can this be prevented eg if field cannot be left null.

I can talk my way into the solution but writing it is still tricky for
me.

Do you mean that you want to prevent them from leaving out a criteria
selection? If so, you can add a little code like this:

If IsNull(Me![wing_id]) Then
MsgBox "Enter a Wind Id", vbOkOnly+vbInformation, Missing criteria"
Else
strLinkCriteria = "[Wing_ID]=" & Me![wing_id]
End If
If Not IsNull( Me![Squadron_id]) Then
If stLinkCriteria <>"" Then
stLinkCriteria = stLinkCriteria & " AND "
End If
stLinkCriteria = stLinkCriteria & "[Squadron_ID]=" & Me![Squadron_id]
End If
DoCmd.OpenForm stDocName, , , stLinkCriteria

Barry

 

[Barry Gilbert]

But the Open form command still worked and the user was given all records.

Yes, I would like the user to enter at least 1 of the 2 criteria and if not
when they press the open form button they get the polite repoly Missing
criteria.

My mistake. If you want to force at least one, try this:

If IsNull(Me.WingId]) And IsNull(Me.[Squandron_Id) Then
MsgBox "Enter at least one criterion", vbOkOnly+vbInformation, "Missing
criteria"
Else
If Not IsNull(Me.WingId) Then
strLinkCriteria = "[Wing_ID]=" & Me![wing_id]
End IF
If Not IsNull( Me![Squadron_id]) Then
If stLinkCriteria <>"" Then
stLinkCriteria = stLinkCriteria & " AND "
End If
stLinkCriteria = stLinkCriteria & "[Squadron_ID]=" & Me![Squadron_id]
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If

This is really the simplest part of VBA. If you understand If/Then/Else
logic, it's not too tough to work with different scenarios.

Barry

 

[Martin Hopkins]

Barry,

thanks for the informnation. Got it to work as follows:

If IsNull(Me.[Wing_id]) And IsNull(Me.[Squadron_ID]) And
IsNull(Me.section_id) Then
MsgBox "Enter at least one criterion", vbOKOnly + vbInformation,
"Missing criteria"
Else
If Not IsNull(Me.[Wing_id]) Then
stLinkCriteria = "[Wing_id]=" & Me![Wing_id]
End If
If Not IsNull(Me.[Squadron_ID]) Then
stLinkCriteria = "[squadron_id]=" & Me![Squadron_ID]
End If
If Not IsNull(Me.[section_id]) Then
stLinkCriteria = "[Section_id]=" & Me![section_id]
End If
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If

lets the user at least one criteria otherwise they get the mesage above.

Martin

But the Open form command still worked and the user was given all
records.

Yes, I would like the user to enter at least 1 of the 2 criteria and if
not
when they press the open form button they get the polite repoly Missing
criteria.

My mistake. If you want to force at least one, try this:

If IsNull(Me.WingId]) And IsNull(Me.[Squandron_Id) Then
MsgBox "Enter at least one criterion", vbOkOnly+vbInformation, "Missing
criteria"
Else
If Not IsNull(Me.WingId) Then
strLinkCriteria = "[Wing_ID]=" & Me![wing_id]
End IF
If Not IsNull( Me![Squadron_id]) Then
If stLinkCriteria <>"" Then
stLinkCriteria = stLinkCriteria & " AND "
End If
stLinkCriteria = stLinkCriteria & "[Squadron_ID]=" &
Me![Squadron_id]
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If

This is really the simplest part of VBA. If you understand If/Then/Else
logic, it's not too tough to work with different scenarios.

Barry