Data entry creates a new client

[dullster]

In my database, when I enter a revenue in the Revenue form, it creates a new client even though I'm selecting from the client list. Any ideas why it does that? Should I create a save button? DB is attached.

I just tried a save button and it still creates a new client.

 

[MajP]

That is because the field is bound.
When you open the form it is open to a new record. When you select a name it puts it in the clientname field which is a bound field.
If you want to move to an existing client then that field should be unbound with code to find that client. But then you will need a place to create clients.

 

[Ken Sheridan]

In my database, when I enter a revenue in the Revenue form, it creates a new client even though I'm selecting from the client list. Any ideas why it does that? Should I create a save button? DB is attached

Your frmCDRevenue form is bound to the tblDemoClients table, and its DataEntry property is set to True (Yes). This property is rather confusingly named as it causes the form to always open at an empty new record. Consequently a new row is inserted into the tblDemoClients table.

You need a RevenueReceipts table or similar in which a ClientID column or similar is a foreign key of long integer number data type referencing the autonumber primary key of tblDemoClients. In the form a combo box should be bound to the foreign key column, and set up to hide the bound column and show the client name by setting its ColumnWidths property to zero.

 

[dullster]

Is there a way to make the field unbound without recreating it?

 

[MajP]

Is there a way to make the field unbound without recreating it

Yes.

However, are you suggesting that for a given client they can only have a single revenue receipt? If not you tables are not designed to allow one client to have more than one revenue receipt.

 

[dullster]

Yes.

However, are you suggesting that for a given client they can only have a single revenue receipt? If not you tables are not designed to allow one client to have more than one revenue receipt.

No, the client could have more than one revenue receipt. What do you mean my tables are not designed to allow one client to have more than one revenue receipt?

 

[dullster]

Your frmCDRevenue form is bound to the tblDemoClients table, and its DataEntry property is set to True (Yes). This property is rather confusingly named as it causes the form to always open at an empty new record. Consequently a new row is inserted into the tblDemoClients table.

You need a RevenueReceipts table or similar in which a ClientID column or similar is a foreign key of long integer number data type referencing the autonumber primary key of tblDemoClients. In the form a combo box should be bound to the foreign key column, and set up to hide the bound column and show the client name by setting its ColumnWidths property to zero.

Are you saying the I need a separate Revenue table from the tblDemoTrans? My tblDemoTrans does reference the primary key but it is doing a data look up.

 

[MajP]

See demo. You can search for existing clients and add transactions.

No, the client could have more than one revenue receipt

Your table relationship is simply Clients to transaction, but no intermediate table.
Normally you have something like a Receipt table. That has a ClientID and things like receipt date. Then inside the receipt are multiple transactions.
But you can only have a Client associated with a lot of transactions. There is no higher grouping of the the transactions to an specific, receipt, invoice, purchase order.

 

[tvanstiphout]

I think you did this correctly, both in table design and in parent form / child form.
Just set Data Entry = No in the parent form.

 

[MajP]

I think you did this correctly, both in table design and in parent form / child form.
Just set Data Entry = No in the parent form

No it is not correct. The client name combobox is bound and chooses a value from a combobox pulling in existing values. That makes no sense, even if this is not set to data entry. You could make it a text box as I did but it should not be editable since you are adding from the pop up instead.

 

[tvanstiphout]

No it is not correct. The client name combobox is bound and chooses a value from a combobox pulling in existing values. That makes no sense, even if this is not set to data entry. You could make it a text box as I did but it should not be editable since you are adding from the pop up instead.

You are correct. I wasn't trying to say the app was flawless, but that the db design with respect to the two tables is correct, and the form/subform setup as well.

 

[dullster]

See demo. You can search for existing clients and add transactions.


Your table relationship is simply Clients to transaction, but no intermediate table.
Normally you have something like a Receipt table. That has a ClientID and things like receipt date. Then inside the receipt are multiple transactions.
But you can only have a Client associated with a lot of transactions. There is no higher grouping of the the transactions to an specific, receipt, invoice, purchase order.

