Possible Coding Problem - Need Help

[terryechols1]

I'm having a major brain collapse on this issue. I have tried everything I can think of but it won't behave the way I want it to.

This problem, I'm hoping someone can help me out with, involves 2 forms and 1 table.

The first form (Call Details) has a tab for "Related Calls". The purpose of this is to relate calls to one another - for us showing a history of a service call for an appliance. We then group calls for the address based on the appliance being serviced, ie fridge, stove, washer, dryer, etc...

When you click the tab on Call Details it should show the related calls (if any). The problem I'm having is the display of the related calls isn't working. (img_1 shows this part)

Here is the code behind this tab: (Record Source: "Calls" table) - Calls table has a field "Related Calls" that is used for this purpose.

SELECT Calls_1.ID, Calls_1.Address AS Address, Calls_1.City AS City, Calls_1.Appliance AS Appliance, Calls_1.[Appliance Issue] AS [Appliance Issue], [Customers Name].[Customer Name] AS [Called In By]
FROM (Calls INNER JOIN Calls AS Calls_1 ON Calls.[Related Calls].Value = Calls_1.ID) INNER JOIN [Customers Extended] AS [Customers Name] ON Calls_1.[Called In By] = [Customers Name].ID; 
 

Column Count: 6; Column Widths: 0";2";1";1.5";1.7";2"; Column Heads: Yes

The from will show a related call if one is added but when the form is closed and the record looked at again the call doesn't display.

The rest of this process goes like this:

1. Pull up the record on Call Details
2. Click the "Related Calls" tab
3. Click the "Add Related Calls" button
4. A pop-up form is displayed (img_2)
a. from here the data entry person can find related calls using various criteria (all filters works)
b. they are supposed to check the box then close the form and the calls are supposed to be written to the "Related Calls" field in the table "Calls"

Here is the code for that form: (Control Source: Related Calls)

SELECT Calls.ID, Calls.Address, UCase(Calls.City) AS City, Calls.Appliance, UCase(Calls.[Appliance Issue]) AS [Appliance Issue], [Customers Name].[Customer Name] AS [Called In By]
FROM Calls LEFT JOIN [Customers Extended] AS [Customers Name] ON Calls.[Called In By] = [Customers Name].ID WHERE (((Calls.ID)<>Form!ID) And ((Calls.Address) Like "*" & [txtAddressContains] & "*") And (([cboAssignedTo]) Is Null Or ([cboAssignedTo])=[Assigned To]) And (([cboOpenedBy]) Is Null Or ([cboOpenedBy])=[Opened By]) And (([cboStatus]) Is Null Or ([cboStatus])=[Status]) And (([cboCategory]) Is Null Or ([cboCategory])=[Category]))
ORDER BY Calls.Address; 
 

Column Count: 6; Column Widths: 0";2";1";1.5";1.8";1.8"; Column Heads: Yes

5. Once they find the call to relate the check the box and close the form. Then in the Call Details form it shows the information requested. Once the Call Details form is closed and opened back up to that record the information isn't there.

Now, in the "Calls" table the "Related Calls" field shows

SELECT Calls.[ID], Calls.Address
FROM Calls
ORDER BY Calls.Address;

for the "Lookup" and if a call has been related, in datasheet view you see the drop-down for all the calls with a checkbox and the call added earlier shows here checked but only the address - AND - it still doesn't display in the "Call Details" form under "Related Calls". I have tried adding all the exact same code used on the Call Details from but that didn't work either. (img_3)

I am so lost on this one I don't even know how to properly phrase the question. If I have missed something important in my code/descriptions above please don't hesitate to ask.

What I'd like displayed for related calls is shown in img_1 (which I assume must also wind up being stored as "Related Calls" in the calls table) but, as mentioned, it only shows the address in Datasheet View and does not display on the Call Details from.

If you need I can shrink the db and upload it but someone will have to help me with that. The db now has over 1,000 Customers, 100's of calls and the Employees and is just over 27MB in size.

Thanks,

[PhilOfWalton]

Much depends on your table structure, and with the correct structure all calls for a particular CustomerID and ApplianceID should automatically be displayed in a subform.

So, brief table details please

Phil

[terryechols1]

Not exactly sure what "details" you need. I can say that "Related Calls" in the table is defined as "Number" if that makes any difference.

Related Calls field:

General
Size: Long Integer
Decimal Places: Auto
Required: No
Text Align: General

Lookup
Control: Combo Box
Row Source Type: Table/Query
Row Source:

SELECT Calls.[ID], Calls.Address
FROM Calls
ORDER BY Calls.Address;

Bound Column: 1
Column Count: 2
Column Heads: No
Widths: 0";1"
List Rows: 16
With: Auto
Limit to List: Yes
Allow Multiple Values: Yes
Allow Value List Edits: Yes
Show Only Row Source Values: Yes

If you need something else let me know.

Terry

[PhilOfWalton]

My apologies, re-reading my post it was not very clear.

What is required are the table designs.

Plan B is to compact your databases (I assume you have a Front End & Back End), turn them both into Zip files and post them on this site or possibly send them directly to me and I will have a look.

Plan C is to a copy of your DB just have a few records and have another go at Plan B

Phil

[jforbes]

My guess is that after you insert a related call into your SubForm, the Multi-value field in Calls table isn't getting updated with the latest addition.

I wouldn't use Multi-Value fields unless you are publishing to a SharePoint server. Even then I would consider alternatives. Multi-value fields are nice if you don't do anything other than let Access Edit and Display the fields. Once you move beyond that, you can get odd and non-standard results.

One non-multi-value way of accomplishing this is to add a PreviousCallID field so that you can relate a Call to a Previous Call which works pretty well if there are only a limited amount of calls against something, but gets messy if there are more than three linked calls and/or you want to see all the related calls at once (unless you use SQL Server).

Another way is to create a link table to link calls to themselves in a Many-to-Many scenario. This way works well for a SubForm to list all related calls.

But the way I would recommend doing this is to implement some basic Serial Tracking. To do this create a table for your Appliances:

ApplianceID AutoNumber
CustomerID Long
SerialNumber Text
Description Text

Then add the ApplianceID to the Calls Table. Change the Related calls SubForm to link on the ApplianceID. When a Call is created, prompt for the ApplianceID, or modify the Selector you have to allow you to select calls in a batch and assign the ApplianceID.

You'll probably need a way to create new Appliance Record when entering a call against a New Appliance. For this, I would create a function that takes the CustomerID, and optionally a SerialNumber and Description, which creates a new Appliance Record and returns the ApplianceID.

The nice thing about this is that, you would then be able to create an Appliance SubForm for your Customers that would list all the Appliances that they have. You could also create a SubForm on the Customers Form that list all the Calls against that Customer.

[terryechols1]

If I had designed this DB from scratch I would have implemented a lot of changes but, since I'm working off of a pre-designed MS Access Template I didn't want to re-design it all over again.

Thanks,
Terry

[terryechols1]

I sent you a private message PhilOfWalton. I can't upload the file here. It's under 4MB, smaller that the 5MB allowed but it keeps failing.

Terry