2 subforms, 1 command button

  • Thread starter Thread starter hermanko
  • Start date Start date
H

hermanko

Hi,

I have a form [Duplication] which has two subforms, [Sub1] and [Sub2].
Each subform contains records that the user can select to remove by
first selecting the record's associated checkbox (a yes/no field), and
then clicking on a Command button "cmd_remove" on the Duplication form.
The tricky part is that I need the cmd button disabled when no
checkboxes are selected in both subforms, and enabled if any ONE box is
selected from EITHER subform.

I used the following code under each subform's checkbox control
AfterUpdate event. It is the same for both subforms:

Private Sub Yes_No_AfterUpdate()
Dim rsClone As Recordset
Dim blnChecked As Boolean
Me.Dirty = False
Set rsClone = Me.RecordsetClone
rsClone.MoveFirst
Do Until rsClone.EOF
If rsClone![Yes/No] Then
blnChecked = True
Exit Do
End If
rsClone.MoveNext
Loop
[Forms]![Duplication]!cmd_remove.Enabled = blnChecked
Set rsClone = Nothing
End Sub

The problem with this code is that the cmd button reacts fine but
dependantly to each subform. i.e. if i check a box from Sub1 as well as
Sub2, the cmd button enables correctly, but once i clear all boxes
from, say, Sub1, the cmd button disables even tho there are still
selected boxes from Sub2. I need to get the code to check both
recordsets on each AfterUpdate event for both subforms.

any suggestions would be greatly appreciated, as my vb is not strong.
Thanks!
Herman
 
I didn't test it but it may help, this is how I'd do it:

Create on the duplication form a new function Refresh_Button()

Public Function Refresh_Button()
Dim rsClone As Recordset
Dim blnChecked As Boolean
Me.Dirty = False

Set rsClone = Sub1.Form.RecordsetClone
rsClone.MoveFirst

Do Until rsClone.EOF
If rsClone![Yes/No] Then
blnChecked = True
Exit Do
End If
rsClone.MoveNext
Loop

if not blnChecked then
Set rsClone = Sub2.Form.RecordsetClone
rsClone.MoveFirst

Do Until rsClone.EOF
If rsClone![Yes/No] Then
blnChecked = True
Exit Do
End If
rsClone.MoveNext
Loop
End If

[Forms]![Duplication]!cmd_remove.Enabled = blnChecked
Set rsClone = Nothing
End Sub

On the event after update of Yes/No check of both subForms you put:

Forms("Duplication").Refresh_Button()

Let us know if this helped
Mauricio Silva
 
Where do I place this new function on the Duplication form? OnOpen?
OnCurrent? After Update?
 
As this is a function, I understand now that you dont put it under an
"event"....

However, i'm having problems with this part of your code:

"On the event after update of Yes/No check of both subForms you put:
Forms("Duplication").Refresh_Button() "

When i put that in the afterupdate event, i get an error saying can't
find the Macro expression? Is this the correct way to call the
Refresh_Button() function?
 
I tried putting the above code in a new module. and setting each
AfterUpdate event of the Yes/No checkbox on both subforms to
"=Refresh_Button()"

I still get an error with the "Me.Dirty = False" in the function code
(is it because i have it in a module?).

Please provide clarification thanks :)
Herman
 
Herman,

The "Me" in "Me.Dirty = False" is the Access shorthand for
"[Forms]![Duplication].Dirty "

It sounds like you put the function in a standard module instead of the
class module for the form "Duplication".

Looking at the code, there are references to "Sub1" and "Sub2" which will
cause errors if the function is in a standard module because they are not
fully qualified names (ie something like
"[Forms]![Duplication]!Sub1.Form.RecordsetClone")

Once you delete the code from the standard module and paste it in the form
(class)module, you should be able to get it to run.

HTH
 
Ok, I seem to have gotten this code to work now.

Is it possible, however, that this code will be slow? In testing, i
have a macro that calls this function on an Onclick event, and the
underlying table contains about 100 records....and the process took
about 10 whole seconds before i could go on to the next step. The
actions i have are given below:

Private Sub cmd_selectall_Click()
DoCmd.SetWarnings No
DoCmd.OpenQuery "Select All", acViewNormal, acEdit 'this action
selects the older duplicate record and sets the yes/no checkbox to
true, so of the 100 records, for example, there will be 50 duplicates,
and the 50 older records, by Date field, are selected
DoCmd.Requery
DoCmd.RunMacro "PrepDelete.CheckboxRefresh" 'this action calls the
function below, which checks the subform for ANY selected boxes, and
then enables a command button on the main form
End Sub

