Record Not Appearing in Form

[MatthewR]

I have a form named EDIT INCIDENT that is linked to a table that is named FATAL INCIDENTS. But when I enter a new record into the FATAL INCIDENTS table, it is not appearing as a record in the EDIT INCIDENT form.



However, there is another table called EVENT INFORMATION, which is related to FATAL INCIDENTS. The primary key for the FATAL INCIDENTS table is a foreign key in the EVENT INFORMATION table.

And I've found that once I create a record in EVENT INFORMATION that corresponds to the new record in FATAL INCIDENTS, then the problem goes away. That is, the EDIT INCIDENT form then happily displays the new record.

Why is this happening? Why is the record not appearing in the EDIT INCIDENT form right away? Please help!

[cbende2]

It's happening because of the way you have it designed:

[EVENT INFORMATION]>[FATAL INCIDENTS]>[EDIT INCIDENT]

With this structure, how could you have a record in EDIT INCIDENT if the parent record does not exist in EVENT INFORMATION?

You can post DB for analysis, or if you could provide more info about the forms and relationships. Maybe post a picture of your relationships, and a picture of your form. Or you can post your DB.

[MatthewR]

Cbende,
Thanks very much for your reply, and apologies for my delayed response.

I believe that FATAL INCIDENTS is the parent table, not EVENT INFORMATION. Hence, my confusion.

The site won't let me upload the database because it's too big, so I've uploaded screen shots of the Relationships and the Form.

I think I've managed to pinpoint the source of the problem, but I'm still not sure how to fix it.

Even though all of the fields in the EDIT INCIDENTS form come from the FATAL INCIDENTS table, for some reason, the Record Source property for the form is:

SELECT [FATAL INCIDENTS].*, [EVENT INFORMATION].Incident FROM [FATAL INCIDENTS] INNER JOIN [EVENT INFORMATION] ON ([FATAL INCIDENTS].[Fatal Incident #] = [EVENT INFORMATION].Incident);

I tried changing the Record Source to just FATAL INCIDENTS, but then when I try to search for a record in the EDIT INCIDENTS form, it gives me an error message, which I've also attached.

Any thoughts? Again, thanks so much for your help.

[cbende2]

Hmmm, I'm not sure why you're getting the error.

Could you please attach DB for analysis.

Send DB to Zip file>Go Advanced(located next to quick reply)>attachment>select file>upload>Insert Inline.

[MatthewR]

Okay. I've attached it.

Thanks!

[cbende2]

Why do you have [EVENT INFORMATION].Incident as part of your query?

Removing [EVENT INFORMATION] table from your query gives you all you're records.

[MatthewR]

Which query are you referring to?

(Please pardon my ignorance- I'm new to Access.)

[MatthewR]

Are you talking about the Record Source Property for the form?

If so, what should the Property be set to? I'm not exactly sure how to write it.

[cbende2]

Yes, the Record Source Property.

Just simply delete the [EVENT INFORMATION] table from it.

If you click the elipse(...) next to Record Source, the design view of SQL will pop up. Right click [EVENT INFORMATION] table and delete it.

[cbende2]

The reason why not all of your records from FATAL INCIDENTS were showing up is because you had the table EVENT INFORMATION as part of the query, and you had EVENT INFORMATION.Incidents in your query. Therefor, only records that are present in both tables would show up. So if you didnt have a record in EVENT INFORMATION that was not linked to FATAL INCIDENTS, then it would not show up in your EDIT INCIDENT Form.

Hope that explanation helps!

[MatthewR]

Okay, cool. I did that, and now I see that all the records for EDIT INCIDENT are there.

At the same time, that created a separate problem for me. Now, when I try to use my combo box to open and search the EDIT INCIDENT form, I get that "Enter Parameter Value" error message again- the one I attached to my earlier post.

The Combo Box is on the HOME PAGE form in the ADD/EDIT DATA subform. The Combo Box is unbound, with a row source of:
SELECT [FATAL INCIDENTS].[Fatal Incident #], [FATAL INCIDENTS].[Incident Date], [FATAL INCIDENTS].[Short Address] FROM [FATAL INCIDENTS] ORDER BY [Incident Date] DESC;

The button that says "Edit Incident Information" has the following code for On Click:
Private Sub Command85_Click()
DoCmd.OpenForm "EDIT INCIDENT", , , "Incident=" & Me.Incident.Value
End Sub

So now, when I click on that button, I get that error and I don't quite understand why. Do you know why that is?

I feel like the solution to this is staring me in the face, but I just can't see it.

Thanks very much.

[cbende2]

Matthew, I'll get back with you in an hour.

[MatthewR]

Okay, no problem. Take your time please.

I really appreciate your help.

[cbende2]

MatthewR, I'm not really sure how to solve the problem, was this working before the other change we made?

[MatthewR]

Yeah, it was working before.

At the same time, I feel like this problem is probably less difficult for me to solve on my own with some further research, compared to the original problem.

So, if you're not sure what to do about this, it's cool. I feel as though we've still made progress here.