Fill in checkbox discriptions based on combobox selection

[sparkbyte]

I have just inherited an Access application that no less than 3 other people have made major changes to and I am trying to clean it up and move the data over to a MS SQL backend.

So far I have modified the column names to conform to standards (no column names like [Delete]) (Not joking).

Modified all the forms, reports and queries to reflect the new column names.

Converted all the queries that would not migrate via the upsizing wizard.

I also have built a SSIS package to import, verify and remove duplicate records.

Now for the real question.
There are 2 forms I would like to change (One add Case, and one Update Case). What I would like to be able to do is have a combo box select what processing track is being used and based on that selection 4 to 7 check box descriptions get filled in.

So say John Smith calls in and requests XYZ documents, these documents are of XXX type so they would be a Track 2 request, which means that there are 7 checkboxes that could be selected depending on the type of information they need out of the requested documents. When the Call Rep. selects 'Track 2' in this case, the descriptions for the checkboxes get filled in from on a lookup table based on the 'track 2' selection in the combo box.

Any help on how to accomplish this would be greatly appreciated.


Thanks

John Fuhrman
faq329-6766
thread329-1334328
thread329-1424438

 

[dhookom]

As long as you are changing the tables, you should consider normalizing your table. If I understand, you have yes/no fields for tracking which types of information are required. IMO, these fields should create individual records in a related table.

Since you didn't provide any actual table and field names I can't be specific. However assuming you have
[tt][blue]
tblCases
==========
CaseID
TrackNum
InfoTypeA
InfoTypeB
InfoTypeC
...
InfoTypeG
[/blue][/tt]
I would change the structure to:
[tt][blue]
tblCases
==========
CaseID
TrackNum

tblInfoTypes
============
InfoTypeID
InfoTypeTitle

tblCasesInfoTypes
==========
CaseID
InfoTypeID
Status
[/blue][/tt]
This structure allows you to add any number of info types without having to change structures, forms, reports, code, ... In addition, you can add some functionality for storing additional information about the status of each info type for each case.

You could create another table that store the standard info types for each track number. This would allow you to easily append the appropriate info types to the tblCasesInfoTypes table based on the track number.


Duane
Hook'D on Access
MS Access MVP

 

[sparkbyte]

I understand the need for normalizing the data, but for this problem I am not understanding how this will enable me to accomplish what I want.

My problem is that there are many other AccessDB's that have table links to this one and if I change the data structure to much I will break LOTS of daily/monthly reports. So I was hoping to accomplish this without making big changes to the main data tables.



Thanks

John Fuhrman
faq329-6766
thread329-1334328
thread329-1424438

 

[dhookom]

If you can't or won't change the structure then I would create a table of Track Numbers with fields matching the check boxes. You could then select the track number from a combo box of track numbers that also contains the columns for the check boxes. Use the after update to set the values of the check boxes on the form.

Duane
Hook'D on Access
MS Access MVP

 

[sparkbyte]

OK, I am pretty sure I understand what you are saying about the lookup table and using the after update. But how can I have the description of the checkboxes updated based on the lookup like on the Access Switchboard menus?

Example:

Track 1
Photos_Only Prints_Only Both_Prints_And_Photos

Track 2
Appl_Status Aff_Support Bond
Photos Prints Bio_Data

Track 3
File_Review Removal_Doc Prints
Photos Prints Research

So when the user chooses "Track 1" they see only the checkboxes for that track.
If they select track 2 they see those choices.

Eventually I want it to also clear any previous selections made when the Track is changed.
Note: This is when creating a new Case and not updating. If a client calls again and
requests information from the same file a new case is created.
The Updates to the case would only be for adding notes and time being worked on.
So once the case has been created the track and information being retrieved
cannot be changed. Another request is required for tracking.


Thanks

John Fuhrman
faq329-6766
thread329-1334328
thread329-1424438

 

[dhookom]

Can you provide your significant table and field names? I'm confused what you mean by "description". A check box yes/no field stores -1 or 0 (possibly null).

Duane
Hook'D on Access
MS Access MVP

 

[sparkbyte]

