Forms Conversion

[danbl]

I have a database I am updating which has four individual data entry forms. I want to combined these into one form as they all have three fields in common and will be much more efficient for entering data.

I have successfully placed all the fields from the associated tables onto a form but I can't get it to allow data entry even though the individual forms work. I also tried using a tab control but again have been unsuccessfull with making this do data entry. In both cases when I go out of design mode the forms show the data that already exists but no data entry capability.

I realize there is something, probably simple, that I have ommitted. Any ideas on how to address this ????

 

[vbaInet]

See this link for the reasons why it is not updatable:

http://allenbrowne.com/ser-61.html

Look into using a tab control which will help spread out the layout and use subforms on each of the tabs.

 

[danbl]

Being a novice at this, I looked at the link and can only see one possible issue. I have "Relatinships" between a "Date of Birth" filed which is common to all tables. This is not a "Primary Key" field. Will making it so in all tables help? Would I need to do this in all four tables?

I did place the fields from the individual forms on four individual tabs, but I am at a total loss as to " use subforms on each of the tabs " and how to accomplish this. How does that change my situation?

Thanks for your help.

 

[vbaInet]

What you've most likely done is create a query which is based on several tables, and you've selected most or all of the fields from each of these tables. In a situation where the joins in the query are too complex, the query becomes read-only. Could this be your situation?

If the DOB is part of one or more links between the two tables then it's ok.

With a subform you can have individual record sources which will be based on the individual tables. These subforms can then be linked to each other via a common ID of some sort. Maybe you want to read up on subforms.

 

[danbl]

The form is based on the tables and not a query. I have Access 2003 Bible and will read up on subforms to see if I can figure it out. Do I need to make the date of birth field a primary key field in each of the four tables?? How many sub forms can you have within on form?

 

[vbaInet]

If it's a table then look at the AllowEdits and the AllowAdditions properties of the form. Ensure that they are both set to Yes.

There's no limit per se but you won't go putting over 10 subforms would you?!?

Let's see a screenshot of your relationships.

 

[danbl]

The allow additions and edits is set to Yes.

As to the number of subforms, I would have the main form for say table1, and three subforms which would provide the data entry for the other three tables.

My ability for a screen shot is not working. The relationship is table1DOB to table2DOB to table3DOB to table4DOB.

 

[vbaInet]

Why are all these table names prefixed with an incrementing number followed by DOB? This would indicate that your tables aren't normalized.

When you say data entry is not allowed, what do you actually mean? It allows you to click the New button but after adding the details it doesn't get saved? Or the New button is disabled?

 

[danbl]

The numbers only represent the different tables and are not actually in the real table names. Remember I am a novice !! but I think part of the issue is I have no New buttom (embarassed)!

After creating the tabs for the different forms, in open mode I can view all the data for each form that I have finished the layout. I can not edit anything and can not get to a new record.

So I need to add a command button that in some way will take me to the next record???

 

[vbaInet]

There's a New Record button on the Navigation Bar. I'm sure you're aware of that? So no you don't really need one unless you want a separate one in the form of a Command Button control.

Let's see your db. Also indicate which is the problem form.

 

[danbl]

Your right I am aware of that .... but when you elect to go to a new record nothing happens. When you reach the last record, the arrow for new record is grayed out so you can not get to a new record. Also with making sure edits and additions are allowed you still have no functiionality.

 

[vbaInet]

I will insist again that even though you say your form is not based on a query it is using a SELECT statement. If the Record Source property of your form starts with SELECT then it is based on a Select statement. You have joined too many tables together causing it to become read-only.

Use subforms.

 

[Simon_MT]

You can work this out for yourself, if using are using a query. Within that query, if you can not add records you sure ain't going to able to on the form that associated with that query.

What are you taking to acheive?

Simon

 

[danbl]

Ok, I went to the Record Source and you were correct. Here is what it contains:


