Split Form / Phone Log

[kyle1234567]

Hello, everyone:

I have a split form titled, frmPhoneLog. On the top half, I have multiple textboxs that allow the user to input information about a single phone encounter with a client (i.e., the client calls us, we call them, etc.) There are fields like CallerName, CallDate, CallSubject, CallNotes, etc. There also three command buttons: cmdAddNew, cmdSaveEdits, and cmdClear. On the bottom of the split form is the characteristic table. Here is what I would like to do:

1. Allow the user to log an unlimited number of phone encounters with the client by inputting information in the upper part of the window and having it show up in the lower part. cmdAddNew will create a new row each time different information is entered.

2. Allow the user to select any of the phone encounters listed in the datasheet, for that information to re-populate into the textboxes described above for the purpose of editing/changes, and then for cmdSaveEdits to rewrite the table data accordingly.

Now, here is the rub... this entire form view needs to be specific to a single primary key. In other words, the purpose of the form is ONLY to log phone encounters with client John Smith (one person). Client Patty Sue has a different primary key and, thus, a different set of phone encounters. How can I link MULTIPLE records in a split form datasheet like this to ONE primary key and have it show up accordingly?

Lastly, I will need to work this out in the backend, too. Can Access automatically create table fields based on how many phone encounter records have been created? Can you point me to some resource for learning more about this "auto-population" or "cascade creation?"

Thanks for your help. I have learned so much about Access using this forum.

 

[pbaldy]

I'm not sure a split form is appropriate, and I think you have the design wrong. The normalized approach would be a clients table and an encounters table. That table would be related one-to-many to the clients table on your client ID, and would have a record for each encounter. That enables you to have an unlimited number of encounters without adding fields. The normal setup to represent this relationship would be a form bound to the client table and a subform bound to the encounters table. Master/child links keep them in sync with each other.

 

[kyle1234567]

Hi, Paul:

Thank you so much for this quick reply. I followed your instructions and it's working perfectly now! MUCH better than what I had before, which didn't even work. I appreciate your time and expertise.

-Kyle

 

[pbaldy]

Happy to help Kyle! I think you'll be much happier with that design in the long run.

 

[kyle1234567]

Paul (or others),

With the above advice, I have successfully created a Phone Encounter Log. The normalized design is as follows:

Components:
-frmPhoneLog
-subfrmPhoneLog
-tblPhoneLog

subfrmPhoneLog is nested inside frmPhoneLog. It looks like a table with the following fields: CallDate, CallerName, ReceivedBy, Subject, Notes. frmPhoneLog has a command button that allows a user to add a new encounter. A separate frmPhoneLogAddNew writes the data to tblPhoneLog using CASEID as an indexed field that allows duplicates (i.e., for an unlimited number of encounters per CASEID). subfrmPhoneLog and frmPhoneLog use master/child links to only display the phone encounters from a particular CASEID.

All of the above works great!

Now, my only remaining problem is how to edit an encounter. Also, extensive notes are usually taken about an encounter that are easier to view in a separate frmPhoneLogViewEdit than in the subform table. My idea is to use the subfrmPhoneLog's double-click event to trigger an opening of frmPhoneLogViewEdit. Below is my current code for this idea. Because multiple encounters exist for each CASEID, I set-up an autonumber field in the table called ENCOUNTERID so each call can be uniquely identified.

Private Sub CALLDATE_DblClick(Cancel As Integer)
Dim StrForm As String, strCriteria As String
If IsNull(Me!ENCOUNTERID) Then
Dim msg, style, title, response
msg = ("No details exist for this encounter")
style = vbOKOnly + vbExclamation
title = "Error"
response = MsgBox(msg, style, title)

Else
Forms!PhoneLog.Visible = False
DoCmd.OpenForm "PhoneLogViewEdit", , , "EncounterID = " & Me.ENCOUNTERID
End If
End Sub

Alas, while frmPhoneLogViewEdit opens just fine, none of the data from that subform's table row appears in the fields... they are empty. This form is bound to tblPhoneLog and all the textboxes use the appropriate fields as their control source. What could be the problem?

Thanks for all your help!

 

[pbaldy]

That form's Data Entry property isn't set to Yes, is it? If not, can you post the db here? The code you've posted looks correct.

 

[kyle1234567]

That was it, Paul! The data entry property was set to Yes. I just changed it and all the data instantly appeared! Very exciting. Thanks for your help, as always. This issue is now completely resolved.

 

[pbaldy]

No problemo!