Access 2010: to "SubForm" or "VBA+SQL" (1 Viewer)

[InstructionWhich7142]

So I'm making a "simple" Fault and Solution system,

When I made the form to add Faults I just put some unbound fields and list boxes onto a form bound to the Parent table and used SQL to insert records into the Child table,

Later when I made the Solution form I used a subform bound to a listbox, this made editing existing records easier but I'm finding it harder to control what Access does when the subform loses focus etc as Access has a load of default behaviour for subforms.

I'm now looking at editing Faults and would probably use a RecordSet to repopulate the unbound fields and SQL to Update the child table and so realised I'd have one form doing it the "Subform" way and one form doing it with VBA and SQL so i wondered which is "best" or recommended at least and people's opinions on both,

Thanks,

 

[Galaxiom]

I'm now looking at editing Faults and would probably use a RecordSet to repopulate the unbound fields and SQL to Update the child table,

Why would you use this technique?

 

[InstructionWhich7142]

Why would you use this technique?

its just the neatest way I've seen to quickly populate a number of unbound fields,

edit: would you suggest swapping it to a subform?

 

[Uncle Gizmo]

I think the essence of what you are asking is, Is it better to use a "main-form" "sub-form" arrangement or if it is better to append records with SQL statements. It really depends on what you are doing; one way might be more applicable than the other, depending on circumstances. Bearing in mind you are using MS Access, then MS Access has a set of tools, in this case the main-form sub-form arrangement. It seems to me that the best policy would be to adopt the ready made tools if they shoot the puppy.... (I am using speech recognition and I spoke "suit the purpose" and the Machine wrote "shoot the puppy" a think the Machine understood my subconscious intentions!)

 

[InstructionWhich7142]

I think the essence of what you are asking is, Is it better to use a "main form" "sub-form" rangement or if it is better to append records with SQL statements. It really depends on what you are doing; one way might be more applicable than the other, depending on circumstances. Bearing in mind you are using MS Access, then MS Access has a set of tools, in this case the main-form sub-form arrangement. It seems to me that the best policy would be to adopt the ready made tools if they shoot the puppy.... (I am using speech recognition and I spoke "suit the purpose" and the Machine wrote "shoot the puppy" a think the Machine understood my subconscious intentions!)

Haha! Access has shot a hell of a lot of puppies in my book!

edit: But yea, I take your point on "use the tools that are there", I'll knock up a subform,

My only question then is what's the recommended solution for handling when Access tries to update when it loses the focus?

 

[Galaxiom]

Access updates bound forms when they lose focus.

If you want to avoid this then use the Transactions with bound forms technique discussed here.

BTW. Don't underestimate what goes on in a bound form. They smoothly handle conflicting updates in multiuser databases that otherwise require quite involved logic and code to manage. Unbound forms isn't simply a matter of blindly running commands if you want to avoid wiping out other users' concurrent edits to the same records.

 

[InstructionWhich7142]

Yea, I'd just started to worry about how to manage multi user issues and it was seeming complicated so I've started redoing the unbound form as a subform,

This also makes it easier to use the Fault creation form as a Fault editing form, however I have a number of objects that change visibility based on updates of other fields, obviously when navigating between records no fields are updated so none of these triggers occur correctly,

Should I duplicate this code into the "On Current" event? (duplicating code seems like a bad idea) how would you suggest I trigger events both on updates and on change of record?