Sub Form shows Chinese symbol and box

  • Thread starter Thread starter Bob Vance
  • Start date Start date
B

Bob Vance

When I open my form my Form/sub form is correct but when I do a filter on
the sub form one text box shows 2 symbols a Chinese figure and a box!
 
The symptoms suggest that Access is misunderstanding the data type of the
field, or aggregating it in some way.

Here's an example of what happens when JET mis-handles an aggregated memo:
http://allenbrowne.com/bug-18.html

What is the Record Source of this subform?
If it's a query:
- Does it contain multiple tables?
- Does it use DISTINCT, UNION, or GROUP BY?

What's in the Link Master Fields / Link Child Fields?
Is the memo field mentioned in these properties?

Are you filtering on the memo field?
 
Thanks Allen, this is the query in Row Source of my Sub Form:
SELECT Max(tblAccountStatus.BillDate) AS LastDate, qryOverdueAll.Name,
tblAccountStatus.PaidAmount, tblOwnerInfo.OwnerFirstName,
tblOwnerInfo.OwnerLastName, tblAccountStatus.OwnerID,
tblAccountStatus.BillID1, tblAccountStatus.BillID, tblAccountStatus.BillID,
tblAccountStatus.ModeOfPayment
FROM (tblAccountStatus INNER JOIN tblOwnerInfo ON tblAccountStatus.OwnerID =
tblOwnerInfo.OwnerID) INNER JOIN qryOverdueAll ON tblOwnerInfo.OwnerID =
qryOverdueAll.OwnerID
GROUP BY qryOverdueAll.Name, tblAccountStatus.PaidAmount,
tblOwnerInfo.OwnerFirstName, tblOwnerInfo.OwnerLastName,
tblAccountStatus.OwnerID, tblAccountStatus.BillID1, tblAccountStatus.BillID,
tblAccountStatus.BillID, tblAccountStatus.ModeOfPayment
ORDER BY Max(tblAccountStatus.BillDate) DESC;
 
OOPS Had BillID listed twice...Regards Bob

SELECT Max(tblAccountStatus.BillDate) AS LastDate, qryOverdueAll.Name,
tblAccountStatus.PaidAmount, tblOwnerInfo.OwnerFirstName,
tblOwnerInfo.OwnerLastName, tblAccountStatus.OwnerID,
tblAccountStatus.BillID1, tblAccountStatus.BillID,
tblAccountStatus.ModeOfPayment
FROM (tblAccountStatus INNER JOIN tblOwnerInfo ON tblAccountStatus.OwnerID =
tblOwnerInfo.OwnerID) INNER JOIN qryOverdueAll ON tblOwnerInfo.OwnerID =
qryOverdueAll.OwnerID
GROUP BY qryOverdueAll.Name, tblAccountStatus.PaidAmount,
tblOwnerInfo.OwnerFirstName, tblOwnerInfo.OwnerLastName,
tblAccountStatus.OwnerID, tblAccountStatus.BillID1, tblAccountStatus.BillID,
tblAccountStatus.ModeOfPayment
ORDER BY Max(tblAccountStatus.BillDate) DESC;
 
Okay, you do have a GROUP BY clause, so it is doing aggregation.

I'm not sure which of these is the memo, but whichever field it is, choose
First instead of Group By under that field (in the Total row in query
design.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


Bob Vance said:
OOPS Had BillID listed twice...Regards Bob
Click to expand...
SELECT Max(tblAccountStatus.BillDate) AS LastDate,
qryOverdueAll.Name,
tblAccountStatus.PaidAmount,
tblOwnerInfo.OwnerFirstName,
tblOwnerInfo.OwnerLastName,
tblAccountStatus.OwnerID,
tblAccountStatus.BillID1,
tblAccountStatus.BillID,
tblAccountStatus.BillID,
tblAccountStatus.ModeOfPayment
FROM (tblAccountStatus INNER JOIN tblOwnerInfo
ON tblAccountStatus.OwnerID = tblOwnerInfo.OwnerID)
INNER JOIN qryOverdueAll
ON tblOwnerInfo.OwnerID = qryOverdueAll.OwnerID
GROUP BY qryOverdueAll.Name,
tblAccountStatus.PaidAmount,
tblOwnerInfo.OwnerFirstName,
tblOwnerInfo.OwnerLastName,
tblAccountStatus.OwnerID,
tblAccountStatus.BillID1,
tblAccountStatus.BillID,
tblAccountStatus.BillID,
tblAccountStatus.ModeOfPayment
ORDER BY Max(tblAccountStatus.BillDate) DESC;
 
Thanks Allen, Its the ModeOfPayment field, When I changed it to "First" my
form gave the #Name error
Regards Bob Vance
 
Oops Sorry Allen you where right :) just had to change the text box to
FirstOfModeOfPayment :) Brilliant
Regards Bob
 
