Reset rs for comboboxes VBA/SQL (1 Viewer)

B

businesshippieRH

Registered User.
Local time
Today, 17:02
Joined
Aug 8, 2014
Messages
60
I have a form with many comboboxes. The comboboxes need to have their original rowsource set to a table in my database. However, when the user selects a value in a combobox, it is programatically fed into a SQL string which serves to populate a subform. This part all works great.
However, I'm trying to reset the comboboxes as well so that options will "narrow down" until only a single option is possible. So far, I have:

Code: 
Private Sub cmb_Author_Change()
Dim strSQL As String
strSQL = "SELECT tbl_Records.RecordName, tbl_Records.RecordDistinction, tbl_Records.Title, tbl_Records.Author, " & _
"tbl_Records.ProjectManager, tbl_Records.[Site Name], tbl_Records.ChargeCode, tbl_Records.PrimeContractNumber, " & _
"tbl_Records.TaskOrder, tbl_Records.UploadDate, tbl_Records.RecMoYr, tbl_Records.Description, tbl_Records.OrigFileLoc, " & _
"tbl_Records.OrigFileName, tbl_Records.RevisionNotes " & _
"FROM tbl_Records "
Dim Finder As String
Finder = strSQL & GetWhere()
'Set subform equal to results of query
Forms.frm_RevFinder.subfrm_REVSelector.Form.RecordSource = Finder
Me.subfrm_REVSelector.Requery
[COLOR="Red"]'Set other comboboxes to Finder
Set rs = CurrentDb.OpenRecordset(Finder)
    Me.cmb_RecordName.RowSource = rs!RecordName[/COLOR]
End Sub

GetWhere() is the portion that feeds info from the comboboxes to the Finder string.
The red highlighted bit is the part I'm working on. Obviously, once I figure out the proper syntax, all of the other comboboxes will be set up the same way.
What's funny is that in testing (all of the data currently input is simple and allows me to select options where I know what the response will be in other boxes), when I change Author and go to use cmb_RecordName, it replies that "the record source 'My Data' specified on this form or report does not exist" (where 'My Data' represents real data).
So, clearly, it is finding the data, just not using it as a record source, but rather believing that the name of the recordsource is the value.

Thanks in advance for your help!
 
pbaldy

pbaldy

Wino Moderator
Staff member
Local time
Today, 15:02
Joined
Aug 30, 2003
Messages
36,126
The row source is expecting a table or query. Did you mean to set the value property?
 
B

businesshippieRH

Registered User.
Local time
Today, 17:02
Joined
Aug 8, 2014
Messages
60
No. The way I understand it, setting the Value Property will select the proper record in other comboboxes, but will not "narrow it down" to only those records which meet the other criteria.

For example: say I have A,B,C,D in RecordName. If Joe only wrote A and D, I want the RecordName to be cut down to A,D when Joe is the Author.

This would be great if there were only one result. However, one Author may be responsible for many RecordNames.

The same will be true of my other comboboxes. The end result I'm looking for is to filter such that the more comboboxes you fill, the closer to only having one option in other comboboxes you get. Hence, I would like to cull out any values from the comboboxes that don't apply based on previous selections.
 
B

businesshippieRH

Registered User.
Local time
Today, 17:02
Joined
Aug 8, 2014
Messages
60
That is almost what I'm looking for. Using your example: I would also want to be able to select a City and it cut down the State options.

