Display query result in a combo box?

T

tclarke

I have a Microsoft Acess 2003 database which has a form, ExamFrm, whose
record source is a table, StudentTbl (PK = StudentID), and a sub form
ExamSubform (continuous forms), whose record source is a table, ExamTbl
(PK = ExamID).

The form displays student details (from StudentTbl)i.e txtName
comboL1Pass, comboL2Pass, comboL3Pass etc at the top and exam results
(from ExamTble displayed as continuous forms) i.e. txtModuleID,
chbxPass etc for each of eight exam modules.

In order to pass L1 the student must pass modules 1, 2 and 7.

At the moment I inspect the sub form and use the combo box comboL1Pass
to enter either PASS or FAIL manually, depending on the results.

Is it possible to get the form to search the sub form and automatically
display either Pass or Fail, depending on the results in the sub form?

I've tried (without success):-

Private Sub Form_AfterUpdate()
If Me.Exam_details_Subform.[txtModuleID] = 1 And _
Me.Exam_details_Subform.[chbxPass] = True And _
Me.Exam_details_Subform.[txtModuleID] = 2 And _
Me.Exam_details_Subform.[chbxPass] = True _
And Me.Exam_details_Subform.[txtModuleID] = 8 And _
Me.Exam_details_Subform.[chbxPass] = True Then
Me.comboL1Pass = "PASS"
Else
Me.comboL1Pass = "Fail"
End If
End Sub

N.B.

I do have a query, PassedLevelOneQry, which displays all the required
details for students who have passed Level 1 and this works OK. As an
alternative, is there any way to display the result of this query in
comboL1Pass?

Terry
 
K

Ken Snell [MVP]

What is the Row Source for the combo box comboL1Pass? If you just want to
display result, why a combo box? or is it your intent to put an initial
value in the combo box and then let the user change it if desired?

More information about what you want to accomplish, please.
 
T

tclarke

Thanks for the reply Ken. Origionally it was my intention to put no
initial value in the combo box and then let the user change it if
desired. So I set the 'Control Source' to L1Pass (StudentTbl),'Row
Source Type' as Value List and the 'Row Source' as "Pass";"Fail".

This works fine but if the user doesn't choose Pass or Fail from the
combo box the overall result for the Level 1 result is not recorded in
StudentTbl. Hence my question on the possibility of displaying the
result in comboL1Pass.

Is there a way of displaying the result in a text box?

Terry
What is the Row Source for the combo box comboL1Pass? If you just want to
display result, why a combo box? or is it your intent to put an initial
value in the combo box and then let the user change it if desired?

More information about what you want to accomplish, please.
--

Ken Snell
<MS ACCESS MVP>



I have a Microsoft Acess 2003 database which has a form, ExamFrm, whose
record source is a table, StudentTbl (PK = StudentID), and a sub form
ExamSubform (continuous forms), whose record source is a table, ExamTbl
(PK = ExamID).

The form displays student details (from StudentTbl)i.e txtName
comboL1Pass, comboL2Pass, comboL3Pass etc at the top and exam results
(from ExamTble displayed as continuous forms) i.e. txtModuleID,
chbxPass etc for each of eight exam modules.

In order to pass L1 the student must pass modules 1, 2 and 7.

At the moment I inspect the sub form and use the combo box comboL1Pass
to enter either PASS or FAIL manually, depending on the results.

Is it possible to get the form to search the sub form and automatically
display either Pass or Fail, depending on the results in the sub form?

I've tried (without success):-

Private Sub Form_AfterUpdate()
If Me.Exam_details_Subform.[txtModuleID] = 1 And _
Me.Exam_details_Subform.[chbxPass] = True And _
Me.Exam_details_Subform.[txtModuleID] = 2 And _
Me.Exam_details_Subform.[chbxPass] = True _
And Me.Exam_details_Subform.[txtModuleID] = 8 And _
Me.Exam_details_Subform.[chbxPass] = True Then
Me.comboL1Pass = "PASS"
Else
Me.comboL1Pass = "Fail"
End If
End Sub

N.B.

I do have a query, PassedLevelOneQry, which displays all the required
details for students who have passed Level 1 and this works OK. As an
alternative, is there any way to display the result of this query in
comboL1Pass?

