Solved

Create a recordset in VBA

Posted on 2013-01-22
15
437 Views
Last Modified: 2013-01-23
Hi,

I have a form with 4 check boxes on.  I am trying to create a recordset based on whatever checkbox/s is selected by the user. As an example, the check boxes could be labeled:

Red
Blue
Green
Yellow

If a user selects Blue, then the recordset is set to:

Set rs = CurrentDb.OpenRecordset("tbl_members", WHERE [colour] = BLUE, dbOpenDynaset)  - (this is just air code)

Or if the user selects Red & Green, then the Recordset is:

Set rs = CurrentDb.OpenRecordset("tbl_members", WHERE [colour] = GREEN & RED, dbOpenDynaset) - (this is just air code)

This data is then passed to an email script.

I hope this makes sense :)

Thanks in advance.
0
Comment
Question by:anthonytr
  • 7
  • 6
  • 2
15 Comments
 
LVL 15

Expert Comment

by:Ess Kay
ID: 38806315
just do an if else statement,
0
 

Author Comment

by:anthonytr
ID: 38806324
does that mean i have to do an "if else statement" for every possible combination of check box selections?
0
 
LVL 15

Expert Comment

by:Ess Kay
ID: 38806325
i would use dropbox/combobox  where you can select only one,
then do a case statement

dim rs as dataset
select combobox.value
   case "blue": rs = bla
   case "red": rs = blabla
   case else:
      exit sub
end select
0
 

Author Comment

by:anthonytr
ID: 38806332
Hi,
I could, but I want the user to be able to select more than one option
0
 
LVL 15

Expert Comment

by:Ess Kay
ID: 38806337
yes, if you would have a combination checkbox system, then you would need to do each combination individually

1 box = 2 statements
2 boxes = 4 statements
3 boxes = 8 statements
4 boxes = 16 statements


hope that helps
0
 
LVL 15

Expert Comment

by:Ess Kay
ID: 38806431
do something like this


if box3.checked = true then
   If box1.checked = true then
       if box2.checked = true then
            do stuff    ' both checked   (1,1,1)
       else
            do other stuff  ' (1,1,0)
   else
       if box2.checked = true then
            do more stuff '(1,0,1)
       else
            do more other stuff' (1,0,0)
   end if
else
   If box1.checked = true then
       if box2.checked = true then
            do stuff    ' both checked   (0,1,1)
       else
            do other stuff  ' (0,1,0)
   else
       if box2.checked = true then
            do more stuff '(0,0,1)
       else
            do more other stuff' (0,0,0)
   end if
end if
0
 
LVL 15

Accepted Solution

by:
Ess Kay earned 500 total points
ID: 38806481
sorry i must of misread the question, though you had different databases

in this case make 2 varaibles


Dim counter as colorcount = 0
Dim stringparam = ""

if box1.checked = true then
  stringparam = "red"
colorcount = 1
end if

if box2.checked = true & colorcount = 1 then
stringparam &= " & green"
else
stringparam = "green"
colorcount = 1
end if

if box3.checked = true & colorcount = 1 then
stringparam &= " & blue"
else
stringparam = "blue"
colorcount = 1
endif

if box4.checked = true & colorcount = 1 then
stringparam &= " & yellow"
else
stringparam = "yellow"
endif


Set rs = CurrentDb.OpenRecordset("tbl_members", WHERE [colour] = stringparam , dbOpenDynaset)
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 119

Expert Comment

by:Rey Obrero
ID: 38806689
name your check boxes Red, Blue, Yellow, Green

use this codes

Dim ctl As Control, strColors As String, Sql As String
For Each ctl In Me.Controls
     If ctl.ControlType = acCheckBox Then
          If ctl = True Then
         
               strColors = strColors & "," & Chr(39) & ctl.Name & Chr(39)
          End If
     End If

Next
If strColors & "" <> "" Then
     strColors = Mid(strColors, 2)
     Sql = "Select * from tbl_members WHERE [colour] In(" & strColors & ")"
     set rs=currentdb.openrecordset(Sql)
End If
0
 

Author Comment

by:anthonytr
ID: 38807912
Hi,

Thanks for the input everyone.  Thought I would clarify something.  The colours I used were just for example.  The actual check boxes are:
Soprano
Alto
Tenor
Bass

Each check box will need to search the database for two fields, for example, Soprano is broken down to Soprano1 and Soprano2.  I think the counter might be the way forward, but instead of colours it would be "soprano1" & "soprano2".

