Subforms & Unique Records - Please Help!

[smokingmirror]

Firstly, my thanks to everyone who is going to be able to help with my problem, and also my apologies if this thread isn't in the right forum.

Ok, I am trying to construct a database at the moment for a small business. The database holds a number of customer records, and all of the basic design and construction I am skilled enough to write myself.

However, I'm running into major problems whilst trying to tackle a certain task...


The Required Situation

I have a form called Contacts. This form shows all of the customer details, laid out neatly, and draws its information from a table of the same name. This aspect works fine.
Now what I've been asked to look into is the possibility of adding two buttons to this form. Each button, when clicked, will open a pop-up window where extra details, such as a log of received telephone calls, can be entered and searched if necessary. the two buttons should do the same thing where the code is concerned, but store their details separately (one button is for sales calls logged, the other for support calls).
Now it is essential that these records are tied - uniquely - to whichever record the database user was on when they clicked the button.


My Current Thinking

I'm stumped. Well, half stumped. I think that the solution lies in sub-forms, but I'm not nearly skilled enough to actually impliment them (and don't want to waste my time doing so if there's an easier way).
Currently I have two tables, contacts and calls, the former to store the customer information, the latter to store the calls that are entered on this new form.
The form contacts has a button on it which opens the form calls. Now I can go to any record I like, click the button and open the calls form. I can enter details for such, and the record gets saved to my calls table. The problem is that it is not unique. No matter which record I'm on when I open the calls form, the details are always the same.

Yes, I know the absoute easiest way is to simply have a few fields on the main contacts form to log these details, and that it will uniquely tie to that record, but unfortunately that is not a practical solution. These calls may run into the hundreds, and must be easily navigated by the database users.



If I've not been clear enough, I can attach a copy of my database for you guys to take a look at, but what I'm after in the first instance is (a) whether there is an easier way to solve my problem, and, (b) if not, whether I'm right about needing subforms.

 

[Len Boorman]

Basically a call is a seperate entity to a Customer.
A Customer may lodge many calls. A Call will be associated with one and only one customer.

So this defines a 1 to many relationship.

Customer table and Call table.
Within the Call table will be a foreign key that is the same as the primary key of the Customer table.

Define the relationship within the relationship diagram and then use the PK to FK relationship to define the Master/Child link on master/sub form.

I would suggest that you use separate forms for displaying and adding calls.

The add call form can use a combo box to select customer and therefore the relevant value to be used as the FK.

Len

 

[smokingmirror]

I'm sorry, Len, but I'm not sure I quite follow you. I'm reasonably comfortable at writing basic databases, but Master/Child linking business is not something I've seen before.

I've included a copy of the database that I'm trying to get it to work on (which includes my rather poor attampt at doing so) as a point of reference. It's much easier to do if the people guiding me know how my tables and forms are setup.

On that point, the main customer details should all be held in the table 'coredata', whilst the tables 'calls' and 'scalls' should hold call details. Essentially, I'm trying to set up 2 buttons on the main customer form (form coredata1), which both allow unique records and call logs to be recorded to that customer, but which are separate from each other (since one set of people will be using one call log, and another set the other).


Incidentally, this database has password protection embedded. The password is example (case sensitive).

 

[Len Boorman]

Sorry cannot download. Company policy. may be able to look at it tonight at home.

Basically between a main Form and sub form there must be something from the main form that tells the sub form what data to display. A sub form will still have a query as the source but it needs this something extra to act like a criteria within the query to select the correct records.

This is set on the properies of the Main form (I think from memory) or else it is the sub form.

This is the same situation with Reports and sub Reports.

So on the Main form you may have say Customer ID which is the primary defining a Customer.

In the Calls table each call is associated with a customer via the Customer ID which is also in the calls table and acts as the Foreign key.

So the sub form also has Customer ID on it.

You now have the capability of linking Main and Sub form via this piece of information that exists on both forms.

