Display query result in a combo box?

[Ken Snell [MVP]]

Dot indicates that what follows is a method or property.

Bang indicates that what follows is an item in the default collection.
--

Ken Snell
<MS ACCESS MVP>

 

[Ken Snell [MVP]]

So the desired code might be this:

Dim blnTests(1 To 3) As Boolean
Dim intTest As Integer
Dim lngLoop As Long
For lngLoop = 1 To 3
blnTests(lngLoop) = False
Next lngLoop
With Me.RecordsetClone
.MoveFirst
Do While .EOF = False
If .Fields("ModuleID").Value = 1 And .Fields("Pass").Value = "Pass"
Then
blnTests(1) = True
ElseIf Fields("ModuleID").Value = 2 And .Fields("Pass").Value =
"Pass"
blnTests(2) = True
ElseIf Fields("ModuleID").Value = 7 And .Fields("Pass").Value =
"Pass"
blnTests(3) = True
End If
.MoveNext
Loop
End With
intTest = 0
For lngLoop = 1 To 3
intTest = intTest + blnTests(lngLoop)
Next lngLoop
If intTest = -3 Then
Me.txtL1Pass = "Pass"
Else
Me.txtL1Pass = "Fail"
End If

--

Ken Snell
<MS ACCESS MVP>

 

[RuralGuy]

Dot indicates that what follows is a method or property.

Bang indicates that what follows is an item in the default collection.

And the default collection for a form is the controls collection?

 

[tclarke]

Hi Ken and RuralGuy,

Had to amend Ken's code slightly and add some dots and bangs but
eventually I got it to reference the relevant controls...The only
problem now is that all results are displayed as "Fail" even if all
conditions are met...no errors are reported.

Can you look at the code below and see if you can find where it's going
wrong? I think it may be in the last section after 'End With'.

************************************************************
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim blnTests(1 To 3) As Boolean
Dim intTest As Integer
Dim lngLoop As Long
For lngLoop = 1 To 3
blnTests(lngLoop) = False
Next lngLoop
With Me.RecordsetClone
.MoveFirst
Do While .EOF = False
If Me.Exam_details_Subform!txtModuleID.Value = 1 _
And Me.Exam_details_Subform!chbxPass.Value = True Then
blnTests(1) = True
ElseIf Me.Exam_details_Subform!txtModuleID.Value = 2 _
And Me.Exam_details_Subform!chbxPass.Value = True Then
blnTests(2) = True
ElseIf Me.Exam_details_Subform!txtModuleID.Value = 7 _
And Me.Exam_details_Subform!chbxPass.Value = True Then
blnTests(3) = True
End If
.MoveNext
Loop
End With

intTest = 0
For lngLoop = 1 To 3
intTest = intTest + blnTests(lngLoop)
Next lngLoop
If intTest = -3 Then
Me.txtL1Pass = "Pass"
Else
Me.txtL1Pass = "Fail"
End If
End Sub
************************************************************

Terry

 

[RuralGuy]

Hi Ken and RuralGuy,

Had to amend Ken's code slightly and add some dots and bangs but
eventually I got it to reference the relevant controls...The only
problem now is that all results are displayed as "Fail" even if all
conditions are met...no errors are reported.

Can you look at the code below and see if you can find where it's going
wrong? I think it may be in the last section after 'End With'.

************************************************************
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim blnTests(1 To 3) As Boolean
Dim intTest As Integer
Dim lngLoop As Long
For lngLoop = 1 To 3
blnTests(lngLoop) = False
Next lngLoop
With Me.RecordsetClone
.MoveFirst
Do While .EOF = False
If Me.Exam_details_Subform!txtModuleID.Value = 1 _
And Me.Exam_details_Subform!chbxPass.Value = True Then
blnTests(1) = True
ElseIf Me.Exam_details_Subform!txtModuleID.Value = 2 _
And Me.Exam_details_Subform!chbxPass.Value = True Then
blnTests(2) = True
ElseIf Me.Exam_details_Subform!txtModuleID.Value = 7 _
And Me.Exam_details_Subform!chbxPass.Value = True Then
blnTests(3) = True
End If
.MoveNext
Loop
End With

intTest = 0
For lngLoop = 1 To 3
intTest = intTest + blnTests(lngLoop)
Next lngLoop
If intTest = -3 Then
Me.txtL1Pass = "Pass"
Else
Me.txtL1Pass = "Fail"
End If
End Sub
************************************************************

Terry

Ho Terry,

