A Version for you to look at...

[asmodius]

Hello,

I have been posting problems related to a databe I walked into for a couple of weeks now. It has been very difficult to explain what I'm trying to do so I have made the database as small as possible and converted it to access 97 format. If anyone would be willing to take a look and point me in the right direction on a couple of things I would greatly appreciate it. Right now my main problems are as follows.

One: How do I get a drop down menu in the customer data portion of the main form to bring up the proper complaint in the subform if one already exists.

Two: How do I get the relationship of one to many to work between customer and complaints. It gives me a type mismatch error?

three: Do I have the tables as normalized as possible. The linked tables from the second DB are just lists for the drop down boxes. I guess they could all be in one DB, I don't know why they were done in a different one.

Thanks in advance to anyone willing to take on this challenge.

Cheers,

Andy R.

 

[asmodius]

BUMP...

Just bumping up for posterity. Thanks.

Cheers,

Andy

 

[neileg]

Can't open CSAR.mdb because it is secured.

 

[charityg]

First, I'll answer the obvious issue that jumped out at me. You are getting a type mismatch error, because your ContactID is a numeric field, and you are attempting to join to the CustomerID field which is of text type. Change the data type of the CustomerID field in your CustomerService table to a number.

 

[asmodius]

First, I'll answer the obvious issue that jumped out at me. You are getting a type mismatch error, because your ContactID is a numeric field, and you are attempting to join to the CustomerID field which is of text type. Change the data type of the CustomerID field in your CustomerService table to a number.

Thank you, I did that and it worked beautifully. You were absolutely right.

Since you can look at it. In the customer service form is there a way to have a drop down box that lists all of the complaints of the customer who's number is currently in the customer number field, and then if I select one of the complaints it comes up down below?

Thanks.

 

[charityg]

Next, if I understand your question. You want a drop down list in your Customer Service form that will list all complaints for the current customer, and allow you to select the complaint from the list, and display that selected complaint in the subform?

I think there are some other issues that you need to work through first.

Upon looking at your CustomerService form, i'm realizing that you need to make a few changes. The first thing you need to think about is "How will the user enter the information", then "How will the user retrieve the information".