So if you go to nect record on the main form then the Customer ID changes and the sub form refreshes based upon the new Customer ID on the main form .

Clear as mud really isn't it

I will try to have a look tonight but cannot promise anything

L

 

[Len Boorman]

Had a look at your example.

No easy way to say some of this so take it as constructive comments not me being clever or anything like that

1) Please remove passwords when posting onto the forum or state the passwords

2) No worries took me 30 seconds to create a version with your structure and all the objects and code

3) You need to read up on Primary Keys, Relationships, Normalisation

4) All forms, reports whatever should have a source. Normally a query

Attached is a new database with most of your stuff in it

There are two new tables
Look at their design first and see where primary keys are defined. Also note that a naming convention is most useful ... believe me. tbl = table, frm = form etc and have no spaces in the names.

Look at Relationships and see how the two tables are joined by a relationship. The field on the many side (funny looking sign infinity actually) is the foreign key.

So really you need to start again. sorry but that's the hard truth.

Define tables carefully respecting normalisation rules
Construct the relationship diagram.

Some of the earlier posted data will then begin to make sense.

Honestly you will really get nowhere until you have acorrectly designed data structure.

Len

 

[smokingmirror]

Um...Len, I don't actually see any changes in your attached database. The tables look exactly the same as in the original one I posted!

 

[smokingmirror]

Hi Len,

I've made some headway (I think!) with this subforming business, but really need some help just to identify the last place I'm going wrong.

Firstly, I've ditched the old database (which was only a brief example I was working on) and am now trying to replicate the unqiue records of an earlier database that my workplace had (they could have told me that before I spent time trying to write a new one!)

Basically, take a look at the Contacts form. There are two buttons, one for logging Sales calls, the otehr for Support calls. Both of these buttons should be logging their entered details to the same master table (Calls), but to different columns. Everything in that table proceeded by an 'S' relates to a support call, everything without is sales.

I've managed to divide the table up into various columns, and to setup a separate form and subforms for the support window (and link them to their unique parts of the Calls table), but they aren't functioning as they should.
When you open either the Sales or Support forms, the date and time should be automatically filled out according to the PC time. My form seems to just start a new record and waits for your details.
Secondly, I can't actually enter and save details in the Support call form (the Sales works ok). I think I'm having problems with the callID field, but I'm not sure how to solve it.

If you get time, could you please cast your eye over this and help me?

Thank you. Your help seriously is appreciated.

 

[Len Boorman]

Sorry attached wrong file last night

Here is an abridged version

Look at tables and the relatioship between them

Len

 

[Len Boorman]

Having trouble this morning

 

[smokingmirror]

Thanks Len, your help really is appreciated. I'm going to take a look at the database relations and see if I can use it to resolve my current problem (see my last post).

If you get time, could you take a look at the newer database, since it's 99% of the way to what I need. I'm just having difficulty sorting out the unique ID reference for the second calls log.

I don't know if trying to run multiple unique logs off one table is even realistic, but if possible I want to keep it all in one table, if only to make adding search functions easier.

 

[Len Boorman]

I will try to have a look tonight.

If your structure is not normalised then you will have difficulties potentially

len

 

[smokingmirror]

Thanks again. I'm actually beginning to think that my problems with this new database (which, with the exception of the separate call loggers works perfectly), stem from the fact that I'm trying to log everything from both forms to the same table.

I've noticed that if I use the SALES button and log say 1 record, then go to the SUPPORT button, I will find that the first line is blank (where it should have the date and time autofilled and be ready for records), and the second record is the automatically generated one.
This would seem to suggest that the SUPPORT log window is detecting the presence of information from the SALES part of the table.

Perhaps it would be easier to try to split them up into two separate tables, but then I don't know if it is possible to attach multiple subtables to each record in the main table.

**Edit - I've tried setting them up as two separate tables, and tried to define Master/Child relationships between them, but I'm still running nito problems. The attachment is an example of the database with the attempt at running two tables for calls.

 

