open recordset

[jonathan]

Hi all,
I have a main form with a subform (call it subform1), that in turn has a
subform (call it subform2), (both subforms are in datasheet view).
When i open the main it opens subform1, no problem. What i want to do is
make a unbound text box on subform1 change colour or something if there are
related records in subform2. I don;t want to open subform2 when the main
form loads because there is a information overload problem on the screen,
just to much information. If there are any records in subform2 the user can
open it by double clicking the related record in subform1 (i can do this
bit).
So basically i need to either open the recordset of subform2 and see if
there are any records or run a query and do it that way?
Any ideas pls.
cheers
jon

 

[Andy]

I suggest you use a recordsetclone of subform2 and check the recordcount.

hth,
Andy

 

[jonathan]

Hi
I have done a Dcount using a query but i can't my text box to change colour.
Dcount works fine as i get the right message comming up.

Dim lngRed As Long, lngYellow As Long, lngWhite As Long, lngBlack As Long
Dim intcount As Integer
lngRed = RGB(255, 0, 0)
lngBlack = RGB(0, 0, 0)
lngYellow = RGB(255, 255, 0)
lngWhite = RGB(255, 255, 255)
intcount = DCount("[TaskID]", "qryanysubtasks")

If intcount > 0 Then
MsgBox "Number is" & intcount
Me.What_task.ForeColor = lngRed
Else
Me!What_task.FontColor = lngYellow
End If


At the moment this is just on a click event. It needs to be using the
Oncurrent event and also cycle through each record in my subform. Do i need
to something like below and then do a dcount for each row it finds. How can
i do this?

If Not rs.BOF And Not rs.EOF Then
rs.MoveFirst
Do Until rs.EOF

 

[jonathan]

Hi again,
i have played about with some code and got this to work. however it is not
re-running the query for each record it moves to. How do i get this run the
query for each record? thx in advance

Private Sub Form_Current()
Dim rs As DAO.Recordset
Dim intcount As Integer

Set rs = Me.RecordsetClone

If Not rs.BOF And Not rs.EOF Then
rs.MoveFirst
Do Until rs.EOF
intcount = DCount("[TaskID]", "qryanysubtasks")
If intcount > 0 Then
'MsgBox "Number is" & intcount
Me.txtrecords = "SubTasks"
Exit Do
End If
rs.MoveNext
Loop
End If

End Sub

Hi
I have done a Dcount using a query but i can't my text box to change colour.
Dcount works fine as i get the right message comming up.

Dim lngRed As Long, lngYellow As Long, lngWhite As Long, lngBlack As Long
Dim intcount As Integer
lngRed = RGB(255, 0, 0)
lngBlack = RGB(0, 0, 0)
lngYellow = RGB(255, 255, 0)
lngWhite = RGB(255, 255, 255)
intcount = DCount("[TaskID]", "qryanysubtasks")

If intcount > 0 Then
MsgBox "Number is" & intcount
Me.What_task.ForeColor = lngRed
Else
Me!What_task.FontColor = lngYellow
End If


At the moment this is just on a click event. It needs to be using the
Oncurrent event and also cycle through each record in my subform. Do i need
to something like below and then do a dcount for each row it finds. How can
i do this?

If Not rs.BOF And Not rs.EOF Then
rs.MoveFirst
Do Until rs.EOF

I suggest you use a recordsetclone of subform2 and check the recordcount.

hth,
Andy


there
are user
can

 

[jonathan]

Hi Ken, thx for the reply. You code did'nt work so i had to put in a Do
Until rs.EOF
at the start, it kept going on about no Do statement. It is cylcing through
each record no problem. But it seems to be only capturing the number of sub
records for the first main record. It looks like the query is only been run
once or its retaining the first value. It needs to run the query for each
seperate main record and display the number of hits in the query in a text
box, which it is doing.
cheers

Your code will always run just for the first record in the rs recordset
unless that first record has no child records in the subform2; the code then
will run only until it finds a record that does have a child record, and
then it stops. This is because of your use of MoveFirst in the code. Try
this if you just want the current record to have the query's results:

If Not rs.BOF And Not rs.EOF Then
If DCount("[TaskID]", "qryanysubtasks") > 0 Then
Me.txtrecords = "SubTasks"
Exit Do
End If
Loop
End If


--
Ken Snell
<MS ACCESS MVP>

Hi again,
i have played about with some code and got this to work. however it is not
re-running the query for each record it moves to. How do i get this run the
query for each record? thx in advance

Private Sub Form_Current()
Dim rs As DAO.Recordset
Dim intcount As Integer

Set rs = Me.RecordsetClone

If Not rs.BOF And Not rs.EOF Then
rs.MoveFirst
Do Until rs.EOF
intcount = DCount("[TaskID]", "qryanysubtasks")
If intcount > 0 Then
'MsgBox "Number is" & intcount
Me.txtrecords = "SubTasks"
Exit Do
End If
rs.MoveNext
Loop
End If

End Sub

