Re: Access/SQLServer Requery Recordsource
From: Stuart (Stuart_at_discussions.microsoft.com)
Date: 01/27/05
- Next message: Christopher Glaeser: "using subform to navigate form"
- Previous message: Roy Laws: "Re RTF Control from Lebans"
- In reply to: david epsom dot com dot au: "Re: Access/SQLServer Requery Recordsource"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 27 Jan 2005 09:27:05 -0800
This is what I want to accomplish, I just didn't know how to go about it
without tearing into these forms and doing a lot of VB. I knew there had to
be an easier way without rewriting these forms.
I won't get a chance to try it until later, but it looks good.
Thanks David !
"david epsom dot com dot au" wrote:
> If the recordset is empty, the detail section of the form will not
> be initialised. So if there is a chance that the recordset will be
> empty, make sure you put the record selection controls in the header
> of footer of the form.
>
> You want the form to open with one record, so if you are going to
> alter the recordsource SQL, you need to do it in the form Open
> event or before the form Open event. That means you must select
> or determine the initial record before the CBO is visible.
>
> You might save the last record visited (in a table, or in the
> registry or in the saved querydef) If you do this, you need to
> be careful if there is any change that the recordset might be
> empty (see above).
>
> Assuming you decide to re-open at the last saved record, you
> might decide save the SQL when you do the selection from
> the CBO.
>
> If you have the SQL saved as the recordsource of the form, that
> means you have to put the form (and the database) into design mode
> to save the SQL. Which means that instead you want to use
> a saved querydef as the source for the form.
>
> So the coding will take place in the update event of the CBO, where
> you might have something like (air code)
>
> sSQL = codedb.querydefs(me.recordsource).sql
> if instr(ssql,"where") then
> sSQL = left(ssql,instr(ssql,"where"))
> endif
> sSQL = sSQL & "where idx = " & me.cbo
> codedb.querydefs(me.recordsource).sql = ssql
> me.recordsource = me.recordsource
> me.requery
>
> Is this similar to what you have tried?
> (david)
>
> "Stuart" <Stuart@discussions.microsoft.com> wrote in message
> news:5C59069E-E80C-4AC0-BA62-9C52D3329993@microsoft.com...
> > Hi David,
> >
> > I guess it would be Changing the recordsource. All I want to do is run the
> > query that originally brought up the form(s), and attach a "WHERE CustId =
> > nnn", when the user selects a customer id. After he is through with that
> > loaded CustID, he can pick another and then I'd requery again with the new
> > selected CustID.
> >
> > BTW, SQLPass didn't work because I'm drawing from 2 different SQL
> databases.
> >
> > thanks
> >
> > "david epsom dot com dot au" wrote:
> >
> > > Which methods have you tried? Which method would you like to use?
> > >
> > > Changing the recordsource/Applying a filter/Using the Open cmd/
> > > Using code in the open event?
> > >
> > > (david)
> > >
> > >
> > >
> > > "Stuart" <Stuart@discussions.microsoft.com> wrote in message
> > > news:8B404137-42B2-4585-8C72-DB95C4A3B0C3@microsoft.com...
> > > > Hi all,
> > > > I'm working with a new client that upsized from A2K to SQL-S. Access
> is
> > > > using SQL-S as linked tables. Many of his forms retrieve all the
> records
> > > from
> > > > tables, via queries. Big time delay now that SQL-S is the backend.
> > > >
> > > > All he ever works with is one record at a time. A combox exists with
> all
> > > the
> > > > itemno's for those records for the forms.
> > > >
> > > > My thinking, is all I have to change for these forms, is to limit the
> > > > initial form load to one record, and requery when a different itemno
> is
> > > > picked from the combobox.
> > > >
> > > > I've been trying for 2 hours to make this happen and I just can't
> figure
> > > it
> > > > out.
> > > >
> > > > I know it's gotta be simple so someone please hit me upside the head
> with
> > > it
> > > > !!
> > > >
> > > > thanks
> > > >
> > >
> > >
> > >
>
>
>
- Next message: Christopher Glaeser: "using subform to navigate form"
- Previous message: Roy Laws: "Re RTF Control from Lebans"
- In reply to: david epsom dot com dot au: "Re: Access/SQLServer Requery Recordsource"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
