[AccessD] VBA Unbound data entry / update form

jwcolby jwcolby at colbyconsulting.com
Tue Jun 10 14:21:29 CDT 2008 

And both of those methods ignore the fact that there may be 
100 fields in the record, and only one or two changed by 
each person.  Did they change the SAME FIELD?  If not then 
the changes of BOTH are legal and should be stored (and 
retained) without complaint.

John W. Colby
www.ColbyConsulting.com


Mark A Matte wrote:
> Max,
> 
> Thanks...but I am correct...and there is reason behind the mad logic.
> 
> Scenario:  I open a record in this app....I am making changes...but not saving...as I'm working Max opens the same record and starts making changes.  If Max clicks save first...his changes are saved...and when I click save I get an warning message "that this record has been modified since I started...and my changes will NOT be saved."
> 
> The reason it is in this order(in my opinion)...is to force the second person who clicked save to go physically check what the changes were before deciding to overwrite them instead of just NOT knowing someone else was working the same case.
> 
> The way it works:  When I open an existing case...the app retrieves all relevant data elements...one is last_update_dttm...when I click save it compares the last_update_dttm from when I pulled the record to the last_update_dttm currently in the system...if they are the same, I am allowed to save...if they are not...then I get my error message and NOT saved.
> 
> I didn't write it...but thats what it is.
> 
> Mark A. Matte
> 
> 
> 
> 
>> From: max.wanadoo at gmail.com
>> To: accessd at databaseadvisors.com
>> Date: Tue, 10 Jun 2008 19:46:44 +0100
>> Subject: Re: [AccessD] VBA Unbound data entry / update form
>>
>>
>>> Golden rule with the app we purchased...first person to click
>> save...wins!!!
>>
>> Mark, I think you have that back to front.... The LAST person to click save
>> wins. Everybody before gets overwritten!
>>
>> Max
>>
>>
>> -----Original Message-----
>> From: accessd-bounces at databaseadvisors.com
>> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mark A Matte
>> Sent: Tuesday, June 10, 2008 5:51 PM
>> To: Access Developers discussion and problem solving
>> Subject: Re: [AccessD] VBA Unbound data entry / update form
>>
>>
>> I can tell you how our PeopleSoft (web based) App handles it...it compares
>> the last_update_timestamp...if it is the same as when you pulled your
>> recordset...you get to save...if not...you lose your changes.
>>
>> Golden rule with the app we purchased...first person to click save...wins!!!
>>
>> Thanks,
>>
>> Mark A. Matte
>>
>>
>>
>>
>>> Date: Tue, 10 Jun 2008 12:06:43 -0400
>>> From: jwcolby at colbyconsulting.com
>>> To: accessd at databaseadvisors.com
>>> Subject: Re: [AccessD] VBA Unbound data entry / update form
>>>
>>> Rocky,
>>>
>>> I hate to jump all over your method because I want a method to use,
>>> but you are completely ignoring locking / update issues. What happened
>>> if another user updated the record between the time you loaded yours
>>> and the time you write it back?
>>>
>>> John W. Colby
>>> www.ColbyConsulting.com
>>>
>>>
>>> Rocky Smolin at Beach Access Software wrote:
>>>> I know how to make an unbound form but I haven't got enough nerve to
>>>> present such heresy - it runs counter to strongly held religious beliefs.
>>>>
>>>> OK - briefly - but you didn't hear it from me.
>>>>
>>>> 1. Design your form as you would a bound form but no Control Source
>>>> in the text boxes.
>>>> 2. Create a recordset in the Open event of the form (DAO or ADO - I
>>>> prefer
>>>> DAO) using pretty much the same SQL or query as you would for your
>>>> Record Source in a bound form.
>>>> 3. Add a module to put the fields from the current record of the
>>>> recordset into the text boxes which you can call whenever you want to
>>>> display the data on your form.
>>>> 4. Add a module to put the values in the text boxes into the fields
>>>> of the current record (in DAO use .Edit or .Add and .Update) which
>>>> you can call whenever you want to write the textbox values back to the
>> table.
>>>> 5. I always put my own navigation buttons on the unbound for - First,
>>>> Last, Next, Previous with Click events that move the recordset point
>>>> appropriately.
>>>>
>>>> HTH
>>>>
>>>> Rocky Smolin
>>>> Beach Access Software
>>>> 858-259-4334
>>>> www.e-z-mrp.com
>>>> www.bchacc.com
>>>>
>>>>
>>>>
>>>> -----Original Message-----
>>>> From: accessd-bounces at databaseadvisors.com
>>>> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim
>>>> Lawrence
>>>> Sent: Monday, June 09, 2008 5:38 PM
>>>> To: 'Access Developers discussion and problem solving'
>>>> Subject: Re: [AccessD] VBA Unbound data entry / update form
>>>>
>>>> Hi Jennifer:
>>>>
>>>> I have no idea how to create bound Access forms. ;-) The last bound
>>>> database that I have worked with was back in '97.
>>>>
>>>> Jim
>>>>
>>>> -----Original Message-----
>>>> From: accessd-bounces at databaseadvisors.com
>>>> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jennifer
>>>> Gross
>>>> Sent: Tuesday, May 27, 2008 5:12 PM
>>>> To: 'Access Developers discussion and problem solving'
>>>> Subject: Re: [AccessD] VBA Unbound data entry / update form
>>>>
>>>> I have no idea how to create an unbound form in Access. I always use
>>>> bound forms. Though I would be interested to know how it's done.
>>>> Unless I've got it wrong, that seems to be the basic question here -
>>>> For those of you who do it, how do you create an unbound form? How do
>>>> you populate the textboxes initially and then how do you save the
>> information back to the tables?
>>>> It's beginning to sound like nobody really does it.
>>>>
>>>> Jennifer
>>>>
>>>> -----Original Message-----
>>>> From: accessd-bounces at databaseadvisors.com
>>>> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
>>>> Sent: Tuesday, May 27, 2008 2:49 PM
>>>> To: Access Developers discussion and problem solving
>>>> Subject: Re: [AccessD] VBA Unbound data entry / update form
>>>>
>>>>> 1) If your situation gives you the choice between Access and SQL
>>>>> Server,
>>>> then you can use SQL Server Express. For your customer's benefit -
>>>> it's free!
>>>>
>>>> I know that, and you know that, but they have used an MDB BE for most
>>>> of a decade. SQL Server is the great unknown.
>>>>
>>>>> 2) How many concurrent users? Too Many?
>>>> In the majority of cases, no. There ARE specific places where too
>>>> many users cause mysterious issues with an MDB.
>>>> Memos are written in "pages", as are indexes. If you open a record
>>>> and start to edit it, it "locks" an entire "page" of the index
>>>> structure or the memo area, which locks not just your record but
>> potentially many others.
>>>> This simply doesn't happen in SQL Server because SQL Server doesn't
>>>> have this "page" system for storing memo fields etc.
>>>>
>>>>> 3) How many indexes on the tables?
>>>> Not the point, the point is that ALL indexes are stored in Index
>>>> pages, and entire pages of indexs can be locked by a single edit.
>>>>
>>>> Creating / using Indexes should be determined by need, not arcane
>>>> locking issues within Jet.
>>>>
>>>>> 4) Are you starting up by setting a recordset to open and leaving it
>>>>> that
>>>> way?
>>>>
>>>> If you are talking about creating and holding locks on the BE, yes I
>>>> am, but that isn't the issue either.
>>>>
>>>> John W. Colby
>>>> www.ColbyConsulting.com
>>>>
>>>>
>>>> Dan Waters wrote:
>>>>> John - I've been reading this with interest:
>>>>>
>>>>> 1) If your situation gives you the choice between Access and SQL
>>>>> Server, then you can use SQL Server Express. For your customer's
>>>>> benefit - it's free!
>>>>>
>>>>> 2) How many concurrent users? Too Many?
>>>>>
>>>>> 3) How many indexes on the tables? I actually only use the primary
>>>>> key as an index on every table. More indexes slows down writing
>>>> performance.
>>>>> 4) Are you starting up by setting a recordset to open and leaving it
>>>>> that way? This greatly reduces 'churn' in the locking database. When
>>>>> I set this up all my users reported a significant performance
>> improvement.
>>>> Code below:
>>>>> '-------------------------------------------------------------------
>>>>> -----
>>>>> Dim stgConnect As String
>>>>> Dim dbs As DAO.Database
>>>>> Dim tdf As DAO.TableDef
>>>>>
>>>>> stgConnect = BEFullPath
>>>>> Set dbs = DBEngine(0)(0)
>>>>> Set tdf = dbs.TableDefs("tblConnect") If tdf.Connect <> ";Database="
>>>>> & stgConnect Then tdf.Connect = ";Database=" & stgConnect
>>>>> tdf.RefreshLink End If dbs.Close
>>>>>
>>>>> Set GrstConnect = DBEngine(0)(0).OpenRecordset("tblConnect",
>>>>> dbOpenSnapshot)
>>>>> '-------------------------------------------------------------------
>>>>> --
>>>>> ---
>>>>>
>>>>> Notes: BEFullPath is a function to return the full path to the BE.
>>>>> tblConnect is a one row one field table in the BE.
>>>>> The table link is refreshed because the table link is in a
>>>> library.
>>>>> GrstConnect is a globally defined DAO recordset.
>>>>> GrstConnect remains open until just before the database is closed.
>>>>>
>>>>> Good Luck!
>>>>> Dan
>>>> --
>>>> AccessD mailing list
>>>> AccessD at databaseadvisors.com
>>>> http://databaseadvisors.com/mailman/listinfo/accessd
>>>> Website: http://www.databaseadvisors.com
>>>>
>>>>
>>>> --
>>>> AccessD mailing list
>>>> AccessD at databaseadvisors.com
>>>> http://databaseadvisors.com/mailman/listinfo/accessd
>>>> Website: http://www.databaseadvisors.com
>>>>
>>>> --
>>>> AccessD mailing list
>>>> AccessD at databaseadvisors.com
>>>> http://databaseadvisors.com/mailman/listinfo/accessd
>>>> Website: http://www.databaseadvisors.com
>>>>
>>>> No virus found in this incoming message.
>>>> Checked by AVG.
>>>> Version: 8.0.100 / Virus Database: 270.1.0/1492 - Release Date:
>>>> 9/6/2008
>>>> 10:29
>>>>
>>> --
>>> AccessD mailing list
>>> AccessD at databaseadvisors.com
>>> http://databaseadvisors.com/mailman/listinfo/accessd
>>> Website: http://www.databaseadvisors.com
>> _________________________________________________________________
>> Search that pays you back! Introducing Live Search cashback.
>> http://search.live.com/cashback/?&pkw=form=MIJAAF/publ=HMTGL/crea=srchpaysyo
>> uback
>> --
>> AccessD mailing list
>> AccessD at databaseadvisors.com
>> http://databaseadvisors.com/mailman/listinfo/accessd
>> Website: http://www.databaseadvisors.com
>>
>> --
>> AccessD mailing list
>> AccessD at databaseadvisors.com
>> http://databaseadvisors.com/mailman/listinfo/accessd
>> Website: http://www.databaseadvisors.com
> 
> _________________________________________________________________
> Instantly invite friends from Facebook and other social networks to join you on Windows Live™ Messenger.
> https://www.invite2messenger.net/im/?source=TXT_EML_WLH_InviteFriends

More information about the AccessD mailing list