SELECT t_Advanced_Directive.DOB, t_Advanced_Directive.Date, t_Advanced_Directive.Physician, t_Advanced_Directive.[PAT at AMAASS], t_Advanced_Directive.Q1, t_Advanced_Directive.NA_1, t_Advanced_Directive.Q2, t_Advanced_Directive.NA_2, t_Advanced_Directive.Q3, t_Advanced_Directive.NA_3, t_Advanced_Directive.[PAT by Phone], t_Advanced_Directive.Q5, t_Advanced_Directive.NA_4, t_Advanced_Directive.Q6, t_Advanced_Directive.NA_5, t_Advanced_Directive.Q7, t_Advanced_Directive.NA_6, t_Advanced_Directive.[PAT Phone Cmt], t_Advanced_Directive.[All Patients], t_Advanced_Directive.Q8, t_Advanced_Directive.NA_7, t_Advanced_Directive.Q9, t_Advanced_Directive.NA_8, t_Advanced_Directive.Preoperative, t_Advanced_Directive.Q10, t_Advanced_Directive.NA_9, t_Advanced_Directive.Q11, t_Advanced_Directive.NA_10, t_Advanced_Directive.Interoperative AS t_Advanced_Directive_Interoperative, t_Advanced_Directive.Q12, t_Advanced_Directive.NA_11, t_Advanced_Directive.Q13, t_Advanced_Directive.NA_12, t_Advanced_Directive.Postoperative AS t_Advanced_Directive_Postoperative, t_Advanced_Directive.Q14, t_Advanced_Directive.NA_13, t_Advanced_Directive.[ADVANCE DIRECTIVES IMPLIMENTED], t_Advanced_Directive.NA_14, t_Advanced_Directive.Comments AS t_Advanced_Directive_Comments, [t_Perioperative Data].DOB2, [t_Perioperative Data].Date2, [t_Perioperative Data].Physician2, [t_Perioperative Data].[Peroperative Indicator], [t_Perioperative Data].PQ1, [t_Perioperative Data].PNA_1, [t_Perioperative Data].PQ2, [t_Perioperative Data].PNA_2, [t_Perioperative Data].PQ3, [t_Perioperative Data].PNA_3, [t_Perioperative Data].PQ4, [t_Perioperative Data].PNA_4, [t_Perioperative Data].PQ4a, [t_Perioperative Data].PNA_4a, [t_Perioperative Data].PQ5, [t_Perioperative Data].PNA_5, [t_Perioperative Data].PQ5a, [t_Perioperative Data].PNA_5a, [t_Perioperative Data].PQ6, [t_Perioperative Data].PNA_6, [t_Perioperative Data].Interoperative AS [t_Perioperative Data_Interoperative], [t_Perioperative Data].PQ8, [t_Perioperative Data].PNA_8, [t_Perioperative Data].PQ9, [t_Perioperative Data].PNA_9, [t_Perioperative Data].PQ10, [t_Perioperative Data].PNA_10, [t_Perioperative Data].PQ11, [t_Perioperative Data].PNA_11, [t_Perioperative Data].PQ12, [t_Perioperative Data].PNA_12, [t_Perioperative Data].PQ13, [t_Perioperative Data].PNA_13, [t_Perioperative Data].PQ13a, [t_Perioperative Data].PNA13a, [t_Perioperative Data].PQ14, [t_Perioperative Data].PNA_14, [t_Perioperative Data].Postoperative AS [t_Perioperative Data_Postoperative], [t_Perioperative Data].PQ15, [t_Perioperative Data].PNA_15, [t_Perioperative Data].PQ16, [t_Perioperative Data].PNA_16, [t_Perioperative Data].Comments AS [t_Perioperative Data_Comments], [t_Perioperative Data].PQ17, [t_Perioperative Data].PQ18, [t_Perioperative Data].PQ19, t_DVT_Data.DOB3, t_DVT_Data.Date3, t_DVT_Data.Physician3, t_DVT_Data.BMI, t_DVT_Data.[History of VTE Prophylaxsis in the Past], t_DVT_Data.[ASA Class], t_DVT_Data.[Cardiovascular History], t_DVT_Data.[Ca Channel or Beta Blocker Medication], t_DVT_Data.[Antihypertensive Medication], t_DVT_Data.Position, t_DVT_Data.Position_2, t_DVT_Data.Position_3, t_DVT_Data.[VTE Prophylaxsis Used], t_DVT_Data.[VTE Prophylaxsis Used_2], t_DVT_Data.[VTE Prophylaxsis Used_3], t_DVT_Data.[VTE Prophylaxsis Used_4], t_DVT_Data.[VTE Prophylaxsis Used_5], t_DVT_Data.[VTE Prophylaxsis Used_6], t_DVT_Data.[VTE Prophylaxsis Used_7], t_DVT_Data.[Therapy In Place Until LII], t_DVT_Data.[Comment(s)] AS [t_DVT_Data_Comment(s)], t_DVT_Data.[Anesthesia Type], t_DVT_Data.[Location of Surgery], t_DVT_Data.[DVT Occurred], t_DVT_Data.[DVT Tx], t_DVT_Data.[History of Breast Cancer], [t_Temperature _Monitoring].DOB4, [t_Temperature _Monitoring].Date4, [t_Temperature _Monitoring].Physician4, [t_Temperature _Monitoring].[Anesthesia Start Time], [t_Temperature _Monitoring].[Anesthesia End Time], [t_Temperature _Monitoring].TQ1, [t_Temperature _Monitoring].TQ2, [t_Temperature _Monitoring].TQ3, [t_Temperature _Monitoring].TQ4, [t_Temperature _Monitoring].TQ5, [t_Temperature _Monitoring].TQ6, [t_Temperature _Monitoring].TQ7, [t_Temperature _Monitoring].TQ8, [t_Temperature _Monitoring].TQ9, [t_Temperature _Monitoring].TQ10, [t_Temperature _Monitoring].TQ11, [t_Temperature _Monitoring].TQ12, [t_Temperature _Monitoring].TQ13, [t_Temperature _Monitoring].TQ14, [t_Temperature _Monitoring].[Comment(s)] AS [t_Temperature _Monitoring_Comment(s)] FROM ((t_Advanced_Directive INNER JOIN [t_Perioperative Data] ON t_Advanced_Directive.DOB=[t_Perioperative Data].DOB2) INNER JOIN t_DVT_Data ON [t_Perioperative Data].DOB2=t_DVT_Data.DOB3) INNER JOIN [t_Temperature _Monitoring] ON t_DVT_Data.DOB3=[t_Temperature _Monitoring].DOB4;