I would guess that the user is a CSR who gets a call from a customer. So step 1 for the user would probably be to enter the Customer information (if it doesn't already exist). So if we think about it in those terms, your CustomerService form detail section should be for the customer information. Now, the customer has a complaint. And the customer can have more that one complaint. This indicates to me that the complaint portion of the form should be a subform that links to the Main form based on the customerID.
I noticed that you have the customer info in the header of the form and the complaint in the detail. It is much easier to handle the one to many relationship if you create a subform for the complaints. I'll be more than happy to help you modify the form, just let me know if I'm making sense to you.

 

[asmodius]

Yes... You Are!!

I'll be more than happy to help you modify the form, just let me know if I'm making sense to you.

You're making perfect sense. Just point me in the right direction. Think of me and this DB as clay that you talk to and will form itself. I thought there was already some kind of form sub form connection, but I see now there isn't. How do I create that? Thank you.

cheers,

Andy

 

[charityg]

Open the CustomerService form, and delete the controls out of the Header. Save this and rename the form as sfrmComplaint. Then rename Contacts1 to CustomerService and change the BorderStyle to Thin, Sizable, or Dialog. Increase the height of the detail section, and from the toolbox, select Subform and insert a subform in the Detail section below the Customer information fields. Use sfrmComplaint as the form you would like to insert, for the field links, click define my own, and for the form/report fields dropdown select ContactID, and the Subform dropdown select CustomerID. This will create a one to many relationship between the main form and the subform.

Let me know when you get to this point.

 

[asmodius]

Done...

The only thing I didn't do there was the border style. I don't get that when I have the whole form selected, I only get border style for specific boxes. Other than that all done though.

Did I say thanks already?

Cheers,

Andy

 

[asmodius]

Got the border thing...

Wow!.! You are helping so much. I see what that did already, I can scroll through the complaints that match each customer with my wheel mouse. Its working fantastic. I really appreciate the help. And I am learning how to fish here, you aren't just handing fish to me.

 

[charityg]

Okay. Great.

Now do you notice that when you change customers, the complaints are filtered for that specific customer? Another benefit of this setup is that when you enter a new complaint, the customerID will automatically be linked to the ContactID on the main form.

Now for your dropdown question. Create a new query and call it qryCustComplaintList. Add the tbl_CustomerService table and select the C_Date, ComplaintNumber and Description (I'm just guessing at the information I think should be in your drop down.)
Also Select the CustomerID, but uncheck the Show box. For the criteria of the CustomerID field enter
forms!CustomerService!ContactID

When you finish with the query, the SQL statement should look like this
SELECT TBL_CustomerService.C_Date, TBL_CustomerService.C_CompaintNumber, TBL_CustomerService.Description
FROM TBL_CustomerService
WHERE (((TBL_CustomerService.CustomerID)=[forms]![CustomerService]![ContactID]));

Now use this as the recordsource for your dropdown. If you put the dropdown in the header of the subform, it will make things a little easier in the next step.

 

[asmodius]

Done.....

Its done up to this point. Although for some reason I don't get a drop down list of the complaints. It comes up blank.

Cheers and thanks,

Andy

P.s. I will try to post it agian so you can see what I've done thus far but its hard to get it the right size.

 

[asmodius]

Here is the DB

Go to this web site.

ftp:\\da-computing.net\DB

user: drew
PW: drew

 

[asmodius]

weird auto change.....

When you finish with the query, the SQL statement should look like this
SELECT TBL_CustomerService.C_Date, TBL_CustomerService.C_CompaintNumber, TBL_CustomerService.Description
FROM TBL_CustomerService
WHERE (((TBL_CustomerService.CustomerID)=[forms]![CustomerService]![ContactID]));

Now use this as the recordsource for your dropdown. If you put the dropdown in the header of the subform, it will make things a little easier in the next step.

When I put this in it gets turned into this automatically

=qryCustomerComplaintList!C_CompaintNumber

Is that the intended code?

cheers,

Andy

 

[asmodius]

Figured out the weird change

So I figured out why it made that change. I had put the data into control source not row source. I fixed that now. But you only get the right list in the complaintlist box when you open up the Form. After you change the customer that drop down list doesnt requery. So I wrote the following code in macro mode on the customer data main form. attached to the customerID field.

Private Sub CustomerNumber_AfterUpdate()
Form.CustomerComplaintsList.Requery
End Sub

That doesn't seem to work.

Issue Below is fixed now: Problem was that the criteria didn't require [forms]![sfrmComplaint]

Another issue that may be related is the following. In the sfrmcomplaint I have cascading combo boxes. When you open up sfrmcomplaint alone the cascading combo works, but in the complete form it asks for the paramater value. Like it doesn't recognize the subform name.

The paramater for that is
[Forms]![sfrmComplaint]![specific]

 

[charityg]

[Forms]![CustomerService]![sfrmComplaint]![specific]

sfrmComplaint is now a subform of the CustomerService form, so you must reference the parent form control, then the subform control, then the specific combo box. This line equates to

The value of the control named "specific" which is a located in the control collection for the form named "sfrmComplaint" which is located in the control collection for the the form named "CustomerService" which is a form in the Database's form collection.

Make sense?

Also, you want to requery the listbox in the OnCurrent event of the main form.

 

[asmodius]

A little trouble with the requery...?

So When I try to requery the CustomerComplaintList Box I don't get it. I type in the following:

Me.C

and CustomerComplaintList isn't there. I don't know how to have it requery something in a subform.

So I tried this:

Private Sub Form_Current()
Me.CustomerNumber.Requery
End Sub


I still get nothing. I get the proper customer complaint list the first time I click the drop down box but if I change the record the requery doesn't work. Any ideas?

Thanks...

 

[asmodius]

Re: A little trouble with the requery...?

So When I try to requery the CustomerComplaintList Box I don't get it. I type in the following:

Me.C

and CustomerComplaintList isn't there. I don't know how to have it requery something in a subform.

So I tried this:

Private Sub Form_Current()
Me.CustomerNumber.Requery
End Sub


I still get nothing. I get the proper customer complaint list the first time I click the drop down box but if I change the record the requery doesn't work. Any ideas?

Thanks...

Got IT WORKING!!!!! I figured out the right code it was:

form_sfrmComplaint.CustomerComplaintList.Requery


Thank you SOO much....

I've never had anyone be so helpful. If there is anything I can do, hook you up with software, movies, games or any way I might be able to help you let me know I would love to return the favor.

 

[charityg]

I just really enjoy helping people learn. I'm glad you were able to figure it out!

Let me know if you need any more help.

~Charity