Solved Access cannot find the specific record in a related table when creating a new record via form

[JMongi]

I'm rounding towards home on my hour tracker app and go to add a basic task hour record using my form and I get the following error:
"The Microsoft Access database engine cannot find a record in the table 'Projects' with key matching field(s) 'ProjIDFK'."

I think it's because I've passed a projID value around to pull the correct info and now it doesn't like working in reverse. I may have approached my record filtering in a not good way, so I guess we'll see. Here is the basic DB structure:

Here is the main form:

And its recordsource query:

The main fields are from the projects table with the hour tracker table Hours field pulled in so that I can sum my hours for display for each project.
To my knowledge, this part is working fine.

More to come in post #2....

 

[JMongi]

Clicking on the pencil will bring you to this form:

And here is it's recordsource query:

This one is more involved because of the data being displayed. The form itself is a continuous form where I've put the project information in the header since it's the same for all records. You can see where the focus of the click on the previous form is used to pass the appropriate ProjID to the query to pull just those records associated with that ProjID.

Since I didn't make the name a combo box, I can't directly edit values to test if editing on this form can successfully add the appropriate record in the "HourTracker" table. I'm going to do that soon if this thread doesn't point me in a different direction.

Clicking on the plus button brings you to the task hour addition form. More in post #3....

 

[JMongi]

Here is the form "FrmAddProjHours"

Default date of today, combo boxes to pull my task lists and employee names while storing the appropriate FKs and an Hours field.

I use the SAME underlying query as in post #2 as the recordsource for this form. Maybe this is where it is all falling apart. After adding a task name, employee and hours and then exiting the form (to save the record) I get the error from post #1: "The Microsoft Access database engine cannot find a record in the table 'Projects' with key matching field(s) 'ProjIDFK'."

I'll just leave it at that since I'm not sure of anything at this point.

 

[JMongi]

The more I think about, the more I feel that this last recordsource is the issue. If I was just doing a form tied to table "HourTracker", the only difference would be entering/selecting the projectid. In this case, I already know the project id. And, I don't really care what the current query is, or what records are already there. So, if I was to make the table "HourTracker" the record source for this form, how would I automatically pass the correct ProjID to it? That's probably what has me all twisted in my thinking.

 

[JMongi]

So I created another query to use a recordsource for my form as a test. It consists of all of the fields from HourTracker and feeding the ProjID to it. I can run the query and it works fine. So when I bind the add task hours form to this new query I get the following error when I try to add a record:
"You cannot add or change a record because a related record is required in table 'Projects'.

As a note because it may be easy to miss and could be the root of the issue. I changed the join type on the project list query in post #1 so that I pull all of the projects whether or not they have any hours with them or not.

 

[JMongi]

Man, troubleshooting is hard when your not even sure you have the right idea of what's supposed to be happening.

 

[JMongi]

Just to clarify post #6.
I understand that a form is just manipulating fields when it's recordsource is a table. The nuances of how that interaction occurs under the hood when the recordsource is a query is less clear to me. Especially when I try and think about the various types of queries. I think I'm going to decompress, grab a coffee and approach it again.

 

[theDBguy]

So, if I was to make the table "HourTracker" the record source for this form, how would I automatically pass the correct ProjID to it?

You have two optioins:

1. Use a Form/Subform setup, so you can use ProjID as the Linked Fields, or
2. Use the OpenArgs argument to pass ProjID and then assign it as the DefaultValue for ProjIDFK

 

[JMongi]

So, is this not accomplishing the same thing?

The FrmProjList is active and hidden in the background at the time of form launch.

All my queries seem to run fine, its when I go to add a new record that it falls apart.

 

[theDBguy]

So, is this not accomplishing the same thing?

View attachment 89089
The FrmProjList is active and hidden in the background at the time of form launch.

All my queries seem to run fine, its when I go to add a new record that it falls apart.

I don't think it does. What it does is "filter" the resultset of the query. If you view it in Datasheet mode, you will see an "empty or blank" value for the new record, which is what you're trying to do when you try to add one. So, when you fill out the rest of the fields, the one you want is empty, isn't it? That would be my guess.

 

