I DON'T UNDERSTAND WHY MY TABLES ARE NOT BEING UPDATED 3

[chubby]

I don't understand why my tables won't update when I add a new case #.

ACCESS: 2000
OS: Win NT 4.0
# of Users: 15
# of tbls: 12
1 pri key: on the master tbl called case #, all other
tbls contain just the case # and Enforce
Referential integrity on all tbl's.

Forms type: Tab form and Sub tab forms.
Why Tab form: So I can use all 12 tables on one form.

Problem: Why won't access add a new record to all 12
tbls? I thought thats what Enforce Referential
integrity was going to do for me?

I need is this:When a end users I adds a new record
1. Access adds a new record to all 12
tables. How can I make that happen?

PLEASE HELP...

 

[sko]

Hi Cubby,

Is this the same problem you were working on before?
Incident table
CaseNo
other fields

Safety table
CaseNo
other fields

On your tab form, the case number will be added to your safety table when you add the rest of the safety information.

Are you saying you want a safety record saved with the case number and the rest of the fields blank? I'm not sure why you want that if you are treating the incident table as your main table. I would think an incident needs to exist before you add safety information and that you wouldn't need a record in the safety table until you were actually ready to record this information. What am I not understanding about the process?

 

[Darrylles]

Hi Chubby,

You need to understand relational database theory before you can even understand an answer to your question.

Just because Access is available with Word, Excel etc, don't expect the same kind of simplistic usage. Access is an RDBMS - people study for 2-3 years to understand RDBMS (and Access is pretty low in pecking order for RDBMS's).

You can 'knock' simple tables, queries and reports together with Access.

You seem to be using tables as records.

You may want to study some MS Access examples...

Regards,

Darrylle

"Never argue with an idiot, he'll bring you down to his level - then beat you with experience."

 

[chubby]

OKAY I'll read up on relational database theory. Also thanks for your help to sko...

 

[Quehay]

Chubby,

There's no substitute (nor excuse) for not doing the parallel study on your own, but this forum is here to facilitate your learning. (This particular forum is probably the one most likely to have newbie questions--no one is in this forum for help if they already know how to correctly allocate Oracle tablespaces for their tables, temp tables, and indexes.)

Jeff Roberts
Analysis, Design, & Implementation
RenaissanceData.com

 

[Steve101]

Chubby that if you use subforms with LinkChildFields and LinkMasterFields set for the various subforms on the tab pages, you might just achieve the effect that you are looking for without too much effort. This will not auto generate 12 records associated with the master record, but will generate the associated records where any info is provided. Then with appropriate queries, you'll still be able to draw all of the info together in the way you want.

Have a go at what I'm suggesting; we're all happy to pitch in if required,

Marching on ....

Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au

 

[chubby]

I'm still having problems with all of these tables. Example: My end users goes to put a record in the incident form He/she starts out by entering a case # in the incident form which happens to be the primary key. But when they try to move to the next tab on the form and it keeps saying " YOU CANNOT ADD OR CHANGE A RECORD BECAUSE A RELATED RECORD IS REQUIRED IN TABLE _ASSESSMENT"

question how do I it setup the DB to when the end users adds a case # to the incident table, it's also added to every other tables so that error msg doesn't pop up? or Do they have to manually add that case number to every table?

 

[sko]

Chubby,

You'll get this error if you created your Incident form as a subform on the first tab.

 

[chubby]

So, how can I fix this problem? My end users want to be able open a new record and all tables contain the case #...
Do I switch the tab order?

 

[Steve101]

Chubby, I think that the problem may be caused by the fact that the record is not being saved as you move from one tab to the next. As a result, as you move to the next tab after creating a new record, the record has not yet been saved to the database.

Try adding the following code to the OnChange event of your tab control. It will ensure that the record is saved every time the tab page is changed.

Private Sub TabCtl0_Change()
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
End Sub

There are better ways to optimise this code, but see if this solves the problem first.

Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au

 

[sko]

Steve,

I think, from previous posts, that Chubby is expecting the case numbers to auto generate records in the related tables before the rest of the information is added to those records. He's placed the tables in a 1:1 relationship because he says he needs more fields than one table will allow. He says there are no repeating groups in the tables but I suspect the data isn't normalized. I'm not sure why he's not making the case number the primary key in all his tables.

In Chubby's original post, LittleSmudge suggested opening separate forms and asked how he'd like to see them linked but didn't get an answer. I've suggested a tabbed form making Incident the main form on the first tab and placing sub-forms on the remaining tabs. I think either he's made them all sub-forms or his table relationships have been changed.

 

[sko]

Chubby,

