Click on ID in Subform datasheet to open Single Form view

[GADTG]

Maybe I'm just saying it wrong (I'm self taught so my "jargon" leaves something to be desired) because I feel this is pretty fundamental and used a lot... but I've hit {yet another} wall in finding info. I'm already a solid month behind on this project so any help would be massively appreciated (by me and my poor employees who are way overdue on a better system).

I have a Project form, which holds StockArtProduction_Subform in datasheet view. I need for them to be able to click on the ID in the StockArtProduction_Subform datasheet and it open a single view form (StockArtProduction) with the correlating ID.

Ideally, it would also work for new entries in the StockArtProduction_Subform, but the StockArtProduction form would have to "auto populate" (did I use it right?) with the ID of the currently open Project form.

Again, our sanity and general well-being thanks you in advance.

 

[jdraw]

Do you have your form and sub form set up to use (my guess here) ProjectId as the link field?
And you must have a Record in your Main form before you can add a child record in the subform.
Sometimes it's easier to attach a dumbed down(no personal info; and enogh records to illustrate an issue) copy of your database -often after a Compact and Repair, and making a zip file to attach.

 

[pbaldy]

Is this what you're after?

http://www.baldyweb.com/wherecondition.htm

 

[GADTG]

Do you have your form and sub form set up to use (my guess here) ProjectId as the link field?
And you must have a Record in your Main form before you can add a child record in the subform.
Sometimes it's easier to attach a dumbed down(no personal info; and enogh records to illustrate an issue) copy of your database -often after a Compact and Repair, and making a zip file to attach.

I'm estatic to attach for review! It's all dummy info (that'll probably be fairly obvious). Just testing functionality because I've already built the pretty version once just to find out it didn't work... and was unsalvagable :banghead:

The link is the ProjectID. I don't know if this is normal, but when I created the form from from the tblProjects it automatically created the StockArtProduction_Subform. I was happy to have it, because it's where I was going anyways...

PBaldy- I have not done ANY VBA but it looks like what I'm thinking so I'll give it a shot.
I did try "on click" macro with [ID]=!Forms[StockArtProduction]![ID] but that just opened a blank form each time...

 

[pbaldy]

I don't have a VM running with that version right now, but try VBA code of

DoCmd.OpenForm "FStockArtProduction", , , "ID = " & Me.ID

The macro would probably work, but you need the reference to the subform:

http://www.mvps.org/access/forms/frm0031.htm

 

[pbaldy]

Oh, and if you need it:

http://www.baldyweb.com/FirstVBA.htm

 

[GADTG]

Thanks all I'll report back!

 

[GADTG]

DoCmd.OpenForm "FStockArtProduction", , , "ID = " & Me.ID

http://www.mvps.org/access/forms/frm0031.htm

Why are y'all such geniuses? That worked perfectly! THANK YOU x A MILLION! I hope I'm not pushing my luck, but any suggestions on clicking the "new" entry and having it automatically enter the ID from the open form?

 

[jdraw]

I recommend you do not use Lookups at the table field level.
see http://access.mvps.org/access/lookupfields.htm
If you want to use Lookups, then build appropriate Lookup tables and relate your tables accordingly.

If you need additional info, post specifics.

 

[GADTG]

I recommend you do not use Lookups at the table field level.
see http://access.mvps.org/access/lookupfields.htm
If you want to use Lookups, then build appropriate Lookup tables and relate your tables accordingly.

This is actually a new concept to me. But I want to do things right this time. So, to make sure I understand, every time I used the Lookup Wizard in a table field I should have created a separate table?

For example, I have tblCustomers and tblProjects. tblProjects has a lookup for CustomerID from tblCustomers. Instead, I should have a tblProjectsCustomers that links the two, and then bring it all home in a query later... then build my forms of queries?

 

[GADTG]

And I'll take the rest of the question over to the VBA section- since I guess that technically is where I am at. Thank you for getting me past my first hurdle!!!

 

[jdraw]

In a nutshell, yes. This "feature" was added by M$oft, but has not been accepted generally. It is not compatible with other database systems, and tends to get people into confusing situations. I would not use it. I am on various forums where it is discussed and there appears to be no promotion of this feature. Most experienced users advise Not to have lookups at the Table field level.

I looked again at your Relationships. I would also advise against Attachment fields, but others may disagree with me. I prefer to store such "things" in the file system and just record the path and identifying attributes in the database table.

I don't understand some of the free floating tables in your relationships window.

Do you have a clear, concise description of your business rules that this database will/should support?

 

[GADTG]

In a nutshell, yes. This "feature" was added by M$oft, but has not been accepted generally. It is not compatible with other database systems, and tends to get people into confusing situations. I would not use it. I am on various forums where it is discussed and there appears to be no promotion of this feature. Most experienced users advise Not to have lookups at the Table field level. I appreciate any general consensus of the experts in a field. I will likely go back and restructure. Break the habits now- because I actually love working with Access and would like to develop this skill more thoroughly.

I looked again at your Relationships. I would also advise against Attachment fields, but others may disagree with me. I prefer to store such "things" in the file system and just record the path and identifying attributes in the database table. I was on the fence about them as well. We have them in our current database and barely use them since all our artwork files are stored and notated just as you said.

I don't understand some of the free floating tables in your relationships window. I don't either. I don't know how to get rid of them or why they are there and the make me uncomfortable...

Do you have a clear, concise description of your business rules that this database will/should support? I feel like I do, but I'm very open about this process. Please, if you see red flags and have interest in helping me further I am a total pupil.

It's my business and my workflow system that we've been using for 5 years. The business has just grown so I'm trying to provide my employees with a better tool to support the growing number of printing processes. I've been noting the "struggles" with the simple database we use now and planning this for about 6 months. Now, has it translated the best into the database yet? Eh. Working on it.

EDIT: I do know what those floating tables are now. Sorry, I just went through troubleshooting with Plog on eliminating circular retlationships and they were apart of that. I did have a couple phantom tables I couldn't get off the relationship view even thought they had no relationships. But that was on the other version- sorry. I attached a pic of why they are there.

 

[pbaldy]

Why are y'all such geniuses? That worked perfectly! THANK YOU x A MILLION! I hope I'm not pushing my luck, but any suggestions on clicking the "new" entry and having it automatically enter the ID from the open form?

Happy to help! Did the second question get addressed elsewhere?

 

[GADTG]

Did the second question get addressed elsewhere?

Not yet. (un)Patiently waiting here: http://www.access-programmers.co.uk/forums/showthread.php?p=1342489#post1342489

Any suggestions?? I'm a sponge today and I LOVE it.

 

[pbaldy]

I was posting there as you typed.