Group by week in editable form

[niteraven]

Hello all

I have a report that groups Sales orders by week due. Now they want this information displayed in an editable form. The same way it displays on the report and i am having trouble getting this to display correctly.

I am using two tables - tblorder and tblitems I am using a due date as the date to get the week number.

here is the code i used for the report:

SELECT tblorder.sonum, tblorder.Customer, tblorder.productClass, tblorder.podate, tblorder.deliverydate, tblorder.wip, tblorder.comments, tblorder.reqdate, tblorder.ackdate, tblorder.wsnum, tblorder.prodreq, tblorder.status, tblorder.sstage, tblorder.fstage, tblorder.sapprec, tblorder.prodque, tblorder.engcompdate, tblorder.engreq, DatePart("ww",[reqdate]) AS WkNo, Year([reqdate]) AS YRNO, tblitems.itemqty, tblitems.itemnum, tblitems.itemrate
FROM tblorder INNER JOIN tblitems ON tblorder.sonum = tblitems.sonum
WHERE (((tblorder.productClass)<>"mining") AND ((tblorder.deliverydate) Is Null))
ORDER BY tblorder.sonum, tblorder.productClass;
[\code]

I have tried to use a main form with just the week number and the subform with the query above using the week number as the master and child link fields, it give unstable results ( there should only be 150 records but it has 1000's records in results)

Any info as to what i am doing wrong would be greatly appreciated.

Thanks
Raven

 

[DougP]

if you have a query in the subform as you know it is not going to be editable. Is that the problem you can't edit the data?






DougP
< I Built one

http://www.robots2000.com

 

[dhookom]

Does this describe a primary/foreign key relationship? If you look at the datasheet of this query, can you edit the records?

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

How are ya niteraven . . .

To tell immediately if a query or recordsource is editable, have a look at the [blue]AddNew[/blue] navigation button of the query or form (form [blue]Allow Additions[/blue] property is set to Yes). If the button has that disabled look the returned recordset is uneditable!

According to Why is my query read-only? it looks like your query is editable.

Your mainform has a problem in that the query needs to be a sum query to afford distinct week numbers (instead of stepping thru a number of records in the same week). However, this type of query is uneditable! Don't know if this is tolerable as far as the mainform is concerned.

Also ... if your editing due dates in the subform its possible to generate a different WkNo. This record will be fine except it won't match WkNo's in the subform. A requery of the mainform will be required to set things in order.

A more detailed description of your editing intent is needed here . . .

[blue]Your Thoughts! . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[niteraven]

Thank you all for the responses. The subform had doubled the amount of records that should have shown up; there were too many parameters for this query.

I have changed the form / sub form to a form with a list box, displaying the information. Then they dbl click on the order they want to edit in the list box and it opens a separate form displaying editable information for just that sales order. This is working correctly.

The main problem now is I have the user is selecting the week number from a drop down menu, then only the records with that week number are showing up. The users now want any sales order that missed their week number to be displayed in the listbox.

here is the SQL behind the list box:

SELECT qryprodmeet1.sonum, qryprodmeet1.productClass, qryprodmeet1.wsnum, qryprodmeet1.fstage, qryprodmeet1.sapprec, qryprodmeet1.prodque, qryprodmeet1.WkNo, qryprodmeet1.itemnum, qryprodmeet1.itemqty, qryprodmeet1.ackWkNo, qryprodmeet1.WkNo, qryprodmeet1.YRNO, qryprodmeet1.itemrate
FROM qryprodmeet1
WHERE (((qryprodmeet1.ackWkNo)=[forms]![FRMTESTPRODUCTION]![Combo6])) OR (((qryprodmeet1.WkNo)=[forms]![FRMTESTPRODUCTION]![Combo6]))
ORDER BY qryprodmeet1.sonum, qryprodmeet1.productClass;

I am wondering if a do while loop behind the list box will work. Because now I need to check week number and check year in order to place the record in the current week number with in the list box.

any help is greatly appreciated.
Raven