472,341 Members | 2,118 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,341 software developers and data experts.

URGENT!! Problem sending data to field

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
3 1708
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: UrgeOverkill | last post by:
I'm having a problem sending data from a socket server. The server side reports that it has sent 4845 bytes but the client reports only 1448 bytes...
1
by: Daniel | last post by:
after opening socket, sending data then closing socket 3000 times i get "Only one usage of each socket address" what am i doing wrong? is there...
4
by: David | last post by:
I'm wondering if python is capable of fairly precise timing and also sending data out the parallel port. For example ; making a 7.5 KHz square...
1
by: ccr | last post by:
Please view in a text editor so that the columnar text lines up. I used Terminal 9pt font to compose this post and copied/pasted to my newsreader...
4
by: yaron | last post by:
Hi, I have a problem when sending data over TCP socket from c# client to java server. the connection established ok, but i can't send data from...
0
by: vladimir.plotnikov | last post by:
Hello! I have problem: I have IPB forum installed. After search in IPB (search takes about 3-4 seconds for post table about 300 000 records)...
1
by: Charlie | last post by:
Hi: I need a way to test my TcpListner, but not sure what applications are sending data to tcp ports. How about Email or Instant Messenger? If...
5
by: Navin Mishra | last post by:
Hi, In load test of our .NET 2.0 socket application on Win2003 server, we are seeing sometimes WSEWOULDBLOCK error when sending data to clients....
3
by: Mateo | last post by:
(As.Net 1.1 framework!) Hi! So, I have problem as described in subject. Here is source code:
1
by: markla | last post by:
Hi, Can someone help me understand why for the code below, when added as a "FieldTemplate" in Dynamic Data, and rendered for a field, does not...
0
by: concettolabs | last post by:
In today's business world, businesses are increasingly turning to PowerApps to develop custom business applications. PowerApps is a powerful tool...
0
by: teenabhardwaj | last post by:
How would one discover a valid source for learning news, comfort, and help for engineering designs? Covering through piles of books takes a lot of...
0
by: CD Tom | last post by:
This only shows up in access runtime. When a user select a report from my report menu when they close the report they get a menu I've called Add-ins...
0
jalbright99669
by: jalbright99669 | last post by:
Am having a bit of a time with URL Rewrite. I need to incorporate http to https redirect with a reverse proxy. I have the URL Rewrite rules made...
0
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was...
0
by: Matthew3360 | last post by:
Hi there. I have been struggling to find out how to use a variable as my location in my header redirect function. Here is my code. ...
2
by: Matthew3360 | last post by:
Hi, I have a python app that i want to be able to get variables from a php page on my webserver. My python app is on my computer. How would I make it...
0
by: AndyPSV | last post by:
HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable...
0
by: Arjunsri | last post by:
I have a Redshift database that I need to use as an import data source. I have configured the DSN connection using the server, port, database, and...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.