Allen, If I put a Control button in my sub form that will The payment form
should it be Something like this?...Regards Bob
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmAccountStatus"

stLinkCriteria = "[BillID1]=" & Me![BillID1]
DoCmd.OpenForm stDocName, , , stLinkCriteria
 
If this is a subform (i.e. a form within another form), then it won't work
to use OpenForm. You may be able to set its Filter (and FilterOn.)

If it's not actually a subform, the code would work provided the form is not
already open.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


Bob Vance said:
Allen, If I put a Control button in my sub form that will The payment form
should it be Something like this?...Regards Bob
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmAccountStatus"

stLinkCriteria = "[BillID1]=" & Me![BillID1]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Bob Vance said:
Oops Sorry Allen you where right :) just had to change the text box to
FirstOfModeOfPayment :) Brilliant
Regards Bob
Click to expand...
Click to expand...
 
Thanks Allen, it is actually opening up another form but not at the right
record (BillID1)
Regards Bob

Allen Browne said:
If this is a subform (i.e. a form within another form), then it won't work
to use OpenForm. You may be able to set its Filter (and FilterOn.)

If it's not actually a subform, the code would work provided the form is
not already open.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


Bob Vance said:
Allen, If I put a Control button in my sub form that will The payment
form should it be Something like this?...Regards Bob
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmAccountStatus"

stLinkCriteria = "[BillID1]=" & Me![BillID1]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Bob Vance said:
Oops Sorry Allen you where right :) just had to change the text box to
FirstOfModeOfPayment :) Brilliant
Regards Bob


Okay, you do have a GROUP BY clause, so it is doing aggregation.

I'm not sure which of these is the memo, but whichever field it is,
choose First instead of Group By under that field (in the Total row in
query design.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


OOPS Had BillID listed twice...Regards Bob

SELECT Max(tblAccountStatus.BillDate) AS LastDate,
qryOverdueAll.Name,
tblAccountStatus.PaidAmount,
tblOwnerInfo.OwnerFirstName,
tblOwnerInfo.OwnerLastName,
tblAccountStatus.OwnerID,
tblAccountStatus.BillID1,
tblAccountStatus.BillID,
tblAccountStatus.BillID,
tblAccountStatus.ModeOfPayment
FROM (tblAccountStatus INNER JOIN tblOwnerInfo
ON tblAccountStatus.OwnerID = tblOwnerInfo.OwnerID)
INNER JOIN qryOverdueAll
ON tblOwnerInfo.OwnerID = qryOverdueAll.OwnerID
GROUP BY qryOverdueAll.Name,
tblAccountStatus.PaidAmount,
tblOwnerInfo.OwnerFirstName,
tblOwnerInfo.OwnerLastName,
tblAccountStatus.OwnerID,
tblAccountStatus.BillID1,
tblAccountStatus.BillID,
tblAccountStatus.BillID,
tblAccountStatus.ModeOfPayment
ORDER BY Max(tblAccountStatus.BillDate) DESC;

The symptoms suggest that Access is misunderstanding the data type of
the field, or aggregating it in some way.

Here's an example of what happens when JET mis-handles an aggregated
memo:
http://allenbrowne.com/bug-18.html

What is the Record Source of this subform?
If it's a query:
- Does it contain multiple tables?
- Does it use DISTINCT, UNION, or GROUP BY?

What's in the Link Master Fields / Link Child Fields?
Is the memo field mentioned in these properties?

Are you filtering on the memo field?

When I open my form my Form/sub form is correct but when I do a
filter on the sub form one text box shows 2 symbols a Chinese figure
and a box!
Click to expand...
Click to expand...
Click to expand...
 
Suggestions:

1. Make sure the target for is not already open (not even in design view.)

2. To verify that criterion is being set to the right value, temporily
insert this line just above the OpenForm line:
Debut.Print stLinkCriteria
Then when the form opens, open the Immediate Window (Ctrl+G) to see if you
passed it the right value.

3. Once the target form is open, open the Immediate Window and enter:
Debug.Print Forms!frmAccountStatus.Filter
to verify that the actual filter that was applied matches the criteria you
passed.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


Bob Vance said:
Thanks Allen, it is actually opening up another form but not at the right
record (BillID1)
Regards Bob

Allen Browne said:
If this is a subform (i.e. a form within another form), then it won't
work to use OpenForm. You may be able to set its Filter (and FilterOn.)

If it's not actually a subform, the code would work provided the form
is not already open.

Bob Vance said:
Allen, If I put a Control button in my sub form that will The payment
form should it be Something like this?...Regards Bob
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmAccountStatus"

stLinkCriteria = "[BillID1]=" & Me![BillID1]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Oops Sorry Allen you where right :) just had to change the text box
to FirstOfModeOfPayment :) Brilliant
Regards Bob


