Hey subform - let go of my Table

0
votes

I'm having a bit of an issue with timing, I think, that's driving me batty!

  1. I have a Form with a subform with a Table as recordsource (as a datasheet)
  2. Users can edit the data in the datasheet
  3. Upon closing the form (via btnSave), I intend to run a function that exports the datasheet, with its changes to an XLS via Transfer spreadsheet.
  4. Access is complaining the table is "already in use"
  5. I have attempted to set the subform's recordsource = "" then requery
  6. Same complaint from Access about the table - but table is not open!

Any suggestions how and where to attach the export function?

Note: The export function works just fine under its own dedicated button on the MainForm. I just want to run this same function when I close the form/subform.

2
vbams-access
I don't understand what's going on here but I wonder have you tried setting the subform control's Source Object property to ""? – HansUp
HansUp, No luck - err 2465. Synax as follows? Forms![Matched]![Matched subform].Form.SourceObject = "" – Mark Pelletier
A subform control contains a form, specified by its (the control's) Source Object property. You would need something like NameOfSubformCONTROL.SourceObject = vbNullString In other words, there should not be any .Form. in there at all because a form does not have a Source Object property --- it has a Record Source. – HansUp

2 Answers

1
votes

HansUp, I can confirm the following syntax also works:

Forms![Matched]![Matched subform].SourceObject = ""

Thanks for the suggestion!

0
votes

Crude, but effective:

DoCmd.Close acForm, "Forms![Matched]![Matched subform]", acSaveNo

No more complaints about the locked table

HansUp, I'll play with that syntax; always fuzzy trial and error on those pesky sufbform properties.