Greetings!
Apologies in advance for the long post, but it's a complex problem. I'm writing a collection management module. It makes use of the following tables:
tblCallMstr
CollectID, CustomerID, Comments, Status
tblCallDetl
CollectID, CallID, CallDate, Comments, NextCallDate
tblCustomers
CustomerID, CustomerName, Address, etc.
tblAccountTrans
TransID, CustomerID, TransType, Amount
The customers and account transactions are managed by a different module, all I'm doing is using them as supporting data for call tracking. There will be a process that is run once at the end of each fiscal year to create tblCallMstr records for all past due accounts. A summary of the transaction amounts will get me the account balance for each customer. An account is past due if it has a positive balance.
I want to create a master/detail form where the master section is based on tblCallMstr and shows the CollectID, all the customer information, and the account balance for each past due account. Only Comments and Status of the master record are editable, and insertion or deletion of master records is not allowed. The detail section of the form is based on tblCallDetl and will show collection calls made for each past due account. The CallDate, Comments, and NextCallDate fields are all editable. Insertion and editing of call records is allowed.
If insertion of master records was allowed, I'd use a combo box on the master form to look up and enter the customer ID. If I based that combo box on a query that pulled in the customer data and the account balances, I could use the combo box's Columns() properties to bind text boxes on my form and display all the non-editable data. In fact, that's exactly what I've done, and to prevent editing of the customerID I've set the combo box's Locked property to Yes. This is working. But I don't like this solution because the combo box still looks like a combo box, it allows the user to click on the down arrow which re-displays the current value in pull-down format, but they then have to click elsewhere on the form to get rid of the pull-down list since they can't select the displayed value due to the lock.
What I'd like is some other solution that allows me to display the customer and account data without using a combo box to sync the customer and account tables with the current tblCallMstr record. I tried using the forms wizard to create a master/detail form based on all of the above tables, but as soon as I pull in tblCallDetl the relationships get too complex and the wizard won't finish the form. I also tried writing a query to pull in the tblCallMstr, Customer and account data, then based the entry form on that query and on tblCallDetl, but then it won't let me edit the call status and comments field in the master.
Any ideas appreciated!
Dale Beitz
dale.beitz@agreliantgenetics.com
Apologies in advance for the long post, but it's a complex problem. I'm writing a collection management module. It makes use of the following tables:
tblCallMstr
CollectID, CustomerID, Comments, Status
tblCallDetl
CollectID, CallID, CallDate, Comments, NextCallDate
tblCustomers
CustomerID, CustomerName, Address, etc.
tblAccountTrans
TransID, CustomerID, TransType, Amount
The customers and account transactions are managed by a different module, all I'm doing is using them as supporting data for call tracking. There will be a process that is run once at the end of each fiscal year to create tblCallMstr records for all past due accounts. A summary of the transaction amounts will get me the account balance for each customer. An account is past due if it has a positive balance.
I want to create a master/detail form where the master section is based on tblCallMstr and shows the CollectID, all the customer information, and the account balance for each past due account. Only Comments and Status of the master record are editable, and insertion or deletion of master records is not allowed. The detail section of the form is based on tblCallDetl and will show collection calls made for each past due account. The CallDate, Comments, and NextCallDate fields are all editable. Insertion and editing of call records is allowed.
If insertion of master records was allowed, I'd use a combo box on the master form to look up and enter the customer ID. If I based that combo box on a query that pulled in the customer data and the account balances, I could use the combo box's Columns() properties to bind text boxes on my form and display all the non-editable data. In fact, that's exactly what I've done, and to prevent editing of the customerID I've set the combo box's Locked property to Yes. This is working. But I don't like this solution because the combo box still looks like a combo box, it allows the user to click on the down arrow which re-displays the current value in pull-down format, but they then have to click elsewhere on the form to get rid of the pull-down list since they can't select the displayed value due to the lock.
What I'd like is some other solution that allows me to display the customer and account data without using a combo box to sync the customer and account tables with the current tblCallMstr record. I tried using the forms wizard to create a master/detail form based on all of the above tables, but as soon as I pull in tblCallDetl the relationships get too complex and the wizard won't finish the form. I also tried writing a query to pull in the tblCallMstr, Customer and account data, then based the entry form on that query and on tblCallDetl, but then it won't let me edit the call status and comments field in the master.
Any ideas appreciated!
Dale Beitz
dale.beitz@agreliantgenetics.com
