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

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
>
>