To the question of what I am trying to achieve? I want one form that allows entry of data into all the respective tables. As you can see DOB is common in all tables, as well as two other factors.

 

[vbaInet]

There's a join that Access doesn't like, or in more appropriate terms, there's a table within this relationship that makes it impossible for an update to happen directly. As already mentioned, you will need to look into using a subform.

Maybe we can provide some more insight. Let's see a screenshot of your RELATIONSHIPS diagram.

 

[danbl]

Okay here is the screen shot.

 

[vbaInet]

Right click each join (i.e. the line that joins two tables together) and select EDIT RELATIONSHIPS, tick ENFORCE REFERENTIAL INTEGRITY and ok it. Get a screenshot once you've done that for all joins and upload it.

Fyi: You don't need to zip the file unless it's above 2mb. It takes less time to view your file when it's not zipped.

 

[Simon_MT]

You could consider using subforms for progress of the Patient. This would independent data entry. This does solve see the whole procedure in one hit even with Union joins as they require thesame number of fields in each segment of the Union. the subForms will allow entry.

Simon

 

[vbaInet]

You could consider using subforms for progress of the Patient. This would independent data entry. This does solve see the whole procedure in one hit even with Union joins as they require thesame number of fields in each segment of the Union. the subForms will allow entry.

Simon

Using subforms has already been mentioned several times in previous posts. Now we're trying to establish what kind of joins those tables have.

 

[Simon_MT]

What happens if you are dealing with twins or encounter people born on the same date?

Simon