Record Not Appearing in Form

[cbende2]

Ok, well please post back if you figure it out, I'd like to know how you go about solving it.

[cbende2]

Maybe look into reverting back to your original, and whenever you go to create a new incident, have it append that incident to EVENT INFO table also. I'll do a little looking into this.

[cbende2]

Ooh bro, I just caught a big problem in your database. You're relationships are not established correctly. You have the PK(autonumber) in [FATAL INCIDENTS] going to Incidents(date field) in [EVENT INFORMATION].

You may want to do a little research on relationships and Foreign Keys.

Basically, a PK(autonumber) will be linked to a FK(number).

[MatthewR]

Uh oh. You're talking about the Incident field in [EVENT INFORMATION], right?

But it's technically a number field, right? That's what it says in Design view. It's a lookup that has a hidden foreign key, a date, and an address, and only displays the date.

Is that a problem, that I have it displaying the date?

[cbende2]

Ooh, It's a lookup field....

Sorry I didn't realize that.

Not many people here use lookup fields. I actually have never used them before, therefor I have no experience with them.

Check out this link: http://access.mvps.org/access/lookupfields.htm

It just kind of talks about the different problems that will arise with lookup fields.

[MatthewR]

Well, I'm relieved that my relationships are okay.

But darn, that's too bad that there's all those problems with lookups. I've been teaching Access to myself, and I watched a couple tutorials that said that Lookups were useful. I didn't realize there were downsides.

[cbende2]

Same here

I think most access users are self taught, using forums and videos.

Hmmm, back to the original problem.

If we revert the change that we made you said it works.

Now... one option you could do is run an append query to add the new record created in FATAL to EVENT when you click the "save" button. That way, all new records will automatically be created in the EVENT Table.

[MatthewR]

Ah, that's good to know that I'm not the only one.

Yeah, that's an interesting idea, with appending the query when saving a new incident. I think I tried looking up how to do that once, and couldn't figure it out. Do you know how I would write that? Here's my current code for the save button on the ADD INCIDENT form:

Private Sub AddIncident_Click()
Call DoCmd.RunCommand(acCmdSaveRecord)
DoCmd.Close ObjectType:=acForm, ObjectName:=Me.Name
End Sub

[cbende2]

Hmm, we need to think of criteria for the append query.

Let me ask you... whenever you add a new FATAL record, will it have today's date. If so, you could run an append query WHERE Incident Date = Date()

[MatthewR]

Unfortunately no, it won't have today's date. When adding a new incident, the user only enters the date of the incident (which is always in the past), as well as the address.

[cbende2]

Or we can create a field specifically for this process.

In FATAL INCIDENTS you can add a field called something like... [NewRecord], make it a (Yes/No) Data Type.

Create an Append query Query1 to append all records from FATAL INCIDENTS where NewRecord Is checked Yes.

Create an Update query Query2 that updates all FATAL INCIDENTS where NewRecord is Yes to update to No.

When adding a new record, have the defaultvalue of [NewRecord] set to Yes. This Field does not have to be displayed anywhere. It is simply being used for this process.

After hitting the save button, run Query1, then run Query2

[MatthewR]

Okay, thanks so much! I need to bolt from the office in a few minutes, but I'll give this a shot tomorrow or Thursday, and I'll let you know how it goes.

[cbende2]

Ok sounds good!

[MatthewR]

So, I really liked your suggestion... but I was in a really stubborn mood today. It was driving me crazy that my command button wasn't opening and filtering the EDIT INCIDENT form like I wanted it to, especially because I've had similar problems before. So, I wanted to try to get to the bottom of it, in case it happens again in the future.

I did a lot of tinkering, and eventually I got it to work. I think there were 2 problems with what I was doing. First, my VBA for the command button was referring to the name of the control on the EDIT INCIDENT form, rather than the name of the control source. Second, my combo box held multiple values, whereas the corresponding field on the EDIT INCIDENT form only held one value, so that might have screwed things up. At least, I think that's what the problems were. I don't really know. All I know is that it works, and I'm a happy guy now.

The VBA that is working for me now is:
DoCmd.OpenForm "EDIT INCIDENT", , , "[Fatal Incident #]=" & Me.Text86

(Text 86 is the field on the ADD/EDIT data page that displays the Fatal Incident #)

I also found that it works as a Openform Macro as well, with this Where Condition:
[Fatal Incident #]=[Forms]![HOME PAGE]![NavigationSubform]![Text86]

So, my apologies for being stubborn and not following your suggestion. I'm sure it would have worked just fine, but I wanted to figure this out for my peace of mind.

Again, thanks so much for all our help on this.

[cbende2]

You're welcome, I'm glad you were able to solve the original problem!