buttons and subforms

[Stephanie T.]

I have a form that has two subforms. One is part of the regular form and works just fine, relating to the form as a subform. That is, once the information has been entered it is saved both in the form and the related table. The table for the subform automatically enters the related field information from the main form. For example, the main form is for the Broker Company, the subform is for the individual Field Reps. When the data entry person enters the Field Rep information, she does not need to also enter the (redundant) Broker Company info, that is automatically entered into the table - i.e. the two forms are relating properly.

I want to add a button to open the second subform and have it relate to the main form the same way that the first subform relates. Here is the code that I have been using (thank you for help from this group so far):

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmProductBrokerSynch"
stLinkCriteria = "[ProductBrokerSynch]=" & "'" & Me![ProductName] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_btnBrokerProducts_Click:
Exit Sub

Err_btnBrokerProducts_Click:
MsgBox Err.Description
Resume Exit_btnBrokerProducts_Click

It looks like there is an error, but I don't know enough VBA to understand it. This has sort of worked, I could get the form, and enter the data, it went into the table, but A) it didn't save to the form and B) it still is not relating to the main form like a subform (i.e. entering the redundant date into the table). So, that if I want the Broker Company information I would need to have that as a field in this subform.

Any help would be greatly appreciated.

Thank you,
Stephanie

 

[Fizzio]

Do you really mean a subform (ie resident on your main form), or a separate form. By your code I suspect you mean a separate form, not a subform. I'm a bit confused by your 2 statements
a) did not save it to the form - It doesn't, it should save it in the table, but display it in the form
b).... ie entering the redundant date to the table

What is the controlsource of your 2nd 'subform' If you want to filter the form, it must include the field [ProductBrokerSynch] so you will likely need to set up a query to populate this form, not a table.

Can you clarify these a bit?

 

[Stephanie T.]

buttons and Subforms

This is a separate form, that is bound to a table. I want it to relate to the main form the same way that a subform would relate – i.e. the individual Broker information from the main form would be automatically part of the information that goes into the table that is maintained by the second (sub)form, without having to have the redundant information re-entered. Essentially, to make it really easy, I need to have two subforms on this main form, but one of them is information that rarely changes, so it does not need to be up on the form all the time. A button that would open this and allow it to relate to the main form the way a subform relates is what I want to do.

One last thing, when I said it didn’t save the information to the form, I meant that every time I opened the form, the information that was put in previously was now gone. At the bottom of the form, it was showing no records recorded, but when I went into the table it did show the information (without the Broker information from the main table). What I would like, is just like any form bound to a table, when you open the form, you can see what information has been stored in that table, this should be the same with this second (sub)form.

I hope that is clearer, your help is appreciated.

 

[Stephanie T.]

Pat,

Thanks for your help. I changed the DataEntry property of frmProductBrokerSynch to No. When I open the ProductBrokerSynch table, I can see all of my test entries. But when I open the form through the Broker form, it says there are no entries made. So I can’t scroll through them. However, when I open the ProductBrokerSynch form as it’s own form, not through the Broker form, it does let me scroll through the entries. How do I get the forms to recognize a relationship with each other and then display the information the same no matter how I open the form?

Do you mean foreign key or primary key? I am not familiar with the term foreign key. I am on Access 97, could that be something new? The only information that is stored in the second table (from the subforms, etc.) is the linked information. For example, we have a table that we keep a list of all of our Products, we have a another table that we keep a list of all of our Distributors. Both of those tables have significantly more information about each of those items. Then we have a third table where we keep the list of which products are carried by which Distributors. This information is entered into through a subform on the Distributor form. We do not enter the information a second time, but pull it directly (as linked through a query) from the Product table. We never enter redundant data, that would undermine the entire database. If the same information needs to be kept in more than one place/format it is only entered into one place and then queries, look up fields etc., are used to link the information to the other places it is used. Is that what you mean, or am I still missing something? I am somewhat new to Access.

I know that my button/subform problem has to be something really simply that I’m missing.

Any help is greatly appreciated,
Stephanie

 

[Stephanie T.]

Thanks for your help. I don’t know what you mean by “OpenForm Method” I compared the properties between the two and cannot find the problem. I put the form into the main form as a subform and it works just fine. But it would be ideal if I could simply have this as a “subform” that open through a button. Not happening.

Thanks,
Stephanie

 

[Stephanie T.]

I simply used the form wizard to create the form. I try to use as little VBA as possible since it's very new to me. I could send you a copy of all three forms if you want to take a look, or let me know what you're looking for and I can look for you.

Thanks for your continued interest in my problem. Tell you what, if you can figure this out for me, there are cookies in your future (this is a gourmet cookie company and my boss would be as ever greatful as myself).

Stephanie