Hi James,
Being that I am a Partsperson, I am using a totally different approach ...
<Grin>
My main form contains the Supplier information from tblSuppliers, and the
subform contains the Order items from tblTransactions , sorted by TransID.
My datasheet style subform has a combo-box (yes, datasheet-style forms do
support combo-boxes) that is used to select a part number.
The Master / Child Links are by SupplierID, so I suppose that the portion of
my code below that sets this value is redundant.
Selecting the part number also fills in the corresponding description and
pricing information, which CAN be over-ridden if desired, so that all that
is left to do is to enter a TransQty!
My subform also has 2 unbound textbox controls in it's footer section that
calculate totals and counts.
These controls have the following properties:
------------------------------------------------------------------
Name: txtOrderValue
Control Source: =Sum([TransQty]*([PartCost]+[PartCore]))
Format: Currency
Name: txtItemCount
Control Source: =Count(*)
Note that while these textboxes are NOT visible in datasheet view, their
values CAN be carried forward to the main form.
My main form unbound textbox controls which are used to display these
values:
------------------------------------------------------------------
Name: txtItemCount (which is same name as the suform control)
Control Source: =[sbfPartsOrder].[Form]![txtItemCount]
Name: txtOrderValue (again, the same name as the subform control)
Control Source: =[sbfPartsOrder].[Form]![txtOrderValue]
Format: Currency
------------------------------------------------------------------
Here is the code:
=============== =============== =============== =============== ======
Private Sub cboPartNumber_A fterUpdate()
'This is the Row Source for this combo-box, which is bound to PartID
'SELECT DISTINCTROW PartID, SupplierID, Line, PartNumber, Description,
Retail, Cost, Core FROM tblPartsInvento ry;
Dim ctl As Control
Set ctl = Me.cboPartNumbe r
Dim MySupp, MyLine, MyPart, MyDesc As String
Dim MyRetl, MyCost, MyCore As Currency
With ctl
MySupp = ctl.Column(1) 'Probably don't need this. The Link Master / Child
criteria should force this value.
MyLine = ctl.Column(2)
MyPart = ctl.Column(3)
MyDesc = ctl.Column(4)
MyRetl = ctl.Column(5)
MyCost = ctl.Column(6)
MyCore = ctl.Column(7)
End With
Me.TransDate = Date
Me.TransType = "Parts Order"
Me![SupplierID] = MySupp
Me![Line] = MyLine
Me![PartNumber] = MyPart
Me![Description] = MyDesc
Me![PartRetail] = MyRetl
Me![PartCost] = MyCost
Me![PartCore] = MyCore
Me![TransQty].SetFocus
Set ctl = Nothing
End Sub
=============== =============== =============== =============== ======
Private Sub TransQty_AfterU pdate()
Me.Recalc 'Refresh all calculated controls on this form
End Sub
=============== =============== =============== =============== ======
--
HTH,
Don
=============== ==============
Use
My*****@Telus.N et for e-mail
Disclaimer:
Professional PartsPerson
Amateur Database Programmer {:o)
I'm an Access97 user, so all posted code
samples are also Access97- based
unless otherwise noted.
=============== ==============
"James" <ja************ @hotmail.com> wrote in message
news:7a******** *************** ***@posting.goo gle.com...
Hi,
I have a combo box(cmboParts) and the row source is a part name. Once
a part is selected I have button(cmdOrder Add) to add data about this
part to a sub-form(frmParts_O rdered). However this sub-form does not
have the field PartName. Instead it has a field called PartCode.
Therefore i have had to set up a QBE to find PartCode(both PartName
and PartCode are in the same table 'tblPart') and this will be used in
the code for the button.
I have been having major problems trying out different things but they
haven't seemed to work. This is what i have so far:
Private Sub cmdOrderAdd_Cli ck()
msg = Forms!frmOrder! cmboParts
strSQL = "SELECT PartCode FROM tblPart WHERE PartName = "' & msg & "'"
Following this the QBE query has to come into use but i don't know how
to do it.
I'm sure there are some clever people who can help me!
Many thanks
James
