Updating Multiple Tables in ColdFusion/MSAccess

What am I missing...I've been beating my head for the past two hours trying to get this to function properly and I think I'm missing something...anyone help????

I have one form for a user to update/add data to fields...once the form is submitted, I want it to update the information in the tables...now there are two tables that the data needs to update to.  

Here is my code for the form query (I even tried having two queries too) and the page address holds the EmpID (emp_file-awid.cfm?EmpID=1111111):

<CFIF ISDefined("EmpID")>
      <CFSET to_do="edit">
      <CFELSE>
      <CFSET to_do="add">
</CFIF>
<CFIF to_do IS "edit">
<CFQUERY datasource="myds" name="employees" blockfactor="100">
      SELECT *
      FROM tbl_employees, tbl_personal
      WHERE tbl_employees.EmployeeID=tbl_personal.EmployeeID
            AND tbl_employees.EmployeeID=tbl_employees.EmployeeID
            AND tbl_personal.EmployeeID=tbl_personal.EmployeeID
            AND tbl_employees.EmployeeID LIKE '#EmpID#%'
            AND tbl_personal.EmployeeID LIKE '#EmpID#%'
</CFQUERY>
</CFIF>

Here is my code for the form:

<FORM ACTION="empfile_update.cfm" METHOD="post">                  
<tr class="small">
      <td align="right"><b>First Name:</b></td>
      <td><INPUT TYPE="text" NAME="FirstName" size="25" class="small"
                  <CFIF to_do IS "edit">
                        VALUE="<CFOUTPUT>#employees.FirstName#</CFOUTPUT>"
                  </CFIF>
            >
      </td>
</tr>
<tr class="small">
      <td align="right"><b>MI:</b></td>
      <td><INPUT TYPE="text" NAME="MI" size="5" class="small"
                  <CFIF to_do IS "edit">
                        VALUE="<CFOUTPUT>#employees.MI#</CFOUTPUT>"
                  </CFIF>
            >
      </td>
</tr>
<tr class="small">
      <td align="right"><b>Last Name:</b></td>
      <td><INPUT TYPE="text" NAME="LastName" size="25" class="small"
                  <CFIF to_do IS "edit">
                        VALUE="<CFOUTPUT>#employees.LastName#</CFOUTPUT>"
                  </CFIF>
            >
      </td>
</tr>
<tr class="small">
      <td align="right"><b>Sort Name:</b></td>
      <td><INPUT TYPE="text" NAME="SortName" size="35" class="small"
                  <CFIF to_do IS "edit">
                        VALUE="<CFOUTPUT>#employees.SortName#</CFOUTPUT>"
                  </CFIF>
            >
      </td>
</tr>
<tr class="small">
      <td align="right"><b>Email Name:</b></td>
      <td><INPUT TYPE="text" NAME="EmailName" size="35" class="small"
                  <CFIF to_do IS "edit">
                        VALUE="<CFOUTPUT>#employees.EmailName#</CFOUTPUT>"
                  </CFIF>
            >
      </td>
</tr>
<tr class="small">
      <td align="right"><b>Extension:</b></td>
      <td><INPUT TYPE="text" NAME="Extension" size="10" class="small"
                  <CFIF to_do IS "edit">
                        VALUE="<CFOUTPUT>#employees.Extension#</CFOUTPUT>"
                  </CFIF>
            >
      </td>
</tr>
<tr class="small">
      <td align="right"><b>Wireless:</b></td>
      <td><INPUT TYPE="text" NAME="Wireless" size="15" class="small"
                  <CFIF to_do IS "edit">
                        VALUE="<CFOUTPUT>#employees.Wireless#</CFOUTPUT>"
                  </CFIF>
            >
      </td>
</tr>
<tr class="small">
      <td align="right"><b>Location:</b></td>
      <td><INPUT TYPE="text" NAME="Location" size="15" class="small"
                  <CFIF to_do IS "edit">
                        VALUE="<CFOUTPUT>#employees.Location#</CFOUTPUT>"
                  </CFIF>
            >
      </td>
</tr>
<tr class="subheadblue">
      <td colspan="2" height="35" valign="middle">Personal Information:</td>
</tr>
<tr class="small">
      <td align="right"><b>Address:</b></td>
      <td><INPUT TYPE="text" NAME="Address" size="35" class="small"
                  <CFIF to_do IS "edit">
                        VALUE="<CFOUTPUT>#employees.Address#</CFOUTPUT>"
                  </CFIF>
            >
      </td>
</tr>
<tr class="small">
      <td align="right"><b>City:</b></td>
      <td><INPUT TYPE="text" NAME="City" size="20" class="small"
                  <CFIF to_do IS "edit">
                        VALUE="<CFOUTPUT>#employees.City#</CFOUTPUT>"
                  </CFIF>
            >
      </td>
</tr>
<tr class="small">
      <td align="right"><b>State:</b></td>
      <td><INPUT TYPE="text" NAME="State" size="5" class="small"
                  <CFIF to_do IS "edit">
                        VALUE="<CFOUTPUT>#employees.State#</CFOUTPUT>"
                  </CFIF>
            >
      </td>
