Two subform questions

[ukdane]

1) Is it possible to have a subform in a form, which is based on the form that it is in?

I'll try and explain. If I have a form, which shows the specific details of one item in the database table (including all the things linked to it), and include a subform (dataform), which shows all the items that can be selected to show on the mainform.
For example, the table "tbl_books" includes:

ISBN (primary)
Title
Author
Subject
Pages
Hardback/Softback

On my main form, I can look at one specific book on the table, and all the data. On the subform (dataform), I want to see a list of all the books, listed as they would be in the table, and from which I can choose the book to show in the main menu.


2) I have a subform that is an address book, and on the main "order" form there are three different customer fields; fromcustomer, tocustomer, and loadingcustomer. I only want one subform of the address book, but I want it to be controlled by each of the fields.
Currently I've programmed the vba so that if I click on the fromcustomer field, it will show the address subform, and the address of the fromcustomer. If I double click in the samefield, it will hide the address subform.
If I repeat this code for the other customer fields, it will still only show the data for the fromcustomer, as that is where the subform was linked (the parent (mother?)). How can I change the parent in VBA?

Cheers

[CreganTur]

1) Is it possible to have a subform in a form, which is based on the form that it is in?


I'm sure it's possible. One thing you can try is: create a query that will feed your subform. For the WHERE condition for your primary key value, have it set to pull the value from an object on your main form using [Forms]![FormName]![ObjectName]; where FormName is the name of your main form, and ObjectName is the name of the object holding the value you want. Be sure to set this exact thing as a parameter for your query- click on Queries - Parameters.

Whenver the value for the chosen object changes, you should requery your subform. This should show you the data you want.


If I repeat this code for the other customer fields, it will still only show the data for the fromcustomer, as that is where the subform was linked (the parent (mother?)). How can I change the parent in VBA?


You're going to need to change your form's RecordSource. Depending on how you setup your subform, you can refer to is using this:
Me.SubformName.RecordSource
or this:
Forms!FormName!SubFormName.RecordSource

HTH:thumb

[ukdane]

One thing you can try is: create a query that will feed your subform. For the WHERE condition for your primary key value, have it set to pull the value from an object on your main form using [Forms]![FormName]![ObjectName]; where FormName is the name of your main form, and ObjectName is the name of the object holding the value you want. Be sure to set this exact thing as a parameter for your query- click on Queries - Parameters.

Not 100% I understand. Can I make a query based on a form? Can you try and give me an example of what you mean?



You're going to need to change your form's RecordSource.


Excellent, then I can just put the recordsource (the 3 customer names) in a variable, in the code, right?

Cheers
:beerchug:

[OBP]

Wouldn't it be easier to use a Tabbed Mainform?
One tab for each Customer Type?
Linked to the mainform via that Customer type field.

[OBP]

Yes you can have the query filtered by a field in the mainform, which would provide the data for your Subform(s).
But linked Subforms do not need it.
The easiest option is to have 3 subforms on the mainfrm and make the required one visible and the others Not visible based on the field you click on the mainform.

[CreganTur]

Not 100% I understand. Can I make a query based on a form? Can you try and give me an example of what you mean?


You can make a query where the criteria for a field is determined by the value of an object on an open form.

Let's say you have a textbox for CustomerID on your Form and a button that uses the DoCmd.OpenQuery method to show you the value of a SELECT query.

Open your SELECT query in design view. For your CustomerID field, click on the Criteria section. You are going to enter a reference to the CustomerID textbox on your form, which will tell SQL to use the textbox's value as WHERE criteria for the query. The format for this is: [Forms]![FormName]![ObjectName] ; where FormName is the name of the form that contains the desired object, and ObjectName is the object whose value you want.

You also need to tell your Query to expect a parameter value. To do this you need to click on Query and then Parameters. This will bring up the parameter window. Enter [Forms]![FormName]![ObjectName] into the first column of the new window, and then select the data type of the parameter value.

Now you can open your form, put a value in the CustomerID textbox, and click the button. Since you are using the DoCmd.OpenQuery method, it will open the defined query in a datasheet view. You should only see the values that match the CustomerID you entered into the textbox.

Does that help?

[ukdane]

With reagrds to question 2,

I have tried using the following code:
Private Sub customername_Click()
On Error Resume Next
Me.frm_customeraddress.LinkMasterFields = customernumber
Me.frm_customeraddress.LinkChildFields = Forms!frm_overview!frm_customeraddress.idcustomernumber
frm_customeraddress.Visible = True
End Sub

The idea is that when the user clicks on the customer name field, it shows the form, with the relevant address.

If you look at the code above, the LinkMasterFields works without any problem, and stepping through the code, I can see that it returns the primary key as expected for the current record.

However when I step into the next line, the LinkChildField returns nothing.

Edit: If I remove the "On Eror Resume Next" code, I do get a Run-time error '438' here. (Object doesn't support this property or method.

If I were using the build in the property sheet, and return the values Link Child Fields = idcustomernumber it works without any problem. But this is the bit I need to vary, so I can't set it in the build.

If I change the LinkChildField code to the following:
Me.frm_customeraddress.LinkChildFields = Me.frm_customeraddress.idcustomernumber
and run the code, I get a "Compile Error: Method or data member not found", and it highlights the idcustomernumber of the line above.

Clearly I'm doing somethign wrong, but I can't see what. Any ideas?

I'm using Access 2003.

Cheers

[OBP]

If you are adament that you only want one form then it is far easier to use Randy's Query Filter method to control the Records that are being shown.
1. Base the Subform on the Query.
2. In the Query have the Filter on each Customer Field, but on seperate rows.
3. On the mainform have an invisible Dummy field, use VBA to put 1, 2 or 3 in the dummy field and requery the form.
4. On the query add a Column with the Heading Forms![mainformname]![Dummyfieldname] and for each Filter Criteria row enter a 1, a 2 or a 3.
Where mainformname and Dummyfieldname are the real names of your form and field.
Now when you double click the appropriate Customer Field it will put the value in the Dummy field and use it to filter the query and then requery the subform.

I have used htis technique for years to to do searching and Filtering, but usually with an Option Frame. I use the Dummy field technique to "Cascade" Tabbed Form selections, i.e. base each tabbed subform on the selection made in the previous tab.

[OBP]

Refferring to your setting Master/Child Links, this can be done in VBA BUT see this comment in the Help
"LinkChildFields Property and LinkMasterFields - The properties can only be set in Design view (http://www.vbaexpress.com/forum/#) or during the Open (http://www.vbaexpress.com/forum/acevtOpen.htm) event of a form or report."

[OBP]

Of course you can set the Subforms "Filter" in VBA and providing that you have the correct field(s) on the subform you can set the Filter according to which ever Mainform Field is Double clicked ( Iwould still prefer an option Group).
Or use an SQL statement to reset the Recordsource of the Subform each time.