Solved

Extra record created on table in Access

Posted on 2011-02-23
32
294 Views
Last Modified: 2012-05-11
I'll start with the basics and provide more detail as needed. I have a table called ActiveCases that creates the header for a veterinary case. After creating a new record on this table via the bound ActiveCase form, the user then clicks "New Diagnosis" which opens a form in Add mode that is bound to a table called CaseTrtmt. Through openArgs the form inherits the activeCaseID and the locationID. After creating the related record in the CaseTrtmt table, the user clicks a back button that returns them to the related activeCase. It is at this point that for some reason Access creates a new record on the ActiveCases table that has no data other than the location field (which is a duplicate of the location stored in the previous record).

I realize this is not a lot to go on, but before posting all of the relevant code I thought I would first see if anyone has encountered a similar scenario and can perhaps list a candidate or two that I can investigate. I'm usually able to track these kinds of issues down on my own but this one has had me baffled for a few days.
0
Comment
Question by:NashVegas
  • 12
  • 9
  • 3
  • +6
32 Comments
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 34966052
If the two tables are linked by a key field (I think they are), why not put the Diagnosis form on the Active Cases form as a subform?  Link them by the key field, and then all you have to is start typing on the subform to add a new linked record (without adding another record to the main form's table).
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 34966060
We might have to see the code on the Back button to see what is going on here.  If you save the key field value for the main form, and then return to that record, you won't get a new record.
0
 

Author Comment

by:NashVegas
ID: 34966071
Thanks for the suggestion. It is mostly a design decision due to the need for more real estate. Both the ActiveCase and the CaseTrtmt forms both take up almost all of the available space in the window.
0
 

Author Comment

by:NashVegas
ID: 34966137
Even if I don't return to the form (say I just close the CaseTrtmt form rather than use the back button) I still get the extra record. In fact, I removed all the code that executes when the CaseTrtmt form is closed just now for testing purposes. The extra record is still being created! When I open the CaseTrtmt form from the ActiveCases form I check the ActiveCases table and there is no extraneous record. Then I close the CaseTreatment form (I click the menu option in the custom ribbon to open a different form which runs a sub to close any open forms) and afterwards I immediately check the ActiveCases table and the extra record is there.
0
 
LVL 75
ID: 34967125
Do you have any code in the Form Current event that is setting some values?  If so, this is the likely reason for the issue ...

?

mx
0
 
LVL 14

Expert Comment

by:Don Thomson
ID: 34970963
Is the LocactionID unique?
0
 

Author Comment

by:NashVegas
ID: 34992250
I apologize for the delay but I had a lot of meetings last week and could not get back to this until now. I have spent a lot of time stripping everything out of this application other than the bare bones necessary to demonstrate the issue. If you open the attached database, open the ActiveCases form and select a fclty/location and then click "New Diagnosis..." then the caseTrtmt form will open and you can select a diagnosis and treatment. Then simply close the caseTrtmt form. Then open the ActiveCases table and you will see 2 records. If you selected "MCN CC1303" as the facility/room when you created the new case, then you will see 2 records on the ActiveCases table that have MCN CC1303 as the facility/room.
DBTest.accdb
0
 

Author Comment

by:NashVegas
ID: 35017767
Has anyone had a chance to take a look at this?
0
 
LVL 11

Expert Comment

by:LambertHeenan
ID: 35096433
" Both the ActiveCase and the CaseTrtmt forms both take up almost all of the available space in the window. "

You can still set this up as a Parent/Child form by using a Tab Control.

Add the tab control ot the ActiveCase form, then move all the exisiting controls on it onto the Tab, page1. Next insert a SubForm control on Page2 of the tab control, and set its Souirce Object to the CaseTrtmt form.

Both forms will then have all the real estate they need.
0
 

Author Comment

by:NashVegas
ID: 35096546
LambertHeenan,

Thanks for the suggestion, but I really don't want to redesign at this point. I have created a workaround the deletes the unwanted record, but I would like to determine how to prevent its creation prior to releasing as the production version. I don't like the way tab controls look in the context of this application so I made a design decision early on not to use them.
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 37042439
I don't have Acc2k7, so I'm going to have to ask a lot of questions:

How are you passing the the info to the sub form? Using the tags?

What if you use a recordset and an unbound form. Does the same thing happen?

Do you have the first table to not have duplicates on the primary key fields?

Just a few thoughts.
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Access MVP) earned 375 total points
ID: 37042608
The reason is quite clear.
Form fActivecases has a record source tied to table activeCases.  
The Fclty/Room combo is has it's Control Source set to Location.
As soon as you select a Location, this form is Dirty.
Then, when you open form fCaseTrtmt, Form fActiveCases automatically saves that record.  Thus, there is your first record.