The rowsource for my combos starts off as their respective column in a table where every (Distinct) option is available. I want to reset them to my sql "Finder" as the boxes are selected (in no particular order- the user may have different knowledge of what it is they're looking for each time).

Thanks again.
 
B

businesshippieRH

Registered User.
Local time
Today, 17:02
Joined
Aug 8, 2014
Messages
60
The idea I keep coming back to is that I wish I knew how to write a SQL statement to set the combobox's rowsource to another SQL statement... Something like:
Code: 
Set rs = CurrentDb.OpenRecordset(Finder)
Dim Name As String
Name = "SELECT RecordName FROM Finder"
    Me.cmb_RecordName.RowSource = Name
But, I can't think of how to actually write a SQL statement based off of another SQL statement that isn't a saved query...
 
EdFred

EdFred

knows enough, dangerous
Local time
Today, 18:02
Joined
Mar 2, 2007
Messages
129
I've done similar. I make the row source for Combo2 based on saved Query2 based with =Form![FormName].[Combo1] limiter in it and Query3 =Form![FormName].[Combo2] and on down the line.
 
B

businesshippieRH

Registered User.
Local time
Today, 17:02
Joined
Aug 8, 2014
Messages
60
Both as a "bump" and to better explain what exactly I'm trying to do, my full code is provided below:

Code: 
Option Compare Database

Private Function GetWhere() As String
Dim strTemp As String
'Set Null Arguments for each criteria (Added to SQL WHERE clause "on the fly")
If Not IsNull(Me!cmb_RecordName) Then
    strTemp = strTemp & " AND tbl_Records.RecordName = " & Chr(34) & Me!cmb_RecordName & Chr(34)
    End If
If Not IsNull(Me!cmb_RecordDistinction) Then
    strTemp = strTemp & " AND tbl_Records.RecordDistinction = " & Chr(34) & Me!cmb_RecordDistinction & Chr(34)
    End If
If Not IsNull(Me!cmb_Title) Then
    strTemp = strTemp & " AND tbl_Records.Title = " & Chr(34) & Me!cmb_Title & Chr(34)
    End If
If Not IsNull(Me!cmb_Author) Then
    strTemp = strTemp & " AND tbl_Records.Author = " & Chr(34) & Me!cmb_Author & Chr(34)
    End If
If Not IsNull(Me!cmb_ProjectManager) Then
    strTemp = strTemp & " AND tbl_Records.ProjectManager = " & Chr(34) & Me!cmb_ProjectManager & Chr(34)
    End If
If Not IsNull(Me!cmb_SiteName) Then
    strTemp = strTemp & " AND tbl_Records.[Site Name] = " & Chr(34) & Me!cmb_SiteName & Chr(34)
    End If
If Not IsNull(Me!cmb_ChargeCode) Then
    strTemp = strTemp & " AND tbl_Records.ChargeCode = " & Chr(34) & Me!cmb_ChargeCode & Chr(34)
    End If
If Not IsNull(Me!cmb_ContractNumber) Then
    strTemp = strTemp & " AND tbl_Records.PrimeContractNumber = " & Chr(34) & Me!cmb_ContractNumber & Chr(34)
    End If
If Not IsNull(Me!cmb_TaskOrder) Then
    strTemp = strTemp & " AND tbl_Records.TaskOrder = " & Chr(34) & Me!cmb_TaskOrder & Chr(34)
    End If
If Not IsNull(Me!txt_UPDateStart) Then
    If Not IsNull(Me!txt_UPDateEnd) Then
        strTemp = strTemp & " AND  tbl_Records.UploadDate BETWEEN  #" & Me!txt_UPDateStart & "# AND #" & Me!txt_UPDateEnd & "#"
    End If
    End If
If Not IsNull(Me!txt_RECDateStartMo) Then
    If Not IsNull(Me!txt_RECDateStartYr) Then
        If Not IsNull(Me!txt_RECDateEndMo) Then
            If Not IsNull(Me!txt_RECDateEndYr) Then
        'Create Date Strings
        Dim StartDate As Variant
            StartDate = CDate(Me!txt_RECDateStartMo & "/" & "01" & "/" & Me!txt_RECDateStartYr)
        Dim EndDate As Variant
            EndDate = CDate(Me!txt_RECDateEndMo & "/" & "01" & "/" & Me!txt_RECDateEndYr)
    'Write SQL add-in
    strTemp = strTemp & " AND tbl_Records.RecMoYr BETWEEN #" & StartDate & "# AND #" & EndDate & "#"
            End If
        End If
    End If
End If
If Not IsNull(Me!txt_KeyWord) Then
    'Set up strings necessary for search
    Dim intIdx As Integer
    Dim strWork As String
    Dim strSearch As String
        'Generate search criteria
        strSearch = txt_KeyWord
        Do
            intIdx = InStr(1, strSearch, ",")
            If intIdx > 0 Then
                strWork = Trim(Left(strSearch, intIdx - 1))
                strSearch = Mid(strSearch, intIdx + 1)
            Else
                strWork = Trim(strSearch)
                strSearch = ""
            End If
                'Add to SQL statement
                strTemp = strTemp & " AND tbl_Records.Description LIKE '*" & strWork & "*'"
        Loop While strSearch > ""
    End If
    'Set string to add
    strTemp = Mid(strTemp, 6)
    'Make sure not all null
    If Len(strTemp) > 0 Then
    GetWhere = "WHERE " & strTemp
    End If
    End Function


Private Sub cmb_Author_Change()
Dim strSQL As String
strSQL = "SELECT tbl_Records.RecordName, tbl_Records.RecordDistinction, tbl_Records.Title, tbl_Records.Author, " & _
"tbl_Records.ProjectManager, tbl_Records.[Site Name], tbl_Records.ChargeCode, tbl_Records.PrimeContractNumber, " & _
"tbl_Records.TaskOrder, tbl_Records.UploadDate, tbl_Records.RecMoYr, tbl_Records.Description, tbl_Records.OrigFileLoc, " & _
"tbl_Records.OrigFileName, " & _
"FROM tbl_Records "
Dim Finder As String
Finder = strSQL & GetWhere()
[COLOR="Red"]'Set subform equal to results of query
Forms.frm_RevFinder.subfrm_REVSelector.Form.RecordSource = Finder
Me.subfrm_REVSelector.Requery[/COLOR]
End Sub

Private Sub cmb_ChargeCode_Change()
Dim strSQL As String
strSQL = "SELECT tbl_Records.RecordName, tbl_Records.RecordDistinction, tbl_Records.Title, tbl_Records.Author, " & _
"tbl_Records.ProjectManager, tbl_Records.[Site Name], tbl_Records.ChargeCode, tbl_Records.PrimeContractNumber, " & _
"tbl_Records.TaskOrder, tbl_Records.UploadDate, tbl_Records.RecMoYr, tbl_Records.Description, tbl_Records.OrigFileLoc, " & _
"tbl_Records.OrigFileName " & _
"FROM tbl_Records "
Dim Finder As String
Finder = strSQL & GetWhere()
[COLOR="red"]'Set subform equal to results of query
Forms.frm_RevFinder.subfrm_REVSelector.Form.RecordSource = Finder
Me.subfrm_REVSelector.Requery[/COLOR]
End Sub

Private Sub cmb_ContractNumber_Change()
Dim strSQL As String
strSQL = "SELECT tbl_Records.RecordName, tbl_Records.RecordDistinction, tbl_Records.Title, tbl_Records.Author, " & _
"tbl_Records.ProjectManager, tbl_Records.[Site Name], tbl_Records.ChargeCode, tbl_Records.PrimeContractNumber, " & _
"tbl_Records.TaskOrder, tbl_Records.UploadDate, tbl_Records.RecMoYr, tbl_Records.Description, tbl_Records.OrigFileLoc, " & _
"tbl_Records.OrigFileName " & _
"FROM tbl_Records "
Dim Finder As String
Finder = strSQL & GetWhere()
[COLOR="red"]'Set subform equal to results of query
Forms.frm_RevFinder.subfrm_REVSelector.Form.RecordSource = Finder
Me.subfrm_REVSelector.Requery[/COLOR]
End Sub

Private Sub cmb_ProjectManager_Change()
Dim strSQL As String
strSQL = "SELECT tbl_Records.RecordName, tbl_Records.RecordDistinction, tbl_Records.Title, tbl_Records.Author, " & _
"tbl_Records.ProjectManager, tbl_Records.[Site Name], tbl_Records.ChargeCode, tbl_Records.PrimeContractNumber, " & _
"tbl_Records.TaskOrder, tbl_Records.UploadDate, tbl_Records.RecMoYr, tbl_Records.Description, tbl_Records.OrigFileLoc, " & _
"tbl_Records.OrigFileName " & _
"FROM tbl_Records "
Dim Finder As String
Finder = strSQL & GetWhere()
[COLOR="red"]'Set subform equal to results of query
Forms.frm_RevFinder.subfrm_REVSelector.Form.RecordSource = Finder
Me.subfrm_REVSelector.Requery[/COLOR]
End Sub

Private Sub cmb_RecordDistinction_Change()
Dim strSQL As String
strSQL = "SELECT tbl_Records.RecordName, tbl_Records.RecordDistinction, tbl_Records.Title, tbl_Records.Author, " & _
"tbl_Records.ProjectManager, tbl_Records.[Site Name], tbl_Records.ChargeCode, tbl_Records.PrimeContractNumber, " & _
"tbl_Records.TaskOrder, tbl_Records.UploadDate, tbl_Records.RecMoYr, tbl_Records.Description, tbl_Records.OrigFileLoc, " & _
"tbl_Records.OrigFileName " & _
"FROM tbl_Records "
Dim Finder As String
Finder = strSQL & GetWhere()
[COLOR="red"]'Set subform equal to results of query
Forms.frm_RevFinder.subfrm_REVSelector.Form.RecordSource = Finder
Me.subfrm_REVSelector.Requery[/COLOR]
End Sub

Private Sub cmb_RecordName_Change()
Dim strSQL As String
strSQL = "SELECT tbl_Records.RecordName, tbl_Records.RecordDistinction, tbl_Records.Title, tbl_Records.Author, " & _
"tbl_Records.ProjectManager, tbl_Records.[Site Name], tbl_Records.ChargeCode, tbl_Records.PrimeContractNumber, " & _
"tbl_Records.TaskOrder, tbl_Records.UploadDate, tbl_Records.RecMoYr, tbl_Records.Description, tbl_Records.OrigFileLoc, " & _
"tbl_Records.OrigFileName " & _
"FROM tbl_Records "
Dim Finder As String
Finder = strSQL & GetWhere()
[COLOR="red"]'Set subform equal to results of query
Forms.frm_RevFinder.subfrm_REVSelector.Form.RecordSource = Finder
Me.subfrm_REVSelector.Requery[/COLOR]
End Sub

Private Sub cmb_SiteName_Change()
Dim strSQL As String
strSQL = "SELECT tbl_Records.RecordName, tbl_Records.RecordDistinction, tbl_Records.Title, tbl_Records.Author, " & _
"tbl_Records.ProjectManager, tbl_Records.[Site Name], tbl_Records.ChargeCode, tbl_Records.PrimeContractNumber, " & _
"tbl_Records.TaskOrder, tbl_Records.UploadDate, tbl_Records.RecMoYr, tbl_Records.Description, tbl_Records.OrigFileLoc, " & _
"tbl_Records.OrigFileName " & _
"FROM tbl_Records "
Dim Finder As String
Finder = strSQL & GetWhere()
[COLOR="red"]'Set subform equal to results of query
Forms.frm_RevFinder.subfrm_REVSelector.Form.RecordSource = Finder
Me.subfrm_REVSelector.Requery[/COLOR]
End Sub

Private Sub cmb_TaskOrder_Change()
Dim strSQL As String
strSQL = "SELECT tbl_Records.RecordName, tbl_Records.RecordDistinction, tbl_Records.Title, tbl_Records.Author, " & _
"tbl_Records.ProjectManager, tbl_Records.[Site Name], tbl_Records.ChargeCode, tbl_Records.PrimeContractNumber, " & _
"tbl_Records.TaskOrder, tbl_Records.UploadDate, tbl_Records.RecMoYr, tbl_Records.Description, tbl_Records.OrigFileLoc, " & _
"tbl_Records.OrigFileName " & _
"FROM tbl_Records "
Dim Finder As String
Finder = strSQL & GetWhere()
[COLOR="red"]'Set subform equal to results of query
Forms.frm_RevFinder.subfrm_REVSelector.Form.RecordSource = Finder
Me.subfrm_REVSelector.Requery[/COLOR]
End Sub

Private Sub cmb_Title_Change()
Dim strSQL As String
strSQL = "SELECT tbl_Records.RecordName, tbl_Records.RecordDistinction, tbl_Records.Title, tbl_Records.Author, " & _
"tbl_Records.ProjectManager, tbl_Records.[Site Name], tbl_Records.ChargeCode, tbl_Records.PrimeContractNumber, " & _
"tbl_Records.TaskOrder, tbl_Records.UploadDate, tbl_Records.RecMoYr, tbl_Records.Description, tbl_Records.OrigFileLoc, " & _
"tbl_Records.OrigFileName " & _
"FROM tbl_Records "
Dim Finder As String
Finder = strSQL & GetWhere()
[COLOR="red"]'Set subform equal to results of query
Forms.frm_RevFinder.subfrm_REVSelector.Form.RecordSource = Finder
Me.subfrm_REVSelector.Requery[/COLOR]
End Sub


Private Sub txt_KeyWord_LostFocus()
Dim strSQL As String
strSQL = "SELECT tbl_Records.RecordName, tbl_Records.RecordDistinction, tbl_Records.Title, tbl_Records.Author, " & _
"tbl_Records.ProjectManager, tbl_Records.[Site Name], tbl_Records.ChargeCode, tbl_Records.PrimeContractNumber, " & _
"tbl_Records.TaskOrder, tbl_Records.UploadDate, tbl_Records.RecMoYr, tbl_Records.Description, tbl_Records.OrigFileLoc, " & _
"tbl_Records.OrigFileName " & _
"FROM tbl_Records "
Dim Finder As String
Finder = strSQL & GetWhere()
[COLOR="red"]'Set subform equal to results of query
Forms.frm_RevFinder.subfrm_REVSelector.Form.RecordSource = Finder
Me.subfrm_REVSelector.Requery[/COLOR]
End Sub

Private Sub txt_RECDateStartMo_LostFocus()
If Not IsNull(Me!txt_RECDateStartMo) Then
    If Not IsNull(Me!txt_RECDateStartYr) Then
        If Not IsNull(Me!txt_RECDateEndMo) Then
            If Not IsNull(Me!txt_RECDateEndYr) Then
Dim strSQL As String
strSQL = "SELECT tbl_Records.RecordName, tbl_Records.RecordDistinction, tbl_Records.Title, tbl_Records.Author, " & _
"tbl_Records.ProjectManager, tbl_Records.[Site Name], tbl_Records.ChargeCode, tbl_Records.PrimeContractNumber, " & _
"tbl_Records.TaskOrder, tbl_Records.UploadDate, tbl_Records.RecMoYr, tbl_Records.Description, tbl_Records.OrigFileLoc, " & _
"tbl_Records.OrigFileName " & _
"FROM tbl_Records "
Dim Finder As String
Finder = strSQL & GetWhere()
[COLOR="red"]'Set subform equal to results of query
Forms.frm_RevFinder.subfrm_REVSelector.Form.RecordSource = Finder
Me.subfrm_REVSelector.Requery[/COLOR]
            End If
        End If
    End If
End If
End Sub
Private Sub txt_RECDateStartYr_LostFocus()
If Not IsNull(Me!txt_RECDateStartMo) Then
    If Not IsNull(Me!txt_RECDateStartYr) Then
        If Not IsNull(Me!txt_RECDateEndMo) Then
            If Not IsNull(Me!txt_RECDateEndYr) Then
Dim strSQL As String
strSQL = "SELECT tbl_Records.RecordName, tbl_Records.RecordDistinction, tbl_Records.Title, tbl_Records.Author, " & _
"tbl_Records.ProjectManager, tbl_Records.[Site Name], tbl_Records.ChargeCode, tbl_Records.PrimeContractNumber, " & _
"tbl_Records.TaskOrder, tbl_Records.UploadDate, tbl_Records.RecMoYr, tbl_Records.Description, tbl_Records.OrigFileLoc, " & _
"tbl_Records.OrigFileName " & _
"FROM tbl_Records "
Dim Finder As String
Finder = strSQL & GetWhere()
[COLOR="red"]'Set subform equal to results of query
Forms.frm_RevFinder.subfrm_REVSelector.Form.RecordSource = Finder
Me.subfrm_REVSelector.Requery[/COLOR]
            End If
        End If
    End If
End If
End Sub
Private Sub txt_RECDateEndMo_LostFocus()
If Not IsNull(Me!txt_RECDateStartMo) Then
    If Not IsNull(Me!txt_RECDateStartYr) Then
        If Not IsNull(Me!txt_RECDateEndMo) Then
            If Not IsNull(Me!txt_RECDateEndYr) Then
Dim strSQL As String
strSQL = "SELECT tbl_Records.RecordName, tbl_Records.RecordDistinction, tbl_Records.Title, tbl_Records.Author, " & _
"tbl_Records.ProjectManager, tbl_Records.[Site Name], tbl_Records.ChargeCode, tbl_Records.PrimeContractNumber, " & _
"tbl_Records.TaskOrder, tbl_Records.UploadDate, tbl_Records.RecMoYr, tbl_Records.Description, tbl_Records.OrigFileLoc, " & _
"tbl_Records.OrigFileName " & _
"FROM tbl_Records "
Dim Finder As String
Finder = strSQL & GetWhere()
[COLOR="red"]'Set subform equal to results of query
Forms.frm_RevFinder.subfrm_REVSelector.Form.RecordSource = Finder
Me.subfrm_REVSelector.Requery[/COLOR]
            End If
        End If
    End If
End If
End Sub
Private Sub txt_RECDateEndYr_LostFocus()
If Not IsNull(Me!txt_RECDateStartMo) Then
    If Not IsNull(Me!txt_RECDateStartYr) Then
        If Not IsNull(Me!txt_RECDateEndMo) Then
            If Not IsNull(Me!txt_RECDateEndYr) Then
Dim strSQL As String
strSQL = "SELECT tbl_Records.RecordName, tbl_Records.RecordDistinction, tbl_Records.Title, tbl_Records.Author, " & _
"tbl_Records.ProjectManager, tbl_Records.[Site Name], tbl_Records.ChargeCode, tbl_Records.PrimeContractNumber, " & _
"tbl_Records.TaskOrder, tbl_Records.UploadDate, tbl_Records.RecMoYr, tbl_Records.Description, tbl_Records.OrigFileLoc, " & _
"tbl_Records.OrigFileName " & _
"FROM tbl_Records "
Dim Finder As String
Finder = strSQL & GetWhere()
[COLOR="red"]'Set subform equal to results of query
Forms.frm_RevFinder.subfrm_REVSelector.Form.RecordSource = Finder
Me.subfrm_REVSelector.Requery[/COLOR]
            End If
        End If
    End If
End If
End Sub

Private Sub txt_UPDateStart_LostFocus()
If Not IsNull(Me!txt_UPDateStart) Then
    If Not IsNull(Me!txt_UPDateEnd) Then
Dim strSQL As String
strSQL = "SELECT tbl_Records.RecordName, tbl_Records.RecordDistinction, tbl_Records.Title, tbl_Records.Author, " & _
"tbl_Records.ProjectManager, tbl_Records.[Site Name], tbl_Records.ChargeCode, tbl_Records.PrimeContractNumber, " & _
"tbl_Records.TaskOrder, tbl_Records.UploadDate, tbl_Records.RecMoYr, tbl_Records.Description, tbl_Records.OrigFileLoc, " & _
"tbl_Records.OrigFileName " & _
"FROM tbl_Records "
Dim Finder As String
Finder = strSQL & GetWhere()
[COLOR="red"]'Set subform equal to results of query
Forms.frm_RevFinder.subfrm_REVSelector.Form.RecordSource = Finder
Me.subfrm_REVSelector.Requery[/COLOR]
    End If
End If
End Sub
Private Sub txt_UPDateEnd_LostFocus()
If Not IsNull(Me!txt_UPDateStart) Then
    If Not IsNull(Me!txt_UPDateEnd) Then
Dim strSQL As String
strSQL = "SELECT tbl_Records.RecordName, tbl_Records.RecordDistinction, tbl_Records.Title, tbl_Records.Author, " & _
"tbl_Records.ProjectManager, tbl_Records.[Site Name], tbl_Records.ChargeCode, tbl_Records.PrimeContractNumber, " & _
"tbl_Records.TaskOrder, tbl_Records.UploadDate, tbl_Records.RecMoYr, tbl_Records.Description, tbl_Records.OrigFileLoc, " & _
"tbl_Records.OrigFileName " & _
"FROM tbl_Records "
Dim Finder As String
Finder = strSQL & GetWhere()
[COLOR="red"]'Set subform equal to results of query
Forms.frm_RevFinder.subfrm_REVSelector.Form.RecordSource = Finder
Me.subfrm_REVSelector.Requery[/COLOR]
    End If
End If
End Sub

The red colored text shows everywhere I would like to be able to inset a "Set all comboxes (Name, Distinction, Title, Author...) to their respective results in my finder query.
The users will likely never have this information in the right order to set it up as a "cascade". Rather, they'll have information to fill in say box 1, 5, and 9. Hence, I have to find some dynamic way to do this.
Honestly, the query works just fine as is... I just really wish I could sort it out so that they don't have to look through a ton of options that are not relevant to the information they do know...

Thanks again in advance for being able to help, and thank you to those who have already responded. Just because it's not the answer I'm looking for, doesn't mean it won't help somebody!!!:)
 
