changing sql string based on active userform

Question

I have a SQL query that I'm calling from a couple of different forms using the ctrl as object method. It works fine, but when I run it from a click event it will also open whichever form isn't currently loaded. The query returns the results I want, it just does it to both forms at the same time regardless of which is loaded.

Only one form is loaded at a time. A drop down list called Team exists on both forms. The query passes the currently selected item from that drop down list to return a list of agents assigned to that team.

I know that part of the issue is my query using an or statement that refers to values on both forms, but I'm not sure how to change it to reference the active form.

Attendance and reporting are the names of the two UserForms currently calling this query. Both of them have combobox controls named Team. I've tried activeform, etc. But I can't seem to find a way to make it work.

Sub agents(ctrl As Object)
    database_connect
    Dim SQLStr As String
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    Dim Counter As Long
    SQLStr="select distinct[Agentname] from dbo.[Attendance] Where [Team]='" & _
        attendance.Team.Value & "' or [Team] ='" & Reporting.Team.Value & "'"
    If appconn.State = 0 Then
        Call database_connect
    End If
    rs.Open SQLStr, appconn, adOpenStatic
    With ctrl
        Do
            .AddItem rs![Agentname]
            rs.MoveNext
        Loop Until rs.EOF
    End With
    rs.Close
    database_Disconnect
    Set rs = Nothing
    Exit Sub
End Sub

So your question is not about SQL but on how to read data from a userform? See for example here: https://stackoverflow.com/a/5621996/7599798 — FunThomas, Jul 26 '18 at 14:52
Link: [**Use form data as query criteria**](https://support.office.com/en-us/article/use-parameters-in-queries-forms-and-reports-8209eb5c-1589-42e2-9b20-4181f4c7a356#bmform_param_5) — ashleedawg, Jul 26 '18 at 14:52
And, as an advice: Don't change your SQL-statement but use `ADODB.Parameter`, see https://stackoverflow.com/a/10353908/7599798 — FunThomas, Jul 26 '18 at 14:53
Are you really doing this in [**Microsoft Word**](https://stackoverflow.com/questions/tagged/ms-word)? Could you provide a little more background and explanation of what you're trying to do? I have a feeling there's a key piece of information missing. — ashleedawg, Jul 26 '18 at 14:54
Yes, I'm making this program in VBA in Word. This query pulls a list of agents assigned to a team from a sql table. The value I'm passing in is the selected team from a dropdown list containing all the teams. Although only one form is loaded at a time, two different forms contain a list of teams, and on either form you can select a team and get a list of agents. — Jb83, Jul 26 '18 at 14:57
I need to find a way to replace where [Team]='" & _ attendance.Team.Value & "' or [Team] ='" & Reporting.Team.Value & "'" with one thing that just references the currently selected team from the team control on whichever form is currently loaded. — Jb83, Jul 26 '18 at 15:07

score 0 · Answer 1 · answered Jul 26 '18 at 14:47

0

1) Well first off to refer to controls you have to have the form loaded.

2) Youre referring to controls in the oddest way. Review AND save this URL http://access.mvps.org/access/forms/frm0031.htm

3) If one form is closed your query might always return nothing. Is this a desired output?

answered Jul 26 '18 at 14:47

Doug Coats

6,255
9
27
49

The query returns exactly what I'm wanting it to. The issue is that if the attendance form is currently loaded, running the query will also load the reporting form and then output the results to the control on both forms. I need it to only load the results onto the currently loaded form. – Jb83 Jul 26 '18 at 14:49
@jb83 it appears you're not providing an entire picture of whats going on in the code. What value is being passed to the sub? Can we see the module that calls this? I am beginning to think the issue isnt 100% contained in what youve provided – Doug Coats Jul 26 '18 at 14:52
The values being passed are either attendance.Team.Value or Reporting.Team.Value. Both the attendance form and the reporting form have a dropdown list called Team. So the value is whichever team is selected from that list. If I knew how to refer to the active form then the value would be something like activeform.team.value. That's the goal. – Jb83 Jul 26 '18 at 15:23
@ Doug Coats That page seems to be for people using Access, I'm using Word. Also, I don't see how to refer to the currently active form on there. – Jb83 Jul 26 '18 at 15:29
https://stackoverflow.com/questions/7148185/word-vba-macro-dynamically-reference-form-elements Looks like you need to figure out how to dynamically refer to forms using string variables – Doug Coats Jul 26 '18 at 15:33
@ doug coats I'm sorry if I'm missing something terribly obvious, but the issue I'm having is that I don't know how to refer to the currently active form. I know how to refer dynamically to the combobox. But I need to know how to be able to say "If attendance is active then paramater = attendance.team.value" I need to be able to point it to the currently active form since both forms contain a control called Team but only one is active at a time. – Jb83 Jul 26 '18 at 15:50

score 0 · Answer 2 · answered Jul 26 '18 at 16:22

For anyone who may find this looking for something similar, this is the solution I used.

I used a Function to test if a userform is loaded or not.

Public Function IsLoaded(formName As String) As Boolean
Dim frm As Object
For Each frm In VBA.UserForms
If frm.Name = formName Then
    IsLoaded = True
    Exit Function
End If
Next frm
IsLoaded = False
End Function

Then adjusted my above code like so

database_connect
Dim SQLStr As String
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim Counter As Long
If IsLoaded("Attendance") Then
SQLStr = "select distinct[Agentname] from dbo.[Attendance] Where [Team] ='" & attendance.Team.value & "'"
ElseIf IsLoaded("Reporting") Then
SQLStr = "select distinct[Agentname] from dbo.[Attendance] Where [Team] ='" & Reporting.Team.value & "'"
End If
If appconn.State = 0 Then
Call database_connect
End If
rs.Open SQLStr, appconn, adOpenStatic
With ctrl
Do
.AddItem rs![Agentname]
rs.MoveNext
Loop Until rs.EOF
End With
rs.Close
database_Disconnect
Set rs = Nothing
Exit Sub

now it works like a charm!

changing sql string based on active userform

2 Answers2