Solved

Adding a new record from a form restricted to existing records

Posted on 2010-09-04
10
461 Views
Last Modified: 2012-05-10
Hi,  
I have a form which shows the details of a particular doctor. The user will have chosen which doctor they wish to view based on a control (eg Dr. Jones ID 1234). This works fine and the user can update all the information on any doctor that currently exists.

However, I'm not sure how to add a new doctor using VBA (I've hidden the standard access controls to make it simpler for the user) and whether I can use this same form. Ideally I'd like the user to click a button which generates a new primary key and then lets the user view the new empty record so that they can add data.

One problem I'm having is that the primary key value in my table "Doctors" is set to number rather than autonumber. I've tried changing it to autonumber but it gives me the message "Once you enter data can't change the data type to autonumber".

What is the vba to generate a new record in a table with a unique primary key?

Do I then need to pass that value to the control so that the user can view it and add data?

Thanks for your help
0
Comment
Question by:DanSeal100
10 Comments
 
LVL 75
ID: 33604250
Switch to Auto Number like so:
Add a new Field with is AutoNumber
Save the table Design.
Delete the old Number field.

However, this assumes you do not have any Relationships with use the Old field.  Even if you do, you can probably switch that around also.

This way, there is no hassle adding a new record - no code.

mx
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33604252
you can use this

private sub cmdAddnew_click()

docmd.GoToRecord,,acNewRec

'or

'me.dataentry=true

end sub
0
 
LVL 3

Expert Comment

by:moonlight
ID: 33604285
usually you do a select max on the primary key and add one, then create it and pass it on to the edit form.

or, you can export all the table to else where, empty the table and change it to autonumber, then import all the data..
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 200 total points
ID: 33604298
DanSeal100
If you can ... switch to Auto Number now ... in your design, then this all becomes a non-issue.

mx
0
 
LVL 40

Assisted Solution

by:als315
als315 earned 100 total points
ID: 33604331
You can find maximum ID and add 1 to it.
In addition to capricorn1 text assign this value to your field:
me.DoctorID = DMax("DoctorID", "Doctors")+1

Change names to correct
0
 
LVL 4

Assisted Solution

by:MacRena
MacRena earned 100 total points
ID: 33604470
i would do the following:

add an "Add New Doctor" button on your "details of a particular doctor" form (frmDetailsOfDoctors)

create a form with unbound controls, each representing a different field in the Physicians table (only fields that you want to this form to populate).  Do not include a UniqueKey textbox.
add a button : "Commit Record"
put code in its Click event that
     1) looks up the highest [UniqueKey] value in the Physicians table
             lngUniqueKey = DMax("[UniqueKey]", "[Physicians]")
     2) creates a recordset of the Physicians table WHERE 1=2
         rs.AddNew
         rs![UniqueKey] = lngUniqueKey + 1    ' increment the previous "highest" numeric Unique Key
         rs![field] = me!txtName
         rs![etc] = me!txtEtc
         rs.Update
         rs.close
         set rs = nothing
         Form!frmDetailsOfDoctors.RecordSource = Form!frmDetailsOfDoctors.RecordSource

     3) set this new form to Popup=Yes and Modal=Yes
     4) put a call to this form behind the "Add New Doctor" button (on your "frmDetailsOfDoctors" form)

   
0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 200 total points
ID: 33604503
I should note that ... all schemes involving DMax() or similar will be problematic in a multi-user environment unless extreme care is taken to insure - via locking using VBA/DAO that only one user can be accessing the last used number at one time.

Using an Auto Number avoids this issue and requires *no code*, other than possible a button - as suggested above - to go to a New record. And if you have the Navigation buttons on, you really don't need that, although I don't  use the Nav buttons.

mx
0
 
LVL 9

Assisted Solution

by:Armen Stein - Microsoft Access MVP since 2006
Armen Stein - Microsoft Access MVP since 2006 earned 100 total points
ID: 33607156
If you do have existing child records and you want to preserve your previous key values in the new Autonumber, you can set their values using an Append query.  This would involve copying your records to another work table, temporarily deactivating referential integrity, switching the main table to Autonumber and deleting all the records, and then appending the work table records back in.  Make a backup of everything first!  :)
0
 

Author Closing Comment

by:DanSeal100
ID: 33608470
Thanks for the suggestions. I went with changing to autonumber in the design in the end. It was slightyl labourious because of all the child tables but it works fine and I can avoid more of that VBA stuff!

Thanks again.
0
 
LVL 75
ID: 33608516
Good move ...

mx
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

820 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