B

businesshippieRH

Registered User.
Local time
Today, 17:02
Joined
Aug 8, 2014
Messages
60
After some time away from working on this project, I've thought of a solution, but still can't figure out how to implement it. I haven't changed any of the code above...

Is there a way to turn my "Finder" statement into a table through an Insert Into statement? I tried:

Code: 
    DoCmd.RunSQL "INSERT INTO tbl_REVFinderResults (RecordName, RecordDistinction, Title, Author, ProjectManager, " & _
    "[Site Name], ChargeCode, PrimeContractNumber, TaskOrder, UploadDate, RecMoYr) " & _
    "VALUES (" & Finder & ".RecordName, " & Finder & ".RecordDistinction, " & Finder & ".Title, " & _
    "" & Finder & ".Author, " & Finder & ".ProjectManager, " & Finder & ".[Site Name], " & _
    "" & Finder & ".ChargeCode, " & Finder & ".PrimeContractNumber, " & Finder & ".TaskOrder', " & _
    "" & Finder & ".UploadDate, " & Finder & ".RecMoYr)"

Obviously, this doesn't work. But... once again: I appeal to those with more experience to point me on the right path...

Thanks in advance!
 
B

businesshippieRH

Registered User.
Local time
Today, 17:02
Joined
Aug 8, 2014
Messages
60
I finally solved it. For those interested: I simply output the Finder to a table and base the rowsources on the created table at each control_Change or whatever was called for for that particular control. Code is as follows:

