1

Topic: in shape does not show result of request

Good afternoon!
Situation:
Access 2010 from Office Professional 2010, Russian
There is form Frm, a mode - Only reading, a type - the Table.
Data source - SQL request from table Tbl1, which on a key field x1 (type String)
It is connected to table Tbl2, the relation "one-to-many".
Table Tbl2 is connected to table Tbl3 on NOT to a key field x2 (type String).
bl1. [x1] it is displayed in Frm. [x], type  - a field.
In Frm. [y], type  - the field with the list - should be displayed result SQL of request from Tbl3.
All happens in event OnOpen of form Frm:

Private Sub Form_Open (Cancel As Integer)
Dim RecSource As String, S As String
RecSource = varSQL_PK ' global variable with query name
Me. RecordSource = RecSource
S = Me. [x] ' a key field
With [y]
.BoundColumn = 1
.RowSource = _
"SELECT Tbl3. [x2], Tbl3. [x3] FROM Tbl3" + _
"INNER JOIN (Tbl2" + _
"INNER JOIN" + RecSource + _
"ON Tbl2. [x1] =" + RecSource + ". [x1])" + _
"ON Tbl3. [x2] =Tbl2. [x2]" + _
"WHERE Tbl2. [x1] = '" + _
S + "'" + _
"ORDER BY Tbl3. [x3];"
.RowSourceType = "Table/Query"
.ColumnCount = 2
.ColumnWidths = "0; 3,652"
.LimitToList = True
.AllowValueListEdits = False
.InheritValueList = True
.Requery
End With
End Sub

Problem:
1) at form discovery all column Frm. [y] empty though at a choice of any [y] the list of fields opens
2) [y] the list of records is identical to all, since in a condition of selection Access puts ONLY
The first record from Me. [x] though it is necessary - the value for each record on the form.
If to remove WHERE - the same effect, only in the list all records.
In what an error?
Respond, who knows, PLEASE!
P.S. For Frm. [y] in Tbl1 there is a field [y] - whether it is possible somehow in Tbl1 to do without it?
Since the form only for review, it is not necessary to store result of request.

2

Re: in shape does not show result of request

s_igor_l, basis in studio. Something in any way on fingers I will not think.

3

Re: in shape does not show result of request

s_igor_l,
What induced to thrust this business in Form_Open?
That control something showed it is necessary to fasten it to a resource.ControlSource =...

4

Re: in shape does not show result of request

1) Basis I can not lay out, for some reasons
2).ControlSource tried to install - the same result
3) And where still to thrust, except OnOpen?
4) Access'o I am engaged by "industrial necessity" ("teapot"), the necessary information I can not find, already guess health does not suffice
All answered - THANKS!

5

Re: in shape does not show result of request

s_igor_l, it is normally brought in Form_Load
.requery it is necessary to remove
Construction in.RowSource with S not  - do.x1=Forms! frmname! x

6

Re: in shape does not show result of request

alecko;
1) Form_Load - the same; on help - the same that Form_Open, only it is impossible to cancel
2).Requery - the same, only works faster,
3).x1=Forms! frmname! x - with it began. A problem that the Parameter in request, in this case, Access does not consider as Parameter
It seems that a problem in shape, I will not understand - what.

7

Re: in shape does not show result of request

... I would try here
WHERE Tbl2. [x1] = ' "+ _
S + "'" + _
+ On and to change......

8

Re: in shape does not show result of request

And even in all remaining places.....

9

Re: in shape does not show result of request

s_igor_l wrote:

alecko;
1) Form_Load - the same; on help - the same that Form_Open, only it is impossible to cancel

Here is not present. Only in Form_Open it is possible to consider arguments (like)

s_igor_l wrote:

2).Requery - the same, only works faster,
3).x1=Forms! frmname! x - with it began. A problem that the Parameter in request, in this case, Access does not consider as Parameter
It seems that a problem in shape, I will not understand - what.

And generally, transfer you all this code to section Form_Load
If it does not turn out, in Form_Current

10

Re: in shape does not show result of request

Joss;
Specificity of my DB is that that Null in results of requests can be only in some places which I process separately, therefore "+" and "and" - without a difference
alecko;
1) Argument only in Form_Open (Cancel As Integer) since allows to cancel handling of event OnOpen
2) In Form_Load transferred, without a difference
3) In Form_Current transferred, the form works not correctly
*********************************
I see, it is necessary again most on a ceiling to run, as usual)))))
The subject is closed.
All responded - THANKS!

11

Re: in shape does not show result of request

s_igor_l wrote:

In Form_Current transferred, the form works not correctly

If the rowset in  has to change for each record to change a source of lines  it is necessary on Form_Current.
But it correctly works on the simple form or on tape/tabular provided that in  the bound column is displayed.
At you the bound column is hidden and if in current record to its saved value there corresponds a line in the list and value from other column of this line is displayed, in other visible records of the lines corresponding to saved values can and not to be, then in  is empty (your problem No1).
The standard decision for Aksa - in the tape/tabular form in  to deduce all the line long, and for record input/editing to open other (simple) form.