Query checkbox

[hardyd44]

Right, searched everywhere on this one.

I am currently developing a DB, and have split it, this is very much a work in progress so there are multiple versions of the DB as I always create a copy when starting a new development stage.

Now to the problem I have created some new functions and tided up some code in the latest version, however an existing function that was working fine (and has not been touched) no longer works. If I open a earlier version these functions work fine. the underlying component that no longer works is a select query that has a checkbox that is changed via a form - this checkbox now behaves as if it is locked i.e. selecting and trying to change its value just creats a "bing" noise but will not change. I have tried via the form and direct through the query old version - fine, new version - nada, This has happened on 2 queries/forms that are very simiular in structure but linked to different tables.

I have tried copying the objects from the old to the new, deleting them and copying them, all to no avail. I also tried compact and repair numerous times - has anyone any idea's

cheers Dean

 

[hardyd44]

Thought I would add a bit more info - took a copy of the last working version then added the new stuff by copying across - the old functions stopped working so it looks like something from the new stuff is interfering with this - back on it again tommrow, will update if i find the probllem

Dean

 

[hardyd44]

Right, a bit more investigation has been done - nothing to do with the query, one of the underlying tables has been amended and a data type in it changed, this field is not called in the query - but seems to be the only thing that is different between the versions. will keep investigating and post an update

 

[CBrighton]

Have you checked that the VBA references in the new database match the ones in the old one?

 

[hardyd44]

Solved - really wierd behaviour though, it seems our friend autocorrect was at work - don't know how though ! always switch it off, one of the queries was referncing a table that does not exist anymore but still worked in old version but not in new. replacing the table in the query with the correct one now works - but got to be honest not quite sure whats going on - can anyone through any light on table alias names because in think that was the problem.

Cheers

Dean

 

[hardyd44]

This error has re- occured, biut what fixed it last time is not working this time - I look at the working "live" version - and the table that seems to be at the bottom of the problems is TblAddressBook, I have updated an old version of this table with a new version in the later "faulty" DB - the weird thing is the table is named as TblAddressBook_1 in the WORKING queries - this is a split DB and I wondered if access does someweird behaviour if you rename a table then paste a new version of this table with old name so it now appears as the table. this is the only thing I can think is causing the problem - anyone with any ideas.

 

[vbaInet]

What about the Locked property of the checkbox?

 

[hardyd44]

no its not that - the underlying query won't allow you to change the value of the check box neither - I am convinced it is something to do with changing a table in the BE and the way I have done it - thanks anyway

 

[vbaInet]

Your query is mostly likely read-only. Here are reasons why:

http://allenbrowne.com/ser-61.html

Can you edit other fields in the query?

 

[hardyd44]

No, I can't edit any other fields - had a look at that link nothing there seems to match - No try this for size - I have a update query hung on the side of this query if the user clicks a button - the relevant check box on all records will be set to yes - this still works !!!! - slowly but surely going mad with this one

 

[vbaInet]

Are you sure the:

1. Recordset Type property of the query is set to Dynaset?
2. The query is not a Union query?

 

[hardyd44]

yes - checked both of them, dynaset yes - and not a union query - this query and others like it worked fine until I change one of the tables - still think it is something going on there that has made this and the others read only just don't know what though. I do appreciate your help but think the query becoming read only is a red herring if I be honest

 

[vbaInet]

Alrighty. Can I have a look at your db?

 

[hardyd44]

Right, I have changed the backend link to the old table after I had added/changed the new fields - everything works ok now, so going to plan B, will change this in the live BE, then add the new records using an add query then a loop in VB adding one record at a time the the ref no increase with each record, this I can do. But has anyone had experiences when changing BE links that causes things to screw up - would just like to know what to avoid next (besides not doing it!!!)

cheers

 

[vbaInet]

You're best approach will be to send out an outage notification, block out connections to the backend and propagate your changes.

 

[hardyd44]

cheers think it may be a saturday morning job :-(
but this seems to be the best way to go
thanks for your help anyway

Dean

 

[vbaInet]

Good luck!

 

[hardyd44]

Thanks

will be making plenty of backups before I start just in case

 

[vbaInet]

You know the drill!