ACCESS FEED: Re: [MS_AccessPros] Strange behavior when filtering on second form

Sabtu, 05 November 2011

Re: [MS_AccessPros] Strange behavior when filtering on second form

Thanks John!!

This must be exactly what I need!

Dennis

5 nov 2011 kl. 13:12 skrev John Viescas:

> Dennis-
>
> No, not there either. Do this:
>
> stDocname = "Kontaktpersoner"
> stLinkCriteria = "[Adressnr] = " & Me.Adressnr
> DoCmd.OpenForm stDocname, , , stLinkCriteria
> Forms!Kontaktpersoner!Addressnr.DefaultValue = Me.Addressnr
>
> That makes the Addressnr in the company form the default value for any new row
> created in Kontaktpersoner.
>
> John Viescas, author
> Microsoft Office Access 2010 Inside Out
> Microsoft Office Access 2007 Inside Out
> Building Microsoft Access Applications
> Microsoft Office Access 2003 Inside Out
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Paris, France)
>
> -----Original Message-----
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Dennis Davidsson
> Sent: Saturday, November 05, 2011 12:48 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: Re: [MS_AccessPros] Strange behavior when filtering on second form
>
> John,
>
> thanks for giving me new hope again!
> But the reason being why I do this update is the following:
> When I click on the button Kontaktpersoner on the form Adressregister it is true
> that I get the appropriate contact persons attached to the current company, as
> long as they already exist!
> But when I step to a company in the Adressregister form for which there has not
> yet been added any contact person (or for that matter when I want to add another
> one by clicking on the 'select new record' icon on the bottom line of the form
> Kontaktpersoner) I want the Adressnr to be filled in on the control on the form
> Kontaktpersoner automatically.
> But just now, writing this note to you, it strikes me that I should move this
> code that updates the Adressnr to the event 'Before Update' instead! Am I right?
>
> Dennis Davidsson
>
> 5 nov 2011 kl. 10:25 skrev John Viescas:
>
> > Dennis-
> >
> > That's it. When you apply the filter by form, it REMOVES the existing filter.
> > The Current event erroneously updates Adressnr with the value from the other
> > form! Why are you performing this update? You shouldn't need to do an update
> > to keep the forms in sync.
> >
> > John Viescas, author
> > Microsoft Office Access 2010 Inside Out
> > Microsoft Office Access 2007 Inside Out
> > Building Microsoft Access Applications
> > Microsoft Office Access 2003 Inside Out
> > SQL Queries for Mere Mortals
> > http://www.viescas.com/
> > (Paris, France)
> >
> > -----Original Message-----
> > From: MS_Access_Professionals@yahoogroups.com
> > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Dennis Davidsson
> > Sent: Saturday, November 05, 2011 9:52 AM
> > To: MS_Access_Professionals@yahoogroups.com
> > Subject: Re: [MS_AccessPros] Strange behavior when filtering on second form
> >
> > John,
> >
> > I am sorry I had to shut down my computer shortly after I got your answer
> below
> > yesterday.
> > You ask in your answer below "What´s the code behind the Kontaktpersoner
> form",
> > and the only thing I could think of that could affect the situation is the
> > following piece of code that I have on the Event Property 'On Current':
> > ….
> > If IsLoaded("Adressregister") Then
> > Me!Adressnr=Forms!Adressregister!Adressnr
> > End If
> > …
> >
> > My intention with this code is to keep the forms Adressregister synchronized
> > with the form Kontaktpersoner so that when I step to the next record on the
> form
> > Adressregister, the form Kontaktpersoner will follow automatically showing me
> > the relevant contact persons for the company now shown on the form
> > Adressregister.
> > This works fine as long as I only keep to stepping forwards and backwards
> among
> > the records in the Adressregister form. But as soon as I try to filter on the
> > form Kontaktpersoner by choosing "Filter by form" on the top line of the
> screen,
> > I run into the problem described in my note.
> >
> > Hoping you can help me out of this "locked situation".
> >
> > Dennis Davidsson
> >
> > 4 nov 2011 kl. 16:27 skrev John Viescas:
> >
> > > Dennis-
> > >
> > > If the Kontaktpersoner form is filtered to list only the contacts for the
> > > current company, I wouldn't think that applying a filter/sort would find a
> > > person NOT associated with that company. Perhaps the "advanced filter/sort"
> is
> > > removing your original filter to allow you to search all contacts. But then
> > > there must be something set in your form to cause the person found to have
> his
> > > or her associated company ID changed. What's the code behind the
> > > Kontaktpersoner form?
> > >
> > > John Viescas, author
> > > Microsoft Office Access 2010 Inside Out
> > > Microsoft Office Access 2007 Inside Out
> > > Building Microsoft Access Applications
> > > Microsoft Office Access 2003 Inside Out
> > > SQL Queries for Mere Mortals
> > > http://www.viescas.com/
> > > (Paris, France)
> > >
> > > -----Original Message-----
> > > From: MS_Access_Professionals@yahoogroups.com
> > > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Dennis
> Davidsson
> > > Sent: Friday, November 04, 2011 3:32 PM
> > > To: MS_Access_Professionals@yahoogroups.com
> > > Subject: [MS_AccessPros] Strange behavior when filtering on second form
> > >
> > > Hello,
> > >
> > > I have a form called Adressregister which I use to register and maintain
> misc
> > > address information in my customer table.
> > > On this form I also have a button, on which I click to open a second form
> > called
> > > Kontaktpersoner which I use to register and maintain misc information about
> > > contact persons on the current customer company.
> > > The relation between the customer table and the contact person table is
> > > one-to-many, i.e. each customer company can have one or more contact
> persons.
> > > When I click the button to open the contact person form the following piece
> of
> > > code is executed:
> > > ..
> > > stDocname = "Kontaktpersoner"
> > > stLinkCriteria = "[Adressnr] = " & Forms![Adressregister]![Adressnr]
> > > DoCmd.OpenForm stDocname, , , stLinkCriteria
> > > .
> > >
> > > Adressnr is the primary key of the table Adressregister and a secondary key
> in
> > > the table Kontaktpersoner.
> > > All this works fine. I also have code that makes it possible to click on
> next
> > > record in the Adressregister form and the form Kontaktpersoner is
> > automatically
> > > following so that I then get presented the contact persons for the new
> record
> > in
> > > Adressregister.
> > >
> > > Now however comes my problem: I know the name of a certain contact person
> and
> > I
> > > want to find in which company he is the contact person. So I choose on the
> top
> > > line of the Access workspace the option "Advanced to filter on form" having
> > made
> > > the form Kontaktpersoner the active one. I then fill in a name of the
> contact
> > > person I am looking for and click "Filter on". I get the wanted information
> on
> > > the contact person ok, but the strange thing is that this new person is now
> > > automatically added to the original company that was active in the form
> > > Adressregister!
> > > What shall I do to avoid this unwanted action from Access?
> > >
> > > Regards
> > > Dennis Davidsson
> > >
> > > [Non-text portions of this message have been removed]
> > >
> > > ------------------------------------
> > >
> > > Yahoo! Groups Links
> > >
> > >
> >
> > [Non-text portions of this message have been removed]
> >
> > ------------------------------------
> >
> > Yahoo! Groups Links
> >
> >
>
> [Non-text portions of this message have been removed]
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>