Lookup Field Alternative

[bbrazeau]

I have attached a simple 2 table DB. I have implemented a combobox instead of a lookup field. I was wondering if someone knowledgeable in Access would look at it and critique. I do this because as I searched the subject I found many experienced users talking about the alternative to lookup fields, but not one concrete example. I would like to make sure when I go this route, I don't make any errors. Please, notice that I did not create any relationship between the 2 tables, just used the combobox wizard as many have suggested. Would I need to set-up a relationship to perform this correctly?,Any other thoughts?

Just realized I posted an .accdb instead of .mdb so here's the .mdb
Anybody got any input??

[June7]

Table2 has an autonumber primary key field which is saved as foreign key in Table1 field called Table2. However, field Table2 is a text field and should be a number so that tables could be joined in query.

Primary and foreign key fields must be same type (autonumber/number or number/number or text/text or date/date). Cannot join an autonumber PK to a text FK. Your combobox will allow saving the autonumber value into text field but could not build relationship or query joins on these two fields.

Relationships don't need to be built for db to work but relationships can be effective to enforce data integrity. Try to establish a relationship on the PK/FK fields as they are and see what happens.

[bbrazeau]

Hi June, Thank you for the response. You did some excellent critiquing of the DB, but in essence what I was looking for was someone who is a proponent of Alternative methods to "lookup fields"<(the evil ones) to post a version for all who use lookup fields to be able to learn from. I use lookup fields myself, in the following manner; I create what will be the child table with an "auto number" primary key, and in the parent table I create a field with the lookup wizard that has the ID field of the child and usually the 2nd field which is a Descriptive "text" field from the child.
This method sets the one-many relationships and allows setting of all the other options associated with it "referential integrity" etc...
I have seen some draw backs to this method, usually in the places where the proponents of Alternatives to lookup fields suggest, but I haven't seen one of them post an example of the alternate method. Describing it by saying to set the field with a combobox from the form doesn't address all the nuances that arise in implimenting it. A simple, 2 table, 5 field example would provide a basic template for all who ask or have asked this question.
Should I take another stab at it, post and wait for more instruction?

[June7]

I'm really not sure what you are aiming for. The only 'evil' in regard to lookup in table is lookup with an alias. Otherwise, I don't have issue with them but I just don't bother building any lookups in tables. I just build comboboxes on forms, whether or not an alias is involved.

[bbrazeau]

Could you provide an example DB

[June7]

No reason for me to duplicate what's already been done. There are already plenty of posted examples of comboboxes that 'lookup' their RowSource from a table, with or without alias (think I have at least once provided db in response to posted issue). The db you posted in this thread is an example of lookup with an alias. Just need to fix the field type.