Mark Brown's Blog

Sorry for the delay in getting this updated. I haven't had a lot of time lately.

 My last post was about my confusion with what looked to be a very easy topic. Writing one form, or better yet two forms on a page to multiple tables within a Access Database. I was a bit confused, but with the help of a friend who is experienced with this stuff, and looking over code snippets, I got it figured out.

Here's what I was trying to do. I've got a form where you would add a customer. That form also has a notes section. Another form on the site will allow you to edit the customer and add notes to it. So for any given customer, I needed a way to have multiple notes attached and displayed. So, here's what I did. In my db, MS Access Db in this case, I created another table call custnotes, with 3 fields, the notesID (primary key), notes, and the customerID. I then created a relationship between custmerID field in my  customer table, and the customerID field within my custnotes table. That was the easy part, or so it seemed.

So, my next goal was to get the form to write to the multiple tables within the database. First major hurddle I ran up against was, "How do I tell the database which record this note belongs to?" or better yet, "how do I get the form to "link " them as it's being inserted into the database" Well, based on the simple recommendation from my friend Mike, here's what I did.

The form page is just a typical form page, nothing special about it, execpt that I made the notes field a seperate form. Seperate from all the contact information, but to the user it looks like there is only one form as there is only 1 submit button. Then, on my action page for that form, I input all the contact information using cfInsert, and here's where the real fun starts.

After all the contact information is stored, I run a cfQuery on the data that was just entered to get the customerID. Just a simple query ran to find the clientID that was just created by searching for the name and zipcode of the customer that was just entered. Once I got that customerID I was home free. Right? Wrong....LOL

I was reading about cfInsert, and found that it can only be used to input form data. Since I just got the customerID from a query, it's not form data, so how do I put it into the notes table with the note?  hmmm.  So I got to looking around and found that cfquery can insert data as well as cfinsert.  Remember, i'm a newbie, so don't laugh.  So, I wrote a cfquery to insert the notes and the customerID into the custnotes table.  

Much to my surprise, it worked like a charm.  I about fell out of my chair.  I start cheering for myself.  My wife thought I lost my mind.

Anyway.  For my edit customer screen, it works on the same principal, but I get the customerID from the URL Variable passed when I drill down into the customer i want to edit, and on the edit page I've split out my notes into a seperate form, incase that's all the user wants to edit. I've still got a fair amount of work to do to secure it all, but it seems to work fine.

I haven't had time to clean the code up to put on here, but if you want it email me and let me know.

One note to all this.  Upon further research, and a hint from a friend, I could use CFinsert for this, by storing the clientid as a form variable using a cfset.  something like <cfset form.customerid="#customerid#">  I haven't tried it , but it sounds like it would work.

I'm probably missing something here, and will update this as soon as it hits me.

Thanks for reading and looking forward to you all's imput,

Mark