Complicated problem

[Guest]

Hello,

I will try to explain:
I have a examinations database, with the tables as such:

Members
memid - autonumber primary key
name
address
etc

Examinations
examid - autonumber primary key
grade - combo box
examcode - combo box
memid - number, linked to memid in Members Table 1 to Many
date
etc

Examination details
examcode - primary key, text eg M1, M2 linked to examcode in Examinations
Table 1 to many
Exam Name
Level


I want to find a way of when you are on a Members record, to run some code
to find if the member has passed a certain examination. Such as:
Dlookup([grade],examinations,"memid=" & me.memid & " And examcode = 'M2')
But all this does is find the first examination (if they failed the first
time it will find that result but they may have passed a second time, I want
to find if there is a record where The grade = Pass

Can anyone help?

James

 

[Guest]

First, why would you do a Dlookup instead of an ADO select? You can specify
exactly what criteria you want to in that kind of table lookup.

Also, you should ba able to add the grade to your Dlookup criteria IIRC...

 

[ChasW]

Hello,

I will try to explain:
I have a examinations database, with the tables as such:

Members
memid - autonumber primary key
name
address
etc

Examinations
examid - autonumber primary key
grade - combo box
examcode - combo box
memid - number, linked to memid in Members Table 1 to Many
date
etc

Examination details
examcode - primary key, text eg M1, M2 linked to examcode in Examinations
Table 1 to many
Exam Name
Level


I want to find a way of when you are on a Members record, to run some code
to find if the member has passed a certain examination. Such as:
Dlookup([grade],examinations,"memid=" & me.memid & " And examcode = 'M2')
But all this does is find the first examination (if they failed the first
time it will find that result but they may have passed a second time, I want
to find if there is a record where The grade = Pass

Can anyone help?

James

I am not clear on exactly what you mean "when you are on a Members
record"... which records are NOT Members records?

Just an idea, not sure if this is what you want, but you could run a
simple query from some event to check for a passing grade.

i.e. from the Form_Current or cmdButton_Click, etc.

open a record set query that checks for a passing grade
do something if a passing grade is found

Dim strSQL As String
Dim rst As New ADODB.Recordset

strSQL = "SELECT * FROM etc WHERE etc op val;"

rst.Open strSQL, CurrentProject.Connection, adOpenStatic,
adLockReadOnly

If rst![grade] = "some passing value" Then
"do something meaninful"
End If

rst.Close
CurrentProject.Connection.Close

Again, not sure if this is what you mean, but it sounds like it could
be, anyway.

Also, you dont have to build a query string in the code, you can open
an existing query object just the same.

rst.Open "myQuery", CurrentProject.Connection, adOpenStatic,
adLockReadOnly

HTH,
Chas

 

[Guest]

Hi,

Thanks this seems to be able to find the first record, but as someone may
have failed their first attempt it needs to then find the next to see if they
passsed a second time.

So I guess I need something that will run a Where statement, maybe find 3
records for the exam, then go through each record to see if any of them have
Passed in the Grade field.

Can this be done?

Many thanks
James

Hello,

I will try to explain:
I have a examinations database, with the tables as such:

Members
memid - autonumber primary key
name
address
etc

Examinations
examid - autonumber primary key
grade - combo box
examcode - combo box
memid - number, linked to memid in Members Table 1 to Many
date
etc

Examination details
examcode - primary key, text eg M1, M2 linked to examcode in Examinations
Table 1 to many
Exam Name
Level


I want to find a way of when you are on a Members record, to run some code
to find if the member has passed a certain examination. Such as:
Dlookup([grade],examinations,"memid=" & me.memid & " And examcode = 'M2')
But all this does is find the first examination (if they failed the first
time it will find that result but they may have passed a second time, I want
to find if there is a record where The grade = Pass

Can anyone help?

James

I am not clear on exactly what you mean "when you are on a Members
record"... which records are NOT Members records?

Just an idea, not sure if this is what you want, but you could run a
simple query from some event to check for a passing grade.

i.e. from the Form_Current or cmdButton_Click, etc.

open a record set query that checks for a passing grade
do something if a passing grade is found

Dim strSQL As String
Dim rst As New ADODB.Recordset

strSQL = "SELECT * FROM etc WHERE etc op val;"

rst.Open strSQL, CurrentProject.Connection, adOpenStatic,
adLockReadOnly

If rst![grade] = "some passing value" Then
"do something meaninful"
End If

rst.Close
CurrentProject.Connection.Close

Again, not sure if this is what you mean, but it sounds like it could
be, anyway.

Also, you dont have to build a query string in the code, you can open
an existing query object just the same.

rst.Open "myQuery", CurrentProject.Connection, adOpenStatic,
adLockReadOnly

HTH,
Chas

 

[ChasW]

Hi,

Thanks this seems to be able to find the first record, but as someone may
have failed their first attempt it needs to then find the next to see if they
passsed a second time.

So I guess I need something that will run a Where statement, maybe find 3
records for the exam, then go through each record to see if any of them have
Passed in the Grade field.

Can this be done?

Many thanks
James

Yes it can. If you are referring the this snippet:

If rst![grade] = "some passing value" Then
"do something meaninful"
End If

You are correct in that as written only one grade is checked. The
next logical step if you wanted to look at more rows for a given
column would be to loop this in such as way that you can check any
number of records in this set based on your criteria.

An example might be:

rst.MoveFirst
Do Until rst.EOF
If rst![grade] = "some passing value" Then
"do something meaninful"
'you could exit the loop if desired
Exit Do
End If
rst.MoveNext
Loop

I hope this helps,
Chas