[Len Boorman]

Okay had a look

You must normalise other wise believe me you will get into trouble.

Calls. A call is a call.

Call and sCalls are basically the same. The difference is "s" crudely. So add "s" as a field and then you can differentiate between the type of call vey easily and all the call are in one table. Which is as things should be.

Also in your tables I would leave fields as defined and not change the lookUp to say combo. If you needa combo then you can create one. If you need something declared as a lookup combo as a pure say text field then life gets a bit more complicated.

You can have as many sub forms on a main form as you like. I often use a button to switch the visible property so I can have many subforms without cluttering the actual display.

I have created a new form with a sub form to show how the Child and Master fields work

Put frm_Contacts into Design. Click on the subform and then go to the Properties from the top menu bar. You will then see the master and child field links.

The link is Contact ID because that appears on both forms and is actually the PK and FK from the Relationship diagram.

Try it

Step through the records on the main form and you will see the details in the sub form change. You can page through the sub form to look at calls for a particular contact.

Len

 

[smokingmirror]

Hi Len,

And thank you for taking a look at it for me...

...unfortunately, I'm still not sure I see what you mean. I've had a look at your two new forms, and I understand that the new subform Calls is embedded into the new form Contacts. But what I'm trying to achieve is having two buttons which cause separate windows to popup, allowing separate call details to be added, which then get logged to that unique customer record.

At the moment, my Support calls window (scalls) crashes everytime I try to enter information, stating that there is a null error (I think it has something to do with the Contact ID for that form, which I'm trying to get to link to the main record). I also still get into problems if I try to log information to the same calls table (as I said, if I log a call from the Sales form, then the Support form automatically scrolls down to the next record, and vice-versa).

Would it be better to add the Sales and Support subforms to the main Contacts form, rather than trying to access them in different windows with buttons?

 

[Len Boorman]

Attached is a very quick demo
Basically there is an Enquiry form with a combo box. Select the contact and a form with contact details is displayed. Two buttons bring up pop up forms. Okay these are both the same but the principle is the same. Notice that all forms have a query as their sourse and teh query uses teh value selected in the combo box

Len

 

[smokingmirror]

Hi Len,

Thankyou very much. I've had a look at your demo and now understand the problems I was encountering (well, enough to proceed on my own for a whiel longer yet!). I think part of my problem was that I didn't have a proper query to define certain fields; that and my relationships were not properly established.

I've tried to replicate your code and produce a third button, and have managed to get it working, so I think I'll try one from scratch, just to make sure I've got everything clear.

I have only one more question, however, regarding the popup call recording form (frm_calls). I can write as many forms as necessary, and alter any aspect of the form to tailor it to my needs, but I'm not 100% certain on the process of getting the three fields Time, Date, and ContactID to automatically fill with information from the PC clock and the main contacts form respectively. I can't see users being that keen to manually fill these out. Ideally, these should autofill as soon as the user begins to type in the 'Subject' box of the new record. Is this possible, or is there a better way around this?

 

[Len Boorman]

You need only one field for date and time. Make the default value in the table Now() and you will automatically get cuttent date and time

L

 

[smokingmirror]

You need only one field for date and time. Make the default value in the table Now() and you will automatically get cuttent date and time

L

Doh! It's always the obvious basics you learn at the start that get overlooked when you are working on more complex databases!

Thanks, Len.

 

[Mark-BES]

There is a good example of what your after supplied with MS Access.
Look in the sample database section and take a look at Contact Managment. It features Contacts and calls.

 

[smokingmirror]

Thanks Mark, but what I'm trying to do is understand the process behind relationships and linked tables so I can replicate this kind of database in the future.

I've been working from a very old database written before I started with this company, which was - I believe - itself based on Microsoft's example. What I was finding tricky was understanding the way in which it was put together, since I couldn't effectively replicate that call log to allow for separate logging.