How about this?

intTest = True
For lngLoop = 1 To 3
intTest = intTest AND blnTests(lngLoop)
Next lngLoop
If intTest = True Then
Me.txtL1Pass = "Pass"
Else
Me.txtL1Pass = "Fail"
End If

The For...Next loop will exit with intTest = False (0) if any of blnTests
(lngLoop) are False.

hth

 

[Ken Snell [MVP]]

Yes. And Controls is also the default collection for Report.

For a recordset, the default collection is Fields.

 

[Ken Snell [MVP]]

The code is running in the main form, not the subform, right? Then I gave
you the wrong code syntax for which RecordsetClone to use. Let's use this
tweaking of your just-posted code:


Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim blnTests(1 To 3) As Boolean
Dim intTest As Integer
Dim lngLoop As Long
For lngLoop = 1 To 3
blnTests(lngLoop) = False
Next lngLoop
With Me.Exam_details_Subform.Form.RecordsetClone
.MoveFirst
Do While .EOF = False
If !ModuleID.Value = 1 _
And !Pass.Value = True Then
blnTests(1) = True
If !ModuleID.Value = 2 _
And !Pass.Value = True Then
blnTests(2) = True
If !ModuleID.Value = 7 _
And !Pass.Value = True Then
blnTests(3) = True
End If
.MoveNext
Loop
End With

intTest = 0
For lngLoop = 1 To 3
intTest = intTest + blnTests(lngLoop)
Next lngLoop
If intTest = -3 Then
Me.txtL1Pass = "Pass"
Else
Me.txtL1Pass = "Fail"
End If
End Sub

 

[tclarke]

Hi RuralGuy,

I've discovered, by a process of elimination, that the 'For ingLoop'
routine always returns a value of -1 if the value for txtModuleID is
either 1, 2 or 7...but only if the value is in the first record
displayed on the continuous forms sub form.

In the VB window I also discovered that when I click my right hand
mouse over any other statement, a drop-down menu appears which contains
an option to 'List Properties and Methods'...MoveFist and MoveNext are
not in the list.

When I click within the word 'MoveFirst' and press F1, a context help
dialogue box appears which says that MoveFirst is an object in the DAO
and ADOB libraries but doesn't mention VB...by the way I'm using VB
6.3.

It looks to me like the 'Where' routine is only searching the first
record in the multiple forms sub form because it doesn't know what
MoveFirst or MoveNext means...What do you think and have you any
suggestions?
************************************************************
With Me.RecordsetClone
.MoveFirst
Do While .EOF = False
If Me.Exam_details_Subform!txtModuleID.Value = 1 _
And Me.Exam_details_Subform!chbxPass.Value = True Then
blnTests(1) = -1
ElseIf Me.Exam_details_Subform!txtModuleID.Value = 2 _
And Me.Exam_details_Subform!chbxPass.Value = True Then
blnTests(2) = -1
ElseIf Me.Exam_details_Subform!txtModuleID.Value = 7 _
And Me.Exam_details_Subform!chbxPass.Value = True Then
blnTests(3) = -1
End If
.MoveNext
Loop
End With
***********************************************************

Terry

 

[RuralGuy]

Yes. And Controls is also the default collection for Report.

For a recordset, the default collection is Fields.

Again, thank you kind sir!

 

[RuralGuy]

Hi RuralGuy,

I've discovered, by a process of elimination, that the 'For ingLoop'
routine always returns a value of -1 if the value for txtModuleID is
either 1, 2 or 7...but only if the value is in the first record
displayed on the continuous forms sub form.

In the VB window I also discovered that when I click my right hand
mouse over any other statement, a drop-down menu appears which contains
an option to 'List Properties and Methods'...MoveFist and MoveNext are
not in the list.

When I click within the word 'MoveFirst' and press F1, a context help
dialogue box appears which says that MoveFirst is an object in the DAO
and ADOB libraries but doesn't mention VB...by the way I'm using VB
6.3.

It looks to me like the 'Where' routine is only searching the first
record in the multiple forms sub form because it doesn't know what
MoveFirst or MoveNext means...What do you think and have you any
suggestions?
************************************************************
With Me.RecordsetClone
.MoveFirst
Do While .EOF = False
If Me.Exam_details_Subform!txtModuleID.Value = 1 _
And Me.Exam_details_Subform!chbxPass.Value = True Then
blnTests(1) = -1
ElseIf Me.Exam_details_Subform!txtModuleID.Value = 2 _
And Me.Exam_details_Subform!chbxPass.Value = True Then
blnTests(2) = -1
ElseIf Me.Exam_details_Subform!txtModuleID.Value = 7 _
And Me.Exam_details_Subform!chbxPass.Value = True Then
blnTests(3) = -1
End If
.MoveNext
Loop
End With
***********************************************************