This function loops thru each recordset and see if ANY ONE checkbox is
selected (i.e. true), so that it will enable a cmd button.

Public Function Refresh_Button()
Dim rsClone As Recordset
Dim blnChecked As Boolean
[Forms]![Duplication]![Subform Different Proxy].Form.Dirty = False
[Forms]![Duplication]![Subform Same Proxy].Form.Dirty = False

If (DCount("*", "Duplicate Fund Name (different Proxy)") > 0) Then
Set rsClone = [Forms]![Duplication]![Subform Different
Proxy].Form.RecordsetClone
rsClone.MoveFirst

Do Until rsClone.EOF
If rsClone![Yes/No] Then
blnChecked = True
Exit Do
End If
rsClone.MoveNext
Loop
End If

If Not blnChecked Then
Set rsClone = [Forms]![Duplication]![Subform Same
Proxy].Form.RecordsetClone
rsClone.MoveFirst

Do Until rsClone.EOF
If rsClone![Yes/No] Then
blnChecked = True
Exit Do
End If
rsClone.MoveNext
Loop
End If

[Forms]![Duplication]!cmd_remove.Enabled = blnChecked
Set rsClone = Nothing
End Function

so, this entire process is REALLY slow....what can be done to speed
things up?
Thanks for your help,
Herman
 
Herman,

It is hard to say without more info. But with only 100 records, it should
execute faster than the blink of an eye.


Is the button "cmd_selectall" on the main form?


Private Sub cmd_selectall_Click()
DoCmd.SetWarnings No
DoCmd.OpenQuery "Select All", acViewNormal, acEdit
DoCmd.Requery
DoCmd.RunMacro "PrepDelete.CheckboxRefresh"
End Sub

Note: if you turn warnings off, you should turn warnings back on.


What does the query "Select All" do? Is it an action query? Please post the SQL
of the query.


You are running a macro that calls a function. Why don't you just run the function?




I see what you are trying to do, but I would do this differently. (Everyone has
a different way... :)

You will have to change the SQL for the "db.OpenRecordset" (2 lines) to your
subforms SQL and the form open event delete SQL - marked with "###"


'---------------
In each subform's checkbox control AfterUpdate event, I would have:
(Watch for line wrap)

'*** beg code *********
'in each subform
Private Sub Yes_No_AfterUpdate()
me.dirty = False
IsItSelected
end Sub

'-----------
In the form "Duplication":

' clears all check boxes
Private Sub cmdClearAll_Click()
'####
CurrentDb.Execute "UPDATE tblChkTest SET tblChkTest.checkbox = False,
tblChkTest.Chk2 = False;", dbFailOnError
'###
IsItSelected
MsgBox "All Checkboxes Cleared"
End Sub

'set the button enabled state when the form opens
Private Sub Form_Open(Cancel As Integer)
IsItSelected
End Sub



'---Put this in a standard module---
Public Sub IsItSelected()
Dim rsSame As DAO.Recordset
Dim rsDiff As DAO.Recordset
Dim db As DAO.Database
Dim blnEnableBtn As Boolean

'Set default state for the button "cmd_remove"
blnEnableBtn = True

Set db = CurrentDb

