Re: [MS_AccessPros] To open a from with link criteria from a subform

John,

You are probably right in this. It should not be too complicated. I will try to build a relevant sample and try to upload.
I will have only limited chance to do this today, but I will try it as soon as possible.
Thanks again John for following my case.

Kindly regards
Dennis Davidsson

12 maj 2012 kl. 17:52 skrev John Viescas:

> Dennis-
>
> To build the sample, I would think all you need is the table, the form you're
> trying to open filtered and the form that's doing the OpenForm. Is it more
> complicated than that?
>
> 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)
>
> ----------------------------------
>
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of John Viescas
> Sent: Saturday, May 12, 2012 5:30 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: RE: [MS_AccessPros] To open a from with link criteria from a subform
>
>
> Dennis-
>
> It would be highly unusual for text fields in an Access table to have trailing
> blanks. Access normally strips those off before storing them. Since the string
> you're looking for is coming from the table you're searching, it should match in
> any case. There's something else going on.
>
> 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, May 12, 2012 3:52 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: Re: [MS_AccessPros] To open a from with link criteria from a subform
>
> Thanks John!
> for the suggestion to upload the necessary environment for you to reproduce the
> problem.
> I will try to figure out how i can shrink the needed setup as much as possible
> before uploading, and it might take some time, but I hope it is ok even if it
> will take a couple of days.
> In the meantime one more reflection: I know that what I am trying to do is a
> "quick-and-dirty solution" and an example of bad database design. This is
> because I am trying to filter on the name of Trade Fairs, which is of course a
> character string, and a much better solution would be if I could filter on the
> counter, which is the primary key of the table BranschKP3. This should have been
> implemented as a secondary key in the table Mässor, thus establishing a correct
> connection between the two tables indicating that from a basic base of
> "branches", i.e. categories of fairs, each new calendar occurrence of a fair is
> selected when being registered in the form Mässor (this word means fairs).
> However, this situation has arisen from historical reasons. The function of the
> category table BranschKP3 came into production rather recently in the system.
> I also have many other examples in the system of buttons which when clicked take
> me to different other forms with a correct filtering on e.g. the primary key
> Adressnr of the main form Adressregister. And that works fine. What is an extra
> complexity in this new trial to filter on Fair name is also that the name is
> picked up from a subsubform in the form Adressregister, and that gives rise to a
> rather long string, since the button has to be on the main form Adressregister.
> But since I saw in the error message from Access, that indicated the original
> syntax error in my first note, that the correct name of the branch had been
> evaluated by Access, I got convinced to continue on this road.
> However, one thing stroke me now: Could it be that the filter does not give
> match because of a different number of trailing blanks in the name fields I
> compare between the tables Mässor and BranschKP3?
> If the number of trailing blanks is critical, then I am pretty sure I have to
> give up this "quick-and-dirty" idea and go for a solid clean database design
> after all.
>
> Kindly regards
> Dennis Davidsson
>
> 11 maj 2012 kl. 22:30 skrev John Viescas:
>
> > Dennis-
> >
> > Sorry I missed your earlier reply. I merely asked that because setting Data
> > Entry = Yes would give the results you're seeing.
> >
> > Can you upload the database or a subset with the form, table, and the filter
> > form to Assistance Needed?
> >
> > 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, May 11, 2012 9:45 PM
> > To: MS_Access_Professionals@yahoogroups.com
> > Subject: Re: [MS_AccessPros] To open a from with link criteria from a subform
> >
> > John,
> >
> > I had the Data entry property of the form set to NO!
> > So I changed it to YES and tried the whole thing again.
> > But it made no difference. ;-(
> >
> > Kindly regards
> > Dennis Davidsson
> >
> > 11 maj 2012 kl. 21:22 skrev John Viescas:
> >
> > > Dennis-
> > >
> > > Do you have the Data Entry property of the form set to Yes?
> > >
> > > 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, May 11, 2012 9:02 PM
> > > To: MS_Access_Professionals@yahoogroups.com
> > > Subject: Re: [MS_AccessPros] To open a from with link criteria from a
> subform
> > >
> > > John,
> > >
> > > the Record source of the form Mässor is simply the table Mässor itself.
> > > No extra query based on the table.
> > > It is a form used to create and maintain various info about different Trade
> > > Fairs.
> > > A trade fair may take place once a year, and the table contains one record
> per
> > > such calendar event.
> > > Typical kind of info kept is like: The main title of the Fair, the date, the
> > > address, various prices etc.
> > > The control box containing the main title of the Fair I have implemented as
> a
> > > combo box in order to simplify for the administrator to enter the name not
> > > having to remember the exact spelling. The record source of the combo box is
> > as
> > > I described in the last mail below.
> > >
> > > Kindly regards
> > > Dennis Davidsson
> > >
> > > 11 maj 2012 kl. 16:58 skrev John Viescas:
> > >
> > > > Dennis-
> > > >
> > > > What is the Record Source of the Form? (The SQL, please.)
> > > >
> > > > 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, May 11, 2012 3:48 PM
> > > > To: MS_Access_Professionals@yahoogroups.com
> > > > Subject: Re: [MS_AccessPros] To open a from with link criteria from a
> > subform
> > > >
> > > > John,
> > > >
> > > > The combo box has the column [Mässor Huvudtitel] in the table Mässor as
> > > Control
> > > > source.
> > > > And has as Record source the query:
> > > > SELECT [Mässor Huvudtitel] FROM Mässor
> > > > ORDER BY Mässår DESC
> > > >
> > > > And I cannot find anything wrong with that. It works fine when I open the
> > form
> > > > Mässor from scratch, i.e. without using the button on the form
> > Adressregister.
> > > >
> > > > Kindly regards
> > > > Dennis Davidsson
> > > >
> > > > 11 maj 2012 kl. 15:31 skrev John Viescas:
> > > >
> > > > > Dennis-
> > > > >
> > > > > Yes, the field must be in the Record Source. It sounds like your combo
> box
> > > has
> > > > > not Control Source. Is that the case?
> > > > >
> > > > > 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, May 11, 2012 3:05 PM
> > > > > To: MS_Access_Professionals@yahoogroups.com
> > > > > Subject: Re: [MS_AccessPros] To open a from with link criteria from a
> > > subform
> > > > >
> > > > > Hi John,
> > > > >
> > > > > but that is not the case. I.e. the combo box is bound only to one
> column,
> > > and
> > > > > that is the very column called (Mässor Huvudtitel] containing the name
> of
> > > the
> > > > > Trade Fair.
> > > > > So I tried a "quick-and-dirty" idea: I added a new control box to the
> form
> > > > with
> > > > > the column [Mässor Huvudtitel] in the table Mässor as datasource and
> then
> > > used
> > > > > that one in the link criteria in my VBA code. But I still got just an
> > empty
> > > > form
> > > > > opened. Could it be that the field I use in my link criteria must also
> be
> > a
> > > > > column in the table? I.e. is it not enough if it is a control box on the
> > > form?
> > > > >
> > > > > Kindly regards
> > > > > Dennis Davidsson
> > > > >
> > > > > 11 maj 2012 kl. 14:51 skrev John Viescas:
> > > > >
> > > > > > Dennis-
> > > > > >
> > > > > > Probably. You have to filter the bound value of the combo box, which
> is
> > > not
> > > > > > necessarily what is displayed. For example, if it is bound to and ID
> > field
> > > > but
> > > > > > displays a name, you have to filter for the ID value.
> > > > > >
> > > > > > 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, May 11, 2012 2:32 PM
> > > > > > To: MS_Access_Professionals@yahoogroups.com
> > > > > > Subject: Re: [MS_AccessPros] To open a from with link criteria from a
> > > > subform
> > > > > >
> > > > > > Thanks Graham and John for your suggested solution to my problem!
> > > > > > It solved my syntax problem correctly.
> > > > > > However, then coming one step further in my debugging I immediately
> ran
> > > into
> > > > > the
> > > > > > next problem:
> > > > > > I got my form Mässor opened ok. Fine so far.
> > > > > > But, instead of, as I had expected, getting the form opened filtered
> by
> > > the
> > > > > > name of the Trade Fair 'BRAU Beviale' , I just got the form opened
> with
> > > the
> > > > > > combo box called (Mässa Huvudtitel] (and all other controls on the
> form
> > as
> > > > > > well) empty!
> > > > > > I have checked in the tables that I should get a match on the used
> > value,
> > > so
> > > > > the
> > > > > > problem is not as simple as that.
> > > > > > So my question is: Do I have to modify my sentence stLinkCriteria in
> > some
> > > > way,
> > > > > > because it is a combo box rather than an ordinary control box?
> > > > > >
> > > > > > Kindly regards
> > > > > > Dennis Davidsson
> > > > > >
> > > > > > 11 maj 2012 kl. 12:48 skrev John Viescas:
> > > > > >
> > > > > > > Dennis-
> > > > > > >
> > > > > > > You must enclose the criteria string in quotes. Try this:
> > > > > > >
> > > > > > > stLinkCriteria = "[Mässa Huvudtitel]=""" & _
> > > > > > >
> > [Forms]![Adressregister]![Kontaktpersoner]![BranschKP3].[Form]![Bransch]
> > > &
> > > > > > > """"
> > > > > > >
> > > > > > > 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)
> > > > > > >
> > > > > > > --------------
> > > > > > >
> > > > > > > From: MS_Access_Professionals@yahoogroups.com
> > > > > > > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Dennis
> > > > > Davidsson
> > > > > > > Sent: Friday, May 11, 2012 12:20 PM
> > > > > > > To: MS_Access_Professionals@yahoogroups.com
> > > > > > > Subject: [MS_AccessPros] To open a from with link criteria from a
> > > subform
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > Hello,
> > > > > > >
> > > > > > > Trying to open another form from my primary form by clicking a
> button
> > I
> > > > get
> > > > > > the
> > > > > > > following error message from Access , which I cannot figure out how
> to
> > > > > > correct:
> > > > > > >
> > > > > > > Syntax error(operator missing) in the query expression '[Mässa
> > > > > > Huvudtitel]=BRAU
> > > > > > > Beviale'
> > > > > > >
> > > > > > > The character string value 'BRAU Beviale' is the name of a Trade
> Fair
> > > and
> > > > it
> > > > > > is
> > > > > > > correctly obtained from the reference to a value in a combo box in a
> > > > subform
> > > > > > of
> > > > > > > a subform in the piece of code below.
> > > > > > >
> > > > > > > What is wrong with my piece of VBA code behind the button?
> > > > > > > The code is as follows:
> > > > > > >
> > > > > > > Private Sub Mässor_Click()
> > > > > > > On Error GoTo Err_Mässor_Click
> > > > > > >
> > > > > > > Dim stDocName As String
> > > > > > > Dim stLinkCriteria As String
> > > > > > >
> > > > > > > stDocName = "Mässor"
> > > > > > > stLinkCriteria = "[Mässa Huvudtitel]=" &
> > > > > > >
> > [Forms]![Adressregister]![Kontaktpersoner]![BranschKP3].[Form]![Bransch]
> > > > > > > DoCmd.OpenForm stDocName, , , stLinkCriteria
> > > > > > >
> > > > > > > Exit_Mässor_Click:
> > > > > > > Exit Sub
> > > > > > >
> > > > > > > Err_Mässor_Click:
> > > > > > > MsgBox Err.Description
> > > > > > > Resume Exit_Mässor_Click
> > > > > > >
> > > > > > > End Sub
> > > > > > >
> > > > > > > Kindly regards
> > > > > > > Dennis Davidsson
> > > > > > >
> > > > > > > [Non-text portions of this message have been removed]
> > > > > > >
> > > > > > >
> > > > > >
> > > > > > [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
> >
> >
>
> [Non-text portions of this message have been removed]
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>



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



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

Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/MS_Access_Professionals/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/MS_Access_Professionals/join
(Yahoo! ID required)

<*> To change settings via email:
MS_Access_Professionals-digest@yahoogroups.com
MS_Access_Professionals-fullfeatured@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
MS_Access_Professionals-unsubscribe@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/