Access Developer Forum

Form Question Upload Images Link
Amina Jones
4 months ago

I have a database where I have two types of clients. Employee and Customers. I have two separate tables for each. I also have a pymt table to track payments for each client. The pymt table identifies the type of customer (employee or regular customer) I have two forms, one for employee & one for customer, and each form has a pymt button. I also created a pymt form for the pymt button to view and add pymts.

The issue I am having is adding both the CustomerID and the EmployeeID to the field to the pymt form. I have to add them in order for the pymt to show but it leaves a blank box for the employeeId field if I am viewing the customer form and the same when I view the employee form. Is there a way to get both IDs to show without the blank box showing but still showing the ID number for that type of customer?

Amina Jones
4 months ago

I only want the customerid to show when i view the customer pymt history and only want the employeeid to show when i am viewing the employee pymt history

Dg Ewing
4 months ago

If I am an employee and purchase something, am I assigned a customer ID, and if so how are trying to populate the employee id field on the payment history form?

Amina Jones
4 months ago

The employee table has an employeeID -PK and Customer table has CustomerID - PK. The third table is the pymt table and they both use the same table. Both PKs are foreign keys in the pymts table. I have a pymt form and the pymt form is used on the employee form and the customer form.

Richard Rost
4 months ago

I would put customers and employees together in the same table and just differentiate between them with a field (IsEmployee for example). Would make everything else so much easier.

Amina Jones
4 months ago

I did two separate tables because the customers membership to the gym subscription is through referral link from the employee. So i did two separate tables.

Amina Jones
4 months ago

Dg Ewing
4 months ago

Amina, I may not yet understand your problem, my questions are seeking to understand, not to question your logic/architecture>

Your payments table does not have both customer AND employee IDs populated for any paymentID records....if your pymttransactF is reading the payments table there is no data to read....???

Dg Ewing
4 months ago

Is your question how to populate the payments table with the employee ID when the transaction is an employee? and if so I would use vba on after update of the customer ID in the pymttransactF and set the employee id equal to the employee id to what appears to be subform {right hand side} on the Customer Information Form above {employee 1}

Kevin Robertson
4 months ago

Can't you simply hide both ID fields?

Richard Rost
4 months ago

You're going to run into problems with your joins. You could use Outer Joins for the relationships between customers to payments and employees to payments, but it's still going to be a messy situation. Again, in my professional opinion, I'd put them together in one Person table.

This thread is now closed. If you wish to comment, start a NEW discussion, below.

Back to Access Developer Forum Comments