Solved

Adding a new record from a form restricted to existing records

Posted on 2010-09-04
10
463 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

728 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