Solved

Adding a new record from a form restricted to existing records

Posted on 2010-09-04
10
458 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

Expert Comment

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

Expert Comment

by:Rey Obrero
Comment Utility
you can use this

private sub cmdAddnew_click()

docmd.GoToRecord,,acNewRec

'or

'me.dataentry=true

end sub
0
 
LVL 3

Expert Comment

by:moonlight
Comment Utility
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
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Access MVP) earned 200 total points
Comment Utility
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
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 4

Assisted Solution

by:MacRena
MacRena earned 100 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
Good move ...

mx
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

763 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

15 Experts available now in Live!

Get 1:1 Help Now