Sub Form shows Chinese symbol and box

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

 

[Allen Browne]

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?

 

[Bob Vance]

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;

 

[Bob Vance]

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;

 

[Allen Browne]

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;

 

[Bob Vance]

Thanks Allen, Its the ModeOfPayment field, When I changed it to "First" my
form gave the #Name error
Regards Bob Vance

 

[Bob Vance]

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

 

[Bob Vance]

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

 

[Allen Browne]

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.

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

 

[Bob Vance]

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 Browne - Microsoft MVP. Perth, Western Australia

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

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

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

 

[Allen Browne]

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.

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

 

[Bob Vance]

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

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.

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

 

[Bob Vance]

Great Got it, I was opening the form from the navigation pane and not a
control , now its working Brilliant Regards Bob

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

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.

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