strSQL in DLookup

[MajP]

A bound form is one that has a table or query as its RecordSource property

Athough I concur with everything Pat and Colin stated the above quote is incorrect or at least incomplete.

A bound form has a recordset bound to the form. This can be done by providing a recordsource and letting Access create and bind the recordset, or you can bind your own DAO or ADO recordset to a form.

 

[The_Doc_Man]

True, MajP - the specification for <form>.Recordsource allows you to redefine it by overwriting the <form>.Recordset, and we actually had a case of this on the forum less than a month ago. But developers DO have to remember that doing so alters event flow if starting from an unbound form and THEN defining a .RecordSource later.

Until the .Recordsource is defined there can be no Form_Current event, nor a Before_Update event nor an After_Update event. As long as the putative developer understands that, no big deal.

Out of curiosity, do you have an opinion as to the best event in which this .RecordSource reset would best occur - if you were planning to do this in a Form_xxxx event as opposed to a button click? I'm kind of leaning towards Form_Open because none of the bound controls get set up until Form_Load, of course.

 

[Thales750]

Athough I concur with everything Pat and Colin stated the above quote is incorrect or at least incomplete.

A bound form has a recordset bound to the form. This can be done by providing a recordsource and letting Access create and bind the recordset, or you can bind your own DAO or ADO recordset to a form.

That's how I have always thought of it.

 

[Thales750]

True, MajP - the specification for <form>.Recordsource allows you to redefine it by overwriting the <form>.Recordset, and we actually had a case of this on the forum less than a month ago. But developers DO have to remember that doing so alters event flow if starting from an unbound form and THEN defining a .RecordSource later.

Until the .Recordsource is defined there can be no Form_Current event, nor a Before_Update event nor an After_Update event. As long as the putative developer understands that, no big deal.

Out of curiosity, do you have an opinion as to the best event in which this .RecordSource reset would best occur - if you were planning to do this in a Form_xxxx event as opposed to a button click? I'm kind of leaning towards Form_Open because none of the bound controls get set up until Form_Load, of course.

This is why I say in many cases, better to just create new records in DAO. When the complex record is created. Hit the save button and zero chance of something going wrong.

 

[Thales750]

I'm not saying it needs replacing. And 90% of my forms are bound. But when it comes to extremely difficult business rules. Built in Access will not work in all of those cases.

The real world has infinite examples of multiple "many to many" relationships. Creating new records in those scenarios is very simple and straight forward using DAO,and not at all straight forward in regular bound forms. Once you have a group of records,displaying them is straight forward on bound forms.

A place for all is what I am saying.

 

[isladogs]

I'm not saying it needs replacing. And 90% of my forms are bound. But when it comes to extremely difficult business rules. Built in Access will not work in all of those cases.

The real world has infinite examples of multiple "many to many" relationships. Creating new records in those scenarios is very simple and straight forward using DAO,and not at all straight forward in regular bound forms. Once you have a group of records,displaying them is straight forward on bound forms.

A place for all is what I am saying.

Back in post 12, you seemed to suggest most of your forms were unbound

The only bound forms I use are for Continuous Forms, and those have very limited editing capabilities. all popups and data entry is done with unbound. It takes longer to build them but you have complete control over when records are created and have much more flexibility to conform to business rules.

 

[Thales750]

I think people get used to doing things a certain way.

Until a few months ago, after writing databases in Access since 1996, I had never cared about multi-criteria DLookups. I just used a Querrydef. Yesterday Pat had to explain that a multi-criteria was actually only one string. For 22 years, I never had a clue about that.

For years, every query I needed was either an object, or tied directly to forms. I even remember Pat having this conversation with someone else in 2008 when I was making a system for the TSA. At the time I completely agreed with her. As a result of that conversation I started thinking about unbinding Main Forms to create more versatility in controlling the display of the subform. I found that using the data link between forms and subform was restrictive. So I stated using one list to control another.

The next step was to replace text boxes, on Forms, that held criteria data for underlying forms, with Public Function that return the value stored in a Public Variable.

The rest is history. I now have an order of magnitude more flexibility than before. Not because I stopped using bound forms, but because I didn't have any reason not to use unbound ones.

The point is, that Access has powerful built in capabilities, and others that allow as much flexibility as you need to do almost anything database. Except, you know, build a decent Web Ap, or make something work across platforms.

Maybe someday Real Web Aps will be available. I would bet on driverless cars first though.

 

[MajP]

True, MajP - the specification for <form>.Recordsource allows you to redefine it by overwriting the <form>.Recordset, and we actually had a case of this on the forum less than a month ago. But developers DO have to remember that doing so alters event flow if starting from an unbound form and THEN defining a .RecordSource later.

Until the .Recordsource is defined there can be no Form_Current event, nor a Before_Update event nor an After_Update event. As long as the putative developer understands that, no big deal.

Out of curiosity, do you have an opinion as to the best event in which this .RecordSource reset would best occur - if you were planning to do this in a Form_xxxx event as opposed to a button click? I'm kind of leaning towards Form_Open because none of the bound controls get set up until Form_Load, of course.

Sorry, not sure I understand. My point was simply you can have a bound form without ever having a recordsource. A simple example (with little utility)

Private Sub Form_Load()
  Dim rs As DAO.Recordset
  Set rs = CurrentDb.OpenRecordset("Data", dbOpenDynaset)
  Set Me.Recordset = rs
End Sub

That is now a bound form. More utility would be to bind one forms recordset to another to synchronize forms and not have to pass complex filters and sorts. Another may be to bind a disconnected ADO recordset.

But once it is bound you most certainly have a current and update events. A recordsource is not required.

 

[Thales750]

Back in post 12, you seemed to suggest most of your forms were unbound

I was exaggerating.
The ones that stand out in your mind are the ones that required excessive thought and creativity.

I tend to forget about the mundane ones.