MadPhilly
Posts: 19
|
| Posted: 11/21/2007, 3:11 AM |
|
Hi all!
I have a form that populates a SQL DB. One of the fields needs to be the result of a kind of lookup function. I need to query a different table in the DB based on the values of three fields in the form. I looked at the session variable example in the Help manual which looks like it might do it but I'm not having a great deal of luck. I've never touched ASP before and am no programmer by any stretch of the imagination.
Here is the code I wrote:
Function Production_Entry_getCycleTime_OnClick(Sender) 'Production_Entry_getCycleTime_OnClick @23-E079FA0F
Dim Connection1
If Production_Entry_getCycleTime_OnClick = True Then
Set Connection1 = New clsDBConnection1
Connection1.Open
PartNo = Production_Entry.ProductNo.Value
Cell = Production_Entry.CellNo.Value
Op = Production_Entry.Operation.Value
Session("User_Name") = CCDLookUp("R18","C001_UNIDATA_PLANNINGL_NF","ASSY = PartNo" AND "R14 = Cell" AND substring(C001_UNIDATA_PLANNINGL_NF.R0,1,3) = "Op")
Connection1.Close
Set Connection1 = Nothing
End if
End Function
Of course it's just ripped from the help manual and changed for my needs. Basically I need to pick out a cycle time from the table named C001_UNI........ if:
the value of CellNo matches field R14
the value of ProductNo matches field ASSY
and the value of Operation matches the first 3 digits of field R0
Any help would be greatly appreciated.
Thanks in advance
Andy 
|
 |
 |
Oper
Posts: 1195
|
| Posted: 11/21/2007, 5:15 PM |
|
let see,
you have few field on a record form let said 5 Fields, but one of those field to update is a Math Calc using anotehr database?
if that what you trying to said:
define the field No. 5 as Hidden
on before update database or before insert do this:
FORM.yourhiddencalcfiedl.datasource.value=CCDLookUp("R18","C001_UNIDATA_PLANNINGL_NF","ASSY = PartNo" AND "R14 = Cell" AND substring(C001_UNIDATA_PLANNINGL_NF.R0,1,3) = "Op")
_________________
____________________________
http://www.7bz.com (Free CMS,CRM Developed in CCS)
http://www.PremiumWebTemplate.com
Affiliation Web Site Templates
Please do backup first |
|
|
|
MadPhilly
Posts: 19
|
| Posted: 11/22/2007, 1:09 AM |
|
This value needs to be displayed so that the user can either accept it by submitting the form, or changing the value then submitting the form.
The form has the following fields.
[Shift] [OpNo] [Date] [CellNo] [PartNo] [Operation] [CycleTime] [Qty]
CycleTime is a value from another table in the database based on the values of CellNo, PartNo and Operation. In the other table the equivalent field names are:
CellNo - R14
PartNo - ASSY
Operation - R0 (first 3 digits)
So the value should be found using CCDLookUp("R18","C001_UNIDATA_PLANNINGL_NF","ASSY = PartNo" AND "R14 = Cell" AND substring(C001_UNIDATA_PLANNINGL_NF.R0,1,3) = "Op")
Is it possible to do what I want? Or do the values need to be submitted to the database first?
|
|
|
|
Oper
Posts: 1195
|
| Posted: 11/23/2007, 7:18 PM |
|
use the ccdlookup onbefore show field.
_________________
____________________________
http://www.7bz.com (Free CMS,CRM Developed in CCS)
http://www.PremiumWebTemplate.com
Affiliation Web Site Templates
Please do backup first |
|
|
|
MadPhilly
Posts: 19
|
| Posted: 11/29/2007, 2:03 AM |
|
Thanks for your efforts Oper. I can't get this to work though.
When you enter any values into a form, are those values actually known before you submit the form? I'm thinking that my ccdlookup won't work because the values I'm using to do the lookup are in the current form. As you see in the previous mail I assigned the values of the controls to variables and used the variables in the ccdlookup.
Example:
Cell No. [ cell00 ] << -- Listbox
Product [ abc123 ] << -- Listbox
Op No. [ 010 ] << -- Listbox
I've used an 'After Execute Select' and declared a variable for the value like this: form.cellno.value = cell
I've done the same for the three controls.
Then my ccdlookup looks like this:
ccdlookup(R18,KPI_Cycle_Times,R14 = cell AND ASSY = Product AND Operation = Operation, DBConnection1)
What are your thoughts?
I appreciate your efforts on helping me with this glitch.
Andy
|
|
|
|
|
