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

[boardinhank]

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.

set RSnew = conn1.execute("select * from leads where whenadded = #" & CurrentDateTime & "#")    
 
##------------------------------------- Break -------------------------------------------------
 
Dim new_fname, new_lname, new_phone, new_lev, new_status
 
new_fname = RSnew.Fields.Item("fname").Value
new_lname = RSnew.Fields.Item("lname").Value
new_phone = RSnew.Fields.Item("phone").Value
new_lev = RSnew.Fields.Item("lev").Value
new_lead = RSnew.Fields.Item("lead_id").Value
new_status = "New"
 
      #------------------------------ 
  Dim Conn2, dbPath2, match_1, match_2
  dbPath2 = "c:\inetpub\ftproot\brhenrie\bluecollar\dizniz\services.mdb"
  Set Conn2 = Server.CreateObject("ADODB.Connection")
  Conn2.Open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & dbPath2
 
set RSm1 = conn2.execute("select * from service where lead = new_lead")    
 
If empty do this
 
else
 
match_1 = RSm1.Fields.Item("contractor_id").Value
 
 
    #--------------------------------------- 
 
set RSm2 = conn2.execute("select * from service where lead = match_1")    
 
if empty do this.. 
 
else
 
match_2 = RSm2.Fields.Item("contractor_id").Value
 
    #-----------------------------------------
 
  Dim Conn3, dbPath3, ass_company, ass_pri_phone, ass_contractor_id
  dbPath3 = "c:\inetpub\ftproot\brhenrie\bluecollar\dizniz\contractor.mdb"
  Set Conn3 = Server.CreateObject("ADODB.Connection")
  Conn3.Open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & dbPath3
 
set RSm3 = conn3.execute("select * from contractor where contractor_id = match_2 & status = "active" ")    
 
ass_company = RSm3.Fields.Item("company").Value
ass_pri_phone = RSm3.Fields.Item("pri_phone").Value
ass_contractor_id = RSm3.Fields.Item("contractor_id").Value
 
##------------------------------------------------- Break -------------------------------------------------------
 
CurrentDateTime = Now
    conn.Execute "insert into service (company, pri_phone, contractor_id, lead, lev, status, fname, lname, phone, whenadded) " _
    & "values (" _
    & "'" & ass_company & "', " _
    & "'" & ass_pri_phone & "', " _
    & "'" & ass_contractor_id & "', " _
    & "'" & new_lead & "', " _
    & "'" & new_lev & "', " _
    & "'" & new_status & "', " _
    & "'" & new_fname & "', " _
    & "'" & new_lname & "', " _
    & "'" & new_phone & "', " _
    & "'" & CurrentDateTime & "')"
 
    response.redirect "Thank_you.asp"