Now, Form fCaseTrtmt also has table activeCases as part of the Record Source - specifically, the Location field. So, when you selected a Treatment and Diagnosis, then close this form, your 2nd record is created.  

It's just that simple.  So, you need to rethink your interface.

mx
0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) earned 375 total points
ID: 37042649
"Then, when you open form fCaseTrtmt, Form fActiveCases automatically saves that record. "
In fact, you command does the Save because of the Me.Dirty = False.  Of course, I realize your goal is to get the ID.
0
 
LVL 34

Expert Comment

by:James0628
ID: 37043733
You guys do realize that the last post here was 7 months ago, right?  It's probably a little late for suggestions now.  :-)

 James
0
 
LVL 84
ID: 37045486
James: We were asked to review this question by the Moderators.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 125 total points
ID: 37045517
And in addition to what mx said:

The behavior described by mx is default behavior. If you dirty that record and then leave it, Access will save it. The only way to stop that is to either (a) trap the save, and somehow discard it (which wouldn't do you any good, since you need the data selected/entered by the user) or (b) use a temporary table as the basis for your forms, and then save the data to the "live" tables when the user takes an action (like clicking a button) or (c) move to unbound forms, which allows you more precise control over your data.

Of the 2 relevant choices (b) and (c), choice (b) would be the simplest way - although it would be anything but "simple". As mx said, redesigning your interace to some degree would probably be a better overall solution.
0
 
LVL 75
ID: 37045773
I actually spent some time trying to patch it to make it work, however there are just too many issues, unfortunately.  There is waaaay too much code in an attempt to do what is relatively (conceptually) straight forward.   It's your basic one to many - Cases to Diagnosis/Treatments.

Anyway ...

mx
0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) earned 375 total points
ID: 37045788
Clarification:

RE: "In fact, you command does the Save because of the Me.Dirty = False. "

The two command buttons for Treatment / Diagnosis have code that Saves the record when the are clicked, then the other form opens.  That part is fine. It's what happens next is where the problems start.

mx
0
 

Author Comment

by:NashVegas
ID: 37046000
Thank you both for taking the time analyze this issue. The application has been in production for several months, but I am working on a new release and wanted to address this issue for the sake of performance.  I will spend some time with this over the weekend and report back.
0
 
LVL 75
ID: 37046011
ok ... thx for the feedback ...

mx
0
 
LVL 75
ID: 37046020
btw ... it appears this is a veterinarian application ...?

mx
0
 

Author Comment

by:NashVegas
ID: 37046051
mx - yes, it is. The users are quite pleased with it, despite my clumsy programming. Nothing like learning on the fly!
0
 
LVL 75
ID: 37046078
Where are you located?
I'm good friends with my vet here in Rancho.  He has a state of the art facility.  All of there hardware (Dell) and software are supplied by IDEXX.

mx
0
 
LVL 75
ID: 37046088
Geez "All of there hardware " >>> All of their hardware .  Need more coffee !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
0
 

Author Comment

by:NashVegas
ID: 37046166
I'm in NashVegas! (a.k.a. Nashville, TN). I just sent you a connection request through LinkedIn. We can take this offline.
0
 
LVL 75
ID: 37046178
LI ... Done.thx
0
 
LVL 84
ID: 37047107
<We can take this offline. >

It'd be best to continue this discussion here, on EE. This way others can benefit from the process - plus, EE policy states that you should not use outside communication to resolve your issues here, as it gives an unfair advantage to that single Expert.

See here: http://www.experts-exchange.com/help.jsp#hs=23&hi=15
0
 
LVL 75
ID: 37047114
He means the Vet thing ... not this subject ...

mx
0
 
LVL 44

Expert Comment

by:GRayL
ID: 37047528
Can mx (-: outstare :-) LSM ?  ;-)
0
 
LVL 75
ID: 37047570
@ http:#a37046020   ...

mx
0
 

Author Closing Comment

by:NashVegas
ID: 37069498
I was able to confirm that mx had the right diagnosis and I am on the right track now to get it fixed (still more redesign to do, but making progress). LSM had some good suggestions as well for different approaches to the redesign. Thanks!
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
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 …

758 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

16 Experts available now in Live!

Get 1:1 Help Now