Here are the columns in the main activity table.
I am still trying to see if it will be possible to normalize the data, so for the time being I am having to keep things as is. There are to many other DB's pulling data from this table to make major changes without approvals.

	[ID] [int] IDENTITY(1,1) NOT NULL,
	[LoginID] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[ContactName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[ContactPhone] [nvarchar](35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[ContactFax] [nvarchar](35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[OfficeCode] [nvarchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[TypeOfContact] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Digitized] [bit] NOT NULL DEFAULT (0),
	[XYZCode] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[ProgramName] [nvarchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[XfileNumber] [nvarchar](12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[ReasonForCall] [nvarchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[ReceivedFile] [datetime] NULL,
	[StatusOfActivity] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[StartTimeDate] [datetime] NULL,
	[LoginIDClosed] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[ClosedTimeDate] [datetime] NULL,
	[Complex] [bit] NOT NULL DEFAULT (0),
	[Noncomplex] [bit] NOT NULL DEFAULT (0),

	[Track1] [bit] NOT NULL DEFAULT (0),
	[Track2] [bit] NOT NULL DEFAULT (0),
	[Track3] [bit] NOT NULL DEFAULT (0),

	[FileReviewButton] [bit] NOT NULL DEFAULT (0),
	[TrkCodeStatusButton] [bit] NOT NULL DEFAULT (0),
	[ApplStatusButton] [bit] NOT NULL DEFAULT (0),
	[RemovalDocsButton] [bit] NOT NULL DEFAULT (0),
	[PhotoOnlyButton] [bit] NOT NULL DEFAULT (0),
	[PrintOnlyButton] [bit] NOT NULL DEFAULT (0),
	[PhotosButton] [bit] NOT NULL DEFAULT (0),
	[PrintsButton] [bit] NOT NULL DEFAULT (0),
	[PhotosAndPrintsButton] [bit] NOT NULL DEFAULT (0),
	[NatzCertButton] [bit] NOT NULL DEFAULT (0),
	[XffSupportButton] [bit] NOT NULL DEFAULT (0),
	[BondButton] [bit] NOT NULL DEFAULT (0),
	[FAXButton] [bit] NOT NULL DEFAULT (0),
	[ccMailButton] [bit] NOT NULL DEFAULT (0),
	[OtherButton] [bit] NOT NULL DEFAULT (0),
	[phonebutton] [bit] NOT NULL DEFAULT (0),
	[Photos3Button] [bit] NOT NULL DEFAULT (0),
	[Prints3Button] [bit] NOT NULL DEFAULT (0),

	[UpdatedCIS] [bit] NOT NULL DEFAULT (0),
	[XFileCreate] [bit] NOT NULL DEFAULT (0),
	[BioDataButton] [bit] NOT NULL DEFAULT (0),
	[ResearchButton] [bit] NOT NULL DEFAULT (0),
	[TrkCodeFileButton] [bit] NOT NULL DEFAULT (0),
	[FTFButton] [bit] NOT NULL DEFAULT (0),
	[ResponseMemo] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[LastDate] [datetime] NULL,
	[Project] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Memo] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Login1] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Start1] [datetime] NULL,
	[End1] [datetime] NULL,
	[Login2] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Start2] [datetime] NULL,
	[End2] [datetime] NULL,
	[Login3] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Start3] [datetime] NULL,
	[End3] [datetime] NULL,
	[StaffingSheetToggle] [bit] NOT NULL DEFAULT (0),
	[SEIPrint] [datetime] NULL,
	[StartTimeOnly] [datetime] NULL,
	[StartDateOnly] [datetime] NULL,
	[ClosedTimeOnly] [datetime] NULL,
	[DateOnly] [datetime] NULL,

Here is the start of a lookup table.

	[TrackID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
	[TrackNumber] [tinyint] NOT NULL,
	[TrackDiscription] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[TrackCode] [nvarchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

Sample Data for lookup table

TrackID      Track Number      [highlight] Description[/highlight]	       TrackCode
1               1               Photos Olny             IL0100
2               1               Prints Only             IL0100
3               1               Natz Cert               IL0100
4               1               Photos & Prints Only    IL0100
5               2               Photos                  IL0200
6               2               Bond                    IL0200
7               2               Prints                  IL0200
8               2               Bio Data                IL0200
9               2               Appl Status             IL0200
10              2               Aff. Support            IL0200
11              2               Alien Status            IL0200
12              3               Removal Docs            IL0300
13              3               Photos                  IL0300
14              3               Prints                  IL0300
15              3               File Retrieve           IL0300
16              3               Research                IL0300
17              3               Other (Specify in Memo) IL0300

Hope this helps.

Thanks

John Fuhrman
faq329-6766
thread329-1334328
thread329-1424438

 

[dhookom]

I would expect each Description value should relate back to a column in the main activity table. I don't know which subset of fields from the main activity table are represented by the descriptions.

I would have a table of each unique description and with it's relationship to a field in the main activity table. Then, replace the Description field in the lookup table with a foreign key to the unique descriptions table. The lookup table should not need both the track number and track code since one depends on the other.

I would then create a crosstab from the lookup table that has the track as the Row Heading and activity as the column heading. The value would count the records. Use the crosstab as the row source for a combo box that selects the track. The combo box would have multiple columns so there would be a 1 or 0 for each of the different activities. Use the columns of the combo box to set the values of the y/n fields in the main table.

Duane
Hook'D on Access
MS Access MVP