LinkCriteria question

  • Thread starter Thread starter Bytemaster
  • Start date Start date
B

Bytemaster

This is my database:

A gene is divided into 30 parts (exons). All of them have to bee screened
separately. On one testplate I can put 96 samples. Every patient has it´s
own DNA number. This means I need 30 testplates to screen 96 whole genes.
In my database I have made a form which shows you a DNA-number and the
results of the 30 exon screenings (so one field with a DNA number and 30
fields with results).
The second form has a platenumber and 96 DNA numbers (which were on that
plate) (so one field with a platenumber and 96 fields with DNA-numbers)
Now I want to make a button on the results form, that opens a new form with
all the records which contains the DNA-number. So I can trace on which (30)
plates that DNA number has been screened.

For one field this is very easy with the wizard´s help. The filter is
looking for the DNA number every first sample of every plate. But it has to
search alle 96 samples on every platenumber for that DNA number.
After using the wizard the following code is placed:

stLinkCriteria = "[DNA Number of Sample 01]=" & "'" & Me![DNA-number] & "'"

I think I have to add an AND or OR function into this code to force
seraching in samples 02 - 96

Can someone help me here?

Dimitri
 
This is my database:

A gene is divided into 30 parts (exons). All of them have to bee screened
separately. On one testplate I can put 96 samples. Every patient has it´s
own DNA number. This means I need 30 testplates to screen 96 whole genes.
In my database I have made a form which shows you a DNA-number and the
results of the 30 exon screenings (so one field with a DNA number and 30
fields with results).
Click to expand...

This isn't an ideal design. It really limits this database to this
particular gene - another gene with 23 exons, or with 37, would
require redesigning your table. Is that OK?
The second form has a platenumber and 96 DNA numbers (which were on that
plate) (so one field with a platenumber and 96 fields with DNA-numbers)
Click to expand...

Mapped to cells on the plate, with A to H, 1 to 12? That's how I'd do
it so that each cell's result is in a separate record: PlateID, Row,
Column, DNANumber, Exon, Result (unless the Exon is implied by the
PlateID).
Now I want to make a button on the results form, that opens a new form with
all the records which contains the DNA-number. So I can trace on which (30)
plates that DNA number has been screened.

For one field this is very easy with the wizard´s help. The filter is
looking for the DNA number every first sample of every plate. But it has to
search alle 96 samples on every platenumber for that DNA number.
After using the wizard the following code is placed:

stLinkCriteria = "[DNA Number of Sample 01]=" & "'" & Me![DNA-number] & "'"

I think I have to add an AND or OR function into this code to force
seraching in samples 02 - 96
Click to expand...

That's why you're having trouble: improper normalization. You would
indeed need OR logic, something like

stLinkCriteria = "[DNA Number of Sample 01]='" & Me![DNA-number] & "'
OR [DNA Number of Sample 02]='" & Me![DNA-number] & "'
OR [DNA Number of Sample 03]='" & Me![DNA-number] & "'

and so on for 96 OR conditions... and this is VERY likely to blow up
on you with the Query Too Complex error. By storing the data in a
"tall skinny" table above it becomes much much easier, as you have
only one field to search.
 
John Vinson said:
This isn't an ideal design. It really limits this database to this
particular gene - another gene with 23 exons, or with 37, would
require redesigning your table. Is that OK?
Click to expand...

Yes, you are right, but it is only for one gene.

Mapped to cells on the plate, with A to H, 1 to 12? That's how I'd do
it so that each cell's result is in a separate record: PlateID, Row,
Column, DNANumber, Exon, Result (unless the Exon is implied by the
PlateID).
Click to expand...

Someone else suggested the same. Maybe I have to rewrite the database
Now I want to make a button on the results form, that opens a new form with
all the records which contains the DNA-number. So I can trace on which (30)
plates that DNA number has been screened.

For one field this is very easy with the wizard´s help. The filter is
looking for the DNA number every first sample of every plate. But it has to
search alle 96 samples on every platenumber for that DNA number.
After using the wizard the following code is placed:

stLinkCriteria = "[DNA Number of Sample 01]=" & "'" & Me![DNA-number] & "'"

I think I have to add an AND or OR function into this code to force
seraching in samples 02 - 96
Click to expand...

That's why you're having trouble: improper normalization. You would
indeed need OR logic, something like

stLinkCriteria = "[DNA Number of Sample 01]='" & Me![DNA-number] & "'
OR [DNA Number of Sample 02]='" & Me![DNA-number] & "'
OR [DNA Number of Sample 03]='" & Me![DNA-number] & "'
Click to expand...

I tried that, but then the message about a not existing field is coming up
and this part is showed in green:

& Me![DNA-number] (only after the OR function)

and so on for 96 OR conditions... and this is VERY likely to blow up
on you with the Query Too Complex error. By storing the data in a
"tall skinny" table above it becomes much much easier, as you have
only one field to search.
Click to expand...

Actually I would need 24 OR conditions, because I am going to run 4x 24
samples (so 4 exons on a plate with only 24 samples instead of one exon with
96 samples). I did not mention that because no one would have understand my
problem anymore.

Thank you for your support so far. I am going to try some more!

Dimitri
 
I tried that, but then the message about a not existing field is coming up
and this part is showed in green:

& Me![DNA-number] (only after the OR function)
Click to expand...

Ah! Missing the forest for the trees here, I was.

Me! works ONLY in VBA code as part of a Form. It will NOT work in a
Query. You'll need to use

[Forms]![namefoyourform]![DNA-number]

as the criterion... and since the criterion is thereby longer, you'll
hit the QTC error that much sooner.
 
Back
Top