Using "Mini-form" to open full record 1

[TechieJr]

Hi,

I have a page of a tabbed form with two subforms on it. Both subforms come from the same table. The subform on the left is in datasheet view and only shows the date, analyst's name, and topic of the record. The subform on the right shows the complete record. what I need to do is to be able to scroll through the subform on the left, click on a record, and have it show up in the subform on the right. Can anyone give me some guidance on how to do this? I'd appreciate it.

Thanks

 

[gusbrunston]

[tt]
Hi:

Instead of an abbreviated sub form to select from, why not just use a three column combo box that finds the record to display in the more detailed sub form?

I hope this is helpful. Gus Brunston An old PICKer, using Access2000
[tt]Want solutions you can understand?
Post understandable questions.[/tt]

 

[TechieJr]

Thanks for the idea. Friday afternoons do not always help one think through all the options. Many thanks, I'll try it.

 

[gusbrunston]

[tt]
Let me know if you need any help with the combo box.

Cheers,[/tt] Gus Brunston An old PICKer, using Access2000
[tt]Want solutions you can understand?
Post understandable questions.[/tt]

 

[TechieJr]

Hello Gus,

Just getting back to work after some medical leave and trying to pick up where I left off.

Tried your combobox idea and got stuck. (BTW, I'm using Access 2002 and am building my db in 2002 format). Clicking on a record in the combobox opens up the entire record nicely, except that it only opens up the first record on the list, not the one selected from the list.

I tried several things, but the only one I got to work was to have a macro run on the After Update property. The macro code is below:

OpenForm
FormName: ContactRecord
View: Form
WhereCondition: [Forms]![f_ClientManager]![f_Contacts Summary subform].[Form]![ContactCallUp]
Datamode: Edit

Any light you could share would be helpful. I'm starting to get under the gun on this one.

Thanks,
TechieJr.

 

[gusbrunston]

Hi:

From the code you have in the macro procedure, you're still using a subform. You may have good reason to do that, but it's not clear. You're probably on the right track, but without some familiarity with your project, it's not easy to advise.

Access 2000 uses ADO as a default and I'm sure Access 2002 does too. I use DAO a lot, and to do that I've had to move the Microsoft DAO 3.60 Library into the third position in the preferences list you'd find in Tools_Preferences in VBA Help. (Click on Help while in design view, viewing Code.)

In any case try using the wizard to build your combo box. Make sure the wizard icon is on, and drag the combo box icon to your form. The wizard will ask if you want the combo box to find a particular record...click on that choice and the wizard will build the control for you. You can amend or adapt it later.

I use combo boxes all the time to find and select the record of a client, a property, a tenant, a check, or a deposit from thousands of records. Here's a typical procedure for the OnClick event of a combo box:[tt]

Private Sub cboProp_Click()

'Find the record that matches the control.

Dim RS As Object
Set RS = Me.Recordset.Clone
RS.FindFirst "[PropID] = " & Str(Me![cboProp])

End Sub[tt]

You should be able to click on the down arrow on the right side of the combo box, scroll down (or up) the box until you find the record you're looking for, select it, and that record will be displayed.

The wizard will build something similar to the above code, but will add code for error handling, which is good. I have profitably spent some time looking at the code that was built by the wizard...for combo boxes, command buttons, etc.

When I go to Help, "Combo_box", I find an almost overwhelming list of articles, beginning with the most useful, for my purposes.

I've also relied on a couple of manuals, Access 2000 Bible and Running Access 2000. A great deal can be learned and copied from the sample database Northwind that shipped with Access 2000 and earlier versions, and I assume ships with Access 2002. If not, it can be downloaded from Microsoft.

Also, Microsoft has a great resource database you can download, named "frmsmp00" for A2K, and I assume there's one named "frmsmp02" for Access2002. That file has all kinds of good techniques, and it will give you references to articles on Microsoft's knowledge base that go into more detail.

I have a small database that would demonstrate the use of a combo box to select and display a record from a recordset. If you want to give me your email address, I could send it to you.

Let me know.

padregus@attbi.com
Gus Brunston, using Access2000 Intermediate skills.

 

[TechieJr]

Good Day Gus (and others),

Did some investigating and learned something (unfortunately, not the answer to my question).

Let me describe my db a bit more.

I have a table (master) recording all our client's contact information (address book type stuff). I have a related table (ProjectDetails) which records further details about the project we are working with that client on. A third table (Contacts) holds summaries of the meetings, project status, etc. we have with each client. A new record is created in this third table each time we do work on the project or have contact with the client. The 2nd and 3rd tables are related to the 1st through a MasterID number. I have a ClientID number in both the 2nd and 3rd tables but when I try to create a relationship between them, Access baulks.

I have a main form with tabbed pages. Each page displays information based on one of these tables. The primary form is based on the master table. A subform on the 2nd page is based on the ProjectDetails table. The third page is where I'm trying to work out my problem.

What I have discovered today is this:

When I use the wizard to create a combo box on the third page itself, I am presented with 3 options (1) Look up values in table or query; 2) Type in the information I want; 3) Find a record based on selected value.) The trouble is, the combo box is based on the Master table, not the Contacts table.

