Assign/Change Selected Default Option (1 Viewer)

MattBaldry

Self Taught, Learn from the Forums
Local time
Today, 11:52
Joined
Feb 5, 2019
Messages
292
Hi all. I am working on an application and I want to add a "Default" feature to some options. One as detailed below.

Each customer will have a list of contacts stored in a separate table. I have now added a contact type table so you can add multiple phone numbers and other contact methods to them with the ever changing list of mediums. I want to be able to assign a check box that allows the user to select the default contact method (this would normally be email).

How would I make it so that when a new default method is selected, it de-selects the old one allowing only 1 default per contact?

This would also then be used in other areas of the application using the same method.

~Matt
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:52
Joined
May 7, 2009
Messages
19,175
add the Default to a table.

tblDefaults (table)
Entity (text)
Value (text)


on the Load event of the Form, dlookup() the above table for the default value, eg:

private the_default as string

Private sub form_load()
me.txtDefault = "'" & nz(dlookup("Value", "tblDefaults", "Entity='" & Me!Name & "/txtDefault'"), "theOldDefaultHere") & "'"
the_default = me!txtDefault
end sub


when the default is changed (through the form):

private sub form_afterupdate()
the_default = me!txtDefault
end sub


when the form closes, saved back the default to the table:

private sub form_unload(cancel as integer)
if dcount("1", "tblDefaults", "Entity='" & Me!Name & "/txtDefault'") > 0 Then
currentdb.execute "update tblDefaults set Value='" & the_default & "' where Entity='" & Me!Name & "/txtDefault'"
else
currentdb.execute "insert into tbldefaults (Entity, Value) select '" & Me!Name & "/txtDefault', '" & the_default & "'"
end if
end sub
 

MattBaldry

Self Taught, Learn from the Forums
Local time
Today, 11:52
Joined
Feb 5, 2019
Messages
292
add the Default to a table.

tblDefaults (table)
Entity (text)
Value (text)


on the Load event of the Form, dlookup() the above table for the default value, eg:

private the_default as string

Private sub form_load()
me.txtDefault = "'" & nz(dlookup("Value", "tblDefaults", "Entity='" & Me!Name & "/txtDefault'"), "theOldDefaultHere") & "'"
the_default = me!txtDefault
end sub


when the default is changed (through the form):

private sub form_afterupdate()
the_default = me!txtDefault
end sub


when the form closes, saved back the default to the table:

private sub form_unload(cancel as integer)
if dcount("1", "tblDefaults", "Entity='" & Me!Name & "/txtDefault'") > 0 Then
currentdb.execute "update tblDefaults set Value='" & the_default & "' where Entity='" & Me!Name & "/txtDefault'"
else
currentdb.execute "insert into tbldefaults (Entity, Value) select '" & Me!Name & "/txtDefault', '" & the_default & "'"
end if
end sub

Thank you. I assume this means I would need a Defaults table for each different module. IE one for DefaultCustomerContactType, one for DefaultCustomerAddress and so on.

~Matt
 

MattBaldry

Self Taught, Learn from the Forums
Local time
Today, 11:52
Joined
Feb 5, 2019
Messages
292
If my above reply is the case, would it make it easier to have default contact fields in the main table and when a new one is selected from the related table, it just updates the information in the main table? Looking at the data structure of the Sage Accounts package we use, this is how they seem to do it.

~Matt
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 06:52
Joined
Feb 28, 2001
Messages
27,001
It makes sense to do it that way. After all, if you want to remember what was selected, you have to have a PLACE to remember it. So making the default contact choices either IN the main table or in an associated table would certainly be right.

However, since you have many choices for which to remember a default, I would do it with

tblDefChoice
- PersonID, foreign key from main Person table
- ChoiceID, foreign key from table that lists the types of choices one can make
- ChoiceItem, the chosen default.

which would be a one-many table relationship rather than included an arbitrary number of choices in the main table which, by implication, becomes the equivalent of a set of one-one relationships. My objection to putting it in the main table would be that if you discover or add yet another option to choose, you now have to maintain the main table. As a one-many side table, you just add a new data row.
 

MattBaldry

Self Taught, Learn from the Forums
Local time
Today, 11:52
Joined
Feb 5, 2019
Messages
292
It makes sense to do it that way. After all, if you want to remember what was selected, you have to have a PLACE to remember it. So making the default contact choices either IN the main table or in an associated table would certainly be right.

However, since you have many choices for which to remember a default, I would do it with

tblDefChoice
- PersonID, foreign key from main Person table
- ChoiceID, foreign key from table that lists the types of choices one can make
- ChoiceItem, the chosen default.

which would be a one-many table relationship rather than included an arbitrary number of choices in the main table which, by implication, becomes the equivalent of a set of one-one relationships. My objection to putting it in the main table would be that if you discover or add yet another option to choose, you now have to maintain the main table. As a one-many side table, you just add a new data row.

My concern with this would be if I have 1 table storing the defaults from many other tables, what happens if 2 primarykeys from different tables get confused as they have the same ID. Or could I have field the denotes which table the PersonID relates to?

At the moment the 2 main tables I want to be able to select a default from are CustomerAddress and CustomerContactType (IE email, phone,linked in).

So if I store these 2 default choices in 1 table, they could both have the same ID. Is it possible to have a field that defines which table the FK comes from? As this grows I will also have the same options for Suppliers, so then at least 4 different tables.

Or is my previous idea of having a default choice table for each the way to go?

~Matt
 

Micron

AWF VIP
Local time
Today, 07:52
Joined
Oct 20, 2018
Messages
3,476
The less elegant solution might be to simply have the checkbox field as you originally suggested - if you are confident that no one can gain access to the tables and the setting can only be via forms. In that case, the selection value could be linked to an option frame on the form, which can have only one result from all the options offered. The change will then be immediate in the table, and the option will be part of the form recordset, thus no extra tables, no DLookups, no code for saving. I'm not saying it is the best method. That really cannot be said one way or the other without knowing how any solution will fit in with the rest of your future plans for the db.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:52
Joined
May 7, 2009
Messages
19,175
At the moment the 2 main tables I want to be able to select a default from are CustomerAddress and CustomerContactType (IE email, phone,linked in).

So if I store these 2 default choices in 1 table, they could both have the same ID. Is it possible to have a field that defines which table the FK comes from? As this grows I will also have the same options for Suppliers, so then at least 4 different tables.
use Form for all your data entry.
one Form's Default may or may not be the same on the other form.
see my table structure, it saves the Form Name and the Control Name and its Default value.
 

Users who are viewing this thread

Top Bottom