Terry

Hi Terry,

Is this a VB front end to an Access back end?

 

[tclarke]

Hi RuralGuy,

Don't quite undestand 'a VB front end to an Access back end?'. I'm
using Access Microsoft 2003 with VB 6.3 and it's not shared by anyone
or networked...Anyway Ken's latest code looks like it should do the
trick as it ie designed to search the continuous forms, one complete
record at a time. The only problem now is that it generates a compile
error 'Loop without Do'. When I click on help it shows:

"Loop without Do

A Do loop must begin with a Do statement. This error has the following
cause and solution:

You have an unterminated loop block nested within another loop.
Check to verify that the correct Do...Loop syntax is used."

I've looked at the code and can't find the error.

Also I can't find any info on using .MoveFirst or .MoveNext in VB.

Here's the code; can you spot the error?

*************************************************************************
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim blnTests(1 To 3) As Boolean
Dim intTest As Integer
Dim lngLoop As Long

For lngLoop = 1 To 3
blnTests(lngLoop) = False
Next lngLoop

With Me.Exam_details_Subform.Form.RecordsetClone
.MoveFirst
Do While .EOF = False
If !ModuleID.Value = 1 _
And !Pass.Value = True Then
blnTests(1) = True
If !ModuleID.Value = 2 _
And !Pass.Value = True Then
blnTests(2) = True
If !ModuleID.Value = 7 _
And !Pass.Value = True Then
blnTests(3) = True
End If
.MoveNext
Loop
End With

intTest = 0
For lngLoop = 1 To 3
intTest = intTest + blnTests(lngLoop)
Next lngLoop

If intTest = -3 Then
Me.txtL1Pass = "Pass"
Else
Me.txtL1Pass = "Fail"
End If
End Sub
******************************************************************

Terry

 

[RuralGuy]

(e-mail address removed) wrote:

Code added in line below. You are right, Ken's code should be just fine
here. I use Access XP (2002) and it uses VBA not VB so I was a little
confused when you said you were using VB v6.3. When you go from looking at
code to Tools>References do you see MIcrosoft DAO x.x (3.6?) Object Library
checked? If not, scroll down to the M's and check the latest version you
have and then ok your way back out and compile again.

Hi RuralGuy,

Don't quite undestand 'a VB front end to an Access back end?'. I'm
using Access Microsoft 2003 with VB 6.3 and it's not shared by anyone
or networked...Anyway Ken's latest code looks like it should do the
trick as it ie designed to search the continuous forms, one complete
record at a time. The only problem now is that it generates a compile
error 'Loop without Do'. When I click on help it shows:

"Loop without Do

A Do loop must begin with a Do statement. This error has the following
cause and solution:

You have an unterminated loop block nested within another loop.
Check to verify that the correct Do...Loop syntax is used."

I've looked at the code and can't find the error.

Also I can't find any info on using .MoveFirst or .MoveNext in VB.

Here's the code; can you spot the error?

*************************************************************************
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim blnTests(1 To 3) As Boolean
Dim intTest As Integer
Dim lngLoop As Long

For lngLoop = 1 To 3
blnTests(lngLoop) = False
Next lngLoop

With Me.Exam_details_Subform.Form.RecordsetClone
.MoveFirst
Do While .EOF = False
If !ModuleID.Value = 1 _
And !Pass.Value = True Then
blnTests(1) = True
If !ModuleID.Value = 2 _
And !Pass.Value = True Then
blnTests(2) = True
If !ModuleID.Value = 7 _
And !Pass.Value = True Then
blnTests(3) = True
End If
.MoveNext
Loop

End While

Put in the above line.

End With

intTest = 0
For lngLoop = 1 To 3
intTest = intTest + blnTests(lngLoop)
Next lngLoop

If intTest = -3 Then
Me.txtL1Pass = "Pass"
Else
Me.txtL1Pass = "Fail"
End If
End Sub
******************************************************************

hth

 

[Ken Snell [MVP]]

