Open form with linkcriteria

  • Thread starter Thread starter Pulse
  • Start date Start date
P

Pulse

the problem is that i need to open the forms with all the
records where klasID = number(1) and all the records
where klasID = number(2) .. the 'and' part of the
statement works fine, but the or won't work.


anyone got an idea?


This doesn't work:
----------------------------
CODE
----------------------------
stLinkCriteria = "[KlasID]=" & "nummer(1)" & "Or [KlasID]
=" & nummer(2) & "And [VakId] =" & Me.VakID
DoCmd.OpenForm "frmPuntenKlasVakLeerling", , ,
stLinkCriteria
----------------------------

Another problem is that it are not always 2 criteria for
the klasID i just used this as an example ... the number
of criteria is max 5
but if i can get it to work with 2 criteria it will
probably no problem to find a way to use the orher
criteria
 
It probably has to do with logical grouping. When you use the OR condition
with the AND condition in the same statement, you should group the ORs like
this:

---
stLinkCriteria = "([KlasID]=" & "nummer(1)" & "Or [KlasID] =" & nummer(2) &
") And [VakId] =" & Me.VakID
---

Another alternative would be to use the In() condition which will perform
and OR logic on two or more values:

[MyField] In("MyValue1", "MyValue2", "MyValue3")

This example would return all records with any of the 3 values listed in the
field being checked.

Hope this helps,
- Glen
 
Pulse said:
the problem is that i need to open the forms with all the
records where klasID = number(1) and all the records
where klasID = number(2) .. the 'and' part of the
statement works fine, but the or won't work.


anyone got an idea?


This doesn't work:
----------------------------
CODE
----------------------------
stLinkCriteria = "[KlasID]=" & "nummer(1)" & "Or [KlasID]
=" & nummer(2) & "And [VakId] =" & Me.VakID
DoCmd.OpenForm "frmPuntenKlasVakLeerling", , ,
stLinkCriteria
----------------------------

Another problem is that it are not always 2 criteria for
the klasID i just used this as an example ... the number
of criteria is max 5
but if i can get it to work with 2 criteria it will
probably no problem to find a way to use the orher
criteria
Click to expand...

You have nummer(1) in quotes, thus building that text -- "nummer(1)" --
into stLinkCriteria, rather than the value of nummer(1). Glen
Appleton's suggestion about using parentheses, or else using an In
clause, is also a good one. Combining that with your statement that
you might have up to five criteria for KlasID, it seems to me you might
use code like this:

Dim I As Integer

strLinkCriteria = vbNullString

For I = 1 To 5
If Len(nummer(I) > 0 Then
stLinkCriteria = stLinkCriteria & "," & nummer(I)
End If
Next I

If Len(stLinkCriteria) > 0 Then
stLinkCriteria = _
"[KlasID] In (" & Mid(stLinkCriteria, 2) & ") And "
End If

stLinkCriteria = stLinkCriteria & "[VakId] =" & Me.VakID
 
thanks for all the help ...
but i realise that the 'or' is the poblem.
It just won't work;
when i do this:
---------------------------
stLinkCriteria = "[KlasID]=" & nummer(2) & "Or [KlasID]
=" & nummer(1)
---------------------------
or:
---------------------------
stLinkCriteria = "[KlasID]=" & nummer(1) & "Or [KlasID]
=" & nummer(2)
---------------------------

It is always the number(1) and never number(2) (or both)
that is (are) used.

.... the or doesn't work in my programm in an openform-
comand
-----Original Message-----
Pulse said:
the problem is that i need to open the forms with all the
records where klasID = number(1) and all the records
where klasID = number(2) .. the 'and' part of the
statement works fine, but the or won't work.


anyone got an idea?


This doesn't work:
----------------------------
CODE
----------------------------
stLinkCriteria = "[KlasID]=" & "nummer(1)" & "Or [KlasID]
=" & nummer(2) & "And [VakId] =" & Me.VakID
DoCmd.OpenForm "frmPuntenKlasVakLeerling", , ,
stLinkCriteria
----------------------------

Another problem is that it are not always 2 criteria for
the klasID i just used this as an example ... the number
of criteria is max 5
but if i can get it to work with 2 criteria it will
probably no problem to find a way to use the orher
criteria
Click to expand...

You have nummer(1) in quotes, thus building that text -- "nummer(1)" --
into stLinkCriteria, rather than the value of nummer (1). Glen
Appleton's suggestion about using parentheses, or else using an In
clause, is also a good one. Combining that with your statement that
you might have up to five criteria for KlasID, it seems to me you might
use code like this:

Dim I As Integer

strLinkCriteria = vbNullString

For I = 1 To 5
If Len(nummer(I) > 0 Then
stLinkCriteria = stLinkCriteria & "," & nummer(I)
End If
Next I

If Len(stLinkCriteria) > 0 Then
stLinkCriteria = _
"[KlasID] In (" & Mid(stLinkCriteria, 2) & ") And "
End If

stLinkCriteria = stLinkCriteria & "[VakId] =" & Me.VakID

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.
Click to expand...
 
thanks for all the help ...
but i realise that the 'or' is the poblem.
It just won't work;
when i do this:
---------------------------
stLinkCriteria = "[KlasID]=" & nummer(2) & "Or [KlasID]
=" & nummer(1)
---------------------------
or:
---------------------------
stLinkCriteria = "[KlasID]=" & nummer(1) & "Or [KlasID]
=" & nummer(2)
---------------------------

It is always the number(1) and never number(2) (or both)
that is (are) used.

... the or doesn't work in my programm in an openform-
comand
Click to expand...

You are mistaken. There is nothing wrong with using "Or" in the
where-condition. In the expressions you posted, you've left out a space
before the "Or"; however, that shouldn't actually make a difference, my
tests show that the SQL parser can still figure out what you mean.

Did you try the code I posted? Although I didn't have your form and
table to test it on, I believe it should work, barring minor syntax
errors I may have committed. Did set a breakpoint in your code and
examine the value of stLinkCriteria immediately before calling OpenForm?
Please post the *exact* value of stLinkCriteria, and it will probably
become obvious what's wrong.
 
Back
Top