So what i need it a table that contains ClientID and receipt number and then the data for that receipt is in the tblDemoTrans using the receipt ID for details? The way you have it, it contains all the data for the Client, I can't enter data. Is that correct?

 

[Mark_]

I'd suggest you work through your business logic first.
Is Revenue just assigned to a client OR is it linked through something like a purchase order, payment for service rendered, transaction fee, or some other source? If the answer is YES, then you need to put in the middle part first. This includes looking at how they get related.

I really can't think of a business that just has "Revenue" linked to a client. It is always Client < Service < Payment for service OR Client < Order < Payment for items type arrangement at least. Work out HOW you need to track and report payments. This will avoid problems later.

 

[dullster]

I'd suggest you work through your business logic first.
Is Revenue just assigned to a client OR is it linked through something like a purchase order, payment for service rendered, transaction fee, or some other source? If the answer is YES, then you need to put in the middle part first. This includes looking at how they get related.

I really can't think of a business that just has "Revenue" linked to a client. It is always Client < Service < Payment for service OR Client < Order < Payment for items type arrangement at least. Work out HOW you need to track and report payments. This will avoid problems later.

It just has revenue related to a client by date with receipt number. The revenues are based off taxes received by date or receipt number. There may be other revenues by ex. driveway permit or cemetery payments but those revenues would never be from the same client. The service would described by the UCA code (tblDemoUCAs).

 

[dullster]

Am I correct in thinking I need a table for a (example) receipt number and the receipt number or transaction number that then relates to the tblDemoTrans with multiple transactions with that receipt number?

 

[MajP]

As Mark said you have to look at your business process.

Is Revenue just assigned to a client OR is it linked through something like a purchase order, payment for service rendered, transaction fee, or some other source? If the answer is YES, then you need to put in the middle part first. This includes looking at how they get related

I do not know your business, but I would think that the answer is yes.
You create a receipt something like

TblReceipt
-ReceiptNumber
-ReceiptDate
-ReceiptDescription
-ClientID_FK (relates to tblDemoClients)

Then you assign transactions to the receipt.
tblDemoTrans
You replace the DemoClientID and make That RecieptID_FK relating to a receipt.

Your main form is Receipts with a pulldown to pick a client for that receipt. The subform is the same but linked by receipt ID not demoClientID.
Now you can have lots of different receipts for a single client.

Again, I do not know your business so this may or may not make sense.

 

[dullster]

As Mark said you have to look at your business process.

I do not know your business, but I would think that the answer is yes.
You create a receipt something like

TblReceipt
-ReceiptNumber
-ReceiptDate
-ReceiptDescription
-ClientID_FK (relates to tblDemoClients)

Then you assign transactions to the receipt.
tblDemoTrans
You replace the DemoClientID and make That RecieptID_FK relating to a receipt.

Your main form is Receipts with a pulldown to pick a client for that receipt. The subform is the same but linked by receipt ID not demoClientID.
Now you can have lots of different receipts for a single client.

Again, I do not know your business so this may or may not make sense.

This is what I was thinking. It is for Towns in the State of Wisconsin. The business in municipal business so most of the Revenue is taxes. I will work on it tomorrow.

 

[dullster]

As Mark said you have to look at your business process.

I do not know your business, but I would think that the answer is yes.
You create a receipt something like

TblReceipt
-ReceiptNumber
-ReceiptDate
-ReceiptDescription
-ClientID_FK (relates to tblDemoClients)

Then you assign transactions to the receipt.
tblDemoTrans
You replace the DemoClientID and make That RecieptID_FK relating to a receipt.

Your main form is Receipts with a pulldown to pick a client for that receipt. The subform is the same but linked by receipt ID not demoClientID.
Now you can have lots of different receipts for a single client.

Again, I do not know your business so this may or may not make sense.

Do you think the ReceiptDescription should be from tblTransType or from the tblDemoUCAs?