Form/subform/query not working for me

[ve7epq]

I have a main form and on it INCIDENT is my unique number. From Main Form I call up form1. On form1 I have a subform that is in continuous form view. What I want to have happen is that information entered onthe subform is linked with the specific "INICENT" What ends up happening is that when I move through my various INCIDENTS the data in the subform gets mixed. What was entered under INCIDENT 2 ends up in INCIDENT 3 or 4. From Subform I call a query based on the table that holds the subform data. Any ideas? I have several continuous forms and I am doing the same thing wrong on all of them

Mike

 

[TheAceMan1]

How are ya ve7epq . . . . .

Wow! . . . . have a look at the following. Do yourself a favor and really read them!

Fundamentals of Relational Database Design

Normalizing Tables

Table Relationships

See Ya! . . . . . .

 

[willir]

Agreed Aceman...
Mike - What you are describing indicates that you may have a design problem.

You may also not have done is defined your relationships formally with the Relationship tool (from the menu, "Tools" -> "Relationships". Then add your tables and click and drag the primary key to the foreign key. If you dont know what a primary key and foreign key are, you definitely need to read the the documentation provided by AceMan.

The reason you are seeing the details in the subform being displayed for other incidents is because the data in the subform has not been properly linked to the main form. When you create a form / subform AFTER you have defined the relationships, Access will automatically create the linkage. To manually link form / subform, open the form in design mode. Make sure the Properties is open (from the menu, "View" -> "Properties")

Select a field control on the main form, and then select the subform. Look at the data tab. If the Child and Master link fields are blank, there is not linkage. Select the Child link field and then select the "..." button to the left. Use the drop down fields to link your primary (master) and foreign (child) keys.

Richard

 

[ve7epq]

Thanks both of you. But the problem isnt that I am seeing data from different incidents. The program is corrupting the table and reassigning the data to different incidents, quite maddeningly I might add. The master/child link is established. I have done it manually to make sure. If you look at NorthWind ORDERS it is the exact same concept they are doing....only theirs works. As I scroll through the incidents the data in the table for the subform under the field name INCIDENT is changeing.

 

[willir]

I am seeing data from different incidents. The program is corrupting the table and reassigning the data to different incidents

Corruption never happens this neatly. Corruption disable the database, not show random records.

I have seen something similar to what you described where the primary key was linked to the wrong foreign key in a different table.

Also coding may be writing the wrong key to the table.

As part of trouble shooting,
- check your relationships ("Tools" -> "Relationships")
- recreate the two forms, and see if the problem repeats.

Richard

 

[TheAceMan1]

ve7epq . . . .

We'll try and get you thru this . . .

Agreed [blue]Willir[/blue] . . .

Post the [blue]TableNames[/blue] and the [blue]Relationships[/blue] (linking fields) between them. [purple]Specifically as they appear in the Relationships Window![/purple]

Then post the [blue]RecordSource[/blue] property for each form. If the RecordSource is a [purple]query[/purple], post SQL of the query. Thats the INCIDENT form, Form1 & its subForm.

Open Form1 in design. Double-Click the small square box just to the right of the ruler. Click on the subform control. Click the Data Tab. [blue]Post whats in the Master/Child Link properties.[/blue]

These things will give us a good picture of whats happening.

See Ya! . . . . . .

 

[ve7epq]

Thanks for taking time with me over this issue. I am going to actually start from scratch tonight and redo the database. Because I was converting an existing database I am not sure where the error was introduced.

Will let you know if I am unsuccessful and have to go back to this one

Mike

 

[TheAceMan1]

ve7epq . . . . .

Do read those links I posted earlier! You'll be surprised how different you'll see things . . . . .

[blue]GoodLuck on your conversion![/blue]

See Ya! . . . . . .

 

[ve7epq]

I have. Unfortunately I am at the point of having to hire a programmer to fix the errors in the database. I have been digging deeper and I have some troubles in the VB code. As it stands, it is beyond my experience to analyze and fix

Mike

 

[willir]

Understood