open recordset

  • Thread starter Thread starter jonathan
  • Start date Start date
J

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
 
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
 
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


jonathan said:
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


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

hth,
Andy


there
are user
can
Click to expand...
Click to expand...
 
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

Ken Snell said:
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>

jonathan said:
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


jonathan said:
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.
Click to expand...
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
Click to expand...
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
Click to expand...
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
Click to expand...
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
Click to expand...
do
this
bit).
So basically i need to either open the recordset of subform2 and
Click to expand...
see
if
there are any records or run a query and do it that way?
Any ideas pls.
cheers
jon
Click to expand...
Click to expand...
Click to expand...
 
OK - you know what your code needs better than I know < g >

Good luck.

jonathan said:
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

Ken Snell said:
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>

jonathan said:
Hi again,
i have played about with some code and got this to work. however it
Click to expand...
is
not
re-running the query for each record it moves to. How do i get this
Click to expand...
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
Click to expand...
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
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top