Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2002
    Posts
    7

    Unanswered: Fiddly Recordsets

    I have various forms which need to display records from a one-to-many-to-many-to-one relationship (Companies and Members - with a Link table in between)

    I am moving away from having query (all records) recordset behind the forms, and now have separate 'Find' forms which set the recordset (in code) of the forms at run-time - the forms themselves have no recordset until it is set.

    I have a couple of questions - I am looking for the most efficient way of doing things as it is all running over a network, and has been split. I am considering upsizing to SQL back end, too;

    1: Is it best to set the recordset using a saved query (in which case how do i pass the parameter required to set the record I wish to find) or a Querydef as opposed to using a SQLstring ?

    2: Should the recordset be just one side of the query I.E. a certain Company, in which case how is it best to display the related members of the Company -Sub form or list/combo box? As is aid, the recordset is currently set using a SQLstring which incorporates all of the joins etc. so will come up with records for all members of a company

    3: If the SQLString with both Cos and members is OK , how is it best, withing the form, to navigate between the records - EG for chosing an adressee for letters/e-mails etc.

    TIA for any help - I understand the need to reduce network traffic thanks to some of the helpful people on here - just trying to establish best practice on how to do it!

    Biscuit

  2. #2
    Join Date
    Jan 2003
    Location
    Vietnam
    Posts
    188

    Re: Fiddly Recordsets

    Originally posted by biscuit23

    1: Is it best to set the recordset using a saved query (in which case how do i pass the parameter required to set the record I wish to find) or a Querydef as opposed to using a SQLstring ?

    2: Should the recordset be just one side of the query I.E. a certain Company, in which case how is it best to display the related members of the Company -Sub form or list/combo box? As is aid, the recordset is currently set using a SQLstring which incorporates all of the joins etc. so will come up with records for all members of a company

    3: If the SQLString with both Cos and members is OK , how is it best, withing the form, to navigate between the records - EG for chosing an adressee for letters/e-mails etc.

    Biscuit
    1: i will not use saved parameterized queries coz they are awful. i would rather setting the form recordsource property in run time (by vb code) it is:

    FormName.Recordsource = "strSQL"

    where strSQL is the SQL string i.e. "SELECT * FROM table WHERE field1=" & parameter

    2: i will also set recordsource property for the subform by a SQL string and forget all about table joins/ links etc.

    3: anybody can help? i dont really understand what he want

    hope this helps
    qha_vn

  3. #3
    Join Date
    Dec 2002
    Location
    Glasgow, UK
    Posts
    100
    I remember a post a while back about using stored queries as recordsets and it set off a fierce debate so we might as well start it again!!

    I use parameter queries all the time and I find them faster than vb recordsets (and simpler to debug) I'm sure someone said that they are like using stored procedures in SQL server - to run the query from code you just need to pass the parameters to it but it's a simple bit of code.

  4. #4
    Join Date
    Dec 2002
    Posts
    7
    Thanks for the replies.

    Sorry I missed the original discussion on the pros and cons of saved parameter queries - I'll try and find it so we don't need to go over it all again!

    qua-vn - thanks again - you helped me out a couple of weeks ago, too.
    My 3rd question is;

    I have a form where I need to show fields from both sides of the 1-many-many -1 relationship and I have set the recordset (using SQL in code ) using innerjoins to show all Members of one Company. Is this the best way , or should the form recordset just be set to one Company, with a subform (again with the recordset set using SQL code) showing the members.

    If the first way is OK (so the recordset contains, for example, three Members for the same company) how is it best to move between the different records? I think, from what you have already said, I should have a Combo box with it's rowsource again set manually using SQL. The correct member record wiould be found on update of the combo box.

    One last question on this - again thanks for you help and patience - Do you set the recodset in the code of the 'Find' form, I.E.

    DoCmd.Open Form "MyMainForm"
    Forms!MyOpenForm.Recordset = SQLRecordset

    OR would it be better to set it on the form being opened, using the parameter passed as an OpenArg, I.E.

    Sub MyMainForm_Open

    Me.Recordsource = "SELECT fields WHERE parameter = "_ & Me.OpenArgs

    End Sub

    Don't know if it makes much difference, but might be helpful if the same form is to be opened from different places - EG a menu form AND a MenuBar

    Thanks again

    Biscuit

Posting Permissions

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