[JMongi]

If I come off frustrated in any posts I apologize in advance. I feel like this is going to to turn out to be something fundamental and basic that I missed causing me so much headaches. For clarification, Project Hours Form is not a typical split form with a single project id record and then a continous subform for the hour tasks. It's just a single continuous form.

 

[theDBguy]

If I come off frustrated in any posts I apologize in advance. I feel like this is going to to turn out to be something fundamental and basic that I missed causing me so much headaches. For clarification, Project Hours Form is not a typical split form with a single project id record and then a continous subform for the hour tasks. It's just a single continuous form.

Did you try any of the two approaches I suggested yet? If none of them work, then we can try to figure out another way.

 

[JMongi]

I think I'm wrapping my mind around what you said. What I was doing was filtering the query results for display purposes (perfectly fine) but not having a value out there as a default value I need to pass back into my structure when I'm adding a new record to that query (to then go out to tables as appropriate).

Since my project hours form is not setup in a typical form/subform setup (for better or worse) let's tackle option 2 using OpenArgs. If my googling worked, I need to launch my add hours form using OpenArgs to pass a defulat ProjID. Do I understand the concept (if not the implementation?)

 

[JMongi]

Plus, I have my coffee now. Surely everything will work out fine now!

 

[theDBguy]

I think I'm wrapping my mind around what you said. What I was doing was filtering the query results for display purposes (perfectly fine) but not having a value out there as a default value I need to pass back into my structure when I'm adding a new record to that query (to then go out to tables as appropriate).

Since my project hours form is not setup in a typical form/subform setup (for better or worse) let's tackle option 2 using OpenArgs. If my googling worked, I need to launch my add hours form using OpenArgs to pass a defulat ProjID. Do I understand the concept (if not the implementation?)

You got the idea!

 

[JMongi]

Cool. As is so often the case it seems, the examples Microsoft uses to illustrate how a property is supposed to be used is so needlessly complicated as to be worthless for me. But, let me give it a try and see if I can figure out the syntax on my own first....

 

[theDBguy]

Cool. As is so often the case it seems, the examples Microsoft uses to illustrate how a property is supposed to be used is so needlessly complicated as to be worthless for me. But, let me give it a try and see if I can figure out the syntax on my own first....

No problem. For what it's worth, I would probably recommend the first approach. It doesn't require you to use any code. So, maybe it's easier to implement. Good luck!

 

[JMongi]

It's probably my fault in not going through the effort of setting up a form/subform and just putting the project info in the form header so that it looks similarly. I'll look at both options. I almost have the 2nd one working but I'm not getting the pass through. Here's what I have:

Private Sub Image10_Click()
    Dim ProjIDv As String
    ProjIDv = Forms("FrmProjList").ProjID.Value
    Debug.Print ProjIDv
    DoCmd.OpenForm ("FrmAddProjHrs"), , , , , , ProjIDv
End Sub

This codes works fine up through the Debug.Print as it prints the ProjID of "1".
Can I use a variable to pass an openArgs or does it HAVE to be an actual string?

Reason I am asking, when I try to pull the OpenArgs in the form open event....

Private Sub Form_Open(Cancel As Integer)
    Dim ProjIDFKv As String
    ProjIDFKv = Forms!FrmAddProjHours.OpenArgs
    Debug.Print ProjIDFKv
    Me.ProjIDFK.Value = ProjIDFKv
End Sub

The Debug.Print there does NOT work. the OpenArgs appears to be null.

 

[theDBguy]

Can I use a variable to pass an openArgs or does it HAVE to be an actual string?

Yes you can!

The Debug.Print there does NOT work. the OpenArgs appears to be null.

What do you get with this?

ProjIDFKv = Me.OpenArgs

 

[JMongi]

Taking a quick lunch, but to answer your question. That's what I tried first and it feels like it was doing the same thing but getting a different error. I'll try in a minute.