Make the FORM recordsource tblIncidents. Place the incident table fields on the first tab by dragging them from the field list. (Don't import a pre-designed form like you did for the other tabs.) If your subforms on the other tabs show 'Link Child Fields' Case Number and 'Link Master Fields' Case Number it should work.

Add a record to the Incident tab. Move to the Assessment tab. You will see the case number on the Assessment tab BUT at this point the case number will not be saved in the Assessment table. The Assessment record will not exist in the Assessment table until you fill in the OTHER fields on the Assessment tab and save the record.

 

[chubby]

sko, I did that!!! When I first designed the tab form you suggested, but here's what gets me. The 'ASSESSMENT' tab is the fifth tab out of ten or so. The tabs are in the order the data entered into the system.

Example: A case is called in: The in-take person inputs the incident information 'INCIDENT' section tab one. (Note: the in-take person assigns the case number) Then a caseworker inputs the 'VICTIM' and 'SPONSOR' information in on Tab two and the 'ALLEGED OFFENDER' information is inputted on tab 3. A Safety Supervisor will then input the 'SAFETY RESPONSE' information using tab 4 and the 'SAFETY ASSESSMENT' information tab 5 and so on, get the idea? They can never get the far. Information is inputted in the order of the tabs it just worked out that way. I was thinking of just adding a way they could just add the case number to the safety 'ASSESSMENT' tab or any tab that pops up and clear up the problem, but why do that, when I know theres a way when you add a new record its automatically adds the case number to each record.

All one more thing,
1. All relationships are one-to-many.
2. All tabs have their own table they connect directly to.
3. Incident table is the base form all others are sub forms
4. Some of the sub forms are tabbed, because they can have
More then one victim or alleged offer in a case.
5. Front_end / Back_end setup on a network
6. Each personnel computer has a front_end MDE file copy
of the DB.

I'm sorry for writing a book, but you have helped me so much, and I really need this to work. I took 18 excel files and tried to turn them into something more user friendly. Thanks to each person who has helped me.
chubby

 

[Steve101]

Chubby,

Send me a copy of the database; I'll be happy to have a look at it. Remove any confidential data leaving only sample data, sufficient for the program to run to reproduce the problem.
Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au

 

[chubby]

Hey, I got it!!!!! It works!!!!!! YES, BABE!!!! I don't Know what to say....

I just re-designed my form and it works just the way my end users wants it too. I removed the Case # from being shown on the every Tab and now it works. For some reason when I did that, it worked. Now when I add a new record, then add a case number to the record, that case number is added to all 18 tables.

Thanks to everyone (specially sko) for all the help... Hey stand-by because I'm sure I'll run into more problems. You guys are that greatest..... I wish I was half a smart as you SKO!!!
chubby

 

[sko]

Glad it's starting to come together but now that you've given us more information about your tables (and you've discovered you don't have a 1:1 relationship between them) you've got me curious.


Some of the sub forms are tabbed, because they can have more than one victim or alleged offender in a case.

This doesn't sound like the place for a tabbed form to me. Are you saying you have one tab for each victim?

All tables are one to many.

Are they all one to many with Incident? I understand that one incident can have many victims. Can one Incident also have many safety responses, assesments? If one incident has many victims do you need to associate the specific offender with a specific victim? Is it like this?

Incident 1

Victim 1
Victim 2

Offender 1
Offender 2
Offender 3

Of more like this?

Incident 1

Victim 1
Offender 1
Offender 2

Victim 2
Offender 1
Offender 3

 

[Steve101]

Well done guys; a good effort. Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au

 

[chubby]

sko it more like this:

Every incident has a unique 9 digit case # in which they input. I use the case # as the primary key for every table.
I have 22 tables so far

One Incident would look like this:
1. # of Victims (can be up to 6)
2. Sponsor (only one sponsor per case)
3. Alleged Offenders (can be up to 6)
4. Safety Assessment (one done on each victim)
5. Safety Response (one done on each victim)
6. Case notes
a. one for each victim
b. one set for each Alleged Offender
7. Domains
a. 7 for child (one for each victim)
b. 5 for spouse (one for each victim)
8. DD 2486 (one for each victim)
9. Risk Assessment summary
10. Risk Assessment findinds
a. Done 3 times during an incident
11. Intervention plan
12. Case Review commmittee Presentation
13. CRC Minutes
14. FINS CRC QA review Checklist
15. CRC/ FINS Concurrence record
16. CRC final Case Review

And that's my nightmare in a nut shell!!!!!



Of more like this?

 

[AlexMidd]

This is where you need to sit doan and plan out the structure of the database on paper, using Systems Analysis principles. As someone said earlier, this is the sort of stuff that people study at University for 3 years or more. Your requirements are sufficiently complex to require a considerable amount of time working out entities, their relationships with each other, then the tables etc, normalising the whole lot (can take several iterations) and finsihing up with something that should be able to be converted very easily into a database.

I know we are all here to help (and happy to do so) but there comes a point where the task is complex enough to require professionals working a lot of hours on it - there is insufficient space or time to adequately answer it here.

I hope it works out for you, or someone has the time to help you with your design, but this is where the need for getting to basics is required, otherwise you will be forever chasing your tail and every time one problem is sorted out another will apear. There is no substitute for full design planning being done at the outset.
Have fun! )

Alex Middleton