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.
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.
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.
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.
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?
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.
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.
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.
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.
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?
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.
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).
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?
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.
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.
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.
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.
I think I misunderstood your original post. I assumed that the parent form, by virtue of its name, was for inserting a revenue transaction, and consequently that the subform was for inserting multiple line items per transaction. As far as I can see on reflection, the parent form is merely to allow navigation to a client record, and each row in the subform is a revenue transaction for the client in question.
The above would work if the combo box in the subform were unbound, i.e. its ControlSource property were left blank, and its RowSource were:
SQL:
SELECT DemoClientID, ClientFullName FROM tblDemoClients ORDER BY ClientFullName;
It's other properties would be set as follows:
BoundColumn: 1
ColumnCount: 2
ColumnWidths: 0cm
By seeing the ColumnWidths property to zero the first column, DemoClientID, is hidden, so only the ClientFullName is seen in the control.
You'd then need to put the following code in the combo box's AfterUpdate event procedure:
Code:
Const MESSAGETEXT = "No matching record"
Dim ctrl As Control
Set ctrl = Me.ActiveControl
If Not IsNull(ctrl) Then
With Me.RecordsetClone
.FindFirst "ContactID = " & ctrl
If Not .NoMatch Then
' go to record by synchronizing bookmarks
Me.Bookmark = .Bookmark
Else
MsgBox MESSAGETEXT, vbInformation, "Warning"
End If
End With
End If
The LinkMasterFields and LinkChildFields properties should both be set to to DemoClientID, i.e the names of the primary and foreign key columns in the two tables.
However, another approach, which I've always found to give better performance, and requires minimal code would to be to not use a parent/subform at all, but to use a form in continuous forms view, with the following as its RecordSource property:
SQL:
SELECT * FROM tblDemoTrans
WHERE DemoClientID = Nz(Forms!NameOfFormGoesHere!NameOfComboBoxGoesHere,0)
ORDER BY TransDate;
In the form header section place an unbound combo box, set up in exactly the same way as I described above. In the combo box's AfterUpdate event procedure put the following lines of code:
The first line sets the default value for the DemoClientID foreign key when a new row is inserted in the form. The txtDemoClientID control is a hidden text box in the form's detail section, bound to the DemoClientID column. The DefaultValue property is a string expression regardless of the data type of the column in question, so is wrapped in literal quote characters as above. The second line reloads the form's recordset to show only the rows which match the client selected in the unbound combo box in the form's header.
Finally, the ClientShortName column in tblDemoTrans is redundant and should be dropped. As it stands, the table is not normalized to ThirdNormal Form (3NF) due to the transitive dependency on the primary key of the column via the DemoClientID column, and is consequently open to the risk of update anomalies.
Again it may be fine the way you have it. By now we have a little better ID of what this is, but it was never explained how transactions, relate to UCAs, to budget. You would only need an intermediate table if there was a requirement to group transactions for a customer and that group of transactions had an important meeting.
example
In my home depot app I have lots of transactions, and they are related to a "purchase". If not it would be hard to return anything. The "purchase" has a date, store location, payment used etc.
If I go to my county site web page I just have transactions related to me. I can see my property taxes paid, water bill, other permits. There is no meaning in grouping more than one of these transactions.
Now it gets trickier when it can be optionally grouped. In my travel claim app I can build a trip and then when I enter an expense it relates it to that trip. I can also file expenses not related to a specific trip.
