Good programming practice wrt lookup tables?

[Buakaw]

Hi
I have a table, tblPaymentDetails storing details of customer transactions. One of these fields is the payment method, which is a number, called fkPaymentMethod.

This number is actually a foreign key of another table called tblPaymentMethod.

The fields of tblPaymentMethod are an Autonumber PrimaryKey, and a text description.



In tblPaymentMethod we have:
1 Cash
2 Cheque
3 Automated Bank Transfer

and so on.

So tblPaymentDetails.fkPaymentMethod will store numbers, 1, 2 and 3.

In my programming, I want to create an enum that mirrors the entries in tblPaymentMethod. E.g.

Enum PaymentType
Cash = 1
Cheque
Automated_Bank_Transfer
End Enum

Problem is, this is not going to be sync'ed with the entries in tblPaymentMethod should tblPaymentMethod ever get updated.

How can I solve this programming problem if I want to maintain some kind of integrity in my data so that someone's not going to update blPaymentMethod, and the Enum is not updated?

Thanks.

[HiTechCoach]

Curious, why would you want or need to create an enum that mirrors the entries in tblPaymentMethod?

[Buakaw]

To enable and disable other controls accordingly, for one.

E.g. if Me.PaymentMethod = Check_Type, enable some controls, apend read value into table, else append something else into table.

[orange]

Why can't you do what you want (enable/disable or even hide controls)
using the 1,2, 3 ?

That's the PK in your tblPaymentMethod, so what's the issue.
You can comment your code accordingly, if that's your concern.

[HiTechCoach]

I find it best to store this additional data in the tblPaymentMethod table so it is totally data driven not hard coded. Example: a flag field for check type. This way you can have multiple records of the same type and still will know how to handle them. This way it is not depended an the primary key value.

For the UI I use a combo box to select the a Payment Method. I include the additional columns to hold the "flag" data in the row source. If you do not want the user to see them then you can set the column width to 0 to hide them.


So the Enum is done by using the table. This avoids duplication of data.

Why can't you do what you want (enable/disable or even hide controls)
using the 1,2, 3 ?

That's the PK in your tblPaymentMethod, so what's the issue.
You can comment your code accordingly, if that's your concern.

This issue I have with that method is that you are hard coding the logic and not making it data/table driven.

IMHO, hardcoding is not a "Good programming practice"

[Buakaw]

I find it best to store this additional data in the tblPaymentMethod table so it is totally data driven not hard coded. Example: a flag field for check type. This way you can have multiple records of the same type and still will know how to handle them. This way it is not depended an the primary key value.

For the UI I use a combo box to select the a Payment Method. I include the additional columns to hold the "flag" data in the row source. If you do not want the user to see them then you can set the column width to 0 to hide them.


So the Enum is done by using the table. This avoids duplication of data.

Coach, is it possible to provide an example so I understand what you mean? THanks.

[HiTechCoach]

I could made a sample database to show you the technique. It would probably benefit you a lot more if you were to post a sample of your database. Include at least the form, tables, etc that you working with. Remove any confidential data and include some sample data. Will you do that?

[Buakaw]

Thanks for the help. Would it be possible to tell me briefly how it's done?

[HiTechCoach]

For the UI I normally use a combo box to select the a Payment Method. I include the additional columns to hold the "flag" data in the row source. If you do not want the user to see them then you can set the column width to 0 to hide them.

I will assume the combo box has three columns in the row source:

Name: cboPaymentMethod
1) index(0) - Primary key - bound - width 0
2) Index(1) - Description - seen by user
3) index (2) - Type Flag

In the combo box's After Update event and the form's on current event you could do something like this:

Select Case Me.cboPaymentMethod.Column(2) 
   Case "Check"
      Me.txtControlName.Visible = True
...   

End Select

[Buakaw]

Thanks Coach, I'll give it a try!

[HiTechCoach]

You're welcome.

Let me know if you need any more assistance.