Relationship advice please

[Lol999]

Could do with a bit of advice on this one please. I have attached the relationship layout for my database and I'm struggling to understand why certain fields automatically fill on a form and others don't.

For instance, I have a form with a subform. In the subform is the table Tbl_Display, this is just a device to display to the operator the items they have scanned before the data is copied to Tbl_Location permanently.
I have a field on the subform titled Description, which appears in the table Tbl_Tools but NOT in the Tbl_Display.
When I enter into the field Part_No in Tbl_Display it automatically inserts the relative description into the text box Description. Gravy.

I'm surprised then when I do exactly the same thing to Tbl_Find, which has the same purpose as Tbl_Display but will also run a query or two.
Why then is the textbox for Description not automatically updating once I enter into Part_No?
All I keep getting is #name?

Can anyone point out the idiot mistake I've made on this one please?

 

[The_Doc_Man]

#name usually means something needs to be qualified due to either (a) ambiguity - same name in two potential tables or (b) named object is spelled wrong.

When you have formal relationships, Access is happy to automagically link stuff for you but there IS this little issue... you have to spell everything correctly and have to select from potentially ambiguous choices.

 

[Lol999]

I've since rationalised the structure a bit and deleted one of the tables, Tbl_Find.
It was unnecessary since I can do all I need to with Tbl_Display.
Cells are still not updating though.
Any ideas please?

 

[The_Doc_Man]

Usually, fields only update automatically if (a) the fields are bound - i.e. have a .ControlSource that is a field in the form's .RecordSource and (b) the name of the control matches the name of the field. There is a reason that the form wizard does this naming for you when you build a bound form through that wizard.

 

[Frothingslosh]

Actually, Doc, b is incorrect. The only requirement for an update is that the control be bound to an updateable field in an updateable recordset.

I regularly deal with fields that don't have the same name as their control source - my predecessors here didn't have a Hungarian fetish; they turned it into a bloody religion (you should SEE some of the variable names I have to deal with) - and have never once run into an issue because the control's name didn't match its bound field.

 

[Lol999]

To be honest it is the need to display what has been entered in a continuous list that has necessitated the formation of extra tables. The relationship between Tools and Location is managed nicely with just 3 tables.
I was using a table to display scanned items, inserting the list into the relevant table, then wiping the display table.
If someone could suggest another way of displaying data other than a table then it would be very helpful.

Cheers, Lol

 

[The_Doc_Man]

Frothy, you might well be right, now. It used to be different and I'm an old fogey on some of that stuff. I have gotten into the habit of using the wizards to build my scaffolding and then come back to fill it in and customize it. Doing it that way will essentially guarantee that the control names match the field names.

 

[Frothingslosh]

Frothy, you might well be right, now. It used to be different and I'm an old fogey on some of that stuff. I have gotten into the habit of using the wizards to build my scaffolding and then come back to fill it in and customize it. Doing it that way will essentially guarantee that the control names match the field names.

The problem is that a few times I've run into issues where a control's name was the same as the field name, and when I tried to pass the control's value to a variable or query, it passed the CONTROL, not the value.

Yes, that can be avoided with proper use of the Value property and ! vs . , but it's easier on the beginners if they're just taught to not have a control named the same thing as a field (ESPECIALLY if they used a reserved word as the field name...).

 

[Frothingslosh]

And in regards to Access, you can't be much more of an old fogey than I am. My first application developed was a project management system in 1995 with Access 2.

In fact, IIRC, the manual back then strongly recommended using Hungarian.

 

[Lol999]

Mods can we close this thread please?

I've changed tack so it is now irrelevant.

many thanks, Lol

 

[Frothingslosh]

Threads don't get closed unless something goes really wrong somewhere or someone is blatantly violating site rules.

The easiest way to terminate a thread is to just not post in it.

 

[Lol999]

Thanks I didn't know.