Not allowing unbound subform

[aytee111]

Access is automatically inserting fields into the Link properties of my subform. This has to be unbound as it is populated with different data depending on user selections.



The code - Me.Invoices_subfrm.LinkMasterFields = "" - sends the code into the BeforeUpdate routine. Totally messed up. I have recreated the database to no avail.

Does anyone have any idea what Access is doing and how to force this to be an unbound subform?

PS - the reason it is a subform and not a listbox is that the user needs to select which records they want from this list and which ones they don't. The only way I have been able to figure out how to do this is to make it a subform with a temp table. If you have a better idea please feel free to share it!

[ItsMe]

I do not understand the association with BeforeUpdate. Also, if the form within the Subform control is unbound, you should adjust the link properties in the subform control via the properties sheet. Link Master and link Child should be empty.

[aytee111]

That entry into the BeforeUpdate is totally messed up, makes no sense to me either.

The subform itself is bound to a table. The Master/Child fields are blank in design view. Yet when I display those properties in the code they are populated. The form opens with the fields blank but as soon as the subform is touched in code those fields are auto-populated.

Debug.Print Me!Invoices_subfrm.LinkMasterFields - this shows a value which Access put there automatically
'Me.Invoices_subfrm.LinkMasterFields = "" - this has been commented out as this sends the code into BeforeUpdate
Me.Invoices_subfrm.Form.RecordSource = TableName - this is repeat code, it has been set previously
Me.Invoices_subfrm.Requery - I suspect that this is where the subform Link fields are populated

[aytee111]

PS - My database is now pared down to just the basics. All of a sudden I noticed a new query "TmprryQryFrLnkFldsCrtn0123456789"! Access is also now creating its own queries. Nowhere do I create any.

[ItsMe]

Perhaps you executed some code that adjusted the Link Field properties. Obviously, I am not privy to everything that has transpired. Having said that, if temp queries are being created that you can see after closing and opening the database, I would suspect corruption and rebuild you DB by importing select objects to a new file. Aside from that, I do not see any explanation here that would warrant using VBA to adjust the link field properties, even if it is to set them to = "". So I would remove this code and also remove any code from BeforeUpdate events. Just store the code in Notepad until after the creation of the new DB. Get your new DB in a state that you can compile the VBA and do a compact and repair.

[aytee111]

It does sound like corruption, doesn't it. I have done all the above, rebooted my computer, etc etc.

Thanks for taking your time to help.

[ItsMe]

Then you are copying over the problem to your new DB. Try bringing over the tables and doing a compact and repair with just the tables. Then, if you do not have any parameterized query objects, bring over you queries and do a compact and repair. Open up the queries and make sure they are functioning. If some of your queries are parameterized, hard code them for the initial migration and compact and repair/test. AFTER THIS, MAKE A COPY OF SOMETHING THAT WORKS WITH QUERIES AND TABLES ONLY. As you bring over additional objects, you may break your Access file and you want a place that you can start again without completely starting over.

[aytee111]

OK, starting from scratch, good idea.

Created DB with only the tables.
Made a form bound to Employees table.
Made a subform bound to Time table with link fields empty.

As I navigate through the employees the subform is automatically requerying itself with no help from VBA. Access obviously doesn't allow the link properties to be blank. Ever heard of this? Can you think of any way around this? All I need to do is allow the user to select some of the records being displayed.

[June7]

Yes, the Link properties can be blank.

If you want to provide db for analysis, follow instructions at bottom of my post.

[ItsMe]

I guess June7 is going to take over from here.

[aytee111]

Not really solved, just closing this thread. Access kept overwriting my Link Master and Child fields with its own choice. At first I left it blank but Access inserted the key from the main form, then I put in a generic item (customer) but again as soon as I requeried the subform Access overwrote my hard values. I gave up and removed the key field from the subform! That seemed to solve it as Access now had no way to screw it up for me, I fooled it!