'recordset for subform [Subform Same Proxy]
'###
Set rsSame = db.OpenRecordset("SELECT [MyRecID], [checkbox] FROM tblChkTest
Where [checkbox]= True ORDER BY [MyRecID];")

'recordset for subform [Subform Different Proxy]
'###
Set rsDiff = db.OpenRecordset("SELECT [MyRecID], [Chk2] FROM tblChkTest
Where [chk2]= True ORDER BY [MyRecID];")

' if a record is at BOF AND EOF, then there are no records - [Yes_No] not
selected
If rsSame.BOF And rsSame.EOF And rsDiff.BOF And rsDiff.EOF Then
' this will disable the button "cmd_remove"
blnEnableBtn = False
End If

[Forms]![Duplication]!cmd_remove.Enabled = blnEnableBtn

'clean up
rsSame.Close
rsDiff.Close
Set rsSame = Nothing
Set rsDiff = Nothing
Set db = Nothing

End Sub


"********************* end code *************


HTH
--
Steve S.
--------------------------------
"Veni, Vidi, Velcro"
(I came, I saw, I stuck around.)

Ok, I seem to have gotten this code to work now.

Is it possible, however, that this code will be slow? In testing, i
have a macro that calls this function on an Onclick event, and the
underlying table contains about 100 records....and the process took
about 10 whole seconds before i could go on to the next step. The
actions i have are given below:

Private Sub cmd_selectall_Click()
DoCmd.SetWarnings No
DoCmd.OpenQuery "Select All", acViewNormal, acEdit 'this action
selects the older duplicate record and sets the yes/no checkbox to
true, so of the 100 records, for example, there will be 50 duplicates,
and the 50 older records, by Date field, are selected
DoCmd.Requery
DoCmd.RunMacro "PrepDelete.CheckboxRefresh" 'this action calls the
function below, which checks the subform for ANY selected boxes, and
then enables a command button on the main form
End Sub

This function loops thru each recordset and see if ANY ONE checkbox is
selected (i.e. true), so that it will enable a cmd button.

Public Function Refresh_Button()
Dim rsClone As Recordset
Dim blnChecked As Boolean
[Forms]![Duplication]![Subform Different Proxy].Form.Dirty = False
[Forms]![Duplication]![Subform Same Proxy].Form.Dirty = False

If (DCount("*", "Duplicate Fund Name (different Proxy)") > 0) Then
Set rsClone = [Forms]![Duplication]![Subform Different
Proxy].Form.RecordsetClone
rsClone.MoveFirst

Do Until rsClone.EOF
If rsClone![Yes/No] Then
blnChecked = True
Exit Do
End If
rsClone.MoveNext
Loop
End If

If Not blnChecked Then
Set rsClone = [Forms]![Duplication]![Subform Same
Proxy].Form.RecordsetClone
rsClone.MoveFirst

Do Until rsClone.EOF
If rsClone![Yes/No] Then
blnChecked = True
Exit Do
End If
rsClone.MoveNext
Loop
End If

[Forms]![Duplication]!cmd_remove.Enabled = blnChecked
Set rsClone = Nothing
End Function

so, this entire process is REALLY slow....what can be done to speed
things up?
Thanks for your help,
Herman
Click to expand...
 
Hi Steve,

To clarify, I have a select query called "Select Old Records", which
selects the older record of each duplicate based on the Date field. The
underlying table will have records with duplicate Fund Name fields
(groups of two records), but the date will be different (the more
recent date indicates a new updated set of data). The SQL for this
select query is:

SELECT T1.[Fund Name], T1.Date, T1.[Yes/No]
FROM [Duplicate Fund Name (same Proxy)] AS T1
GROUP BY T1.[Fund Name], T1.Date, T1.[Yes/No]
HAVING (((T1.Date)=(SELECT Min(T2.[Date]) FROM [Duplicate Fund Name
(same Proxy)] AS T2 WHERE T1.[Fund Name] = T2.[Fund Name])));

Then, I use my "Select All" update query where the SQL is provided
here:

UPDATE [Duplicate Fund Name (same Proxy)] SET [Yes/No] = -1
WHERE [Date] In (SELECT [Date] FROM [Select Old Records] WHERE [Date] =
[Duplicate Fund Name (same Proxy)].[Date] And
[Fund Name] = [Duplicate Fund Name (same Proxy)].[Fund Name]);

This updates all records from "Select Old Records" by setting each
Yes/No checkbox to true.

And to answer your other question:
"Is the button "cmd_selectall" on the main form? "
No, it is on the subform called "Subform Same Proxy" (and "Duplication"
is the main form)

It took about 30 seconds to select 50 records out of 100 (older record
of each duplicate) once I click the cmd_selectall button. The code that
runs is posted back on April 25 which u responded to. I don't know why
it is taking so long.

Herman
 
Hi Herman,

I don't know why it is so slow, but I think it is because you are using a
totals query with a sub query to select the earliest dates for each fund, then
running an update query with a sub query to change the Yes_No field.

I am running Win2K/A2k on a 233mhz gateway with 392mb of ram. I set up a table
and a form with two subforms. In a table I created 50 duplicate records (100
total) with different dates. When I ran your queries, it took about 19 secs.
When I changed the saved totals query to a select query, the time went down to
about 15 secs.

I added another button to the subform and did the update to the [Yes_No] field
totally in code. It took less than 1 sec.

NOTE: I changed the field [Date] to [FundDate], since Date is a reserved word
in Access. And I replaced the slash in the field [Yes/No] with an underline
[Yes_No].

In the code, you will need to change the subform control names with your
subform names. (near the bottom of the code)

You will see



Here is the code - watch for line wrap

'****************
Dim strsql As String
Dim strToFind As String

'selects the earliest fund dates
strsql = "SELECT T1.[Fund Name], T1.FundDate, T1.[Yes_No]"
strsql = strsql & " FROM [Duplicate Fund Name (same Proxy)] AS T1"
strsql = strsql & " WHERE (((T1.FundDate)=(SELECT Min(T2.[FundDate]) FROM
[Duplicate Fund Name (same Proxy)] AS T2 WHERE T1.[Fund Name] = T2.[Fund Name])))"

