Porter, Mark
MPorter at acsalaska.com
Fri Feb 6 14:09:02 CST 2004
I utilize a similar structure, and I've seen large scale List of Value
tables which work well.
Value group
Value name
Display value
Value <property_1> (property can be visiblility, order, active/inactive,
etc.)
Value <property_n>
I've seen this get really complex, the Siebel system I work with has an LOV
table with about 2 dozen attributes including display language. In Access
my structure is pretty simple, mainly the top three on that list.
This can also be used to translate values from a business value (High, Med,
Low) to something more usable (5, 3, 1) or measurable.
Mark
> -----Original Message-----
> From: jeffrey.demulling at usbank.com
> [mailto:jeffrey.demulling at usbank.com]
> Sent: Friday, February 06, 2004 10:45 AM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] How to do an Admin Table
>
>
>
> I have a table in my databases that is called tblProperty and
> it has the
> following structure:
>
> - PropertyInternalNumber (Autonumber)
> -PropertyName (Text:255)
> -PropertyValue (Text:255)
>
> Then I have functions that can retrieve and update the values
> as needed.
>
> Function GetPropertyValue(myPropertyName As String,
> myconnection As String)
> As String
> Dim con As New ADODB.Connection
> Dim rs As New ADODB.Recordset
> Dim cmdtext As String
> Dim mymessage As String
>
> On Error GoTo GetPropertyValueError
>
> con = myconnection
> con.Open
>
> cmdtext = "SELECT tblProperty.PropertyValue"
> cmdtext = cmdtext & " FROM tblProperty"
> cmdtext = cmdtext & " WHERE tblProperty.PropertyName = '" &
> myPropertyName
> & "';"
>
> rs.Open cmdtext, con
>
> If Not rs.EOF And Not rs.BOF Then 'Property is present
> rs.MoveFirst
> GetPropertyValue = rs.Fields("PropertyValue")
> con.Close
> Else
> con.Close
> GetPropertyValue = "Unknown"
> End If
>
> Exit Function
>
> GetPropertyValueError:
> GetPropertyValue = "Unknown"
> End Function
>
> Function SetPropertyValue(myPropertyName As String, mypropertyvalue As
> String, myconnection As String)
> Dim con As New ADODB.Connection
> Dim rs As New ADODB.Recordset
> Dim cmdtext As String
> Dim mymessage As String
>
> On Error GoTo GetPropertyValueError
>
> con = myconnection
> con.Open
>
> cmdtext = "UPDATE"
> cmdtext = cmdtext & " tblProperty"
> cmdtext = cmdtext & " SET"
> cmdtext = cmdtext & " tblProperty.PropertyValue =" & Chr(34) &
> mypropertyvalue & Chr(34)
> cmdtext = cmdtext & " WHERE"
> cmdtext = cmdtext & " tblProperty.PropertyName=" & Chr(34) &
> myPropertyName
> & Chr(34) & ";"
>
> rs.Open cmdtext, con
>
> con.Close
>
> Exit Function
>
> GetPropertyValueError:
> SetPropertyValue = "Unknown"
> End Function
>
> I can store an custom "property" and change it. For example
> I have the
> version of the program, the program name, last time interest was
> caluculated, etc..
>
> I believe I saw the idea orginally from John Colby.
>
> -Jeff
>
>
>
>
>
> "John Clark"
>
>
> <John.Clark at niagaracounty. To:
> accessd at databaseadvisors.com
>
> com> cc:
>
>
> Sent by: Subject:
> [AccessD] How to do an Admin Table
>
> accessd-bounces at databasead
>
>
> visors.com
>
>
>
>
>
>
>
>
> 02/06/2004 01:24 PM
>
>
> Please respond to "Access
>
>
> Developers discussion and
>
>
> problem solving"
>
>
>
>
>
>
>
>
>
>
>
>
> Forgive me, if this is an easy one, but I've even asked someone that
> teaches the Microsoft Access certification classes, and they
> didn't know
> how to do this either--or maybe I wasn't clear, when I asked.
>
> I have had many programs where it would be useful to have an "admin"
> table. This table would contain one single record with information
> referenced in other parts of the program. For instance, I
> have a program
> that figures monthly mileage for our county legislators, but it is
> currently setup so that the rates are hard-coded and I have to alter
> them everytime they are changed. I would like the user to alter this
> themselves.
>
> I have also thought about using this for programs--maybe in their
> splash screens--to list the current directors names, and
> other info that
> may or may not change in the future.
>
> Any ideas how?
>
> Thanks ahead of time...because I know if it can be done, somebody on
> this list knows how to do it!!!
>
> John W Clark
>
> John W. Clark
> Computer Programmer
> Niagara County
> Central Data Processing
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
>
>
>
>
>
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
***********************************************************************************
6/2/2004
This transmittal may contain confidential information intended solely for
the addressee. If you are not the intended recipient, you are hereby
notified that you have received this transmittal in error; any review,
dissemination, distribution or copying of this transmittal is strictly
prohibited. If you have received this communication in error, please notify
us immediately by reply or by telephone (collect at 907-564-1000) and ask to
speak with the message sender. In addition, please immediately delete this
message and all attachments. Thank you. ACS