When I use the wizard to create a combo box on a subform on the third page(based on the Contacts table), I am only presented with the first two options mentioned above. The "record based on selected value" is not there.

I'll keep working on it, but any further guidance would be appreciated. Thanks for your help and patience. You've been great.

 

[gusbrunston]

[tt]
Hi:

Your application is not unique. A couple of comments:

1) The combo box you're having trouble with ought to be on the "main" form, it seems to me. (Is this the same form as the "primary" form you mention?) Or else, you may want a sub form on the sub form on page three (I think they can be nested 3 deep, though I've never gone that far.)

2) Your data is stored in tables. You must know the "relationship" between those tables. Are the relationships, "one-to-one", "one-to-many", "many-to-many", or in the relationship view is one or more of the tables independent? You say[/tt] "I have a ClientID number in both the 2nd and 3rd tables but when I try to create a relationship between them, Access baulks."[tt] What does that mean? How does Access balk? When does it balk? In the "Relationship View", when you display the three tables, is there a line between all the tables, or just from tblClient to each of the others?

3) Forms: Is your "main" form related to the sub forms properly? Are the "Link Master Fields" and the "Link Child Fields" properly assigned?

4) Because I am unfamiliar with your database, I may be asking the wrong questions. The answer to your question may be much less complicated that I seem to be making it. Perhaps I've become the one that needs to be rescued...I'd like to help, but I can't seem to get a handle on the problem.

5) One more suggestion: Look at Microsoft's "Time and Billing" database template. When Access first opens, you can select "Access database wizards, pages, and projects", and then select "Time and Billing" (or one more appropriate template). Build a sample database with some of the fields from each of your tables. Maybe the resultant sample database will be helpful.

Is this how your tables are related?

One-to-Many One-to-Many
tblClients tblProjects tblProjectDetails
DetailID(PK)
ProjectID(PK)-->ProjectID
ClientID(PK)-->ClientID DetailNumber
Name ProjectNumber Field0
Address Fielda Field1
Telephone Fieldb Field2
Contact Fieldc Field3
Fieldd Field4






Gus Brunston, using Access2000 Intermediate skills.

 

[gusbrunston]

[tt]
Just noticed you haven't marked this thread for email notification. If you want to hear more from me, please do so. Thanks. Gus Brunston, using Access2000 Intermediate skills.

 

[TechieJr]

Hello again,

1) When I try to create a combo box on the main form, it populates the box with information from the same tbl the main form is based upon. This is not the tbl I want. If I create a subform based on the tbl I want, I can create a combo box that will pull its information from the correct source, but then I lose the wizard option I need to open the full record (as mentioned in a previous post).

2) In response to the comment about Access baulking over the relationships between the 2nd and 3rd tables, I found the problem. It would not allow referential integrity to be turned on because there was some inconsistency in the data between the two. Once I discovered and fixed it up, things worked well. Problem solved.

3)The parent/child links check out ok. So far the db works well with test data. No integrity problems.

Your table relationship example is almost dead on. The only thing missing is a field in tblProjectDetails forming a FK with the PK in the tblClients. This seems to be necessary because the main/primary form is based on tblClients.

I'll take a look at the Time & Billing templates and see if it has what I'm looking for. Thanks for the tip.

Your time and assistance have been great. Let me know if this clarifies things for you or if I need to give more detail.

 

[gusbrunston]

You wrote: "When I try to create a combo box on the main form, it populates the box with information from the same tbl the main form is based upon. This is not the tbl I want."

[tt]
You tell the wizard what table or query you want the combo box to use. In this case, it's "tblProjectDetails." Gus Brunston, using Access2000 Intermediate skills.

 

[TechieJr]

Hi Gus,

