Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-22-2018, 12:50 PM   #31
ridders
Part time moderator
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,177
Thanks: 83
Thanked 1,520 Times in 1,419 Posts
ridders is just really nice ridders is just really nice ridders is just really nice ridders is just really nice ridders is just really nice
Re: strSQL in DLookup

Quote:
Originally Posted by Thales750 View Post
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

Quote:
Originally Posted by Thales750 View Post
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.

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

New example databases:

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

-----------------------------------------------------------------------------------
nil illegitimi carborundum est
ridders is offline   Reply With Quote
Old 05-22-2018, 12:59 PM   #32
Thales750
Formerly Jsanders
 
Join Date: Dec 2007
Location: Galveston, Texas
Posts: 1,610
Thanks: 40
Thanked 28 Times in 28 Posts
Thales750 is on a distinguished road
Re: strSQL in DLookup

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.
__________________
Aboard Oceans Eleven on Offatts Bayou
inveniemus viam aut faciemus
Thales750 is offline   Reply With Quote
Old 05-22-2018, 02:00 PM   #33
MajP
Newly Registered User
 
Join Date: May 2018
Posts: 432
Thanks: 6
Thanked 104 Times in 102 Posts
MajP will become famous soon enough
Re: strSQL in DLookup

Quote:
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)
Code:
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.

MajP is offline   Reply With Quote
Old 05-22-2018, 02:31 PM   #34
Thales750
Formerly Jsanders
 
Join Date: Dec 2007
Location: Galveston, Texas
Posts: 1,610
Thanks: 40
Thanked 28 Times in 28 Posts
Thales750 is on a distinguished road
Re: strSQL in DLookup

Quote:
Originally Posted by ridders View Post
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.

__________________
Aboard Oceans Eleven on Offatts Bayou
inveniemus viam aut faciemus

Last edited by Thales750; 05-22-2018 at 03:09 PM.
Thales750 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
dcount - strsql sven2 Modules & VBA 13 11-29-2007 10:53 AM
strSQL with WHERE edojanssen Forms 4 09-04-2007 02:23 AM
strSQL doubt EL-g Modules & VBA 5 11-09-2006 06:04 AM
DoCmd.run(strSQL) vs. CurrentDb.Execute (strSQL) Willem! Modules & VBA 2 06-14-2006 11:09 PM
[SOLVED] strSQL Rich Forms 0 02-21-2001 11:37 AM




All times are GMT -8. The time now is 03:42 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World