ACCESS FEED: Re: [MS_AccessPros] Checklist with a twist 1

Senin, 11 Januari 2016

Re: [MS_AccessPros] Checklist with a twist 1 - different input types

Hi Adam,

I use a table corresponding to the Access data type number (DatTypN) for stuff like this:

**a_DataTypes**
DatTypN	DatTypS	DatTypeS	DatTypN_
1	YN	Boolean	4
2	Byt	Byte	4
3	Int	Integer	4
4	Lng	Long	4
5	Cur	Currency	5
6	Sgl	Single	7
7	Dbl	Double	7
8	Date	Date/Time	8
9	Bin	Binary
10	Text	ShortText	10
11	Ole	OLE Binary BMP
12	Memo	LongText	10
15	Guid	Guid
16	BigInt	Big Integer
17	BinVar	Binary Variable
18	TxtFix	Fixed Text	10
19	oNum	Numeric ODBC
20	oDec	Decimal ODBC
21	oFloat	Float ODBC
22	oTime	Time ODBC
23	oDatTim	DateTime ODBC
101	Att	Attachment
102	mvByt	Byte MV
103	mvInt	Integer MV
104	mvLng	Long Integer MV
105	mvSgl	Single MV
106	mvDbl	Double MV
107	mvGuid	GUID MV
108	mvDec	Decimal MV
109	mvTxt	Text MV

a_DataTypes

DatTypN_ is for rolling up data types

I would rethink OptionA and OptionB. The RecordSource should report all answers in a calculated text field (that can't be edited) so they line up. Then, an edit button that will turn on the appropriate control (checkbox, combo, textbox, etc) to fill out with the value with validation for the proper data type. Generally, I store:
- yn (can also use Long to store this)
- long integer
- currency
- text

warm regards,
crystal

~ have an awesome day ~

On 1/11/2016 11:37 AM, runuphillracing@yahoo.com [MS_Access_Professionals] wrote:

This is the first of two checklist questions, which I'll post in separate topics.

With this topic, the checklist responses may can have different types of input: date, Y-N, short text. Also, some of the responses may determine the next steps. I'm looking for feedback on my approach, and/or advice on a better one.

Assume the standard checklist vertical structure: tblChecklistMaster (key ChecklistID), tblChecklistTasks (TaskID), tblChecklistData (ChecklistID, TaskID). When a new project is added to tblChecklistMaster, the tasks are copied from tblChecklistTasks to tblChecklistData.

For different input types, my idea is to add a field to tblChecklistTasks, let's call it InputType, long integer; e.g., 1=date, 2=Y-N, 3=text). On tblChecklistData, make the field Done as short text. On the subform (based on tblChecklistData, main form based on tblChecklistMaster), have code that will do different things, when the Done text box is clicked, depending on InputType:

Enter today's date

Toggle between Y-N

Allow the input of text.

For next steps, my idea is to add another table, tblChecklistTaskOptions, with 3 fields: TaskID (link to tblChecklistTasks), OptionA, OptionB. TaskID would be the originating ID of the Task that determines the next set of tasks. OptionA would be the TaskIDs if Y is chosen, OptionB if N is chosen. There can be multiple entries of next steps for each originating TaskID. Initially, I wouldn't populate tblChecklistData with any task that is in OptionAorB. When a choice is made on the originating task, I would then populate tblChecklistData with the next step tasks from OptionAorB. If the choice were changed, I could delete one set of next steps and replace it with another. tblChecklistTasks has the field SortOrder, so the next step tasks would go in the right place when the form is refreshed.

I know how to do the coding of this. I'm looking to see if my approach/idea is off base.

Thanks

Adam