strsql = strsql & " ORDER BY T1.[Fund Name], T1.fundDate;"

Set rs = CurrentDb.OpenRecordset(strsql)

'check for records
If Not rs.BOF And Not rs.EOF Then
rs.MoveLast
rs.MoveFirst
Else
rs.Close
Set rs = Nothing
Msgbox "No Records found - aborting!!"
Exit Sub
End If

'opens a recordset of the table - all records
Set tbl = CurrentDb.OpenRecordset("SELECT [Fund Name], FundDate, [Yes_No]
FROM [Duplicate Fund Name (same Proxy)] ORDER BY [Fund Name], fundDate;")

With rs

' loop thru the recordset
Do Until .EOF
strToFind = "[Fund Name] = '" & ![Fund Name] & "' and FundDate = #" &
!FundDate & "#"

tbl.FindFirst strToFind
If Not tbl.NoMatch Then
tbl.Edit
tbl!Yes_No = True
tbl.Update
End If
.MoveNext
Loop

End With


'*** change 'sfTrueFunds' below to the name of one subform control
Me.Parent.sfTrueFunds.Requery

'*** change 'sfFalseFunds' to the name of the other subform control
Me.Parent.sfFalseFunds.Requery



rs.Close
Set rs = Nothing

tbl.Close
Set tbl = Nothing

MsgBox "done"
'****************


HTH
---
Steve S.
--------------------------------
"Veni, Vidi, Velcro"
(I came, I saw, I stuck around.)

Hi Steve,

To clarify, I have a select query called "Select Old Records", which
selects the older record of each duplicate based on the Date field. The
underlying table will have records with duplicate Fund Name fields
(groups of two records), but the date will be different (the more
recent date indicates a new updated set of data). The SQL for this
select query is:

SELECT T1.[Fund Name], T1.Date, T1.[Yes/No]
FROM [Duplicate Fund Name (same Proxy)] AS T1
GROUP BY T1.[Fund Name], T1.Date, T1.[Yes/No]
HAVING (((T1.Date)=(SELECT Min(T2.[Date]) FROM [Duplicate Fund Name
(same Proxy)] AS T2 WHERE T1.[Fund Name] = T2.[Fund Name])));

Then, I use my "Select All" update query where the SQL is provided
here:

UPDATE [Duplicate Fund Name (same Proxy)] SET [Yes/No] = -1
WHERE [Date] In (SELECT [Date] FROM [Select Old Records] WHERE [Date] =
[Duplicate Fund Name (same Proxy)].[Date] And
[Fund Name] = [Duplicate Fund Name (same Proxy)].[Fund Name]);

This updates all records from "Select Old Records" by setting each
Yes/No checkbox to true.

And to answer your other question:
"Is the button "cmd_selectall" on the main form? "
No, it is on the subform called "Subform Same Proxy" (and "Duplication"
is the main form)

It took about 30 seconds to select 50 records out of 100 (older record
of each duplicate) once I click the cmd_selectall button. The code that
runs is posted back on April 25 which u responded to. I don't know why
it is taking so long.

Herman
Click to expand...
 
Hi Steve,

You brought up something that prompted me to look at my queries closer.
I realized that I did not need to have a Totals query and I
accidentally left that in the QBE grid....after i changed it back to a
Select query, it ran in 1 second! I am not sure WHY this works out, but
at this point i'm willing to just be happy that it works :P Thank you
so much for taking the time to explain everything to me in detail and
providing clear examples :) You have been a great help to me this past
week (and just in time for my Monday morning deadline!!)

Thanks again!
Herman
 
You must log in or register to reply here.

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

Linking two subforms together. 3
Current event not getting fired on subform 3
#Error appears on subform residing on a tab control 1
Main form and two Subs claculation 2
Problem refreshing child form 1
Two subroutines controlling one form 4
Combo sync-ing to form after setvalue of Maco 1
Reference to calc field on sister subform? 1

Back
Top