Radio buttons, queries and forms. Oh my! 1

[sdollen]

I have a database where we input contacts we have with clients. Currently there is a field titled "contactedby" and we have sometimes input a visit by multiple staff as "Cindy, Don" or "Cindy & Don" but, I want to be consistent and want to be able to query how many visits done by each staff. Soooooo.... how can I use radio buttons on a form to select each staff member present at a meeting and have that interface with the tblcontact?

Any help is GREATLY appreciated.

 

[SBendBuckeye]

Nice catch. What you are referring to is something called normalization. You do not want redundant data in the same table nor do you want combined data in the same table.

I would suggest checkboxes instead of radio buttons since radio buttons imply a mutually exclusive scenario. You could have 1 to many checkboxes selected on the form. This could be implemented a couple different ways.

What is your background? Are you comfortable in a programming environment doing VBA or not? How many tables are involved here? How many staff members do you have? Are you familiar with table relationships?

Please repost with some additional information and we'll try to help!

Good LucK!

 

[sdollen]

Well, don't laugh - my background is actually finance (I'm a retirement advisor) and I'm self taught on tech stuff like access, html code, etc.. I'm a tech wannabe with only half the brain capacity! So, Visual basic would be outta the window for me.

I have two tables - One is tblmembers (I have names, regions, retirement plan type) and tblcontacts - contactID (pk), memberID (fk), contacttype(phone, personal visit), contactedby, contactdate, notes.

We have three others besides myself that visit our clients (which are actually cities/towns that provide retirement for their employees).

I'm familiar with table relationships and normalization... but, like I mentioned I'm self taught.. I've learned alot about access doing this project, but see where taking some classes would really be beneficial.

Thanks for your help!

 

[FancyPrairie]

What you are asking becomes a little complicated. Plus, as staff is added or leave you will have to modify your forms, etc. to include or exclude them.

If I understand your table layout correctly, you have one table that includes fields such as Client ID, Client Name, ContactedBy, and other client information. And that the field ContactedBy can contain 1 or more names.

If this is the case, in my opinion, your table is not setup properly. You need at least 2 additional tables. The first table (tblStaff) would contain the names of your staff, a unique ID assigned to each staff member (i.e. autonumber) and any other pertanent information regarding them. The second table would contain the contacts. Therefore, your 3 tables would look something like this:

tblClient
lngClientID (unique)
strNameOfClient
Whatever else you need to know about the client

tblStaff
lngStaffID (unique)
strFirstName
strLastName
strMI
strFullName (optional - Last, First MI)
ysnActive (Yes (true) = Active; No (false) = inactive)
Whatever else you need to know about the staff

tblContact
lngClientID (pointer to record in tblClient)
lngStaffID (pointer to record in tblStaff)
dtmContact (Date/Time client was contacted)
memComments
Whatever else you need to know about the contact

Breaking your tables up this way provides several advantages.
1. You can now display a dropdown list of staff members (via tblStaff)
2. A client can have 0 to many contacts
3. It will be much easier generating reports based on staff contacts, etc.

Now, as far as defining when staff members have contacted clients, set up a subform whose controlsource is set to tblContact. The controlsource of the main form would be set to tblClient. The main form and subform would be linked by the fields lngClientID.

Note that I defined a field for staff as ysnActive. I did this so you can identify which people work at the company and those that no longer do. The reason for this is because you don't want to delete staff from the table tblStaff just because the leave your company. If you do delete them, then you will not have an accurate record of contacts made to the client.

 

[sdollen]

FP -

Good point on the employee table. It never crossed my mind to make that a seperate table. That would definitely make the task easier. I appreciate the quick answer and pointing me in the right direction. I LOVE this forum!

 

[sdollen]

Okay... I'm working on redsigning my tables as FancyPrairie suggested. But, I think I'm back to my original question though. How can I make a form so that I can pick multiple staff names when more than one travels together to visit a client? With a combo-box I would have to derive every combo possible, but with a checkbox or radio button, I could make the combo selection. But, how can a form translate that into a table? Would I have to have mutliple fields on the table and label them "staff 1", staff 2, etc?

 

[FancyPrairie]

In my previous post I suggested having a main form and subform. The main form's control source would reference the tblClient. The subform's control source would reference the tblContact. The subform would be linked to the main form via the client ID (lngClientID).

First, I would create 2 queries.

qryClient..."Select * from tblClient;"
qryContact..."Select * from tblContact;"

Second, create 2 forms.

frmClient... Set ControlSource to qryClient
frmContact... Set ControlSource to qryContact and set it to Datasheet view only.

frmClient should contain at least one text box. This ControlSource for this text box is lngClientID.

