Solved

Adding a new record from a form restricted to existing records

Posted on 2010-09-04
10
460 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Access MVP) 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 39

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 - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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 …

777 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