Query Form

D

Dennis

I have a Query Form in which I have a list box that has a
table as it's recordsource. The recordsource is to supply
the list of units to select. This List box allows me to
choose one unit, or any units or a range of units I
select. Once I select the Units, it populates an unbound
field called txtValList and then the query returns those
records on my continious form. The problem I am having is
it only works with 13 or less units. More than 13 units
and nothing happens not even an error message. The design
of the form is intended to be able to select up to 1,000
units and return the records of those units. My question
is, why would it limit me to only 13 records? What
settings or parameters should I be looking at to fix this
problem?

Thanks,

Dennis
 
J

John Vinson

I have a Query Form in which I have a list box that has a
table as it's recordsource. The recordsource is to supply
the list of units to select. This List box allows me to
choose one unit, or any units or a range of units I
select. Once I select the Units, it populates an unbound
field called txtValList and then the query returns those
records on my continious form. The problem I am having is
it only works with 13 or less units. More than 13 units
and nothing happens not even an error message. The design
of the form is intended to be able to select up to 1,000
units and return the records of those units. My question
is, why would it limit me to only 13 records? What
settings or parameters should I be looking at to fix this
problem?
Click to expand...

Please post your code.

A query with 1000 criteria (using OR logic or even the more efficient
In() clause) will very likely be too big for Access to handle, but 13
should be easily doable.

Any chance you could use a (perhaps temporary) table with a checkbox
to allow the user to select records? This would let you use a JOIN
query rather than trying to use 1000 criteria.
 
D

Dennis

I probably should have given query code for form query:

SELECT *
FROM [Diagnoses Report Query]
WHERE (UNIT=[Forms]![DiagnosesBuildingForm]![cboUnitFirst]
OR [Forms]![DiagnosesBuildingForm]![cboUnitFirst] IS NULL);
 
D

Duane Hookom

What does txtValList have to do with
[Forms]![DiagnosesBuildingForm]![cboUnitFirst]?


--
Duane Hookom
MS Access MVP


Dennis said:
I probably should have given query code for form query:

SELECT *
FROM [Diagnoses Report Query]
WHERE (UNIT=[Forms]![DiagnosesBuildingForm]![cboUnitFirst]
OR [Forms]![DiagnosesBuildingForm]![cboUnitFirst] IS NULL);
-----Original Message-----
I have a Query Form in which I have a list box that has a
table as it's recordsource. The recordsource is to supply
the list of units to select. This List box allows me to
choose one unit, or any units or a range of units I
select. Once I select the Units, it populates an unbound
field called txtValList and then the query returns those
records on my continious form. The problem I am having is
it only works with 13 or less units. More than 13 units
and nothing happens not even an error message. The design
of the form is intended to be able to select up to 1,000
units and return the records of those units. My question
is, why would it limit me to only 13 records? What
settings or parameters should I be looking at to fix this
problem?

Thanks,

Dennis
.
Click to expand...
Click to expand...
 
D

Dennis

As you can see I posted the wrong part of the code. I did
not mention the other query critera but they are all
included in the code as follows:

SELECT *
FROM [Diagnoses Report Query]
WHERE ((([Diagnoses Report Query].UNIT)=[Forms]!
[DiagnosesBuildingForm]![cboUnitFirst] And ([Diagnoses
Report Query].UNIT) Between [Forms]!
[DiagnosesBuildingForm]![cboUnitFirst] And [Forms]!
[DiagnosesBuildingForm]![cboUnitLast]) AND ((InParam
([Diagnoses Report Query].[Unit],[Forms]!
[DiagnosesBuildingForm]![txtValList]))=True)) OR
((([Diagnoses Report Query].UNIT) Between [Forms]!
[DiagnosesBuildingForm]![cboUnitFirst] And [Forms]!
[DiagnosesBuildingForm]![cboUnitLast]) AND ((InParam
([Diagnoses Report Query].[Unit],[Forms]!
[DiagnosesBuildingForm]![txtValList]))=True) AND (([Forms]!
[DiagnosesBuildingForm]![cboUnitFirst]) Is Null)) OR
((([Diagnoses Report Query].UNIT)=[Forms]!
[DiagnosesBuildingForm]![cboUnitFirst]) AND ((InParam
([Diagnoses Report Query].[Unit],[Forms]!
[DiagnosesBuildingForm]![txtValList]))=True) AND (([Forms]!
[DiagnosesBuildingForm]![cboUnitLast]) Is Null)) OR
(((InParam([Diagnoses Report Query].[Unit],[Forms]!
[DiagnosesBuildingForm]![txtValList]))=True) AND (([Forms]!
[DiagnosesBuildingForm]![cboUnitFirst]) Is Null) AND
(([Forms]![DiagnosesBuildingForm]![cboUnitLast]) Is Null))
OR ((([Diagnoses Report Query].UNIT)=[Forms]!
[DiagnosesBuildingForm]![cboUnitFirst] And ([Diagnoses
Report Query].UNIT) Between [Forms]!
[DiagnosesBuildingForm]![cboUnitFirst] And [Forms]!
[DiagnosesBuildingForm]![cboUnitLast]) AND (([Forms]!
[DiagnosesBuildingForm]![txtValList]) Is Null)) OR
((([Diagnoses Report Query].UNIT) Between [Forms]!
[DiagnosesBuildingForm]![cboUnitFirst] And [Forms]!
[DiagnosesBuildingForm]![cboUnitLast]) AND (([Forms]!
[DiagnosesBuildingForm]![cboUnitFirst]) Is Null) AND
(([Forms]![DiagnosesBuildingForm]![txtValList]) Is Null))
OR ((([Diagnoses Report Query].UNIT)=[Forms]!
[DiagnosesBuildingForm]![cboUnitFirst]) AND (([Forms]!
[DiagnosesBuildingForm]![cboUnitLast]) Is Null) AND
(([Forms]![DiagnosesBuildingForm]![txtValList]) Is Null))
OR ((([Forms]![DiagnosesBuildingForm]![cboUnitFirst]) Is
Null) AND (([Forms]![DiagnosesBuildingForm]![cboUnitLast])
Is Null) AND (([Forms]![DiagnosesBuildingForm]!
[txtValList]) Is Null));

