Sorting Problem

[elliot315]

I have a subform that displays payments by month, that subform comes from a table. That table has a relationship with another table that has the names of the months and their IDs. Those months are named in Spanish I want to display that subform sorted by the months from January thru December.. and I can not use the date of the payment as a sorting rule because some clients are paying the amounts of past months on other months (ie paying January in March) and neither use the receipt number because of the same problem. I want to do it by using the id of the month (thats the only idea I came across so far) but that field is in another table... how could I accomplish this? SQL statement? if so how do I have to write that?

 

[dcobau]

1) add a new numerical field to the table (eg. SortOrder)
2) use a modify query to allocate corresponding month values (ie. Jan = 1, Feb = 2, etc)
3) base your form on a query rather than the table and sort by SortOrder

Dave

 

[elliot315]

If I Do It By The Query how do I enter new information on that subform... the query only presents me the information already entered.. but doesn;t allow me to enter new information

 

[dcobau]

is the form unbound?

 

[elliot315]

I have a the main form which comes from the tblContactInfo and a subform bound to this form by the ContactID
but this subform comes from another table (tblPayments)
I also have another table related to the tblPayments and this table is the tblMonths wich holds the names of the months (in Spanish) and the id of the month (1 thru 12)
I need to bring that Id to statement to do the sorting.... using the ID of the month

 

[dcobau]

It does not matter if a form/subform is bound to a table or to a query but, as long as it is bound, any data entered will be saved to the table. try it. It's normal to have a form bound on a query because often, like in your case, you need to do things with data and display it in the form.

Dave

 

[elliot315]

I tried it already but it doesn't allow me to add record to that query

 

[elliot315]

I resolved that in the report with the sorting and grouping feature but in the form i can not do that

 

[elliot315]

In the report I have January, February, March, April ( In Spanish)
but in the form I have April February, January, March

 

[dcobau]

I tried it already but it doesn't allow me to add record to that query

1) is the AllowAddition in the form properties set to yes?
2) is the query based on 1 or 2 tables?

 

[elliot315]

the AllowAddition is set to YES
the query is based on two tables... tblPayments and tblMonths (to use the id of the month as the sorting field)

 

[elliot315]

if I use the query as you say I can sort correctly but now I have to resolve the add record problem

 

[dcobau]

if I use the query as you say I can sort correctly but now I have to resolve the add record problem

A query that is based on 2 tables is not updatable. Base the subform on its own query and link it to the main form in the usual way.

Dave

 

[elliot315]

here is a sample of the form with the sorting problem (using the subform based on a table) Look where it says MES in the subform
it is supposed to be
ENERO
FEBRERO
MARZO
ABRIL
MAYO

 

[elliot315]

If I base the form to a query based on only one table I will have the same situation.... could not sort because I need that ID to sort the months

 

[dcobau]

You said that the subform is based on a query that has 2 tables. The query must have only tblMonths in it and its data must be sorted by field SortOrder (or whatever you called it).

Just to make sure we are on the same wave lengh:

1) The main form is based on tblPayments,
2) the subform is based on tblMonths
3) the subform is linked to the main form by field id (registro?)
4) field id must also appear in the query underlaying the subform

Yes?

 

[elliot315]

Also trying the form based on the query... the subform is not presenting me the new record with the defaults until I click on the row and type something.. then it fill the fields with the default values established making annoying the data entry.. for example if I type 125 the record automatically fill with 125$0.00 :S I have to delete the incorrect part which is the default value

 

[elliot315]

1) The main form is based on tblContactInfo,
2) the subform is based on tblPayments
3) the subform is linked to the main form by field id (registro?) <-- Correct
4) field id must also appear in the query underlaying the subform <--- Correct

 

[dcobau]

is the sorting field I suggested initially included in the subform?

 

[elliot315]

Look At the report... it comes perfecty.. why life has to be so difficult.. Billy!!! add sorting and grouping to the forms!!