Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Lexington Park, Maryland, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    recordset used in SQL (Access 2000)

    Is is possible to user a recordset in place of the table or query name in a string SQL statement. For example:

    Dim strSQL As String
    Dim RcrdSet As DAO.Recordset

    RcrdSet = Me.Configuration_Subform.Form.RecordSource

    strSQL = "SELECT Aircraft_Name, Aircraft_Version FROM " & RcrdSet
    Me.Configuration_Subform.Form.lbAircraftFound.Cont rolSource = strSQL


    Thanks
    Mike



    Or something to this effect.

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: recordset used in SQL (Access 2000)

    What is lbAircraftFound ?
    Is this a label ?
    Then you can't use an sql statement.
    You can use a DLookUp function to return the desired value.
    Francois

  3. #3
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: recordset used in SQL (Access 2000)

    Are you just trying to filter your subform?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  4. #4
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Lexington Park, Maryland, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: recordset used in SQL (Access 2000)

    It is a list box (lb).

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: recordset used in SQL (Access 2000)

    If the recordsource of Configuration_Subform is a table or a query your code should work.
    Just add :
    Me.Configuration_Subform.Form.lbAircraftFound.Requ ery
    to see the result.
    Francois

  6. #6
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Lexington Park, Maryland, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: recordset used in SQL (Access 2000)

    I have a form with many multi-pages. Each page has its own subform with its own recordsource. These recordsources are generated with code. I have one subform that does not have a recordsource at all. This lonely subform has a list box in it. I am trying to use the the recordsource for another suform to generate a list of aircraft that are in the seperate subform to be used in the list box. How would you go about doing this?

    I was trying to use SQL to generate the Control Source for the list box. This requires the use of the seperate subform's Record Set in place of the table name in the SQL.

    Mike

  7. #7
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Lexington Park, Maryland, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: recordset used in SQL (Access 2000)

    That is the problem. The recordsource of Configuration_Subform is not a table or a query. It is an extremely complicated SQL statement.

    Mike

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: recordset used in SQL (Access 2000)

    Set the controlsource of the listbox to the same as the subform, but limit the fields to the two you need.
    If the recordsource of the form is build in code, build the same sql string with only the two fields and set the controlsource of listbox to that new string.
    Francois

  9. #9
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Lexington Park, Maryland, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: recordset used in SQL (Access 2000)

    I got the SQL string worked out. Now my problem is with the following. It does not work. "lbAircraftFound" does not come up in the list after "Form."

    Me.Configuration_Subform.Form.lbAircraftFound.Cont rolSource =

    Mike

  10. #10
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: recordset used in SQL (Access 2000)

    Where do you build the sql string ?
    In the main form or in another subform ?
    If in another subform you have to use :
    Me.Parent.Configuration_Subform.Form.lbAircraftFound.Control Source =
    Francois

  11. #11
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Lexington Park, Maryland, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: recordset used in SQL (Access 2000)

    The SQL is built on the onload event of the main form. I tried "Me.Parent.Configuration_Subform.Form.lbAircraftFo und.ControlSource =" and got the following error:

    Run-time error '2452'

    The expression you entered has an invalid reference to the parent property.

    Mike

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: recordset used in SQL (Access 2000)

    If the code is in the On Load event of the main form, you cannot use Parent. Francois indicated that you should use this if you ran it from a subform.

    You wrote that lbAircraftFound wasn't displayed by IntelliSense. This is to be expected, IntelliSense is limited. It will only list standard properties of the subform, not specific controls.

  13. #13
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: recordset used in SQL (Access 2000)

    If the sql is build in the main form with the listbox then you don't have to use Parent.
    Me.Configuration_Subform.Form.lbAircraftFound.Cont rolSource =...
    Francois

  14. #14
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Lexington Park, Maryland, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: recordset used in SQL (Access 2000)

    I have tried that and I get the following error.

    Run-time error '2465'

    Application-defined or object-defined error


    Here is the exact code I am using. I get the same error for both versions of the code.

    it uses this format under one condition:
    Me.Configuration_Subform.Form.lbAircraftFound.Cont rolSource = "SELECT Aircraft_Name FROM Saved_Data_1 WHERE Data_Set_Number = " & intHigh

    snd this format under another condition:
    Me.Configuration_Subform.Form.lbAircraftFound.Cont rolSource = "Search_Data_1"


    Mike

  15. #15
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: recordset used in SQL (Access 2000)

    Sorry I haven't seen it earlier, but you have to set the RowSource of the listbox and not the ControlSource.
    Did it work with :
    Me.Configuration_Subform.Form.lbAircraftFound.RowS ource = "SELECT Aircraft_Name FROM Saved_Data_1 WHERE Data_Set_Number = " & intHigh
    Francois

Page 1 of 2 12 LastLast

Posting Permissions

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