Command buttons based on data selected in a subform

[Laura_C]

So basically what I have is a form with a subform in it. The main form has all these drop downs and check boxes and fill ins that determine which records show up in the subform. Then there are command buttons that are supposed to run reports or make changes based on the records selected in the subform.

So for example: I want to select all our board members who are not on our paperless member program. So I check the box for board members and the box for paperless which has a Y/N drop down and I select No. So my subform (which is just a data table view below the selectors) fills in all the board members who aren't part of the paperless program. There are 14.

Now there is a command button called Global Add with which I can add the same memo (among other things) to a bunch of records at the same time based on the selections in the subform. I click that button, and it's supposed to fill a temp table with the records selected, then takes me to a new form where I add the memo text, then I click the ADD command button and it's SUPPOSED to add that memo ONLY to those 14 records. Instead, it's adding them to every record in the database. The temp table is not being updated properly.

Any thoughts? I appreciate any help I can get. I'm pretty novice with VBA. I can read a lot of it, and I'm decent at copying code and tweaking it (which is how I did this, but copying the code from a working database and pasting it into a new version I'm trying to create) but I've never written code from scratch.

Thank you!

 

[Laura_C]

If it helps, this is the line of code I removed to get the button to work at all.

strSQL = strSQL & ExtractSQL("select * from QryMembersReports ")

Before removing it I got a compile error sub or function not defined when I clicked the button. Obviously that's the line I need because that's the query the subform uses. But I don't know how to fix it.

Here's all the code:

'Global Add Button'

Private Sub cmdFrmMembersMultiAdd_Click()
On Error GoTo Err_cmdFrmMembersMultiAdd_Click


If Not (IsNull(Me.txtNbrOfMembers) Or Me.txtNbrOfMembers = 0) Then
'add all selected members to temp table
Dim strSQL As String
strSQL = "SELECT QryMembersReports.NUM INTO tblMembersMultiAddTemp"
strSQL = strSQL & " FROM QryMembersReports "
strSQL = strSQL & ExtractSQL("select * from QryMembersReports")

'Debug.Print strSQL
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

' SELECT QryMembersReports.NUM INTO tblMembersMultiAdd
' FROM QryMembersReports
' WHERE (((QryMembersReports.Total)>=2500 And (QryMembersReports.Total)<=1000000));

'open form to select parameters
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmMembersMultiAdd"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Else
MsgBox "select at least one member first"
End If


Exit_cmdFrmMembersMultiAdd_Click:
Exit Sub


Err_cmdFrmMembersMultiAdd_Click:
MsgBox Err.Description
Resume Exit_cmdFrmMembersMultiAdd_Click

End Sub

 

[Laura_C]

OK! I was missing this code so I added it, but now when I click the Global Add command button I get "Enter Parameter Value QryMembersReports.NUM.

Function ExtractSQL(strQuery As String) As String
On Error GoTo Err_ExtractSQL

ExtractSQL = Mid(Me.xSelectSQL, Len(strQuery), Len(Me.xSelectSQL) - Len(strQuery) + 1)

Exit_ExtractSQL:
Exit Function


Err_ExtractSQL:
MsgBox "ExtractSQL " & Err.Number & " " & Err.Description
Resume Exit_ExtractSQL

End Function

 

[adam087]

OK a couple of things.

LIFO. The reason that you get the 'Enter Parameter Value' is usually because Access doesn't recognise a criteria. So what is QryMembersReports.NUM ?

Notwithstanding. I've not picked through your code in detail (perhaps also use code tags when posting?). But my general sort of advice would be...

- There must be some code that is currently working to create a criteris to filter the SubForm.
- I'd guess this constructs a text string and then sets SUBFORM.FORM.FILTER = FILTER_STRING or something similar.
- You can use this same code to construct a WHERE clause and then use that in an update or append query, perhaps something like...

mySql = "INSERT INTO dest_tbl (field1, field2, field3) " & _
 "SELECT source_tbl.field1, source_tbl.field2, source_tbl.field3 FROM source_tbl " & _
 "WHERE " & your_criteria & ";"
 
currentDb.Execute mySql

Hope that helps as a starter for 10...

/AJ

 

[Laura_C]

QryMembersReports is the name of the query used by the subform. NUM is a column in that query (basically member ID number). I think what is supposed to happen is this:

I select the criteria that populates the subform from the query. Then the command button is supposed to take the ID numbers (NUM) from the selection and add them to a temp table. That temp table determined which ID's get the info I'm adding.

What is happening is when I click the command button, I'm getting the parameter message, the temp table is loading the right number of entries, but the ID numbers are not showing up. So if the selection is 14 members, there will be 14 lines in the temp table, but they are all empty. And no data gets added anywhere.

Does that clear anything up? I'm working from a database that was designed by someone else, so I'm not sure of all their code.

 

[adam087]

What's the code that populates the subform? There must be something that runs when the criteria are changed?

/AJ

 

[Laura_C]

Well, this is the code for the select command button, which is what fills in the subform. There's a lot of it because there are a lot of selectors.

Private Sub cmdSelect_Click()
On Error GoTo Err_cmdSelect_Click

Dim strSQL As String
strSQL = "SELECT * FROM QryMembersReports"

If Me.chkSelectNameLike = True Then
If Not IsNull(Me.txtChooseNameLike) Then
If Not (strSQL = "SELECT * FROM QryMembersReports") Then
strSQL = strSQL & " AND "
Else
strSQL = strSQL & " WHERE "
End If
strSQL = strSQL & "([LNAME] like '*" & Me.txtChooseNameLike & "*' OR [FNAME] like '*" & Me.txtChooseNameLike & "*')"
Else
MsgBox "Enter First /Last name string or deselect"
End If
End If

'Type checkbox code'
If Me.chkSelectType = True Then
If Not IsNull(Me.cboChooseType) Then
If Not (strSQL = "SELECT * FROM QryMembersReports") Then
strSQL = strSQL & " AND "
Else
strSQL = strSQL & " WHERE "
End If
strSQL = strSQL & "[Type] ='" & Me.cboChooseType & "'"
Else
MsgBox "Select type or deselect"
End If
End If

'Town checkbox code'
If Me.chkSelectTown = True Then
If Not IsNull(Me.cboChooseTown) Then
If Not (strSQL = "SELECT * FROM QryMembersReports") Then
strSQL = strSQL & " AND "
Else
strSQL = strSQL & " WHERE "
End If
strSQL = strSQL & "[TC] ='" & Me.cboChooseTown & "'"
Else
MsgBox "Select town or deselect"
End If
End If

'Area checkbox code'
If Me.chkSelectArea = True Then
If Not IsNull(Me.cboChooseArea) Then
If Not (strSQL = "SELECT * FROM QryMembersReports") Then
strSQL = strSQL & " AND "
Else
strSQL = strSQL & " WHERE "
End If
strSQL = strSQL & "[area] ='" & Me.cboChooseArea & "'"
Else
MsgBox "Select area or deselect"
End If
End If

'Active checkbox code'
If Me.chkSelectActive = True Then
If Not IsNull(Me.cboChooseActive) Then
If Not (strSQL = "SELECT * FROM QryMembersReports") Then
strSQL = strSQL & " AND "
Else
strSQL = strSQL & " WHERE "
End If
If Me.cboChooseActive = "yes" Then
strSQL = strSQL & "[Active] = true"
Else
strSQL = strSQL & "[Active] = false"
End If
Else
MsgBox "Select Active yes or no or deselect"
End If
End If

'Paperless checkbox code'
If Me.ChkSelectPaperless = True Then
If Not IsNull(Me.cboPaperless) Then
If Not (strSQL = "SELECT * FROM QryMembersReports") Then
strSQL = strSQL & " AND "
Else
strSQL = strSQL & " WHERE "
End If
If Me.cboPaperless = "yes" Then
strSQL = strSQL & "[Paperless] = true"
Else
strSQL = strSQL & "[Paperless] = false"
End If
Else
MsgBox "Select Paperless yes or no or deselect"
End If
End If

'Semi-Paperless checkbox code'
If Me.chkSelectSemiP = True Then
If Not IsNull(Me.cboSemiP) Then
If Not (strSQL = "SELECT * FROM QryMembersReports") Then
strSQL = strSQL & " AND "
Else
strSQL = strSQL & " WHERE "
End If
If Me.cboSemiP = "yes" Then
strSQL = strSQL & "[Semi-Paperless] = true"
Else
strSQL = strSQL & "[Semi-Paperless] = false"
End If
Else
MsgBox "Select Semi-Paperless yes or no or deselect"
End If
End If


'BOD checkbox code'
If Me.chkSelectIsBoard = True Then
If Not IsNull(Me.CboChoosememIsBoard) Then
If Not (strSQL = "SELECT * FROM QryMembersReports") Then
strSQL = strSQL & " AND "
Else
strSQL = strSQL & " WHERE "
End If
If Me.CboChoosememIsBoard = "yes" Then
strSQL = strSQL & "[memIsBoard] = true"
Else
strSQL = strSQL & "[memIsboard] = false"
End If
Else
MsgBox "Select Is Board yes or no or deselect"
End If
End If

'Staff checkbox code'
If Me.chkSelectStaff = True Then
If Not IsNull(Me.CboChooseStaff) Then
If Not (strSQL = "SELECT * FROM QryMembersReports") Then
strSQL = strSQL & " AND "
Else
strSQL = strSQL & " WHERE "
End If
If Me.CboChooseStaff = "yes" Then
strSQL = strSQL & "[Staff] = true"
Else
strSQL = strSQL & "[Staff] = false"
End If
Else
MsgBox "Select Staff yes or no or deselect"
End If
End If

'Volunteer checkbox code'
If Me.chkSelectVolunteer = True Then
If Not IsNull(Me.CboChooseVolunteer) Then
If Not (strSQL = "SELECT * FROM QryMembersReports") Then
strSQL = strSQL & " AND "
Else
strSQL = strSQL & " WHERE "
End If
If Me.CboChooseVolunteer = "yes" Then
strSQL = strSQL & "[Volunteer] = true"
Else
strSQL = strSQL & "[Volunteer] = false"
End If
Else
MsgBox "Select Volunteer yes or no or deselect"
End If
End If

'Do Not Call checkbox code'
If Me.chkDoNotCall = True Then
If Not IsNull(Me.cboDoNotCall) Then
If Not (strSQL = "SELECT * FROM QryMembersReports") Then
strSQL = strSQL & " AND "
Else
strSQL = strSQL & " WHERE "
End If
If Me.cboSemiP = "yes" Then
strSQL = strSQL & "[DoNotCall] = true"
Else
strSQL = strSQL & "[DoNotCall] = false"
End If
Else
MsgBox "Select DoNotCall yes or no or deselect"
End If
End If

'Do Not Cultivate checkbox code'
If Me.chkDoNotCultivate = True Then
If Not IsNull(Me.cboDoNotCultivate) Then
If Not (strSQL = "SELECT * FROM QryMembersReports") Then
strSQL = strSQL & " AND "
Else
strSQL = strSQL & " WHERE "
End If
If Me.cboSemiP = "yes" Then
strSQL = strSQL & "[DoNotCultivate] = true"
Else
strSQL = strSQL & "[DoNotCultivate] = false"
End If
Else
MsgBox "Select DoNotCultivate yes or no or deselect"
End If
End If

'Alumni checkbox code'
If Me.chkSelectAlumni = True Then
If Not IsNull(Me.CboChooseBoardAlumni) Then
If Not (strSQL = "SELECT * FROM QryMembersReports") Then
strSQL = strSQL & " AND "
Else
strSQL = strSQL & " WHERE "
End If
If Me.CboChooseBoardAlumni = "yes" Then
strSQL = strSQL & "[BoardAlumni] = true"
Else
strSQL = strSQL & "[BoardAlumni] = false"
End If
Else
MsgBox "Select Alumni yes or no or deselect"
End If
End If

'Scallop Society checkbox code'
If Me.chkScallopSociety = True Then
If Not IsNull(Me.cboScallopSociety) Then
If Not (strSQL = "SELECT * FROM QryMembersReports") Then
strSQL = strSQL & " AND "
Else
strSQL = strSQL & " WHERE "
End If
If Me.cboScallopSociety = "yes" Then
strSQL = strSQL & "[Scallop Society] = true"
Else
strSQL = strSQL & "[Scallop Society] = false"
End If
Else
MsgBox "Select Scallop Society yes or no or deselect"
End If
End If


'Preservation checkbox code'
If Me.chkPreservationSociety = True Then
If Not IsNull(Me.cboPreservationSociety) Then
If Not (strSQL = "SELECT * FROM QryMembersReports") Then
strSQL = strSQL & " AND "
Else
strSQL = strSQL & " WHERE "
End If
If Me.cboSemiP = "yes" Then
strSQL = strSQL & "[Preservation Society] = true"
Else
strSQL = strSQL & "[Preservation Society] = false"
End If
Else
MsgBox "Select Preservation Society yes or no or deselect"
End If
End If

'Group checkbox code'
If Me.chkSelectGroup = True Then
If Not IsNull(Me.cboChooseGroup) Or Me.cboChooseGroup = 0 Then
If Not (strSQL = "SELECT * FROM QryMembersReports") Then
strSQL = strSQL & " AND "
Else
strSQL = strSQL & " WHERE "
End If
strSQL = strSQL & "[memGroupshelp] = true"
Else
MsgBox "Select group or deselect"
End If
End If

'Benchmark checkbox code'
If Me.chkSelectBenchmark = True Then
If Not IsNull(Me.cboChooseBenchmark) Then
If Not (strSQL = "SELECT * FROM QryMembersReports") Then
strSQL = strSQL & " AND "
Else
strSQL = strSQL & " WHERE "
End If
If Me.cboChooseBenchmark = "yes" Then
strSQL = strSQL & "[memBenchmark] = true"
Else
strSQL = strSQL & "[memBenchmark] = false"
End If
Else
MsgBox "Select Benchmark yes or no or deselect"
End If
End If

'Intro checkbox code'
If Me.chkSelectIntro = True Then
If Not IsNull(Me.cboChooseIntro) Then
If Not (strSQL = "SELECT * FROM QryMembersReports") Then
strSQL = strSQL & " AND "
Else
strSQL = strSQL & " WHERE "
End If
strSQL = strSQL & "[intro] = '" & Me.cboChooseIntro & "'"
Else
MsgBox "Select intro or deselect"
End If
End If

If Me.chkSelectDateAnn = True Then
If Not (IsNull(Me.txtDateAnnStart) Or IsNull(Me.txtDateAnnEnd)) Then
If Not (strSQL = "SELECT * FROM QryMembersReports") Then
strSQL = strSQL & " AND "
Else
strSQL = strSQL & " WHERE "
End If
strSQL = strSQL & "[ANNIVDATE] >= #" & Me.txtDateAnnStart & "# AND [ANNIVDATE] <= #" & Me.txtDateAnnEnd & "#"
Else
MsgBox "Select Anniversary date range or deselect"
End If
End If

If Me.chkSelectDateInact = True Then
If Not (IsNull(Me.txtDateInactStart) Or IsNull(Me.txtDateInactEnd)) Then
If Not (strSQL = "SELECT * FROM QryMembersReports") Then
strSQL = strSQL & " AND "
Else
strSQL = strSQL & " WHERE "
End If
strSQL = strSQL & "[INACTIVE_DATE] >= #" & Me.txtDateInactStart & "# AND [INACTIVE_DATE] <= #" & Me.txtDateInactEnd & "#"
Else
MsgBox "Select Inactive date range or deselect"
End If
End If

If Me.chkSelectDateEntry = True Then
If Not (IsNull(Me.txtDateEntryStart) Or IsNull(Me.txtDateEntryEnd)) Then
If Not (strSQL = "SELECT * FROM QryMembersReports") Then
strSQL = strSQL & " AND "
Else
strSQL = strSQL & " WHERE "
End If
strSQL = strSQL & "[ENTRY_DATE] >= #" & Me.txtDateEntryStart & "# AND [ENTRY_DATE] <= #" & Me.txtDateEntryEnd & "#"
Else
MsgBox "Select Entry date range or deselect"
End If
End If


'Last Contribution Date'
If Me.chkSelectDateLast = True Then
If Not (IsNull(Me.txtDateLastStart) Or IsNull(Me.txtDateLastEnd)) Then
If Not (strSQL = "SELECT * FROM QryMembersReports") Then
strSQL = strSQL & " AND "
Else
strSQL = strSQL & " WHERE "
End If
strSQL = strSQL & "[lastdate] >= #" & Me.txtDateLastStart & "# AND [lastdate] <= #" & Me.txtDateLastEnd & "#"
Else
MsgBox "Select Last contribution date range or deselect"
End If
End If

'Last Dues Date'
If Me.chkSelectDateLastDue = True Then
If Not (IsNull(Me.txtDateLastStartDue) Or IsNull(Me.txtDateLastEndDue)) Then
If Not (strSQL = "SELECT * FROM QryMembersReports") Then
strSQL = strSQL & " AND "
Else
strSQL = strSQL & " WHERE "
End If
strSQL = strSQL & "[Maxofduesdate] >= #" & Me.txtDateLastStartDue & "# AND [Maxofduesdate] <= #" & Me.txtDateLastEndDue & "#"
Else
MsgBox "Select Last Dues payed date range or deselect"
End If
End If

'Last Donation Date'
If Me.chkSelectDateLastDon = True Then
If Not (IsNull(Me.txtDateLastStartDon) Or IsNull(Me.txtDateLastEndDon)) Then
If Not (strSQL = "SELECT * FROM QryMembersReports") Then
strSQL = strSQL & " AND "
Else
strSQL = strSQL & " WHERE "
End If
strSQL = strSQL & "[maxofgiftdate] >= #" & Me.txtDateLastStartDon & "# AND [maxofgiftdate] <= #" & Me.txtDateLastEndDon & "#"
Else
MsgBox "Select Last Donations payed date range or deselect"
End If
End If

If Me.chkEChange = True Then
If Not (IsNull(Me.txtDateEchangeStart) Or IsNull(Me.txtDateEchangeEnd)) Then
If Not (strSQL = "SELECT * FROM QryMembersReports") Then
strSQL = strSQL & " AND "
Else
strSQL = strSQL & " WHERE "
End If
strSQL = strSQL & "[memEmailLastchange] >= #" & Me.txtDateEchangeStart & "# AND [memEmailLastchange] <= #" & Me.txtDateEchangeEnd & "#"
Else
MsgBox "Select Last Email change date range or deselect"
End If
End If
If Me.chkSelectDonations = True Then
If Not (IsNull(Me.txtDonationMin) Or IsNull(Me.txtDonationMax)) Then
If Not (strSQL = "SELECT * FROM QryMembersReports") Then
strSQL = strSQL & " AND "
Else
strSQL = strSQL & " WHERE "
End If
strSQL = strSQL & "[donationsAll] >= " & Me.txtDonationMin & " AND [donationsAll] <= " & Me.txtDonationMax
Else
MsgBox "Select donations range or deselect"
End If
End If

If Me.chkSelectDues = True Then
If Not (IsNull(Me.txtDueMin) Or IsNull(Me.txtDueMax)) Then
If Not (strSQL = "SELECT * FROM QryMembersReports") Then
strSQL = strSQL & " AND "
Else
strSQL = strSQL & " WHERE "
End If
strSQL = strSQL & "[duesAll] >= " & Me.txtDueMin & " AND [duesAll] <= " & Me.txtDueMax
Else
MsgBox "Select dues range or deselect"
End If
End If

If Me.chkSelectTotal = True Then
If Not (IsNull(Me.txtTotalMin) Or IsNull(Me.txtTotalMax)) Then
If Not (strSQL = "SELECT * FROM QryMembersReports") Then
strSQL = strSQL & " AND "
Else
strSQL = strSQL & " WHERE "
End If
strSQL = strSQL & "[total] >= " & Me.txtTotalMin & " AND [total] <= " & Me.txtTotalMax
Else
MsgBox "Select total range or deselect"
End If
End If

If Me.chkSelectLastAMT = True Then
If Not (IsNull(Me.txtLastAMTMin) Or IsNull(Me.txtLastAMTMax)) Then
If Not (strSQL = "SELECT * FROM QryMembersReports") Then
strSQL = strSQL & " AND "
Else
strSQL = strSQL & " WHERE "
End If
strSQL = strSQL & "[lastcontributionAMT] >= " & Me.txtLastAMTMin & " AND [lastcontributionAMT] <= " & Me.txtLastAMTMax
Else
MsgBox "Select last amount range or deselect"
End If
End If

If Me.chkSelectTotalPeriod = True Then
If Not (strSQL = "SELECT * FROM QryMembersReports") Then
strSQL = strSQL & " AND "
Else
strSQL = strSQL & " WHERE "
End If
strSQL = strSQL & "[totalperiod] > 0"
End If

If Me.chkSelectDateJoined = True Then
If Not (IsNull(Me.txtDateJoinedStart) Or IsNull(Me.txtDateJoinedEnd)) Then
If Not (strSQL = "SELECT * FROM QryMembersReports") Then
strSQL = strSQL & " AND "
Else
strSQL = strSQL & " WHERE "
End If
strSQL = strSQL & "[datejoin] >= #" & Me.txtDateJoinedStart & "# AND [datejoin] <= #" & Me.txtDateJoinedEnd & "#"
Else
MsgBox "Select joined date range or deselect"
End If
End If

'for trouble shooting only
'MsgBox strSQL

Me.frmMembersSelectList.Form.RecordSource = strSQL
Me.xSelectSQL = strSQL

Exit_cmdSelect_Click:
Exit Sub


Err_cmdSelect_Click:
MsgBox Err.Number & " " & Err.Description
Resume Exit_cmdSelect_Click
End Sub