VERY strange behaviour (1 Viewer)

[Isaac]

Did you actually try Wayne's suggestion?

 

[mounty76]

To be honest I didn't understand it

 

[Isaac]

Follow it literally. Find the hidden sys tables and open them to view the values in their connection strings.
The connection strings will be written in plain english (to some extent) which may or may not , upon reading them, catch your attention as to something that definitely seems amiss.

like a connection string

table;access;PathThatDoesNotExist;something;something

 

[Gasman]

If there was anything wrong with the connection strings, surely the queries would not show the correct data?

 

[mounty76]

Not sure what I was looking for but didn't see anything out of the ordinary.....never knew there were hidden tables!

 

[Pat Hartman]

Recommending that you remove the table level lookup is not the same as removing the form level lookup. As you will see, the form is using a combo to select the Component ID

I rebuilt the two tables and created a form that uses them. When you select a component ID for a new record, the description fields fill automagically. But those fields are locked because I don't want the user to be able to change them on this form. They should only be changeable on the form that updates the Equipment table (which I didn't build). I added some validation to the form so you can see how that might be done. The validation will prevent a record that violates MY edit rules. You might have different rules but I have no way of knowing what they are.

I renamed all the fields in the tables to remove spaces and added validation rules and RI. Leaving the primary key fields named as "ID" just causes confusion since you want to be able to easily see which fields in a table are foreign keys. The way to do that is by using discipline in your naming standards.

I'm also going to include a sample db that shows a simple many-many relationship along with different types of fields to show the related data.

Many-to-many example

I added a new version on 1/16/23 so choose that one rather than the old one unless you specifically want the old one. The old zip file contains both A97 and AXP versions of the database. It uses venue booking as the example and shows the relationship from both sides. A venue main form with...

www.access-programmers.co.uk

And another that shows a simple address book with filtering options.

Address Book

Address Book with alphabet buttons, a sort option, and a different filter option as well as two reports.

www.access-programmers.co.uk

The samples have nothing to do with your project, although they may give you ideas. The point is to show you simple databases that use good practices regarding object names and coding.

 

[mounty76]

Thanks very much Pat, I'm flat out today but will def spend some time on it in the next few days. HNY everyone!

 

[GregDataReno]

2 cents worth - and I haven't checked your database - but I suggest confirming that that the names of the controls on your form that contain table data are not the same name as the field in the table. For instance, a bound text control that holds the value of the table field called "LastName" could be named "txtLastName" and not "LastName".

 

[Pat Hartman]

When the Access wizard builds a form for you, ALL bound controls are given the Name of the RecordSource field they are bound to. So, if the field in the query is LastName, the control's name property will be created as LastName. The two names will be identical. This caused a certain amount of confusion in earlier versions of Access because there was occasional confusion regarding on whether you were referring to the table column or the control itself. The current version of Access is less easily confused but old habits die hard and so I rename all the controls (not necessarily in the samples I build though) to include prefixes. that means you can always tell when reading my code whether it is referring to the control or the bound field.

However, you CANNOT reuse a bound field name as the Name property of a control it is not bound to. So, if you have a field in the RecordSource named AmtPaid, that field might be bound to a control of the same name but you cannot have a control named AmtPaid that is bound to a calculated value or to a different RecordSource field such as WeeklySalary. People don't usually make this mistake initially, they tend to make it if they are making modifications to the form and decide to change the control named AmtPaid and bound to the field from the RecordSource named AmtPaid to a calculation such as =AmtPaid * 52. This looks like it should work but it doesn't since even though AmtPaid is part of the calculation, the control is no longer bound to the field named AmtPaid, the control is now unbound because it is calculated.

 

[GregDataReno]

When the Access wizard builds a form for you, ALL bound controls are given the Name of the RecordSource field they are bound to. So, if the field in the query is LastName, the control's name property will be created as LastName. The two names will be identical. This caused a certain amount of confusion in earlier versions of Access because there was occasional confusion regarding on whether you were referring to the table column or the control itself. The current version of Access is less easily confused but old habits die hard and so I rename all the controls (not necessarily in the samples I build though) to include prefixes. that means you can always tell when reading my code whether it is referring to the control or the bound field.

However, you CANNOT reuse a bound field name as the Name property of a control it is not bound to. So, if you have a field in the RecordSource named AmtPaid, that field might be bound to a control of the same name but you cannot have a control named AmtPaid that is bound to a calculated value or to a different RecordSource field such as WeeklySalary. People don't usually make this mistake initially, they tend to make it if they are making modifications to the form and decide to change the control named AmtPaid and bound to the field from the RecordSource named AmtPaid to a calculation such as =AmtPaid * 52. This looks like it should work but it doesn't since even though AmtPaid is part of the calculation, the control is no longer bound to the field named AmtPaid, the control is now unbound because it is calculated.

Thanks for this very useful clarification - let's hope this helps the OP with their strange problem. I have a feeling it might also relate to 'bang' or 'dot' referencing of control/field names - but then I haven't looked at the OP's database - got enough client's 'novel' designs to work through.