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

Dennis-

Why isn't the Branch information in a subform on the Kontaktpersoner form? You
shouldn't have to open two forms to see all the information relevant to a
contact.

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 8:23 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: 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
>
>

[Non-text portions of this message have been removed]

------------------------------------

Yahoo! Groups Links