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

[theDBguy]

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.

If you can post a sample copy of your db with test data, we can help you better/faster (maybe).

 

[JMongi]

I'm going to post a sample DB. But in creating it, I ran through the code and the changing it to Me.OpenArgs DOES work. Now I get a debug.print of the value but an error on the last line to assign the value "you can't assign a value to this object"

 

[theDBguy]

I'm going to post a sample DB. But in creating it, I ran through the code and the changing it to Me.OpenArgs DOES work. Now I get a debug.print of the value but an error on the last line to assign the value "you can't assign a value to this object"

Probably seeing the sample db would help rather than guess... I'll wait...

 

[JMongi]

Here is the sample database. I imported just the necessary tables/queries/forms for this particular app.

 

[theDBguy]

Here is the sample database. I imported just the necessary tables/queries/forms for this particular app.

Hi. Thanks. Looks like you have more than one value you need to auto-assign to the Hours form. When I get past the ProjID, I get this other error.

You cannot add or change a record because a related record is required in table 'ProjTaskTypes'.

 

[JMongi]

Hmm...let me start looking at the sample DB in more detail...
Edit: you probably figured it out already but the starting form is FrmProjList

 

[Minty]

Use the form Load event to set control values, certain controls and properties aren't available at On Open.

 

[JMongi]

Since you got past the assigning of the value, I can't quite follow where you are at. But...
That information (record in project task types) shouldn't be changing. That information should selected by the combobox on FrmAddProjHours. It's recordsource is QryProjTaskLookup and should store the value in TaskIDFK.

 

[JMongi]

Use the form Load event to set control values, certain controls and properties aren't available at On Open.

That's ironic, I picked OnOpen because I thought that was the better place to make these changes and because that's the example MS used. Lol.

 

[theDBguy]

Since you got past the assigning of the value, I can't quite follow where you are at. But...
That information (record in project task types) shouldn't be changing. That information should selected by the combobox on FrmAddProjHours. It's recordsource is QryProjTaskLookup and should store the value in TaskIDFK.

Okay, I made a minor adjustment by using the Default Value property instead. See if this works for you.

 

[JMongi]

Investigating...Ok. I see what you mean:

Me.ProjIDFK.DefaultValue = ProjIDFKv

It also looks like you defined the second variable as the proper type to use as the ID since is should be a number not a string and used CLng to convert to a long integer.
Then the Nz is to protect CLng from trying to convert a null value.
P.S. Thanks for adding the save code, never got around to it...Ha!

 

[JMongi]

Things seem to be working nicely. Thanks! I've got other wrinkles, but I'll create a separate thread if I need to ask another question.

 

[theDBguy]

Things seem to be working nicely. Thanks! I've got other wrinkles, but I'll create a separate thread if I need to ask another question.

Hi. Glad to hear you got it sorted out. Good luck with your project.

 

[JMongi]

I see light at the end of the tunnel. Let's hope it's not a train!