Add Record on Open Form Behavior

[GREABR]

This is my first post, must say that this forum has been very helpful, so thanks to all for that.

Please stop me from throwing my laptop onto a freeway.

I am not sure what to call this error, as I am unsure exactly what is happening when it occurs. This I what I do:

I open a form to a new blank record, then select from a combo box that is bound to a field on the form, as far as I can tell the combo gets to AfterUpdate.

This is where I have the problem - when I tab,click,move to another field on the form, all fields change to display "#deleted".

Here is some additional info about my forms and Tables. Using the same form, If I open the form and records exist they are displayed, I have an "ADD" button on the form - basically set allowadditions to true and then add a record. When I do it this way it works fine. I only have the problem when a new record is added on Open Form. At first I thought the form was updating, but the form never gets to beforeupdate (I stuck a msgbox there to check).

There are however filters and keys that may have something to do with this problem.

Describe:
Form "Master" has a subform "SubMaster". Master represents a legal document. That leagal document can have many versions over a period of time. So table for master has two field that make up the primary key = docID, verID. a separate relate table contains version specific data for the document.

Next the document can have a list of names associated with it and they must be obtained from a Table called "AllNames".

At any point in the timeline for the document you will have the document, at a particular version, with a small selection of names listed on it.

So, another table "Names" has 3 fields as PK DocID,VerID,Name. "Names" is related to "Master" based on DocID and VerID. The form "Names" is where I have my error.
I get to this form by clicking a button on "SubMaster"

frmMaster data is tblMaster
frmSubMaster data is tblNames
frmSubMaster is a datasheet with record selectors showing only two fields name,desc
a button on frmSubMaster opens frmNames
frmNames data is tblNames
tblNames is filtered to only matching DocID and VerID.
when no names are assigned, The form opens to a new record.
the combobox gets from tblAllNames.names and is bound to tblNames.names

I guess that is it. Very Very sorry if this is a confusing post - I really hope he who fixes this for me does not have to read past the simple discription of the error.

Thank you

 

[Quehay]

Suggestion: Access doesn't work well with composite, intelligent primary keys--it won't allow you to use them as a foreign key (e.g. in Versions table).

I believe you've failed to normalize your data by trying to store attributes that don't pertain to the master but rather the versions of it.

Why not use an autonumber ID field for primary key for document. Then normalize it and use the document ID as a foreign key in the versions table.

Add a UNIQUE constraint to the versions table (Access will run DDL in the SQL window and allow you to store the query for viewing later--which is the only way you'll easily see this constraint):
[tt]
ALTER table versions add constraint UK_version UNIQUE (masterID,version), so that you don't create duplicate versions for a given document.[/tt]

If the name applies only to a particular version (only one name per version) why not just keep name as an attribute of version?

This is just a quick read, but I think you'll be a happier camper if you rethink the data-modeling here.

Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development

http://www.InsightDataConsulting.com

 

[GREABR]

Yes, I agree I should clean up the design.

I believe it will always look something like this:

tblMaster has 5 child records from tblVersion

each 5 from tblVersion has 5 child records from tblNames

the 5 from tblNames must can only be created if they exist in directory tblAllNames (use combo box for new records)

Is this normalized enough?

But I still don't get why I have problems with the combo box with added records from openning the form, and not when I add them with DoCmd.

Bradley Greaves

 

[Quehay]

Bradley:

Definitely let's look at the ADO thing but just to get the data model down...so you've got a 1:1 correspondence of Versions of a Document || Names for Version?

Why isn't name just an attribute of the version? The possible names can be be restricted to a list in a lookup table and either inserted via FKEY of an ID or the name itself (this is where an intelligent primary key makes sense in Access).

Please apprise if I misunderstood!

Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development

http://www.InsightDataConsulting.com

 

[GREABR]

No, Its not 1:1, its one version to many names

Exactly: the document starts at version 0 with many names (could be 1, could be 10). The document is only good for a specific period of time (30 days), but we can repetedly extend the document (30 days at a time). Each extension is likely to have all of the names as the previous - it is not likely to have more names, but it is likely to have some removed.

There is a requirement, that at any time we would need to see details of each version or extension.

example:
Doc 1234 ver 3, from date to date, for name1, name2, and name3.
Doc 1234 ver 4, from newdate to newdate, for name1 and name2.

Bradley Greaves

 

[GREABR]

Me!Again

Just an update after playing around with this last night.
I think I cleaned up the data design a bit an I have the error to where it is more predictable.

Looks like the record updates on its own without the form updating. It does this when I leave the combo box, or when I Save or Close the form. (but not with added records from DoCmd)

For example: If I open the form to an added record, select something from the combo box, now the form is dirty and the Text box does the before and after update. next I try to close the form and that brings the form to beforeupdate and I am prompted to save the record. but by now I already have a saved record with the data from the combo box. If I say no to save the record, I still end up with the saved record.

The "#deleted" that appears in the text boxes on the form are there because the form no longer points the the added record as soon as I run the close command.

Still don't know why or how to get around this.

Bradley Greaves

"Don't be afraid to do things wrong, before you do them right"
Bradley Greaves

 

[Quehay]

Sorry to be pedantic but there's still the structural issue to be resolved (and my understanding may be all that's missing ;-) )