</tr>
<tr class="small">
      <td align="right"><b>Zip Code:</b></td>
      <td><INPUT TYPE="text" NAME="PostalCode" size="10" class="small"
                  <CFIF to_do IS "edit">
                        VALUE="<CFOUTPUT>#employees.PostalCode#</CFOUTPUT>"
                  </CFIF>
            >
      </td>
</tr>                                          
<tr class="subheadblue">
      <td colspan="2" height="35" valign="middle">Emergency Contact Information:</td>
</tr>
<tr class="small">
      <td align="right"><b>Contact Name:</b></td>
      <td><INPUT TYPE="text" NAME="ContactName" size="35" class="small"
                  <CFIF to_do IS "edit">
                        VALUE="<CFOUTPUT>#employees.EmrgcyContactName#</CFOUTPUT>"
                  </CFIF>
            >
      </td>
</tr>
<tr class="small">
      <td align="right"><b>Relation:</b></td>
      <td><INPUT TYPE="text" NAME="ContactRelation" size="20" class="small"
                  <CFIF to_do IS "edit">
                        VALUE="<CFOUTPUT>#employees.EmrgcyContactRelation#</CFOUTPUT>"
                  </CFIF>
            >
      </td>
</tr>
<tr class="small">
      <td align="right"><b>Phone:</b></td>
      <td><INPUT TYPE="text" NAME="ContactPhone" size="15" class="small"
                  <CFIF to_do IS "edit">
                        VALUE="<CFOUTPUT>#employees.EmrgcyContactPhone#</CFOUTPUT>"
                  </CFIF>
            >
      </td>
</tr>
<tr class="small">
      <td align="right"><b>Work:</b></td>
      <td><INPUT TYPE="text" NAME="ContactWork" size="15" class="small"
                  <CFIF to_do IS "edit">
                        VALUE="<CFOUTPUT>#employees.EmrgcyContactWork#</CFOUTPUT>"
                  </CFIF>
            >
      </td>
</tr>                                          
<tr class="subheadblue">
      <td colspan="2" height="35" valign="middle">Dependent Line Information:</td>
</tr>
<tr class="small">
      <td align="right"><b>Line 1:</b></td>
      <td><INPUT TYPE="text" NAME="DepWireless1" size="15" class="small"
                  <CFIF to_do IS "edit">
                        VALUE="<CFOUTPUT>#employees.DepWireless1#</CFOUTPUT>"
                  </CFIF>
            >
      </td>
</tr>
<tr class="small">
      <td align="right"><b>Line 2:</b></td>
      <td><INPUT TYPE="text" NAME="DepWireless2" size="15" class="small"
                  <CFIF to_do IS "edit">
                        VALUE="<CFOUTPUT>#employees.DepWireless2#</CFOUTPUT>"
                  </CFIF>
            >
      </td>
</tr>
<tr class="small">
      <td align="right"></td>
      <td><INPUT TYPE="hidden" NAME="EmployeeID"
                  <CFIF to_do IS "edit">
                        VALUE="<CFOUTPUT>#employees.EmployeeID#</CFOUTPUT>"
                  </CFIF>
            >
      </td>
</tr>
<tr class="small">
      <td colspan="2" align="center"><INPUT TYPE="submit" VALUE="Update Employee Record"></td>
</tr>
</FORM>

This is my code on the update page:

<!-- BEGIN Update Table 1 -->
<CFIF ISDefined("EmpID")>
      <CFSET to_do="edit">
      <CFELSE>
      <CFSET to_do="add">
</CFIF>
<CFIF to_do IS "edit">
<CFUPDATE datasource="harprod"
      TABLENAME="tbl_employees"
      FORMFIELDS="FirstName,MI,LastName,SortName,EmailName,Extension,Wireless">
</CFIF>
<CFIF to_do IS "add">
<CFUPDATE datasource="harprod"
      TABLENAME="tbl_employees"
      FORMFIELDS="FirstName,MI,LastName,SortName,EmailName,Extension,Wireless">
</CFIF>
<!-- END Update Table 1 -->
<!-- BEGIN Update Table 2 -->
<CFIF ISDefined("EmpID")>
      <CFSET to_do="edit">
      <CFELSE>
      <CFSET to_do="add">
</CFIF>
<CFIF to_do IS "edit">
<CFUPDATE datasource="harprod"
      TABLENAME="tbl_personal"
      FORMFIELDS="Address,City,State,PostalCode,HomePhone,EmrgcyContactName,EmrgcyContactRelation,EmrgcyContactPhone,EmrgcyContactWork,DepWireless1,DepWireless2">
</CFIF>
<CFIF to_do IS "add">
<CFUPDATE datasource="harprod"
      TABLENAME="tbl_personal"
      FORMFIELDS="Address,City,State,PostalCode,HomePhone,EmrgcyContactName,EmrgcyContactRelation,EmrgcyContactPhone,EmrgcyContactWork,DepWireless1,DepWireless2">
</CFIF>
<!-- END Update Table 2 -->
<!-- END Update Section -->


Start Free Trial