Hi
I have done a Dcount using a query but i can't my text box to change colour.
Dcount works fine as i get the right message comming up.

Dim lngRed As Long, lngYellow As Long, lngWhite As Long, lngBlack As Long
Dim intcount As Integer
lngRed = RGB(255, 0, 0)
lngBlack = RGB(0, 0, 0)
lngYellow = RGB(255, 255, 0)
lngWhite = RGB(255, 255, 255)
intcount = DCount("[TaskID]", "qryanysubtasks")

If intcount > 0 Then
MsgBox "Number is" & intcount
Me.What_task.ForeColor = lngRed
Else
Me!What_task.FontColor = lngYellow
End If


At the moment this is just on a click event. It needs to be using the
Oncurrent event and also cycle through each record in my subform. Do i need
to something like below and then do a dcount for each row it finds.

How
can

i do this?

If Not rs.BOF And Not rs.EOF Then
rs.MoveFirst
Do Until rs.EOF


I suggest you use a recordsetclone of subform2 and check the recordcount.

hth,
Andy


Hi all,
I have a main form with a subform (call it subform1), that in turn

has

a
subform (call it subform2), (both subforms are in datasheet view).
When i open the main it opens subform1, no problem. What i want to

do

is
make a unbound text box on subform1 change colour or something if there
are
related records in subform2. I don;t want to open subform2 when

the
main

form loads because there is a information overload problem on the
screen,
just to much information. If there are any records in subform2 the user
can
open it by double clicking the related record in subform1 (i can

do
this

bit).
So basically i need to either open the recordset of subform2 and

see
if

there are any records or run a query and do it that way?
Any ideas pls.
cheers
jon

 

[Ken Snell]

OK - you know what your code needs better than I know < g >

Good luck.

Hi Ken, thx for the reply. You code did'nt work so i had to put in a Do
Until rs.EOF
at the start, it kept going on about no Do statement. It is cylcing through
each record no problem. But it seems to be only capturing the number of sub
records for the first main record. It looks like the query is only been run
once or its retaining the first value. It needs to run the query for each
seperate main record and display the number of hits in the query in a text
box, which it is doing.
cheers

Your code will always run just for the first record in the rs recordset
unless that first record has no child records in the subform2; the code then
will run only until it finds a record that does have a child record, and
then it stops. This is because of your use of MoveFirst in the code. Try
this if you just want the current record to have the query's results:

If Not rs.BOF And Not rs.EOF Then
If DCount("[TaskID]", "qryanysubtasks") > 0 Then
Me.txtrecords = "SubTasks"
Exit Do
End If
Loop
End If


--
Ken Snell
<MS ACCESS MVP>

Hi again,
i have played about with some code and got this to work. however it

is
not

re-running the query for each record it moves to. How do i get this

run
the

query for each record? thx in advance

Private Sub Form_Current()
Dim rs As DAO.Recordset
Dim intcount As Integer

Set rs = Me.RecordsetClone

If Not rs.BOF And Not rs.EOF Then
rs.MoveFirst
Do Until rs.EOF
intcount = DCount("[TaskID]", "qryanysubtasks")
If intcount > 0 Then
'MsgBox "Number is" & intcount
Me.txtrecords = "SubTasks"
Exit Do
End If
rs.MoveNext
Loop
End If

End Sub


Hi
I have done a Dcount using a query but i can't my text box to change
colour.
Dcount works fine as i get the right message comming up.

Dim lngRed As Long, lngYellow As Long, lngWhite As Long, lngBlack As Long
Dim intcount As Integer
lngRed = RGB(255, 0, 0)
lngBlack = RGB(0, 0, 0)
lngYellow = RGB(255, 255, 0)
lngWhite = RGB(255, 255, 255)
intcount = DCount("[TaskID]", "qryanysubtasks")

If intcount > 0 Then
MsgBox "Number is" & intcount
Me.What_task.ForeColor = lngRed
Else
Me!What_task.FontColor = lngYellow
End If


At the moment this is just on a click event. It needs to be using the
Oncurrent event and also cycle through each record in my subform. Do i
need
to something like below and then do a dcount for each row it finds. How
can
i do this?

If Not rs.BOF And Not rs.EOF Then
rs.MoveFirst
Do Until rs.EOF


I suggest you use a recordsetclone of subform2 and check the
recordcount.

hth,
Andy


Hi all,
I have a main form with a subform (call it subform1), that in

turn
has

a
subform (call it subform2), (both subforms are in datasheet view).
When i open the main it opens subform1, no problem. What i want

to
do

is
make a unbound text box on subform1 change colour or something if
there
are
related records in subform2. I don;t want to open subform2 when the
main
form loads because there is a information overload problem on the
screen,
just to much information. If there are any records in subform2 the
user
can
open it by double clicking the related record in subform1 (i can do
this
bit).
So basically i need to either open the recordset of subform2 and see
if
there are any records or run a query and do it that way?
Any ideas pls.
cheers
jon