Terry
Click to expand...
Click to expand...
 
K

Ken Snell [MVP]

The code that you've posted should put the value in the combo box. However,
using the form's AfterUpdate event probably is too late in the process, as
that event occurs after the data on the form have been saved to the
underlying table(s).

You might want to use the form's BeforeUpdate event instead.

And, if you're planning to have the form calculate "pass" and "fail" values,
may I suggest that you not "tempt" your user with entering a value at all? I
would use a locked and disabled textbox (that is bound to the proper field
in the form's RecordSource) to show the value of Pass or Fail on the form
(not a combo box) and then modify your code slightly to write the desired
value to this textbox. Then you don't use a combo box at all.
--

Ken Snell
<MS ACCESS MVP>



Thanks for the reply Ken. Origionally it was my intention to put no
initial value in the combo box and then let the user change it if
desired. So I set the 'Control Source' to L1Pass (StudentTbl),'Row
Source Type' as Value List and the 'Row Source' as "Pass";"Fail".

This works fine but if the user doesn't choose Pass or Fail from the
combo box the overall result for the Level 1 result is not recorded in
StudentTbl. Hence my question on the possibility of displaying the
result in comboL1Pass.

Is there a way of displaying the result in a text box?

Terry
What is the Row Source for the combo box comboL1Pass? If you just want to
display result, why a combo box? or is it your intent to put an initial
value in the combo box and then let the user change it if desired?

More information about what you want to accomplish, please.
--

Ken Snell
<MS ACCESS MVP>



I have a Microsoft Acess 2003 database which has a form, ExamFrm, whose
record source is a table, StudentTbl (PK = StudentID), and a sub form
ExamSubform (continuous forms), whose record source is a table, ExamTbl
(PK = ExamID).

The form displays student details (from StudentTbl)i.e txtName
comboL1Pass, comboL2Pass, comboL3Pass etc at the top and exam results
(from ExamTble displayed as continuous forms) i.e. txtModuleID,
chbxPass etc for each of eight exam modules.

In order to pass L1 the student must pass modules 1, 2 and 7.

At the moment I inspect the sub form and use the combo box comboL1Pass
to enter either PASS or FAIL manually, depending on the results.

Is it possible to get the form to search the sub form and automatically
display either Pass or Fail, depending on the results in the sub form?

I've tried (without success):-

Private Sub Form_AfterUpdate()
If Me.Exam_details_Subform.[txtModuleID] = 1 And _
Me.Exam_details_Subform.[chbxPass] = True And _
Me.Exam_details_Subform.[txtModuleID] = 2 And _
Me.Exam_details_Subform.[chbxPass] = True _
And Me.Exam_details_Subform.[txtModuleID] = 8 And _
Me.Exam_details_Subform.[chbxPass] = True Then
Me.comboL1Pass = "PASS"
Else
Me.comboL1Pass = "Fail"
End If
End Sub

N.B.

I do have a query, PassedLevelOneQry, which displays all the required
details for students who have passed Level 1 and this works OK. As an
alternative, is there any way to display the result of this query in
comboL1Pass?

Terry
Click to expand...
Click to expand...
Click to expand...
 
T

tclarke

Hi Ken,

I've tried the following:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.Exam_details_Subform.[txtModuleID] = 1 And _
Me.Exam_details_Subform.[chbxPass] = True And _
Me.Exam_details_Subform.[txtModuleID] = 2 And _
Me.Exam_details_Subform.[chbxPass] = True And _
Me.Exam_details_Subform.[txtModuleID] = 8 And _
Me.Exam_details_Subform.[chbxPass] = True Then
Me.txtL1Pass = "PASS"
Else
Me.txtL1Pass = "Fail"
End If

End Sub

Nothing happens although no errors are shown. I've also tried putting
the numbers in double quotes but it's still the same.

I also tried the following thinking that because I'm referencing a sub
form in 'continuous forms' view, I may be repeating instructions with
the references to chbxPass but this had the same result as above:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.Exam_details_Subform.[chbxPass] = True And _
Me.Exam_details_Subform.[txtModuleID] = 1 And _
Me.Exam_details_Subform.[txtModuleID] = 2 And _
Me.Exam_details_Subform.[txtModuleID] = 8 Then
Me.txtL1Pass = "PASS"
Else
Me.txtL1Pass = "Fail"
End If
End Sub

P.S I've also tried 'compact and repair but this didn't help either.
Any suggestions?

Terry
 
R

RuralGuy

Hi Ken,

I've tried the following:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.Exam_details_Subform.[txtModuleID] = 1 And _
Me.Exam_details_Subform.[chbxPass] = True And _
Me.Exam_details_Subform.[txtModuleID] = 2 And _
Me.Exam_details_Subform.[chbxPass] = True And _
Me.Exam_details_Subform.[txtModuleID] = 8 And _
Me.Exam_details_Subform.[chbxPass] = True Then
Me.txtL1Pass = "PASS"
Else
Me.txtL1Pass = "Fail"
End If

End Sub

Nothing happens although no errors are shown. I've also tried putting
the numbers in double quotes but it's still the same.

I also tried the following thinking that because I'm referencing a sub
form in 'continuous forms' view, I may be repeating instructions with
the references to chbxPass but this had the same result as above:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.Exam_details_Subform.[chbxPass] = True And _
Me.Exam_details_Subform.[txtModuleID] = 1 And _
Me.Exam_details_Subform.[txtModuleID] = 2 And _
Me.Exam_details_Subform.[txtModuleID] = 8 Then
Me.txtL1Pass = "PASS"
Else
Me.txtL1Pass = "Fail"
End If
End Sub

P.S I've also tried 'compact and repair but this didn't help either.
Any suggestions?

Terry
Click to expand...

PMFJI but how can this possible be true?

Me.Exam_details_Subform.[txtModuleID] = 1 And _
Me.Exam_details_Subform.[txtModuleID] = 2 And _
Me.Exam_details_Subform.[txtModuleID] = 8

My best guess is it is only equal to one of those values at a time!

hth
 
R

RuralGuy

Hi RuralGuy,

I think you're right. Do you have any suggestions?

Terry
Click to expand...

I'm not sure if it can be done the way you are going about it. Could you
post the SQL for the RecordSource of the SubForm assuming it is a Query.

Am I correct that you are trying to examine the current record of the
subform to extract the [chbxPass] and [txtModuleID] information? I don't
think I would continue down this particular approach but if you *must*, I
believe a Select Case Statement will work:

Select Case Me.Exam_details_Subform.[txtModuleID]

Case 1
If Me.Exam_details_Subform.[chbxPass] = True Then
Me.comboL1Pass = "PASS"
Else
Me.comboL1Pass = "Fail"
End If
Case 2
If Me.Exam_details_Subform.[chbxPass] = True Then
Me.comboL1Pass = "PASS"
Else
Me.comboL1Pass = "Fail"
End If
Case 8
If Me.Exam_details_Subform.[chbxPass] = True Then
Me.comboL1Pass = "PASS"
Else
Me.comboL1Pass = "Fail"
End If
End Select

One could make a shorter Case Statement for this but I wanted to see
if this is what you were trying to accomplish!

hth
 
K

Ken Snell [MVP]

RuralGuy said:
PMFJI but how can this possible be true?

Me.Exam_details_Subform.[txtModuleID] = 1 And _
Me.Exam_details_Subform.[txtModuleID] = 2 And _
Me.Exam_details_Subform.[txtModuleID] = 8

My best guess is it is only equal to one of those values at a time!
Click to expand...


Good reading of the details, RuralGuy!
< g >
 
T

tclarke

Hi Ken,

The Record Source for the sub form is a table (ExamTbl). I've put the
following code in as you suggested but changed the name comboL1Pass to
txtL1Pass as this is now a text box:

******************************************************************
Private Sub Form_BeforeUpdate(Cancel As Integer)
Select Case Me.Exam_details_Subform.[txtModuleID]

Case 1
If Me.Exam_details_Subform.[chbxPass] = True Then
Me.txtL1Pass = "PASS"
Else
Me.txtL1Pass = "Fail"
End If
Case 2
If Me.Exam_details_Subform.[chbxPass] = True Then
Me.txtL1Pass = "PASS"
Else
Me.txtL1Pass = "Fail"
End If
Case 7
If Me.Exam_details_Subform.[chbxPass] = True Then
Me.txtL1Pass = "PASS"
Else
Me.txtL1Pass = "Fail"
End If
End Select

End Sub
*******************************************************************

When I try to change data in the in the sub form I get a Run-time
errror 438, 'Object doesn't support this property or method' and when I
press OK to debug, the first line of the code i.e 'Select Case .....'
is highlighted in yellow. I've tried re-writing this line but it makes
no difference. Does it have to be declared anywhere in the sub routine?

Terry
Hi RuralGuy,

I think you're right. Do you have any suggestions?

Terry
Click to expand...

I'm not sure if it can be done the way you are going about it. Could you
post the SQL for the RecordSource of the SubForm assuming it is a Query.

Am I correct that you are trying to examine the current record of the
subform to extract the [chbxPass] and [txtModuleID] information? I don't
think I would continue down this particular approach but if you *must*, I
believe a Select Case Statement will work:

Select Case Me.Exam_details_Subform.[txtModuleID]

Case 1
If Me.Exam_details_Subform.[chbxPass] = True Then
Me.comboL1Pass = "PASS"
Else
Me.comboL1Pass = "Fail"
End If
Case 2
If Me.Exam_details_Subform.[chbxPass] = True Then
Me.comboL1Pass = "PASS"
Else
Me.comboL1Pass = "Fail"
End If
Case 8
If Me.Exam_details_Subform.[chbxPass] = True Then
Me.comboL1Pass = "PASS"
Else
Me.comboL1Pass = "Fail"
End If
End Select

One could make a shorter Case Statement for this but I wanted to see
if this is what you were trying to accomplish!

hth
Click to expand...
 
T

tclarke

Ken,

I'm going to sign off right now and get back to it tomorrow. But I'd
like to thank you for your time....I really appreciate it.
 
R

RuralGuy

Ken Snell said:
RuralGuy said:
PMFJI but how can this possible be true?

Me.Exam_details_Subform.[txtModuleID] = 1 And _
Me.Exam_details_Subform.[txtModuleID] = 2 And _
Me.Exam_details_Subform.[txtModuleID] = 8

My best guess is it is only equal to one of those values at a time!
Click to expand...


Good reading of the details, RuralGuy!
< g >
Click to expand...

Thanks Ken -
 
K

Ken Snell [MVP]

If txtModuleID is a textbox on the subform that resides within the subform
object Exam_details_Subform, you need to add a .Form to the expression:

Select Case Me.Exam_details_Subform.Form.[txtModuleID]


Or you can use this expression:

Select Case Me.Exam_details_Subform![txtModuleID]
--

Ken Snell
<MS ACCESS MVP>


Hi Ken,

The Record Source for the sub form is a table (ExamTbl). I've put the
following code in as you suggested but changed the name comboL1Pass to
txtL1Pass as this is now a text box:

******************************************************************
Private Sub Form_BeforeUpdate(Cancel As Integer)
Select Case Me.Exam_details_Subform.[txtModuleID]

Case 1
If Me.Exam_details_Subform.[chbxPass] = True Then
Me.txtL1Pass = "PASS"
Else
Me.txtL1Pass = "Fail"
End If
Case 2
If Me.Exam_details_Subform.[chbxPass] = True Then
Me.txtL1Pass = "PASS"
Else
Me.txtL1Pass = "Fail"
End If
Case 7
If Me.Exam_details_Subform.[chbxPass] = True Then
Me.txtL1Pass = "PASS"
Else
Me.txtL1Pass = "Fail"
End If
End Select

End Sub
*******************************************************************

When I try to change data in the in the sub form I get a Run-time
errror 438, 'Object doesn't support this property or method' and when I
press OK to debug, the first line of the code i.e 'Select Case .....'
is highlighted in yellow. I've tried re-writing this line but it makes
no difference. Does it have to be declared anywhere in the sub routine?

Terry
Hi RuralGuy,

I think you're right. Do you have any suggestions?

Terry
Click to expand...

I'm not sure if it can be done the way you are going about it. Could you
post the SQL for the RecordSource of the SubForm assuming it is a Query.

Am I correct that you are trying to examine the current record of the
subform to extract the [chbxPass] and [txtModuleID] information? I don't
think I would continue down this particular approach but if you *must*, I
believe a Select Case Statement will work:

Select Case Me.Exam_details_Subform.[txtModuleID]

Case 1
If Me.Exam_details_Subform.[chbxPass] = True Then
Me.comboL1Pass = "PASS"
Else
Me.comboL1Pass = "Fail"
End If
Case 2
If Me.Exam_details_Subform.[chbxPass] = True Then
Me.comboL1Pass = "PASS"
Else
Me.comboL1Pass = "Fail"
End If
Case 8
If Me.Exam_details_Subform.[chbxPass] = True Then
Me.comboL1Pass = "PASS"
Else
Me.comboL1Pass = "Fail"
End If
End Select

One could make a shorter Case Statement for this but I wanted to see
if this is what you were trying to accomplish!

hth
Click to expand...
Click to expand...
 
R

RuralGuy

Hi Ken,

The Record Source for the sub form is a table (ExamTbl). I've put the
following code in as you suggested but changed the name comboL1Pass to
txtL1Pass as this is now a text box:

******************************************************************
Private Sub Form_BeforeUpdate(Cancel As Integer)
Select Case Me.Exam_details_Subform.[txtModuleID]

Case 1
If Me.Exam_details_Subform.[chbxPass] = True Then
Me.txtL1Pass = "PASS"
Else
Me.txtL1Pass = "Fail"
End If
Case 2
If Me.Exam_details_Subform.[chbxPass] = True Then
Me.txtL1Pass = "PASS"
Else
Me.txtL1Pass = "Fail"
End If
Case 7
If Me.Exam_details_Subform.[chbxPass] = True Then
Me.txtL1Pass = "PASS"
Else
Me.txtL1Pass = "Fail"
End If
End Select

End Sub
*******************************************************************

When I try to change data in the in the sub form I get a Run-time
errror 438, 'Object doesn't support this property or method' and when I
press OK to debug, the first line of the code i.e 'Select Case .....'
is highlighted in yellow. I've tried re-writing this line but it makes
no difference. Does it have to be declared anywhere in the sub routine?

Terry
Click to expand...

Hi Terry,

Ken will probably jump in here but I'll continue to put in my $0.02.
SubForms are displayed on forms by the use of a SubFormControl. It has its
own name which may be the same name as the SubForm but not necessarily.
You get to a control on a subform by going through the SubFormControl and
then the Form collection of the SubForm. Whewww.

In other words the reference needs to be:
Me.SubFormControlName.Form.ControlName

If "Exam_details_Subform" is the name of the SubFormControl then the
reference would be:

Me!Exam_details_Subform.Form!txtModuleID

Here's an excellent source for accurate information:

http://www.mvps.org/access/forms/frm0031.htm
Forms: Refer to Form and Subform properties and controls

hth
 
T

tclarke

Hi Ken and RuralGuy,

I don't exactly know how a 'Select Case' routine actually works or what
it does although in this instance I presume it searches each form in
the sub form (which is displayed as continuous forms). Case 1 searches
for instances of txtModuleID = 1 and chbxPass = True or chbxPass =
False, (in any of the continuous forms), and so on for Case 2 & 7. Am I
correct?

The code below doesn't generate any errors but it only picks up on the
Case 1 statement. Is there any way of making a 'summary' statement that
sets Me.txtL1Pass only when all three case statements are True e.g.

If Case 1 = True And Case 2 = True And Case 7 = True Then
Me.L1Pass = "Pass"
Else Me.txtL1Pass = "Fail"
End If

********************************************************************
Private Sub Form_BeforeUpdate(Cancel As Integer)
Select Case Me.Exam_details_Subform.Form!txtModuleID

Case 1
If Me.Exam_details_Subform.Form!chbxPass = True Then
Me.txtL1Pass = "Pass"
Else
Me.txtL1Pass = "Fail"
End If
Case 2
If Me.Exam_details_Subform.Form!chbxPass = True Then
Me.txtL1Pass = "Pass"
Else
Me.txtL1Pass = "Fail"
End If
Case 7
If Me.Exam_details_Subform.Form!chbxPass = True Then
Me.txtL1Pass = "Pass"
Else
Me.txtL1Pass = "Fail"
End If
End Select

End Sub
***********************************************************************

Terry
 
K

Ken Snell [MVP]

Select Case is a way of doing a sequence of If...ElseIf...Else....Then
statements, and it often runs a bit faster than the separate If steps. It is
not a way to "scan" a continuous forms view.

So, in this example:

Select Case Me.FormControlName
Case 1
Case 2
Case 3
Case Else
End Select

the code gets the value that is in the FormControlName control, and compares
it to 1. If it matches, it does the code under the Case 1 step. If it
doesn't match, it goes to the next Case step (2) and compares that to the
value. If it matches, it does the code under the Case 2 step. If it doesn't
match, it goes to the next Case step (3), etc.

If you're using a continuous forms view for the form, the value of the
control that is used is the value from the *current* record.

It sounds from your description that you want to search all the records that
are displayed on the form and find out if you have a record with a value of
1, another record with a value of 2, and another record with a value of 7.
Is this what you mean when you say you want to find the situation where a
single control has as many as three values? If this is the case, you'd need
to use code to loop through the RecordsetClone object of the form and read
the value from each record, keeping track of the values that you find, and
then use the code to do something if the desired combinations are found.

One way of doing this would be:

Dim lngValues(1 To 9) As Long
Dim lngLoop As Long
For lngLoop = 1 To 9
lngValues(lngLoop) = 0
Next lngLoop
With Me.RecordsetClone
.MoveFirst
Do While .EOF = False
lngValues(.Fields("NameOfFieldBoundTo'txtModuleID'Control").Value)
= -1
.MoveNext
Loop
End With
If lngValues(1) = -1 And lngValues(2) = -1 And lngValues(7) = -1 Then
Me.txtL1Pass = "Pass"
Else
Me.txtL1Pass = "Fail"
End If
 
R

RuralGuy

Ken Snell said:
If txtModuleID is a textbox on the subform that resides within the
subform object Exam_details_Subform, you need to add a .Form to the
expression:

Select Case Me.Exam_details_Subform.Form.[txtModuleID]


Or you can use this expression:

Select Case Me.Exam_details_Subform![txtModuleID]
Click to expand...

Hi Ken,

Can you really drop the .Form reference when you use a bang?
 
K

Ken Snell [MVP]

Yep.

RuralGuy said:
Ken Snell said:
If txtModuleID is a textbox on the subform that resides within the
subform object Exam_details_Subform, you need to add a .Form to the
expression:

Select Case Me.Exam_details_Subform.Form.[txtModuleID]


Or you can use this expression:

Select Case Me.Exam_details_Subform![txtModuleID]
Click to expand...

Hi Ken,

Can you really drop the .Form reference when you use a bang?
Click to expand...
 
T

tclarke

Hi Ken,

Thanks for explaining 'Select Case'...I can now see why this didn't do
what I intended.

N.B. The continuous forms displayed on the sub form contain records of
both passed and failed exams.

So what I would like to do is to search all the records that are
displayed on the sub form and find out if I have a record with a
txtModuleID value of 1 where the chbxPass state is True, another record
with a txtModuleID value of 2 where the chbxPass state is True, and
another record with a txtModuleID value of 7 where the chbxPass state
is True...If these three conditions are met then I would like the
txtL1Pass control to display "Pass"...If these three conditions are not
met I would like the txtL1Pass control to display "Fail".

P.S. The field bound to the txtModuleID Control is called ModuleID
(from ExamTbl) and the field bound to the chbxPass Control is called
Pass (From ExamTbl).

P.P.S. As you have probably gathered, I'm a comparative novice at
writing code so I'd really appreciate it if you continue to post
examples.

Terry
 
R

RuralGuy

Ken Snell said:
Yep.
Click to expand...

Thanks. I wonder if I will *ever* completely understand all of the
differences between dot and bang. That is why I spend so much time on these
news groups. Always something to learn! 8^)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Create new records in empty recordset 0
Form to display result 6
Help with code for RecordSet.AddNew 5
Combo Box Does Not Display Value 3
Continuous Forms Question 1
Clear Unbound Text box used as input parameter to query 13
Query to display test if multiple tests have been passed 4
Fill Field on main form from subform based on check box result 1

Top