[AccessD] VBA Unbound data entry / update form

jwcolby jwcolby at colbyconsulting.com
Tue Jun 10 12:05:23 CDT 2008


Mark,

Yup.  Kinda barbaric eh?

John W. Colby
www.ColbyConsulting.com


Mark A Matte wrote:
> 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=srchpaysyouback



More information about the AccessD mailing list