how can i fix this?

[eighthundred]

hi there, i'm new to the board and access... got this question, donno how to fix it. anyone, pls help.

i create this db pretty much using wizards and following books...

i have 2 table: tb1 and tb2
tb1 has fields A, B1, C, D; where A is the key and B1 is a combo box that select value from tb2. tb1 now have 100+ records.
tb2 has fields B1, B2, B3, B4; B1 is the key. got 4 records only.
then i got the query (myQuery) using wizard that pick everything (except B1@tb1) from the 2 table. so the query looks like:
A, B1(from tb2), B2, B3, B4, C, D
since the relationship, when i select a value in the combo box(B1) B2-B4 will auto filled.
then i got "myForm" based on myQuery... and here comes the problem.

i DO want the auto filled feature (it's 1 of the reason why i built the db like this). if users select value from the combo box B1, it's ok. since B2-B4 are just textbox, i could lock or disable them. but how can i avoid user change the value of B1 in the form? if i lock/disable B1, the combobox (and the auto filled feature) will dead too. but if i leave it like this, user could actually type in something that change tb2.

another bigger problem is: the 4 records in tb2 will sooner be changed (update), but the exitig 100+ records @ myQuery/tb1 will not need to change. how can i avoid it too? someone told me i'll need to remake the db since it's a design problem... is it true?

 

[neileg]

Not sure if I understand, but here goes.

If you have set up the combo at table level, would remove it and set it up in the form.

If you set the combo up in the form and make sure the limit to list property is set to yes, the user will be able to select any existing value in table2 but not change it.

If you are going to change the entries in tabl2 but want to keep the old values for your existing records, you have a couple of choices that I can think of. There may be more.
1> Change your design so that you store B2, B3, B4 in table1. Any later changes to table2 will not affect table1
2> Leave the existing values in table2. Add new records to table2 that have the new values. Add an extra field that marks the records in table2 as active or inactive. In your combo box, filter out the records for table2 that are inactive.

 

[eighthundred]

1> Change your design so that you store B2, B3, B4 in table1. Any later changes to table2 will not affect table1.

if i keep B2-B4 in table1 too, how can i set the auto fill then?

 

[eighthundred]

thks pat, i did exactly what u suggested. and i solve my first problem by "limit list" on the combo box, B1. so user can only pick values from the drop down list. but i'm still stuck on my second "bigger" problem...

 

[eighthundred]

pat,
B2 is the user name, ie. to indicate who added the record. since some employee are leaving the company, there's no reason to keep the old employee name in the list and make all old added record to belong to the new employee...