Open Form & SubForm

Topic

New Reply

In Access 97,I have database with a Main form which includes a subform generated from a query. The setup is similar to the Northwind Orders Db, with a Details table to hold linking info.
I want to place a button on another form to open my Main form to a specific record. The button wizard guided me through all the steps and I thought I was done. But, when I click the button, I receive 2 consecutive requests to enter parameter values. The first one wants KeyField.MainForm, the second Keyfield.Details. I have no idea how to alter the VBA code to accomplish this behind the scenes, or even what parameters I’m supposed to enter. Is there a simple way to fix this? I don’t care if the form opens filtered to the single record or opens all records with the focus on the correct one.

Reply | Quote

Replies

I believe that I just finished a task that you are trying.

Are you using a Command Button, on a main form such as a main switch board, or is it on another form. The problem may be that you need to create a macro to open your main form.

Give me some more details.

Reply | Quote

Please disregard previous post. Was answering a question that you did not ask. The little hampster took a nap on the wheel.

Access is asking for parameters that you did not put in your query. Post some more details I will try to get it corrected.

Reply | Quote

Hi John,
The parameter values its requesting are definitely not part of the query, I believe are they are the Child and Master links between the Main form and SubForm. Also, a button to simply OpenForm works fine. Its just opening to a specific record that’s a problem and the Wizard wrote the code.
Is there a way to tell the OpenForm what these parameters are in the arguments? And why do I need to tell it?
P.S. Using a Macro does the same thing.

Reply | Quote

One more thing. If I just click the requestors away “OK”, “OK”, then the form opens to the correct record and with the subform data intact. This should be a clue to something but I can’t figure out what.

Reply | Quote

You may have done this. Select the subForm and the it’s properties. Under the Data tab fill in Link Child/Master Fields. I am using a textbox, which when filled in, populates the subForm.

Reply | Quote

Good try, but that’s not it. Everything seems to be set properly and the form opens normally fully populated with subform data as it should.
When I go into the Northwinds sample database and add a button to do the same thing as in mine, it works fine.
I know I’m missing some simple little thing, as you suggest, but in comparing my Db with the sample and in checking for errors I’m not finding it.

Reply | Quote

Well, unless your form, table, field and query names are exactly the same as the Northwinds sample, you aren’t doing exactly the same thing. So let’s start with a few questions:

1. Is the form already open–i.e., you’re trying to change the current record from another form or the main form is hidden and you want to unhide it and change the record?

2. Post the code behind the command button so we can see what’s wrong with it. There are several ways to do what you’re trying to do, but we need to know which way you chose in order to suggest corrections.

3. Is your KeyField field actually on the form, or is it only in the form’s recordset?

4. If the Keyfield is actually bound to a control on the form, what is the name of the control. You can confuse Access thoroughly by naming your control and your field the same thing.

Reply | Quote

Hi Charlotte,
1. The form I’m trying to open is closed.
2. Here is the code that the button wizard wrote:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = “ECN Form”
stLinkCriteria = “[IDNumber]=” & Me![IDNumber]

DoCmd.OpenForm stDocName, , , stLinkCriteria

3. The fields for both forms are the same and on the form. The one I want to open has it as a key field. The one I’m opening it from, does not.
4. I’m not clear on this question. The name of the Form field is [IDNumber] the name of the Table field is [IDNumber]. This was done by the Access form wizard. Aren’t a control and a form field the same thing?

Reply | Quote

On question 4, you have to realize that the wizard is stupid and is still writing Access 95 code, so don’t expect too much from it. No, the fields and the controls ase NOT the same, although Microsoft seems to ignore that fact to avoid “forcing” naming conventions on developers! You need to change the control names to things like txtIDNumber when it’s a textbox bound to the IDNumber field, in order to avoid confusing Access. Otherwise, calculated controls may wind up displaying #ERROR messages instead of values. In your case, make it …

stLinkCriteria = “[IDNumber]=” & Me![txtIDNumber]

and see if that makes a difference.

Reply | Quote

I assume you mean that a text box on a table is a field and a text box on a form is a control? Do I have the terminology correct now?
Changing the name does not help. Even replacing the name with an actual specific record value does not change the response.
i.e. stLinkCriteria = “[IDNumber]=3”
I still can’t help thinking that it has something to do with the way the Main form and subform are linked because (as stated in the first post) it is asking for parameter values for the link (fields or controls) for the two (forms or tables).
But what do I know?
The Main form was originally wizard generated. Then I looked at Northwind to see how to do the subform thing. I probably missed something. Although everything seems to work fine except for this.

Reply | Quote

[indent]

---

The first one wants KeyField.MainForm, the second Keyfield.Details.

---

[/indent] This sounds very much like an incorrect Child/Master parameter error. In the subform properties are the child and master set to KeyField and if so, is there a field on the main and subform named KeyField? Sorry if this sounds simplistic but sometimes simple answers are the best.

Reply | Quote

Oh boy! What’s the matter with Me!?
O.K. guys, sorry to make you all twist your brains for a neophyte like myself. Here’s what was happening. There was a procedure behind the form control [IDNumber]. That number was referenced by another control to concatenate letter characters onto it for another purpose. Both of these procedures were copied from other forms that have been in use for a couple of years. No problem, until I use them in a new form to which I add a subform. Now with the subform linked to the values entered to the table from those controls, when I try to do an OpenForn with LinkCriteria, Access gets confused.
Why? Because all the controls, fields and criteria values had the same name!
Simply adding Me! to the controls’ procedures alleviated the confusion. I have since gone through and renamed or qualified every identifier in the new database.
So, what’s wrong with Me!?
Nothing now, except possibly incurring the wrath of everyone whom I’ve frustrated in their attempts to help someone who thinks they’ve checked everything.
Yep, its always the simple stuff that’ll get you in the end.
Thank you everyone for all your thoughtful assistance.

Reply | Quote

[indent]

---

Yep, its always the simple stuff that’ll get you in the end.

---

[/indent] Isn’t that the truth. Great to hear you got it solved.

Reply | Quote

Simple is as simple does.

Reply | Quote

Yeah, thanks Forrest. I’ll have to pick my chocolates better.

Reply | Quote