Sabtu, 12 Mei 2012

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

 

Dennis-

Put one more line in your code like this:

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

Run your test, and then look at what's in the Immediate Window: CTRL+G

It should display the filter applied.

Just out of curiosity, what are the settings for the Link Master, Link Child
properties of the subform control?

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: Saturday, May 12, 2012 11:26 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: 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]

__._,_.___
Recent Activity:
.

__,_._,___

Tidak ada komentar:

Posting Komentar