All records in subform

[Laocon]

Hi,

I have a mainform where you can choose customer details, and a subform for putting the order details.
Currently you have to select the products one-by-one from a combo box.

Is there any way to make all the products available to be ordered appear without choosing, so the user must submit an order for every product (even if they just choose 0)?

Thanks in advance...

Laocon
---
Struggling to understand...

 

[Rich@ITTC]

Hi Laocon

Yes I am sure you could do something as you have described with a default set of products automatically generated for each subform.

However, there are a couple of things to consider. First, there is the fairly obvious point that you, presumably, will be storing records with the quantity = 0. This is then storing unnecessary data and if you have hundeds of clients/customers who over the years put in thousands of orders you will end up with hundreds of thousands of "empty rows" of data that will inevitably slow down your database, make the database bigger, etc.

Secondly from what you have described each customer has a subform for the order with rows for each item on the order. What happens about new orders from the same customer? Perhaps your subform should contain only Order details (Date, OrderID, etc) and you should place a link (via Double-Clicking on the order row, for example) to open up a form to actually specify the order rows/items.

HTH

Rich Gorvin

[This message has been edited by Rich@ITTC (edited 09-20-2001).]

 

[Laocon]

Thanks Rich...

I forgot to say that I have another subform on the same mainform with order details already - working in a similar way to the method you describe...

Main Form = ForecastMF
Subform 1 = ForecastOrderDetailsSF
Subform 2 = ForecastProductDetailsSF

I see what you say about all those 0s however I collect only 3 orders per month, and one thing I do need to do is record forecast against order (eg I have to supply even if they haven't forecast - likewise - if they forecast and don't order)... IMO, in this case it is acceptable to include lots of 0s...

So how do I go about it??

 

[Rich@ITTC]

Hi again

First set up a table of Default Order Products -tblDefaultProduct - (this need only contain the ProductID number). These will be the six/seven products automatically added to each instance of the ForecastProductDetailsSF subform.

Then create an append query (qappDefaultProduct) that takes these values and the OrderID number from the ForecastOrderDetailsSF subform (this can be refenced in the criteria row of the Append query -
Forms!ForecastMF!ForecastOrderDetailsSF!OrderID) - and adds the rows to the query/table where you store your ProductsAndOrders (presumably you have a table with all the Products ordered with the ProductID and the OrderID, cost, discount, etc) - this would normally be the query or table that is the Record Source for ForecastProductDetailsSF.

Next decide how/when the automatically generated new default order items will be added (probably on some event when the details on the ForecastOrderDetailsSF subform have been sufficiently completed - e.g. OrderDate_AfterUpdate). You will need to have this field as a required entry (or you could end up adding loads of half completed Order Details and hundreds of blank Order Product entries).

-----------------------------------------------
Private Sub txtOrderDate_AfterUpdate()
' Comments :
' Parameters : -
' Returns : -
' Created : 11/04/2000 Rich Gorvin
' Modified : 30/05/2001 Rich Gorvin
'
' --------------------------------------------------------

On Error GoTo Err_txtOrderDate_AfterUpdate
Dim dbsCur As Database
Dim rst As Recordset
Dim varMsg
Dim varStyle
Dim varTitle
Dim varResponse

Set dbsCur = CurrentDb()
Set rst = dbsCur.OpenRecordset("tblDefaultProduct")

'Checks that the default set does not already exist
If IsNull(Me![ForecastProductDetailsSF].Form![ProductID]) Then
DoCmd.SetWarnings False
DoCmd.OpenQuery "qappDefaultProduct" 'Append query brings in default values
Me.Refresh
DoCmd.SetWarnings True

Else
'Warning
varMsg = "You cannot add a set of default products " _
& Chr(13) & "for this order as they already exist! If you want to " _
& "add a new set of defaults, you will need " _
& Chr(13) & "to delete the existing ones."
' Define message.
varStyle = vbCritical ' Define buttons.
varTitle = "Unable to add default set!" ' Define title.

' Display message.
varResponse = MsgBox(varMsg, varStyle, varTitle)
Exit Sub
End If

Exit_txtOrderDate_AfterUpdate:
Exit Sub

Err_txtOrderDate_AfterUpdate:
MsgBox Err.Description & _
" - (Error No:" & Err.Number & ")"
Resume Exit_txtOrderDate_AfterUpdate

End Sub
------------------------------------------------

I have a similar system that works in a database that I use (it automatically generates a list ofservices provided with a standard discount/commission per customer).

HTH

Rich Gorvin

[This message has been edited by Rich@ITTC (edited 09-20-2001).]

 

[Laocon]

Sorry Rich,

That looks fine but Im confused...Im still new to access and am not sure how to implement what you have... My formal access training will be in JANUARY

Here's what I have so far:

6 Tables
--------------------
Company - CompanyID (KEY), CompanyName, CompanyPurpose

Period - PeriodID (KEY), PeriodName, PeriodDate

Product - ProductID (KEY), ProductName, ProductDesc

Forecast - ForecastID (KEY), CompanyID (Lookup to Company Table)

ForecastPeriod - ForecastPeriodID (KEY), ForecastID (Lookup to Forecast Table), PeriodID (Lookup to Period Table)

ForecastDetails - ForecastDetailsID (KEY), ForecastPeriodID, ProductID (Lookup to Product Table), Quantity


1 Query
--------------------
All fields from Forecast / ForecastPeriod / ForecastDetails


Forms
--------------------
DEF_Forecast: Shows current date and submit date (from PeriodDate). Has one subform...

Forecast: Subform of DEF_Forecast, shows CompanyID in a combo. Has two subforms...

ForecastOrderDetails SF: Subform of Forecast, shows PeriodID (from Period table, with PeriodDate formatted as mmmm-yyyy). I have two control buttons to move forward and backwards between periods.

ForecastProductDetails SF: Subform of forecast, shows ProductID (from Product table, and ProductDescription) and Quantity.

If there are any changes you would suggest just let me know...

 

[Rich@ITTC]

Hi Laocon

Sorry that I confused you ... I probably assumed by your Member status, and the fact that your questions were well phrased and very specific, that you were an experienced database designer! So, you consider yourself to be a newbie and you don't start training until January ... Hmmmmm.

Well, to be honest it takes some time to design a database - you need to know a lot about the organisation (and I don't know anything about your organisation), you need to know about the business rules that the organisation follows, you need to know about relational database design and how to implement normalisation, etc, etc. I have been using and learning Access for over six years ... and I am still learning (but also I have done some teaching at my local Tech college for a couple of years). Consequently it means that it is a bit difficult to put someone else on the right track without a much broader view of the organisation and much more detailed specification of what the database will be required to do. That could mean a lot of time ... so I am not sure I can help much more on this one (because although I do enjoy helping others with specific problems it is not something one gets paid for ... and I do actually have a full-time job and two part-time jobs!).

So I must apologise and say I will have to call it quits on this one (i.e. I have gone as far as I can). However, I hope what little I have given you has helped in some way ... probably it has galvanized your determination to do a course in Access and learn about its really awesome potential.

Enjoy your course in January ... and hold on tight as it's a steep learning curve (almost vertical!!)

Rich Gorvin

[This message has been edited by Rich@ITTC (edited 09-21-2001).]

 

[Laocon]

Grrrr.

Well I will do my best - at least I can hopefully get something to work...

As you say I know a little - all self taught so far... Thanks for your help so far...