By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,480 Members | 2,192 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,480 IT Pros & Developers. It's quick & easy.

URGENT!! Problem sending data to field

P: n/a
Hi,

I have a combo box(cmboParts) and the row source is a part name. Once
a part is selected I have button(cmdOrderAdd) to add data about this
part to a sub-form(frmParts_Ordered). 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_Click()
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
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
On 9 Apr 2004 02:44:20 -0700, ja************@hotmail.com (James)
wrote:
Hi,

I have a combo box(cmboParts) and the row source is a part name. Once
a part is selected I have button(cmdOrderAdd) to add data about this
part to a sub-form(frmParts_Ordered). 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_Click()
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


Why not have the row source of cmboParts have both the part name and
part code. Make the widths 1";0" and then only the part name will be
displayed.

The part code corresponding to the user-selected part name will then
be cmboParts.column(1) (column numbering starts at 0).

Chuck
Nov 12 '05 #2

P: n/a
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_AfterUpdate()
'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 tblPartsInventory;

Dim ctl As Control
Set ctl = Me.cboPartNumber

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_AfterUpdate()
Me.Recalc 'Refresh all calculated controls on this form
End Sub
================================================== ================
--
HTH,
Don
=============================
Use My*****@Telus.Net 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.google.c om...
Hi,

I have a combo box(cmboParts) and the row source is a part name. Once
a part is selected I have button(cmdOrderAdd) to add data about this
part to a sub-form(frmParts_Ordered). 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_Click()
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

Nov 12 '05 #3

P: n/a
Hey Jammie:
As Chuck suggested, a multi-column combo box would come in handy.
Search the help for this. You basically just put the partscode in a
hidden column of a combo(set "Select partcode, partname from tblParts"
as the rowsource, set columncount=2, with column widths= 0", 1", and
boundcolumn = 0, that being the first column, aka partcode. this is
just a possible example).

Now, I'm not sure of this exact setup, but it sounds as though maybe
you really don't need a subform, if all this is is a 'lookup/update'
type of form. If this is true, I'd just put the lookup combo in the
form header. Don't set a recordsource in design view for the form at
all. That way you won't pull in all the parts when you open the form
- should make the form open faster. In this scenario, probably don't
want record selectors on the form, either.

Here's some basic code I just wrote and briefly tested. So, behind
your form, something like this:

Option Compare Database
Option Explicit
Private strMySQL As String
Private strWhere As String

Private Sub Form_Open(Cancel As Integer)

Me.Detail.Visible = False
strMySQL = "Select * from [tblParts]"

End Sub

Private Sub cboParts_AfterUpdate()

strWhere = " Where [PartCode]=" & Me.PartCode
Me.RecordSource = strMySQL & strWhere

If Me.RecordsetClone.RecordCount > 0 Then
Me.Detail.Visible = True
Else
Me.Detail.Visible = False
Me.RecordSource = ""
MsgBox "No Record Returned. Try Again!"
End If

End Sub

Saves will automatically occur when you move to a new record, or close
the form...still, most users intuitively look for 'save' buttons, and
it's always nice to have an 'undo' button on forms as well...

I've whipped up a little sample database showing you how to do this
kinda thing (w/data entry mode, too ;)

shoot me an email if you'd like me to forward it to you...
hth
brett

ja************@hotmail.com (James) wrote in message news:<7a**************************@posting.google. com>...
Hi,

I have a combo box(cmboParts) and the row source is a part name. Once
a part is selected I have button(cmdOrderAdd) to add data about this
part to a sub-form(frmParts_Ordered). 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_Click()
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

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.