469,934 Members | 2,151 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,934 developers. It's quick & easy.

Recordset that loops 3 times and checks 2 other tables for the record it found

I am interested on how best to accomplish this task.

I have 3 tables that will be utilized in the matching and then one for an insert function if all the matches are successful

I start with taking form fields with a job number and zipcode, I use the job_number in a select statement from table 1 to obtain a client_id number.

once i have that client_id from the job_number match I use the client_id in my select statment to find where client_id = (the one i found) and zipcode = (form field)

once I have validated that that client_id has a record in table 2 that matches the zipcode i use the client_id to check if the client_id is active if it is active then I
select everything about the client from table 3 and insert the information into table 4.

ha... how about those apples. anyway it looks kinda like this.. not finished but wondering if i should go about this completely different.

Expand|Select|Wrap|Line Numbers
  1. set RSnew = conn1.execute("select * from leads where whenadded = #" & CurrentDateTime & "#")    
  2.  
  3. ##------------------------------------- Break -------------------------------------------------
  4.  
  5. Dim new_fname, new_lname, new_phone, new_lev, new_status
  6.  
  7. new_fname = RSnew.Fields.Item("fname").Value
  8. new_lname = RSnew.Fields.Item("lname").Value
  9. new_phone = RSnew.Fields.Item("phone").Value
  10. new_lev = RSnew.Fields.Item("lev").Value
  11. new_lead = RSnew.Fields.Item("lead_id").Value
  12. new_status = "New"
  13.  
  14.       #------------------------------ 
  15.   Dim Conn2, dbPath2, match_1, match_2
  16.   dbPath2 = "c:\inetpub\ftproot\brhenrie\bluecollar\dizniz\services.mdb"
  17.   Set Conn2 = Server.CreateObject("ADODB.Connection")
  18.   Conn2.Open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & dbPath2
  19.  
  20. set RSm1 = conn2.execute("select * from service where lead = new_lead")    
  21.  
  22. If empty do this
  23.  
  24. else
  25.  
  26. match_1 = RSm1.Fields.Item("contractor_id").Value
  27.  
  28.  
  29.     #--------------------------------------- 
  30.  
  31. set RSm2 = conn2.execute("select * from service where lead = match_1")    
  32.  
  33. if empty do this.. 
  34.  
  35. else
  36.  
  37. match_2 = RSm2.Fields.Item("contractor_id").Value
  38.  
  39.     #-----------------------------------------
  40.  
  41.   Dim Conn3, dbPath3, ass_company, ass_pri_phone, ass_contractor_id
  42.   dbPath3 = "c:\inetpub\ftproot\brhenrie\bluecollar\dizniz\contractor.mdb"
  43.   Set Conn3 = Server.CreateObject("ADODB.Connection")
  44.   Conn3.Open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & dbPath3
  45.  
  46. set RSm3 = conn3.execute("select * from contractor where contractor_id = match_2 & status = "active" ")    
  47.  
  48. ass_company = RSm3.Fields.Item("company").Value
  49. ass_pri_phone = RSm3.Fields.Item("pri_phone").Value
  50. ass_contractor_id = RSm3.Fields.Item("contractor_id").Value
  51.  
  52. ##------------------------------------------------- Break -------------------------------------------------------
  53.  
  54. CurrentDateTime = Now
  55.     conn.Execute "insert into service (company, pri_phone, contractor_id, lead, lev, status, fname, lname, phone, whenadded) " _
  56.     & "values (" _
  57.     & "'" & ass_company & "', " _
  58.     & "'" & ass_pri_phone & "', " _
  59.     & "'" & ass_contractor_id & "', " _
  60.     & "'" & new_lead & "', " _
  61.     & "'" & new_lev & "', " _
  62.     & "'" & new_status & "', " _
  63.     & "'" & new_fname & "', " _
  64.     & "'" & new_lname & "', " _
  65.     & "'" & new_phone & "', " _
  66.     & "'" & CurrentDateTime & "')"
  67.  
  68.     response.redirect "Thank_you.asp"
Oct 4 '07 #1
0 940

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

9 posts views Thread by Dom Boyce | last post: by
22 posts views Thread by Gerry Abbott | last post: by
12 posts views Thread by (Pete Cresswell) | last post: by
1 post views Thread by Ray Holtz | last post: by
1 post views Thread by Jason Lepack | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.