Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 346
  • Last Modified:

Extra record created on table in Access

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
NashVegas
Asked:
NashVegas
  • 12
  • 9
  • 3
  • +6
4 Solutions
 
Helen FeddemaCommented:
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
 
Helen FeddemaCommented:
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
 
NashVegasAuthor Commented:
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
Independent Software Vendors: 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!

 
NashVegasAuthor Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
 
Don ThomsonCommented:
Is the LocactionID unique?
0
 
NashVegasAuthor Commented:
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
 
NashVegasAuthor Commented:
Has anyone had a chance to take a look at this?
0
 
LambertHeenanCommented:
" 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
 
NashVegasAuthor Commented:
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
 
Jim P.Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"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
 
James0628Commented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
James: We were asked to review this question by the Moderators.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
 
NashVegasAuthor Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
ok ... thx for the feedback ...

mx
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
btw ... it appears this is a veterinarian application ...?

mx
0
 
NashVegasAuthor Commented:
mx - yes, it is. The users are quite pleased with it, despite my clumsy programming. Nothing like learning on the fly!
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Geez "All of there hardware " >>> All of their hardware .  Need more coffee !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
0
 
NashVegasAuthor Commented:
I'm in NashVegas! (a.k.a. Nashville, TN). I just sent you a connection request through LinkedIn. We can take this offline.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
LI ... Done.thx
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
<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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
He means the Vet thing ... not this subject ...

mx
0
 
GRayLCommented:
Can mx (-: outstare :-) LSM ?  ;-)
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
@ http:#a37046020   ...

mx
0
 
NashVegasAuthor Commented:
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

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 12
  • 9
  • 3
  • +6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now