Okay, you do have a GROUP BY clause, so it is doing aggregation.

I'm not sure which of these is the memo, but whichever field it is,
choose First instead of Group By under that field (in the Total
row in query design.)
Click to expand...
Click to expand...
Click to expand...
 
None of this did anything Allen, Immediate window did not show anything
I think I am going to have to pass on this filter :(
Thanks Bob

Allen Browne said:
Suggestions:

1. Make sure the target for is not already open (not even in design view.)

2. To verify that criterion is being set to the right value, temporily
insert this line just above the OpenForm line:
Debut.Print stLinkCriteria
Then when the form opens, open the Immediate Window (Ctrl+G) to see if you
passed it the right value.

3. Once the target form is open, open the Immediate Window and enter:
Debug.Print Forms!frmAccountStatus.Filter
to verify that the actual filter that was applied matches the criteria you
passed.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


Bob Vance said:
Thanks Allen, it is actually opening up another form but not at the right
record (BillID1)
Regards Bob

Allen Browne said:
If this is a subform (i.e. a form within another form), then it won't
work to use OpenForm. You may be able to set its Filter (and FilterOn.)

If it's not actually a subform, the code would work provided the form
is not already open.

Allen, If I put a Control button in my sub form that will The payment
form should it be Something like this?...Regards Bob
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmAccountStatus"

stLinkCriteria = "[BillID1]=" & Me![BillID1]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Oops Sorry Allen you where right :) just had to change the text box
to FirstOfModeOfPayment :) Brilliant
Regards Bob


Okay, you do have a GROUP BY clause, so it is doing aggregation.

I'm not sure which of these is the memo, but whichever field it is,
choose First instead of Group By under that field (in the Total
row in query design.)
Click to expand...
Click to expand...
Click to expand...
 
Great Got it, I was opening the form from the navigation pane and not a
control , now its working Brilliant :) Regards Bob
Bob Vance said:
None of this did anything Allen, Immediate window did not show anything
I think I am going to have to pass on this filter :(
Thanks Bob

Allen Browne said:
Suggestions:

1. Make sure the target for is not already open (not even in design
view.)

2. To verify that criterion is being set to the right value, temporily
insert this line just above the OpenForm line:
Debut.Print stLinkCriteria
Then when the form opens, open the Immediate Window (Ctrl+G) to see if
you passed it the right value.

3. Once the target form is open, open the Immediate Window and enter:
Debug.Print Forms!frmAccountStatus.Filter
to verify that the actual filter that was applied matches the criteria
you passed.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


Bob Vance said:
Thanks Allen, it is actually opening up another form but not at the
right record (BillID1)
Regards Bob

If this is a subform (i.e. a form within another form), then it won't
work to use OpenForm. You may be able to set its Filter (and FilterOn.)

If it's not actually a subform, the code would work provided the form
is not already open.

Allen, If I put a Control button in my sub form that will The payment
form should it be Something like this?...Regards Bob
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmAccountStatus"

stLinkCriteria = "[BillID1]=" & Me![BillID1]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Oops Sorry Allen you where right :) just had to change the text box
to FirstOfModeOfPayment :) Brilliant
Regards Bob


Okay, you do have a GROUP BY clause, so it is doing aggregation.

I'm not sure which of these is the memo, but whichever field it is,
choose First instead of Group By under that field (in the Total
row in query design.)
Click to expand...
Click to expand...
Click to expand...
 
Back
Top