Solved

How to open a MS Access Edit Form without showing a record

Posted on 2008-06-22
9
526 Views
Last Modified: 2013-11-29
I'm trying to eliminate users from typing over existing data on a record in an edit form...Not an Add Form, but an Edit Form.  Therefore, I would like the EDIT form to open without any records showing (not an add form).  Then, of course, they select the correct record using a combo box.  I once knew this trick, but lost it over the years.  Any ideas out there?
0
Comment
Question by:Trudger58
  • 2
  • 2
  • 2
  • +3
9 Comments
 
LVL 19

Expert Comment

by:frankytee
ID: 21843558
use an unbound form with an unbound combo box (empty control source) in the forms header. this combo box has its ROWSOURCE set to whatever field you want the user to select (customer_id whatever etc). in the after update event of combo, create a recordset based on the drop box and populate the other controls (textboxes) etc with the values from the recordset.
then have a "save" button so when user clicks, the underlying table gets updated with the values from your form controls etc
0
 
LVL 19

Expert Comment

by:frankytee
ID: 21843564
of course the form controls (textboxes displaying your fields etc) are also unbound
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 250 total points
ID: 21844285
Trudger58,

Or, you could send them to a new record when the form opens, with all the controls locked.
(*No*, this will not be an Add Form, ...all the recorde will be accessible)
;-)
...Then unlock the controls once a selection is made in the combobox.

I have attached a sample

JeffCoachman
AccessEEQ23506573OpenEditFormBla.mdb
0
 
LVL 77

Expert Comment

by:peter57r
ID: 21844319
I guess the 'trick' you are thinking of might be the way you set the recordsource.

If you set a where clause to return no records  (select * from mytable where 1=2)  then you will have no record visible.  Unfortunately, if you have switched off Allowadditions then you will not see any bound controls either until you select something from the combo.  
In the combo afterupdate event you set the recordsource to a single record.
"Select * from mytable where id =" & me.cbocomboname
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 57
ID: 21845128


  Besides manipulating the recordsource, you can also set the dataentry property to true.  When set to true, the form opens with only a blank record showing.

JimD.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 21846487
The easiest method to pull this off would be JDettman's setting the .DataEntry property to true or false on form open.

JeffCoachman's 'roll your own' by setting each control's Locked property to True or False works too, albeit it's a bunch more coding, but depending on your situation if you have a ton of stuff going on in your form that may be needed.
0
 

Author Closing Comment

by:Trudger58
ID: 31469635
Thanks!  This will do for now, however, I recall a simple module being called on the OnOpen for the form that set the value of the primary key to either blank or null.  If y'all run across anything like that, let me know.  Thanks again.
0
 
LVL 57
ID: 21847203
<<albeit it's a bunch more coding, but depending on your situation if you have a ton of stuff going on in your form that may be needed. >>

  Not really, check this out.

JimD.
Function LockForm(FormName As String)

  

  Dim frm As Form
 

  Set frm = Forms(FormName)

  LockForm = StdLockForm(frm, True)
 

End Function
 
 

Function StdLockForm(frm As Form, intState As Integer)

  

  'Lock/unlock all the fields in the detail section of the form that are enabled.
 

  Dim intL As Integer              'Generic loop counter

  Dim intCount As Integer          'Number of controls on the form

   

  intCount = frm.Count             'Find out how many controls are on the form
 

  On Error Resume Next

  For intL = 0 To intCount - 1      'Step thru all controls on the form

    If (frm(intL).Section = False) Then 'Don't do any control execpt those in the detail section

        If (frm(intL).Enabled = True) Then frm(intL).Locked = intState   'Set 'Locked' property to proper state

    End If

  Next intL

  On Error GoTo 0
 

End Function

Open in new window

0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 21848138
Trudger58,

As you can see there are many way to do this.
Switching modes while the form is open is always tricky (at least for me)
You always have to balance:
What the user will need to do, what they might want to do, and what you don't want them to do.
;-)

JeffCoachman

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
simplest php form 3 59
Run Time Error 3075 15 43
Query design issue 2 21
Format a Field AFTER UPDATE 5 21
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now