Lookup Fields

[Tallbloke]

MANY years ago my first ever job was trainee Access DB developer.

Back then I was taught to use lookup fields in tables.

Today I know this is baaaad.

Is there an easy way to remove look up fileds from tables, fix the forms and update the data?

Is there a bset practice for putting this right.

Perfect example is a look up field for "location" that has 36 options. So if I remove the lookup field in the table all the locations become numbers across 6000 records...

 

[pr2-eugin]

Here is a link to Bob's site to remove lookup fields from tables..

So basically the type of the field is Number and the Number that you see is the ID of the table it normally looks into.. To show the Actual location in the Form/Report/Query you can perform a simple Join.. and select the Location name instead of ID..

 

[Tallbloke]

Yeah...got that bit.

My issue was then having 1000's of records with numbers in rather than words.

So there was a combo box on the table that has Bournemouth (yes, I'm from there too!) which gets changed to 12 whne I convert the combo to a text box.

 

[pr2-eugin]

My issue was then having 1000's of records with numbers in rather than words.

Yes as I mentioned earlier.. The type of the Field would have been Number, and it would have been bound to Column 1 (which was the ID) but would have had its width 0cm;3.578cm which would never have stored the text.. It has always been storing only the number.. see the image for me to explain my point..

That is exactly what you need to do as well.. that will avoid human error or mistyping data.. also easier for you to make JOIN operations.. Does this answer satisfy your question or do you need more explanation?

.... Bournemouth (yes, I'm from there too!) ..

Wow.. that's good to hear..

 

[Tallbloke]

I think I'm getting it...

So..

I have TblLocations & TblCustomers

I remove the lookup from TableCustomers

What should I do next, do I remove the relationship that exists already between TblLocations & TblCustomers?

On the form do I create a new unbound combo and get it to look up data from TblLocations and save the the data to TblCustomers.Location?

 

[pr2-eugin]

What should I do next, do I remove the relationship that exists already between TblLocations & TblCustomers?

No there is no need to delete any relation ships.. let them be.. this is because if you create a query based on this two table it will automatically create the JOIN for you..

On the form do I create a new unbound combo and get it to look up data from TblLocations and save the the data to TblCustomers.Location?

Yes that is exactly what you need to do.. So the Data will be from the tblLocation, but the Data will be saved to the Field on the tblCustomers..

 

[Tallbloke]

Ok...but if I want to display the location it's no longer in the combo because the combo is unbound, so do I need to have the field and the combo on the form?

 

[pr2-eugin]

No.. You need to have only one combo box.. that will be bound to the field on the table, however will feed data from the tblLocations..

 

[Tallbloke]

Ok...but when I add this combo it is blank for records that already exist?

 

[Tallbloke]

Ah...now that's weird.

The IDS stored in TblCustomer are different when I use the new Combo box.

For example... Bournemouth is now 44 (which is right based on TblLocations) for a test entry made with the new combo but 2 for an entry made yesterday.

However, if I look at a copy of the database where I haven't removed the look up field Bournemouth is 44 on the old data.

So, to summarise when I remove the lookup the IDs go funny in TblCustomer

 

[pr2-eugin]

That is a bit weird.. Look at the sample I have put.. on the Form view I have two combo box.. One is just a field (ORIGINAL_AGENT) that is from the table (which is a lookup field) the other is a Combo box that I created which is bound to the table field(AGENT_ID) but has its data from the Agents table.. If still in trouble.. could you post a stripped down verison of your DB.??

 

[Tallbloke]

Relating to combo box.
I have one with a criteria on a subform to limit the user's options based on the main form.
Problem is the field is blank until I refresh the subform by hitting F5.
Can a subform be refreshed from the main form... what's the syntax?

 

[pr2-eugin]

Is your Main form and Subform not linked?? because if they are linked then it will automatically perform a refresh for you.. however to manually refresh a subform you have to use..

Forms![COLOR=Blue]firstsubform[/COLOR][B].Form[/B]!Refresh

blue bit needs to change.. for more information on how to access subform from main form look here.

 

[Tallbloke]

They are linked... and they work fine until i put in the criteria to limit the options in the drop down

 

[pr2-eugin]

I am sorry I should have been more clear.. the control that you have as criteria should be linked to the another control on the subform.. Thus when you perform a change it will refresh itself..

However you can refresh using the method above.. Just use the After Update property of the form control that has the criteria now..

 

[Tallbloke]

This worked...

Me!FrmVenue.Form.Refresh

Now I just want it to fire when I use the navigation buttons as well....

 

[pr2-eugin]

Good.. Hope i was not a utter waste there.. lol..