Form to open to specific record (DB attached) Question

[d9pierce1]

The form to open is unbound, sub forms are bound

[Micron]

If that is a combo rowsource and you have the correct number of columns in the combo then the column references would be
[SystemCategoryID] = 0
[SystemCategoryTypeID] = 1
[SystemCategoryName] = 2

but I'm not seeing how this combo reference is related: Me.cboAccountTypeID.Column(1)
Also, when you provide a wherecondition it should equate to some value, not an expression, so I don't see how this is correct: = Me.cboAccountTypeID.Column(1)

I think that instead of saying "where the value = what is in some control" you're saying "where what's in that form control = 5" and it may not be true.

Maybe it's time to post a compacted and zipped db copy.

[Welshgasman]

Maybe it's time to post a compacted and zipped db copy.

@Micron,
Link is the very first line of the first post.

[d9pierce1]

I dont understand why this doesnt open up the form, and go to record on first subform? See Code Below
It should open form, go to record 5 on the subform but it only goes to first record?
Baffles me and is driving me nuts.
Have tried many different ways to solve this but nothing I do seems to get correct results?

DoCmd.OpenForm "frmSystemCategoryData", , , "Forms!frmSystemCategoryData!sfrmSystemCategoryType.Form!SystemCategoryTypeID=" & cboAccountTypeID.Column(1)

Thanks
Dave

[Micron]

@Micron,
Link is the very first line of the first post.

Doh! I guess I got sidetracked by the WHERE and Eval stuff. So has anyone taken a look? It seems odd that we're this far into it if they did. If not, I'll hold off because by the time I get back this afternoon it would probably have been solved.

[Welshgasman]

Normally subforms would be linked to mainform. So when you select a record on the mainform, the subform displays associated records.
You are not doing that.

So if this was me trying to do it this way, I would pass in the ID to the mainform as an OpenArgs.
Then in the mainform test to see if something has been passed in.
If it has, then use that to locate your subform records.

[June7]

I viewed db and made comments in post 4.

[Gicu]

Dave,
Sorry for being late, can you please quickly show an example of what do you expect to see when clicking the Edit. I did download the sample and looked at the table but not sure how they relate (tblCOA with the tblSystem...), you want to select the correct records in the first two subforms or all three?
Cheers,

[d9pierce1]

Hi Gicu,
Thanks
What I am seeking to do here is when I click edit button, it opens form frmSystemCateegoryData and opens it to that record so i can edit without having to seek it out!
Basically, it would set first subform to record (In this case 5) on type, second subform would show category of the combo, and the third would show the record that came from combo for subcateegory.
I was recently just trying to get it to do the first subform and may need to edit the second combo on the form frmCOA to include all the fields so that one combo could run this.

If I click edit, it would open up the form, then go to the specific records from that combo. If I select the first record in my form frmCOA, then the opened form should show
Category Type shoud be 5 or Acct-Accounts Type, Category would be Asset, and sub category would go to the record, I attached a photo....

I have a different form that I use and the second code in first post works very well, this is a bit different but its mostly to edit a mistake without haaving to filter out hundreds of records to correct it!

[Welshgasman]

Just noticed you Catagary is spelt wrong.
So if you put 3 hidden controls for each of the subform links on the mainform and set them on load from openargs, and linked the subforms correctly, that would likely do what you want?

[d9pierce1]

Hi Guci,

Here is a better screen shot,
If on frmCOA, and I select first record and hit edit
I want the frmSystemAccountData to open up and
Go to the specific records in the subforms.
Category Type would show Acct-AccountType
Category would show Asset
CategorySub would show Checking
I hope this makes sense to you....
What ever record I select in the frmCOA
Then I wanbt it to open to that record...

I want to force any changes to that form to edit something in the other form.
Otherwise it creates a new record... Plus that is used elsewhere for other combos that call on it.
Thanks
Dave

[Gicu]

Here you go Dave, I think this should give you what you want, I use recordsetclone navigation to go to the desired records. By the way, you might want to have a closer look at your tables, I don't think you need to store the TypeID in the subcategory table as you already have a link to the related category table that stores that information.
​Cheers,

[d9pierce1]

Thank you so much!
This works like a charm!!!!
You are just the best my friend!!!!
I will look over the other tables as recomended too.
Again,
Thank you
Dave

[Gicu]

Not really Dave, there are others here much, much better and because that, they are probably much busier in their professional life to give entire solutions; me on the other hand have lots of time on my hands....
Cheers,

[Welshgasman]

Best to understand how it was done, else you will just be back when you need to amend it.
If you do not work the 'Access way', then you generally need to do a fair bit of work yourself.

One of the hardest things I had to get used to was Access automatically saving the record, unless you stop it.
However I have rarely coded New,Save buttons in any of my DBs