I know it should be shorter but I'm not skilled enough to
do it.

Two parts of this code does not work correctly. One is the
problem mentioned below (only 13 units or less return
records in the InParam statements) the other is
the "Between" "And" which should allow me to select a
range of units but only returns the first record.

My code for InParam is as follows:

Function GetToken(stLn, stDelim)

Dim iDelim As Integer, stToken As String
iDelim = InStr(1, stLn, stDelim)
If (iDelim <> 0) Then
stToken = LTrim$(RTrim$(Mid$(stLn, 1, iDelim - 1)))
stLn = Mid$(stLn, iDelim + 1)
Else
stToken = LTrim$(RTrim$(Mid$(stLn, 1)))
stLn = ""
End If
GetToken = stToken

End Function

Function InParam(fld, Param)

Dim stToken As String
If IsNull(fld) Then fld = ""
Do While (Len(Param) > 0)
stToken = GetToken(Param, ",")
If stToken = LTrim$(RTrim$(fld)) Then
InParam = -1
Exit Function
Else
InParam = 0
End If
Loop

End Function

Thanks,

Dennis
 
D

Dennis

I can get this to work by itself:

SELECT *
FROM [Diagnoses Report Query]
WHERE ((UNIT) Between [Forms]![DiagnosesBuildingForm]!
[cboUnitFirst] And ([Forms]![DiagnosesBuildingForm]!
[cboUnitLast]) Or [Forms]![DiagnosesBuildingForm]!
[cboUnitFirst] Is Null);

And I can get this to work by itself:

SELECT *
FROM [Diagnoses Report Query]
WHERE (UNIT=[Forms]![DiagnosesBuildingForm]![cboUnitFirst]
Or [Forms]![DiagnosesBuildingForm]![cboUnitFirst] Is Null);

And this to work by itself (But with only 13 records):

SELECT *
FROM [Diagnoses Report Query]
WHERE (InParam([Diagnoses Report Query].[UNIT],[Forms]!
[DiagnosesBuildingForm]![txtValList])=True Or [Forms]!
[DiagnosesBuildingForm]![txtValList] Is Null);

But I cannot get it to work together? Why is that?

Thanks,

Dennis
 
J

John Vinson

And this to work by itself (But with only 13 records):

SELECT *
FROM [Diagnoses Report Query]
WHERE (InParam([Diagnoses Report Query].[UNIT],[Forms]!
[DiagnosesBuildingForm]![txtValList])=True Or [Forms]!
[DiagnosesBuildingForm]![txtValList] Is Null);

But I cannot get it to work together? Why is that?
Click to expand...

Evidently something wrong with the InParam() function - which may be
truncating the ValList. Could you please post the code?
 
D

Dennis

John,

My code for InParam is as follows:

Function GetToken(stLn, stDelim)

Dim iDelim As Integer, stToken As String
iDelim = InStr(1, stLn, stDelim)
If (iDelim <> 0) Then
stToken = LTrim$(RTrim$(Mid$(stLn, 1, iDelim - 1)))
stLn = Mid$(stLn, iDelim + 1)
Else
stToken = LTrim$(RTrim$(Mid$(stLn, 1)))
stLn = ""
End If
GetToken = stToken

End Function

Function InParam(fld, Param)

Dim stToken As String
If IsNull(fld) Then fld = ""
Do While (Len(Param) > 0)
stToken = GetToken(Param, ",")
If stToken = LTrim$(RTrim$(fld)) Then
InParam = -1
Exit Function
Else
InParam = 0
End If
Loop

End Function

Thanks,

Dennis


-----Original Message-----
And this to work by itself (But with only 13 records):

SELECT *
FROM [Diagnoses Report Query]
WHERE (InParam([Diagnoses Report Query].[UNIT],[Forms]!
[DiagnosesBuildingForm]![txtValList])=True Or [Forms]!
[DiagnosesBuildingForm]![txtValList] Is Null);

But I cannot get it to work together? Why is that?
Click to expand...

Evidently something wrong with the InParam() function - which may be
truncating the ValList. Could you please post the code?


.
Click to expand...
 
J

John Vinson

John,

My code for InParam is as follows:
Click to expand...

I wonder if Param is getting truncated to 255 bytes. Try putting a
breakpoint on the first executable statement of InParam and stepping
through the code, checking the values of the variables and the
dimensions of the array.
 
Top