frmContact should contain a text box whose controlsource equals lngClientID. It should also contain a combobox whose controlsource equals lngContactID. The combobox RowSource should contain 2 fields: lngContactID and lngFullName (name of employee). The form should contain another text box whose controlsource equals dtmContact.

Third, open frmClient in design view and select the subform button (on the toolbar). The form you want as the subform will be frmContact. The LinkMaster and LinkChild properties of the subform control should both equal lngClientID.

Now, assume Client 1 is shown in the main form. Then the subform will show all of the employees that have made contact with Client 1 and the date/time the contact was made. To add another contact for the client, simply select the employee from the combobox and enter the date/time of the contact (this is done on a new record within the Datasheet View of the subform).

 

[SBendBuckeye]

I had a similar situation with a promotion tracking system I wrote where each individual client could potentially have any of the available promotions assigned to them.

Here is what I did and I think it could be modified for your situation. Your contacts file would be related to your individual clients. So think of each individual client contact as a line item on an invoice. Each client could have multiple invoices with multiple lines.

Using FancyPrairie's excellent work above and modifying it just slightly, your base table for contacts would be tblClient.

Then I would add one table tlbClientContact. There would be one tblClientContact for each client contact incident. The reason for doing this is that it removes tblClient from this process (you could probably leave it out without causing a problem) and allows you to have a header type record for tracking client contact. To me, it is easier to visualize this process as a header and detail type situation.

So I would split FancyPrairie's tblContact as below:

tblClientContact
lngClientContactID (unique)
lngClientID (pointer to record in tblClient)
dtmContact (Date/Time client was contacted)
memComments

tblContact
lngClientContactID (pointer to record in tblClientContact)
lngStaffID (pointer to record in tblStaff)

After splitting the tables as above, the only thing left in tblContact is a pointer to tblClientContact and a pointer to tblStaff.

Now what you can do on your client contact form is to simululate creating a checkbox array. Use as many as you need and allow plenty of room for future expansion. So you name checkboxes as chkStaff00, chkStaff01, chkStaff02, ..., chkStaff24, chkStaff25, etc.

In form load processing build your checkbox captions on the fly from tblStaff. You can select staff names and keyids into an array and built them similar to this:

For intX = 0 To UBound(StaffArray, 2)
me("chkStaff" & intX).controls(0).cation = _
StaffArray(0, intX)
me("chkStaff" & intX).tag = StaffArray(1, intx)
If intX = MaxNumberOfCheckBoxes Then
Exit For
End If
Next intX

The above code loads current staff names into the captions and saves the staff keyid in the control tag for later processing.

If UBound(StaffArray, 2) < MaxNumberOfCheckBoxes Then
For intX = UBound(StaffArray, 2) + 1 To MaxNumberOf etc
me(&quot;chkStaff&quot; & intX).Visible = false
me(&quot;chkStaff&quot; & intX).Left = 0
me(&quot;chkStaff&quot; & intX).Top = 0
Next intX
End If

The above code hides any unused checkboxes and moves them to the upper left hand corner of the form so they are out of the way in case you wish to resize the form.

Now that the form is set up, you create new ClientContact records by filling in the dates and other misc information about the contact and checking the staff who made them. In the form AfterUpdate code you spin through the controls and insert a tblContact record for each staff checked something like the following:

For Each ctl In Me.Controls
If Left$(ctl.Name, 8) = &quot;chkStaff&quot; Then
If ctl.Value Then
Call InsertContactRecord(ctl.tag)
End If
End If
Next ctl

Make sure you verify checkboxes with .value if you have Access97. Some versions have a bug that doesn't allow Access to close cleanly if you reference an object directly. If you want to allow edits you would need to have logic to determine if it changed so that you could delete the tblContact record if it was unchecked or do nothing if it was still checked.

As an aside, you can save yourself a little work on the front end by creating one checkbox and then copying it on your form as many times as you need it and then using a utility function to do some of the dirty work for you.

When I set mine up I did something like the following after I did massive copies. Open the form in design mode and then run something like this:

On Error Resume Next
For Each ctl In Forms(&quot;YourFormName&quot.Controls
If ctl.ContrlType = acCheckBox then
ctl.Name = &quot;chkStaff&quot; & intX
ctl.Controls(0).Caption = ctl.Name
ctl.TabIndex = intTabIndex + 1
intX = intX + 1
End If
intTabIndex = ctl.TabIndex
If Err.Number > 0 Then
Err.Clear 'In case control can't receive focus
End If
Next ctl

Sorry this is so long. It makes for a pretty slick User Interface once you get it up and running.

Good Luck and Happy New Year!