Open Form on Specific Record (97 SR2)

Topic

New Reply

I have two forms: one is a main form that has an ObjectID field. This field is also in another form called Description. I created a button on the MainForm that opens the Description form. Works great. It shows all the records for Description.

But, I want the Description form to open and show the record for the ObjectID that is selected in the MainForm.

Do you understand what I’m trying to say?

For instance, the MainForm is open and the ObjectID for that record is 985. I want to push the Description form button and open ObjectID 985.

If there is no DescriptionForm for ObjectID 985, then it should open with all fields blank, but have ObjectID 985 filled in with the number from the main form.

How can I do that?

Reply | Quote

Replies

Sounds like a ‘find specific record’ situation. Using the Wizard to create a button that opens another Form has an option to ‘find a specific record’ and produces code like the following:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = “My_form”

stLinkCriteria = “[MyID]=” & Me![MyID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Let us know if I’m not addressing your actual situation.

Reply | Quote

Very close. It opens to the correct form. But, if there is no existing matching record, I want it to pull the ID from the main form, put that ID number in the correct field and open with the rest of the form blank.

Reply | Quote

OK. I don’t have a ready code example for that (hopefully someone else reading this will).

You need to capture the ‘no matching record’ error — open the 2nd form to add a new record and supply the ID.
(I’ll go look for some code; perhaps some other Lounge Lizard will beat me to it.)

Reply | Quote

Thanks. I do not know code, so I wouldn’t know where to begin. Where DO you go to find code? Where would I start if I had no idea where to start?

Reply | Quote

Why don’t you go for a Main form and a subform approach.
When you select a record on the main form all the associated subform records will display, and if there are no records for the associated subform it will show all it’s fields as blank.
HTH
Pat

Reply | Quote

I agree, but I can’t seem to get it to work, which is why I resorted to the button. I can get the subform to display and it seems to be right. But, I can’t enter any data into it. I get error messages: “The Microsoft Jet database engine can’t find a record in the table “tblCollectionUse’ with the key matching field(s) ‘tblConditionReport.UseID”. And, “The LinkMasterFields property setting has produced this error: “The object doesn’t contain the Automation object ‘tblObjects.”

Ahhhh. This has been frustrating me for two weeks. I go round and round with it. I have tried creating subforms and then buttons. I would attach a copy of the database, but it’s 5mb and I can’t seem to strip enough out to get it smaller than 833kb zipped. I would love it if some brave soul would be willing to let me email this zip file and would look at this thing and see if you can help me.

I acquired this database and have been struggling with fixing it. Unfortunately, my knowledge of code is non-existent, and apparently, so is my knowledge of linking forms. I haven’t had this much trouble before with subforms.

Reply | Quote

Email to my address (you will find it in my profile).
Pat

Reply | Quote

Thank you very much for looking at this. I have emailed it to you.

Reply | Quote

I have made changes to your database, if you would let me know your email address I will send it to you as it’s 550k zipped.
All I have done is make the form work, I have done nothing else.
Pat

Reply | Quote

Thanks! I was in the process of deleting forms and re-creating them, but if you got the form to work then that will save me some headache. My email address is in my profile, but I will send it to you separately also. I appreciate the effort you put into this, Pat. Thank you.

Reply | Quote

I just hope it’s what you want. Happy to help.
Pat

Reply | Quote

Thanks, Pat. The tabbed forms work great. I noticed you had to take the USER ID field off the Condition subform. I was having trouble with that myself. And, I noticed you removed the duplicate fields from the subform. The guy who has me designing this wanted those to be duplicate so he could be sure they were on the right record. I managed to convince him that it is working the way he wants it to.

Again, thanks for your help.

Reply | Quote

Did you notice that I had changed the queries behind the Subforms?

Glad to be of help.
Pat

Reply | Quote

I’m confused.

I have quite a few questions.

1. What is your table structure of relationships? Is tblObjects the one side of tblObjectDescriptions (which I preumed to be the many side)?
2. tblObjectDescriptions (holding 10,000+ records) has records that are not in tblObjects. Is this correct?
3. tblCollectionUse has records that are not in tblObjects. Is this correct?
4. tblConditionReport has records that are not in tblObjects. Is this correct?
5. Subform yObjectDescription has the same Source Object (qryObjects – it gets data from 2 tables tblObjects & tblObjectDescription) as the main form. is this correct?

Your main form and subform yObjectDescription both contain fields that are in table tblObjects, I’m assuming this would cause a conflict.

Normally (as I would do it, but I cannot say that it is right) one would base your Main form upon a query (or table) which could refer to more than one table or queries. A subform is based upon a query (or table) that is not the same as the Main form. The link fields are used to link the Subform to the Main form.

I think you need to spend more time in designing what your Main form and Subform requirements.

What I think you want to do is base your Main form on the tblOBjects table, Subform yObjectDescriptions would be based upon the tblObectDescription table, and Subform yCondition2 would be based upon tblCollectionUse and tblConditionReport, but then again I’m guessing here.

HTH
Pat

Reply | Quote

I second Patt’s idea … it’s easier and if you do it by putting your Description subform on a different tab, you can keep the display uncluttered. Then the users just click the Description tab to see or add the related records.

You asked “Where DO you go to find code?”. I keep a library of code I have previously built, I search for examples on this lounge, both Helen Feddema’s site and Dev Ashish’s site have been very helpful resources for me. Basically lots of time searching, reading and asking those who know ever so much more than I.

Reply | Quote

Thomas,

I have tried to open the two sites you listed below, both come up with “cannot display page” could you give me the full URL for these sites?

Thanks!

Reply | Quote

Since Thomas is not online at the moment, I believe, here they are:

Dev Ashish’s Access Web
Helen Feddema’s home page

Reply | Quote

Was out, but see that Hans gave them to you.
Nonetheless, I fixed my original post so that the links work.

Reply | Quote