Linking 2 separate forms

[terces]

In the Agency List (form), i embedded in dbl click a macro in Agency Name (field) in which will open Project List (form) showing a filtered listing of projects from that Agency. The problem is whenever I add a record from that filtered form, error 3101 appears.
"The Microsoft Office Access Database engine cannot find a record in the table 'Agency' with key matching field 'AgencyID'."

I think the macro is missing with something to link between the 2 forms (like in subform with link master/child fields) but I am not a db developer to begin with so I have no idea.

I have attached the file for anybody's reference.

Need assistance please.

Thanks

 

[wiklendt]

it could be happening because you are closing the agency list form after you open and filter the project form. thus the project form has no way to reference the ID anymore because the referring form is closed (which is where the macro is that knows which ID is which).

one way to avoid this is to send OpenArgs to the project form, which is captured in the onOpen event of the project form, but not sure how to do this with macros (i'm a VBA girl).

hope that helps you some, let us know if you come up with a solution!

 

[terces]

Thanks for the help...I removed the close command but still having the same problem...
master/childs linking can be done in VBA right? even for separate forms?

i just got the idea but i don't know how to code it.

I suppose Ms. VBA girl can help. =]

 

[wiklendt]

terces,

unfortunately i don't think i can help. i spent about an hour fiddling with your database trying to get this to work, but there are two things i've never used before which prevent any progress: split forms; and macros.

the master/child properties in a form/subform scenario automatically manage the primary/foreign IDs in that relationship - even with openArgs (the VBA equivalent to TempVars - from what i can tell). but i'm not sure how to get this to work by using only a form filter. this will have to be one for someone else or for google...

good luck.
Agnieszka.

 

[terces]

I have been googling it for days...now i'm thinking to redesign my db...sob

 

[vbaInet]

The problem is the AgencyID doesn't auto populate so even if you move to a new record, the AgencyID needs to be entered.

 

[vbaInet]

I have been googling it for days...now i'm thinking to redesign my db...sob

I don't see anything wrong with your design. The number just needs to be entered in. If opened the Project List form without any filters and you enter a new record, what will the AgencyID be?

If you want it automatically filled in if you got to the form via Agency List then you can set the Default Value of the AgencyID field to the AgencyID in the Agency List form right after you Open the Form.

 

[terces]

Thanks for the assistance...but how to do so? use setvalue? Honestly I just embedded this macro from microsoft access templates and I am not really a db developer, just learned from asking and googling. Thanks again.

 

[wiklendt]

Ha!! got it!

what i did is added the Agency ID field to the Project Lists form and added "=TempVars!CurrentID" in the "Default Value". it now adds the AgencyID automatically

to do this, you also need to remove the "removeVars" part from the Agency Name macro. I would probably re-add it to the close form on Project Lists, though.

Hope that helps

(and don't worry - most of us here started out just like you!)

 

[terces]

It really sounds complicated but is sure solved my query. No need to redesign. Cheers!

you're amazing!. Thanks a lot!