Solved

Adding a new record from a form restricted to existing records

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

Expert Comment

by:Rey Obrero
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
 
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Running Access application from Task Scheduler 6 34
Access Runtime 2010 Error 17 32
subtract 1 in Access 2003 query 7 39
Field Size - Double?  Want to display 0 5 30
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…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
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…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

895 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

13 Experts available now in Live!

Get 1:1 Help Now