Finding a record in one form from another

[bmhamilton]

I have two tables with a one-to-many relationship (it is actually a one-to-zero or one relationship). I have a form for each table. I would like to have a button on Table1 that goes to the corresponding record of Table2 or make a new record if it does not exist. I am currently using filtering, which works, but I would much rather not use filtering. I have been playing around, but my solution is starting to look very ugly, so there must be a better way. Any thoughts? (I am used to Java and still don't have my head around VBA unfortunately)

 

[zanaeira]

Hey, not 100% what you want exactly, but if I'm right you have 2 forms which are related by a certain field (let's say ID as an example). You want to click on a button on the first form to open the second form to its related record. The best way I can think of doing that is this:

DoCmd.OpenForm "Form2", WhereCondition:=[ID] = Me.ID

This seems to me the best way to do it. Hope this helps.

 

[bmhamilton]

That is the way I am doing it currently, but when it opens the new form, it filters the records and only that one record is available without un-filtering. Is there a way so it will not filter the records? I want to open the form and goto the record of interest.

 

[zanaeira]

Oh right sorry. I'm not 100% sure how to do this but I think you should be able o achieve it using DoCmd.GoToRecord

I've never tried this myself but I'll try it out and let you know.

 

[bmhamilton]

Thanks for the input.

I agree DoCmd.GotoRecord seems like a good choice. I just haven't figured out a nice way to figure out which record I would like to go to.

 

[zanaeira]

Hey, sorry I haven't had time to investigate into this further but I've had a thought and it might be better to navigate to the chosen record using a DAO recordset. I'm not very good at DAO which is why I didn't think of it before but I think this is actually how this should be done. I'll try to look into it soon and get back to you. If you come up with a solution please let me, I'm quite interested to know how you get around this problem. Hope this helps.