Results 1 to 4 of 4

Thread: Dynamic Query

  1. #1
    Join Date
    Jun 2010

    Unanswered: Dynamic Query

    I am trying to access a field in a database labled uID1, uID2, uID3,... etc but the problem is the actual number changes every time. So depending on the circumstance I might need uID1 or uID5 or any other uID. I have a query that returns the number that I need, the 1 or 5 respectively given the above example, but I'm not sure how to take that number and get the field labled uID. Any help would be greatly appreciated.

  2. #2
    Join Date
    Nov 2009
    Google, KS
    I'm also having this issue, I posted the question earlier today
    Life - sexually transmitted, always fatal.

    My beer drunken soul is sadder than all the dead christmas trees in the world.

  3. #3
    Join Date
    Feb 2004
    Chicago, IL
    If you can get the result of the query into a recordset you can then reference it and build a dynamic SQL statement with it.

    strSQL ="SELECT * FROM SomeWhere WHERE uID" & rst!Result & "=SomethingElse"

  4. #4
    Join Date
    Mar 2009
    Gatineau, Quebec Canada

    Tried Setting the Control Source?

    You can dynamically select your control sources and your record sources at startup. So, for example,

    Private Sub Form_Load()
    Dim myData As Integer
    myData = [Forms]![frmChoice].cboSpecific.ItemData([Forms]![frmChoice].cboSpecific.ListIndex)

    Me!mark1.ControlSource = "m" & myData
    ' this gives a field such as "m1" which is a distinct field in the database (marks, first week)
    Me!absence1.ControlSource = "a" & myData
    Me!lblWeek.Caption = "Week " & myData

    Select Case [Forms]![frmChoice]![FrmOrder].Value
    Case 1
    Me.RecordSource = "qryStudentsEng"

    Case 2
    Me.RecordSource = "qryStudentsCh"
    Case 3
    Me.RecordSource = "qryStudentsNo"
    Case 4
    Me.RecordSource = "qryByGroup"

    End Select

    End Sub


    John S
    Currently Guangxi U
    Nanning, China

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts