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

John,

your suggested amendment to my piece of code works fine.
Now I would like to augment the code as indicated here:

stDocName = "Kontaktpersoner"
stLinkCriteria = "[Adressnr] =" & Forms![Adressregister]![Adressnr]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms!Kontaktpersoner!Adressnr.DefaultValue = Me!Adressnr

stDocName = "BranschKP3"
stLinkCriteria = "[Kontaktpersonnr] =" & Forms![Kontaktpersoner]![Kontaktpersonnr]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms!BranschKP3!Adressnr.DefaultValue = Me!Adressnr
Forms!BranschKP3!Kontaktpersonnr.DefaultValue = Forms!Kontaktpersoner!Kontaktpersonnr

This
This is because I want to open this other form BranschKP3 at the same button click. This form is built on a table with relation 1:N from Kontaktpersoner meaning each contact person can have one or more "branches". I want both Adressnr and Kontaktpersonnr to be automatically filled in by Access in a similar way as my first case.
My question then is: Can I do like in the piece of code above, or do I have to add some code in between?
It does not behave the way I expect though, because e.g in a test case I have three contact persons with only one branch each, but when clicking on the button I obtain the three branches shown as belonging to the first contact person.

Dennis Davidsson

5 nov 2011 kl. 14:48 skrev John Viescas:

> Dennis-
>
> The code I gave you simply replaces the code you posted before. Setting the
> DefaultValue does not "dirty" the record. But it will automatically supply that
> value when the user creates a new row.
>
> 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 2:44 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: Re: [MS_AccessPros] Strange behavior when filtering on second form
>
> Hi again John,
>
> I became a little unsure of where to put your suggested piece of code.
> I guess I shall put it on the event 'When clicked' under the button on the form
> Adressregister.
> Then it will take care of every situation where I add a contact person to a
> company, for which there was none before.
> But how about the case, where I already have one contact person registered and
> just click on 'next record' on the form Kontaktpersoner in order to add a new
> one?
>
> 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
> >
> >
>
> [Non-text portions of this message have been removed]
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>