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

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