URGENT!! Problem sending data to field

[James]

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

[Chuck Van Den Corput]

Re: URGENT!! Problem sending data to field

On 9 Apr 2004 02:44:20 -0700, jammie_runnies@ hotmail.com (James)
wrote:
[color=blue]
>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[/color]

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.colum n(1) (column numbering starts at 0).

Chuck

[Don Leverton]

Re: URGENT!! Problem sending data to field

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.Name@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" <jammie_runnies @hotmail.com> wrote in message
news:7a6254bf.0 404090144.43c28 e46@posting.goo gle.com...[color=blue]
> 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[/color]

[brett valjalo]

Re: URGENT!! Problem sending data to field

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(Cance l As Integer)

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

End Sub

Private Sub cboParts_AfterU pdate()

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

If Me.RecordsetClo ne.RecordCount > 0 Then
Me.Detail.Visib le = True
Else
Me.Detail.Visib le = 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

jammie_runnies@ hotmail.com (James) wrote in message news:<7a6254bf. 0404090144.43c2 8e46@posting.go ogle.com>...[color=blue]
> 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[/color]