VERY strange behaviour

[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!

 

[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".

 

[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.