I feel like I'm frustrating you to no end, and I apologise for that. I keep checking and double checking my steps and I have found no difference in the results.

I found that your last posting is true, depending on the option you first select in the combo box wizard.

When I drag a combo box from the tool palette to the form in design view, I am presented with a dialog box that has two or three options.

If I choose the first option (Look up values in a table or query), I am taken to a dialog box where I can select the table or query I want. By itself, this option does not produce a result which opens the selected record in the Project Details table.

If I choose the second option, (Type in the values I want), I am taken to a different dialog box where I can begin typing in a list.

If I choose the third option (Find record based on selected value), it takes me to a third dialog box which asks me to select the fields to display. No option is given to select a different table other than the one the form is based on. And this third option does not show up if I try to create the combo box on a subform.

Is this making sense? Am I on the right track? Do I have to change the Record Source property after the combo box is created (or something like that)?

Thanks for your continued patience. I'm learning just how little I really know about Access.

 

[gusbrunston]

[tt]
I don't mind sticking this through with you. One of the ways I learn is by trying to help others!

Let's get some terms settled. You wrote, "When I drag a combo box from the tool palette to the form in design view...". Which form? I think you should drag it to the "main" form, which I'm going to call "frmClients".

You wrote: "...I am presented with a dialog box that has two or three options..." No, it either has two options, or it has three options. Which is it? One is probably: "I want the combo box to look up the values in a table or query." Choose that one.

Then you are asked: "Which table or query should provide the values for your combo box? Choose "tblProjectDetails".

Then you are asked: "Which fields contain the values you want included
Gus Brunston, using Access2000 Intermediate skills.

 

[gusbrunston]

[tt]
Hi:

My last post was incomplete. I didn't know it got sent.

Instead, let's do this:

I have constructed a sample database with three tables:
tblClients, tblProjects, tblProjectDetails.

A form (frmClients) with a subform (subProjects) with a subform (subProjectDetails within that.

There are three clients, each client has from 1 to 3 projects, each project has from 1 to 3 details.

There is a combo box on frmClients. It is unbound, it uses tblClients for it's data source. It has the following code in the After Update event:

Private Sub cboClients_AfterUpdate()
' Find the record that matches the control.
Dim RS As Object
Set RS = Me.Recordset.Clone
RS.FindFirst "[ClientID] = " & Str(Me![cboClients])
Me.Bookmark = RS.Bookmark
Me.cboClients = ""
End Sub

This database is about 200KB in size, and I could email it to you. It will have to be zipped, nevertheless, to get by my anti-virus firewall. You would have to be able to unzip it. "One picture is worth a thousand words."

My email through attbi is down. I'm using an alternate email address: gus@rentdex.com.

Let me know.

Gus Brunston, using Access2000 Intermediate skills.

 

[nathanstevenson]

Hi Gus,

I am not sure how far you are down the road with your database, but I thought I might add something in that might help.

You seemed to be struggling with the problem of not being able to call up the record you want in your 3rd page. I.e. it was pulling records, but only from the table that the form was based upon.

To get past this problem is actually quite easy. It will probably help with other problems too. The trick is to create a query that has all the fields that you want to use in. It doesn't matter how many tables this query is based upon, which means you can get as many fields and as customised as you want.

Then in the form that you want to use these fields, simply set the record source to the name of the query. THen you will be able to use all the fields from that query.

It does work, but as I don't have masses of experience I can't comment on problems that may occur due to using this technique.

One caveat though... the tables in your query have to be linked. So you have to "follow the thread" by including all the keys to maintain the link.

Hope this helps...
Nathan

 

[gusbrunston]

[tt]
Thanks, Nathan:

Actually, I don't have any problem. I've been trying to help TechieJr. Finally designed a database that does exactly what he wants, if he wants me to email it to him.

Cheers, Gus Brunston, using Access2000 Intermediate skills.

 

[gusbrunston]

[tt]
Did you get the database? Gus Brunston, using Access2000 Intermediate skills.

 

[TechieJr]

Good Day All,

Gus, thank you for the database. It was helpful, as have all your postings. I finally got things working. As a matter of fact, it was the beginning of a roll which helped me clear up several problems.

I ended up re-creating the subform from scratch (and actually incorporated two subforms into one). Once I did this, the combobox worked just the way you have been saying it should. I don't know what went wrong, but things are working and I can move on to other parts of the database.

Thank you all for your assistance, patience, and perseverance.

TechieJr.