Results 1 to 4 of 4

Thread: Dynamic Query

  1. #1
    Join Date
    Jun 2010
    Posts
    1

    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
    Location
    Google, KS
    Posts
    47
    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
    Location
    Chicago, IL
    Posts
    1,312
    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.

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

  4. #4
    Join Date
    Mar 2009
    Location
    Gatineau, Quebec Canada
    Posts
    147
    Provided Answers: 1

    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

    Regards

    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
  •