Code: 
'Procedure for Setting Up "Finder" as Table
    'Set up Finder for RS Use
    Dim dbsRecords As Database
    Dim qdfTemp As QueryDef
    Dim qdfNew As QueryDef
 
    Set dbsRecords = CurrentDb
 
    With dbsRecords
    'Delete Old FinderResults
    .QueryDefs.Delete "FinderResults"
    ' Create temporary QueryDef.
    Set qdfTemp = .CreateQueryDef("", Finder)
    ' Open Recordset and print report.
    GetrstTemp qdfTemp
    ' Create permanent QueryDef.
    Set qdfNew = .CreateQueryDef("FinderResults", _
         Finder)
    ' Open Recordset and print report.
    GetrstTemp qdfNew
      .Close
    End With
'Set RowSources
Me!cmb_RecordName.RowSource = "SELECT RecordName FROM FinderResults"
Me!cmb_RecordDistinction.RowSource = "SELECT Distinct(RecordDistinction) FROM FinderResults"
Me!cmb_Title.RowSource = "SELECT Distinct(Title) FROM FinderResults"
Me!cmb_Author.RowSource = "SELECT Distinct(Author) FROM FinderResults"
Me!cmb_ProjectManager.RowSource = "SELECT Distinct(ProjectManager) FROM FinderResults"
Me!cmb_SiteName.RowSource = "SELECT Distinct([Site Name])FROM FinderResults"
Me!cmb_ChargeCode.RowSource = "SELECT Distinct(ChargeCode) FROM FinderResults"
Me!cmb_ContractNumber.RowSource = "SELECT Distinct(PrimeContractNumber) FROM FinderResults"
Me!cmb_TaskOrder.RowSource = "SELECT Distinct(TaskOrder) FROM FinderResults"
 
You must log in or register to reply here.

Similar threads

Z
Assistance - Issue with Expression Function in SQL Query and Invoking Function in VBA Editor
Replies
4
Views
445
June7
June7
W
Hiding Combobox or txt box depending on if data is null
Replies
12
Views
810
Pat Hartman
P
B
Help with CreateQueryDef
Replies
3
Views
2,043
pbaldy
pbaldy
B
Help with SQL pass-through to subform
Replies
11
Views
2,261
pbaldy
pbaldy
C
Add records to a table referencing a table in another database - DAO
Replies
10
Views
1,802
crhh1989
C

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom