strSQL in DLookup

[isladogs]

In cases where editing could be done by multiple Users, than I would use bound popups.

In Data Entry mode, rarely would I use bound popups. Using DAO code allows for data to be pulled in from many locations. This enhances flexibility, impossible to achieve using bound forms.

I'm not saying bound Forms don't have their place, but they are extremely limited compared to unbound. I think many users are frustrated by database deign because developers don't truly map out business requirements. They just push on the Users standard almost fitting solutions.

I am finding it very difficult to think of examples where forms are limited or made inflexible as a result of being bound. Although many developers use one table per form, I don't subscribe to that view. Perhaps you could provide some examples to illustrate your assertions.

 

[Thales750]

I am finding it very difficult to think of examples where forms are limited or made inflexible as a result of being bound. Although many developers use one table per form, I don't subscribe to that view. Perhaps you could provide some examples to illustrate your assertions.

When you use a DAO RecordSet you can wait until the record set is inserted into a table to make decision about other programmatic events. It's 100% positive. No dirty forms, or maybe a delete or maybe a save record.

None of those things. And no missing record.

You can insert code directly in front of assigning a value to a field, that code could be a Select Statement, an If statement, a loop, whatever you might need to get the correct data.

Do you consider it a bound form if you have code to bind it at runtime?

 

[Pat Hartman]

All of the the things you mention can be done with bound forms. You only need to understand how to use form and control level events.

A bound form is one that has a table or query as its RecordSource property. It has nothing to do with whether or not the form has a code module.

 

[isladogs]

Pat replied before I read this - I agree with everything she wrote

I think you are labouring under a misapprehension about bound forms.
You are CERTAINLY labouring by creating masses of extra work using unbound forms

 

[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.

 

[Pat Hartman]

zero chance of something going wrong

Funny, nothing goes wrong in my bound forms either. I guess we'll have to agree to disagree. As I said earlier, I've written my million lines of code and don't need the practice. I actually appreciate what Access is doing for me since I've written it for myself in other platforms so I understand the interpreted VBA and DAO/ADO code that needs to be written to replace the compiled code executed by Access on my behalf.

 

[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.