Sarah:
Opening the relevant form should be simple enough as its just a case of
opening whichever is the appropriate one for whatever is selected in
Job_Installable combo box on the main form. From the code in your original
post it looks like the combo box's value is a hidden first column, presumably
a numeric ID value, as you are referencing its second column. So simply to
open a form the code in the combo box's AfterUpdate event procedure would be
along these lines;
Dim ctrl As Control
Dim strForm As String
Set ctrl = Me.ActiveControl
' first make sure a selection has been made
If Not IsNull(ctrl) Then
If ctrl.Column(1) = "Tank" Then
strForm = "frmTankConsumamables"
Else
strForm = "frmCylinderConsumamables"
End If
DoCmd.OpenForm strForm
End If
However, that would simply give you a form listing the relevant items,
whereas I'd have envisaged that what you'd need to do is create a set of
related records in another table, which together with the record being
created in the main form would constitute the quote, i.e. you'd have one
record in the main Quotes table and multiple records, one per consumable, in
a related QuoteConsumables table with each record having two foreign key
columns, one referencing the primary key of the Quotes table (JobNo say), the
other referencing the primary key of a Consumables table (together these
columns would be the table's composite primary key) and non-key columns for
the quantity and unit price.
In this sort of situation what I'd normally foresee happening is that, when
Tank or Cylinder is selected in the combo box on the main quote form this
would execute some code which would insert the relevant number of rows into
the QuoteConsumables table, each having the same JobNo as the main form's
current record and a different value in the consumable foreign key column,
and by default zero in the quantity column. This would mean the code
executing an 'append' query which selects all the relevant primary key values
from those rows in the Consumables table relevant to Tank or Cylinder and
inserts these into QuoteConsumables along with the current JobNo, the unit
price and a zero Quantity.
Then the relevant form would be opened to show the newly created records in
QuoteConsumables, filtering the form to the current JobNo. This would mean
that you would not need separate forms for Tank or Cylinder as it would
already be filtered to the relevant items. For this the code would now be
along these lines:
Dim cmd As ADODB.Command
Dim strSQL As String
Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText
' ensure current quote record is saved before inserting
' rows into related table
Me.Dirty = False
' insert new rows into QuoteConsumables table
strSQL = "INSERT INTO QuoteConsumables " & _
"(JobNo, ConsumableID, UnitPrice, Quantity) " & _
"SELECT " & Me.JobNo & ", UnitPrice, ConsumableID, 0 " & _
"FROM Consumables WHERE Category = " & Me.Job_Installable
cmd.CommandText = strSQL
cmd.Execute
' open form at newly inserted records for user
' to insert quantities
DoCmd.OpenForm "frmQuoteConsumables", _
WhereCondition:="JobNo = " & Me.JobNo
As an alternative to opening a separate frmQuoteConsumables form the
consumables could be listed in a continuous forms view subform within the
main quote form. You'd then just have to requery the subform in the code
after inserting the new rows into QuoteConsumables.
I've assumed in the above that QuteNumber is a nember data type, and thet the
Category column in Consumables is a number data type referencing the numeric
primary key of a Categories table.
So diagrammatically the model would look like this:
Quotes----<QuoteConsumables >----Consumables>-----Categories
where the < and > signs represent the 'many' side of each relationship. From
this you can see that QuoteConsumables in fact models a many-to-many
relationship between Quotes and Consumables, i.e. its what's colloquially
known as a 'junction' table.
Creating a quote report is a simple task of course; its just a question of
basing the report on a query which joins the necessary tables and grouping it
by JobNo, putting the main quote data in the group header, and the
consumables in the detail section.
Ken Sheridan
Stafford, England
Hi Ken,
The first page of the quote has, A Job No. , Description, Requestor, Date,
Location, Klms, and Installables (in the Installables you can pick if it is a
cylinder or tank via a drop down box). Depending on what is picked i would
like it to open a "consumable form" which either relates to Tanks or
Cylinders. eg, parts that need to be added into the quote. I have a different
form for "tank consumables" and another one "cylinder consumables" (hence the
code), once this form is picked the person will have a list of items that
they will be needed to do the installation, they will add to the qty in the
form (they will only use what is there and cannot add anything new) eg, There
could be 5 pens to be used and 1 ruler etc and the remainder of the items
will not need to be used, so it will say zero. After all information is added
in it will be made into a report so that this can be emailed / printed and
saved so that the sales person know how much the installation will cost.
With regard to the Tables etc, i have used the "normalise table" so that is
spilt with the description qty and another table with description qty valve
and Catagory (this field has either Cylinder or Tank written next to it so
that when the person picks from the "installable" (in the main form) it knows
if it is a cylinder or tank item which is what i want the code to do is open
the form for which it relates to.
i have tested the queries and they do what they are meant to do,
I hope this answer's all your questions?????? Thank you so much for taking
the time to help me out.
Cheers
Sarah Johnston
Melbourne Australia
Sarah:
[quoted text clipped - 45 lines]
Thanks Heaps Sarah
--
.
