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

 

John,

having worked on putting together a sample of reasonable size for upload, as you suggested, I also used the opportunity to reduce the complexity of the case as follows:
1. I added to the table Mässor a new column and to the form Mässor a new control box (just a simple one, no combo box) of data type fixed number and filled in some test numbers in the table that I could use to match against the primary key Branschnr of the table BranschKP3 used in the subsubform BranschKP3.
2. I then moved the button called Mässor that I use to click to open the form Mässor in a filtered way from the main form Adressregister to the subsubform BranschKP3.
3. I then modified the link criteria statements in the VBA code like this:

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Mässor"
stLinkCriteria = "[Testnr]=" & Me![Branschnr]
DoCmd.OpenForm stDocName, , , stLinkCriteria

And now I have a linkage situation that is practically "identical" to another one that I have on a button on the main form Adressregister and which since long works fine to open another filtered form.
I have thus reduced the possible problem with matching on text strings rather than plain numbers.
And I have reduced the possible problem with the long, syntactically delicate link criteria.

And the result is still the same! I.e. the form Mässor is opened in a condition as if I was to enter a new record and with the little box at the bottom lit up indicating 'Filter on' but with none of the matching records, that I know exist, presented.
So the only conclusion I can draw now is that Access does not allow this kind of trial to filter from a field in a subsubform.
I also think I have seen somewhere in the documentation (maybe it was in your book Access 2007 Inside OUt) that there are limitations on filtering in a main form based on values in a subform.
So what do you think, have i come to a dead end here?
What I have described above also seems to indicate that it would not help me if I tried to implement the appropriate linkage between my table BranschKP3 and Mässor in order to make it possible to filter on numbers instead of character strings.
It seems as if I have to give up the whole idea and "go back to square one" and try to figure out a way to make the matching value from the subsubform somehow available directly on the main form Adressregister, before I click the linkage button to open the form Mässor.

Kindly regards
Dennis Davidsson

12 maj 2012 kl. 18:07 skrev Dennis Davidsson:

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

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

__._,_.___

Reply to sender | Reply to group | Reply via web post | Start a New Topic

Messages in this topic (19)

Recent Activity:

• New Members 5
• New Files 1

Visit Your Group

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use

.

__,_._,___