More Information about Lookup Tables

[BennyWong]

Hello All,
I am using Microsoft Access 2000. I am currently working on creating a Mailing List Database. My question is regarding Lookup Tables. Could someone explain alittle more about what it is and how it can be used. The current use that I have implemented is on comboboxes where the user select the items to populate the database table field. Is there another useage for Lookup Tables? Thanks for any advice or suggestion in advance.

 

[LittleSmudge]

That ( are you describe ) is exactly what is meant by lookup tables.

Any table can be a lookup table - it doesn't mean anything about it's structure or anything else - it is just a comment about what USE you are putting it to.

If you have a field with a limited set of acceptable values and you want to restrict the user to entering just one of the valid options then the lookup table is the way to go.

Normalisation Rules suggest that you should have an index number ( Primary Key ) in the lookup table and just store that into the data table that is bound to your form, However, if the 'data' is a single field of text then you could consider using the combo box to actually store the data itself. This would then leave you free to provide the user with the option of selecting from the list OR entering their own unique value if appropriate.

Other options are numerous - to fit your need, but you are certainly on the right track.


'ope-that-'elps.


G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[BennyWong]

Hello LittleSmudge,
Thanks for your response. I appreciate your answer to confirm what I am doing is correct. I have been using combo boxes to allow the user to select the item to populate the table. Then after the data is entered the user can make changes in the future the the data has changed by updating it in the data entry edit mode where the user find the record and change the selection.
I do have a question regarding multi-select listbox doing data entry and then editing the listbox entry if the data has changed. As I understand it, I originally had a subform where the user can enter multiple items in the subform because it is the many side of the relationship. It seems to work fine however Management wants the user to be more accurate and productive so I had to use a listbox and had to set it as simple to get the multi-select feature. My question is in data entry mode the following code is working fine:

Private Sub cmdSave_Click()
Dim db As Database
Dim rs As Recordset
Dim ctl As Control
Dim itm As Variant

Set db = CurrentDb
Set rs = db.OpenRecordset("tblContactType&quot
Set ctl = Me.contactid

For Each itm In ctl.ItemsSelected
rs.AddNew
rs!ContactType = ctl.ItemData(itm)
rs!Client_id = Me.Client_id
rs.Update
Next itm

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

rs.close
end sub

However, I can't get it to work when the user decides to update the data in the data edit mode when the items needs to be either deleted or updated by selecting other items from the list. Do you have any advice or suggestions. Maybe my technique or understanding on the data edit mode is not correct? Thanks for your time and response in advance.

 

[LittleSmudge]

Deleting a selection means deleting an entry in tblContactType

Adding another entry means adding JUST the new entry to tblContactType.
If you try to run the above code during an Add you'll get a problem because you are trying to add duplicates of all the existing entries as well as the new one.

You could do a delete all entries then follow that by the above code to 'add all' ( including the new one )

Or you could modify the code to identify which is the new entry and then just add that new one


'ope-that-elps.


G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.