Does this make sense, or have I mudded the water even more ;-)
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 38807935
i don't see the point of posting dummy names than posting the real names and the real problems.. you are wasting the time of experts who are volunteers to help you out..

please don't do this again.


use this revised codes


Dim ctl As Control, strVoices As String, Sql As String
For Each ctl In Me.Controls
     If ctl.ControlType = acCheckBox Then
          If ctl = True Then
         
               strVoices = strVoices & "," & Chr(39) & ctl.Name & Chr(39)
          End If
     End If

Next
If strVoices & "" <> "" Then
     strVoices = Mid(strVoices, 2)
     Sql = "Select * from tbl_members WHERE [soprano1] In(" & strVoices & ") or [soprano2] In(" & strVoices & ")"
     set rs=currentdb.openrecordset(Sql)
End If
0
 

Author Comment

by:anthonytr
ID: 38807964
How patronising could that comment be!  I see things haven't changed on EE.
0
 

Author Comment

by:anthonytr
ID: 38808044
Reynaldo, I could say the same with posting code/answers which have nothing to do with the actual question.  You are on here of your own free will.  It is you wasting your own time.

Thank you esskayb2d for your input, you have pointed me in the right direction,  I do appreciate it.
0
 

Author Closing Comment

by:anthonytr
ID: 38808047
Thanks again for your input
0
 
LVL 15

Expert Comment

by:Ess Kay
ID: 38808430
There might be an easier way,

Dim color1 = "" 
If checkbox1=true then color1 & = " & blue"
If checkbox2=true then color1 & = " & red"

Then remove the first & from the color1variable
0
 

Author Comment

by:anthonytr
ID: 38810860
Hi esskayb2d,

This is what i went with in the end.  I changed the check boxes to a listbox, allowing the user to select the items instead.  Works well, but... (see below code)

Private Sub cmd_send_Click()
Dim ctl As Control, strVoices As String, Sql As String
Dim strMailList As String, rs As Recordset

Set cdomsg = CreateObject("CDO.message")

With cdomsg.Configuration.Fields
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 'NTLM method
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
.Item("http://schemas.microsoft.com/cdo/configuration/smptserverport") = 587
.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
.Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60
.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "username"
.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "password"
.Update
End With

If Me.lbVoices.ItemsSelected.Count = 0 Then
MsgBox ("Please select Voice Group(s) to send the email to.")
Exit Sub
  
Else

Sql = "SELECT * FROM tbl_members " & BuildFilter

Set rs = CurrentDb.OpenRecordset(Sql)

With rs
  .MoveFirst

    Do
      strMailList = strMailList & !email1 & ";"
        .MoveNext
    Loop Until .EOF

      strMailList = Left(strMailList, Len(strMailList) - 1)
        .Close
End With

With cdomsg
.bcc = strMailList
.From = "email address"
.subject = "Test Subject"
.TextBody = "Test Message"
.Send
End With
Set cdomsg = Nothing
End If
End Sub


Private Function BuildFilter() As Variant
    Dim varWhere As Variant
    Dim varVoice As Variant
    Dim varItem As Variant
    Dim intIndex As Integer

    varWhere = Null 
    varVoice = Null  
       
    ' Check for Voices in multiselect list
    For Each varItem In Me.lbVoices.ItemsSelected
        varVoice = varVoice & "[voice] = """ & _
                    Me.lbVoices.ItemData(varItem) & """ OR "
        
    Next
    
    ' Test to see if we have subfilter for Voices...
    If IsNull(varVoice) Then
        ' do nothing
      
    Else
        ' strip off last "OR" in the filter
        If Right(varVoice, 4) = " OR " Then
            varVoice = Left(varVoice, Len(varVoice) - 4)
        End If
        
        ' Add some parentheses around the subfilter
        varWhere = varWhere & "( " & varVoice & " )"
    End If
    
    ' Check if there is a filter to return...
    If IsNull(varWhere) Then
        varWhere = ""
    Else
        varWhere = "WHERE " & varWhere
        
        ' strip off last "AND" in the filter
        If Right(varWhere, 5) = " AND " Then
            varWhere = Left(varWhere, Len(varWhere) - 5)
        End If
    End If
    
    BuildFilter = varWhere
    
End Function

Open in new window


However I seem to have written in a loop and I don't know how.  When an email is generated with the code it seems to stay running (if I open VBA the session is still running), eventually i will get an error which stops the code on the [.Send] line.

Can you see anything obvious in my code which is causing this.  Not sure if its just my machine or its the code doing it.

Thanks.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now