Why don't you list your tables and columns therein.

Based on my cursory reading, what you should have (in pseudo-DDL):

TABLE master
doc_id autonum PK
col
col

TABLE version
version_number
doc_id

PK for version is version_number + doc_id

*unless you need version to appear in a child table

Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development

http://www.InsightDataConsulting.com

 

[Quehay]

Bradley:

Jeremy Wallace's website has a Paul Litwin article on db normalization that's very good. Make sure you've got this stuff down cold (and please forgive me if you ALREADY know this):

Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development

http://www.InsightDataConsulting.com

 

[GREABR]

Yes, I see your point about the design and normalizaton, and I am a bit confused abuout the proper design in this case. I did mean to go with something like you describe here but I chaned my mind.

Right now I have something like this:

TABLE Master
doc_id
version
Col (specific to master only)
* doc_id + version is PK

TABLE Version
doc_id
version
Col (specific to version only)
* doc_id + version is PK (and FK of Master)

Looks Very wrong and not normalized, right?
Except Master.version is "current version" for master and version.version is for unique version records. Or I could use a logical field - verison.current - to do this.

But in the end only one master record will relate to one verson record when viewing the forms. the additional version records will be used for constructing "point-in-time" reports.

On the other hand, the "Names" table can always have many child records related to the version record. (I was not sure if best to relate to the Master table (that has the current verion field) or the version record. I chose to relate to the version record like this

TABLE Names
doc_id
Version
Name
Col
* doc_id + version + Name is PK (doc_id + Version are FK to Version)

Am I very wrong about this?

Anyway, I wanted to reply to you - but let me go and read about normalization like you suggest.

Don't forget about the ADO thing - still a problem



"Don't be afraid to do things wrong, before you do them right"
Bradley Greaves

 

[Quehay]

Leaving aside names (what are the names "first draft", "final version", etc.?)

Master is fine but remove version--this should be derived dynamically rather than stored. The derived value may be shown on data entry form and reports easily with a "select Max(version) where id=forms!frmDoc!DocID".

And Version will be fine like this:

TABLE Version
doc_id
version
Col (specific to version only)
* doc_id + version is PK (and FK of Master)

*If version is storing versions of documents. If you're using this as a lookup table for possible version values then you can just use version value as PK rather than an ID and then this will be the FKEY used in new "DOCUMENT_VERSIONS" a table that looks like "Version" above. Set referential integrity and cascading updates on so that any changes made in version lookup propagate to the FKEY values. The advantage of doing this rather than ID is that you need no joins for reporting unless there would be other pertinent fields from the lookup.

Glad to upack where this is not clear.


Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development

http://www.InsightDataConsulting.com

 

[GREABR]

My wife says I'm stubborn, go figure.

If:
TABLE Master
doc_ID
ColDocTitle
ColDocSubject
ColDocAction

TABLE Version
Doc_ID
Version
ColDocBeginDate
ColDocEndDate

And I am looing at a form of "documents'
ID
Title
Subject
Action
Version
BeginDate
EndDate

As I Forward and Back, I only see Current Versions and Dates

At some point we are approved to extend the period.

Click "Extend Document" - Popup Form with Max(Version)+1 Enabled=False, (new) BeginDate and (new) EndDate (for this version), Save Version Record.

Now the "Document" Form now displays new version and new dates.

This is how I envisioned it. (it actually does this now, except for that ADO thing)



"Don't be afraid to do things wrong, before you do them right"
Bradley Greaves

 

[Quehay]

Sounds good to me...now get developing ;-)

Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development

http://www.InsightDataConsulting.com

 

[GREABR]

Jeffry,

One last advice type question.

Do I leave this thread as is, or is it within proper Forum etiquette to begin a new thread on the ADO Issue.

Like

"Why does Record Update but Forms does not"

Your Thoughts.

Thanks for your help, the Paul Litwin aticle cleared up many design questions.

"Don't be afraid to do things wrong, before you do them right"
Bradley Greaves

 

[Quehay]

New post for sure. Thank Jeremy he's the one making it available!!

Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development

http://www.InsightDataConsulting.com