Sorry... didn't quite revise the code fully:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim blnTests(1 To 3) As Boolean
Dim intTest As Integer
Dim lngLoop As Long
For lngLoop = 1 To 3
blnTests(lngLoop) = False
Next lngLoop
With Me.Exam_details_Subform.Form.RecordsetClone
.MoveFirst
Do While .EOF = False
If !ModuleID.Value = 1 _
And !Pass.Value = True Then _
blnTests(1) = True
If !ModuleID.Value = 2 _
And !Pass.Value = True Then _
blnTests(2) = True
If !ModuleID.Value = 7 _
And !Pass.Value = True Then _
blnTests(3) = True
.MoveNext
Loop
End With

intTest = 0
For lngLoop = 1 To 3
intTest = intTest + blnTests(lngLoop)
Next lngLoop
If intTest = -3 Then
Me.txtL1Pass = "Pass"
Else
Me.txtL1Pass = "Fail"
End If
End Sub

 

[tclarke]

Hi Ken and RuralGuy,

Thanks for your time, effort and prseverence, Ken's code did the trick
and with a few amendments I adapted it to check for three different
senarios to check for three diffent levels, L1, L2 and ECDL...It works
like a dream!

*******************************************
Private Sub Form_AfterUpdate()
Dim blnTestsL1(1 To 3) As Boolean
Dim blnTestsL2(1 To 8) As Boolean
Dim blnTestsECDL(1 To 7) As Boolean
Dim intTestL1 As Integer
Dim intTestL2 As Integer
Dim intTestECDL As Integer
Dim lngLoopL1 As Long
Dim lngLoopL2 As Long
Dim lngLoopECDL As Long

For lngLoopL2 = 1 To 8
blnTestsL2(lngLoopL2) = False
Next lngLoopL2

With Me.RecordsetClone
' Checks if a recordset exists
If .EOF = True And .BOF = True Then
Exit Sub
End If

.MoveFirst
Do While .EOF = False
If !ModuleID.Value = 1 _
And !Pass.Value = True Then
blnTestsL1(1) = True 'L1 criteria
blnTestsL2(1) = True 'L2 criteria
blnTestsECDL(1) = True 'ECDL criteria
End If
If !ModuleID.Value = 2 _
And !Pass.Value = True Then
blnTestsL1(2) = True
blnTestsL2(2) = True
blnTestsECDL(2) = True
End If
If !ModuleID.Value = 3 _
And !Pass.Value = True Then
blnTestsL2(3) = True
blnTestsECDL(3) = True
End If
If !ModuleID.Value = 4 _
And !Pass.Value = True Then
blnTestsL2(4) = True
blnTestsECDL(4) = True
End If
If !ModuleID.Value = 5 _
And !Pass.Value = True Then
blnTestsL2(5) = True
blnTestsECDL(5) = True
End If
If !ModuleID.Value = 6 _
And !Pass.Value = True Then
blnTestsL2(6) = True
blnTestsECDL(6) = True
End If
If !ModuleID.Value = 7 _
And !Pass.Value = True Then
blnTestsL1(3) = True
blnTestsL2(7) = True
blnTestsECDL(7) = True
End If
If !ModuleID.Value = 8 _
And !Pass.Value = True Then
blnTestsL2(8) = True
End If
.MoveNext
Loop
End With

intTestL1 = 0
For lngLoopL1 = 1 To 3
intTestL1 = intTestL1 + blnTestsL1(lngLoopL1)
Next lngLoopL1

If intTestL1 = -3 Then
Me.Parent.txtL1Pass = "PASS"
Else
Me.Parent.txtL1Pass = "NO"
End If

intTestL2 = 0
For lngLoopL2 = 1 To 8
intTestL2 = intTestL2 + blnTestsL2(lngLoopL2)
Next lngLoopL2

If intTestL2 = -8 Then
Me.Parent.txtL2Pass = "PASS"
Else
Me.Parent.txtL2Pass = "NO"
End If

intTestECDL = 0
For lngLoopECDL = 1 To 7
intTestECDL = intTestECDL + blnTestsECDL(lngLoopECDL)
Next lngLoopECDL

If intTestECDL = -7 Then
Me.Parent.txtECDLPass = "PASS"
Else
Me.Parent.txtECDLPass = "NO"
End If

Me.Requery
End Sub
*************************************************************

Terry

 

[RuralGuy]

Hi Ken and RuralGuy,

Thanks for your time, effort and prseverence, Ken's code did the trick
and with a few amendments I adapted it to check for three different
senarios to check for three diffent levels, L1, L2 and ECDL...It works
like a dream!

[snip]

Hi Terry,

Thanks for posting back with your success.