Automatically add record to another table upon saving of record in another. (1 Viewer)

Jarichardusa

New member
Local time
Today, 11:49
Joined
May 23, 2020
Messages
19
Please excuse me as I try to make my request clear. I'm not an access guru of any sort. I have a db I've created to track jobs I complete for customers. Each job could possible result in a tip from the customer. Tips are tracked in a table: tblTips which has the fields: TipID, TipDate, Gratuity, and Delivery_ID. There is a one-one relationship between TipID and Delivery_ID. Right now, no record gets created into this table unless I add the tip information for the delivery. This is causing issues with other portions of my db forms where I'm trying to calculate certain things, and those things involve the tip amounts, but if there are no tip records to be had in the results of the particular filter I run, then the calculated fields on the form just stay blank. I've tried to find a way to just set a default value of 0.00 on the field of the form that calculates the total tips for filtered range, but since it is a calculated field, I can't do that, at least I can't get it to work. So, what I have surmised, is that the only way around this would be to have a Tip record automatically created in tblTips as part of the Save Record button code when I save the newly created delivery utilizing the The Delivery_ID would be the DeliveryID of the record I just created, the TipDate would be Today and the Gratuity would be simply 0.00. Any help would be appreciated. I looked up append query was just royally confused, and not sure tha would be how to do it in this case.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:49
Joined
Oct 29, 2018
Messages
21,360
Hi. I wouldn't suggest adding records simply to have a value of zero. Have you tried using the Nz() function in your calculation to get a value of zero?
 

Jarichardusa

New member
Local time
Today, 11:49
Joined
May 23, 2020
Messages
19
Hi. I wouldn't suggest adding records simply to have a value of zero. Have you tried using the Nz() function in your calculation to get a value of zero?
Don't know what that is or how that would work. This is what is in the control source of the TextBox that is calculating the tips:

Code:
=[sbfrm_qryDeliveries].[Form].[TipAmountTotal]

TipAmountTotal comes from a hidden totals field on the subform which is on the mainform where this textbox resides. How would I set that up so that if there is no TipAmountTotal, that textbox just returns 0 as the value?
 

plog

Banishment Pending
Local time
Today, 10:49
Joined
May 11, 2011
Messages
11,613
There is a one-one relationship between TipID and Delivery_ID

You shouldn't have 1-1 relationship. Simply put the tip fields in the Delivery table.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:49
Joined
Feb 28, 2001
Messages
27,005
You are obviously new to the forum based on your post count and you have made it clear that you do not consider yourself a "guru." I would like to suggest that if you have not already done so, you should study the concept of "normalization." If you search this forum, that's all you need to put in the search box. However, if you decide to search the web, make that "database normalization" because there are other types of normalization. If you do the web search, select responses from .EDU sites first. Not that .COM sites won't have good articles, but frequently they have something to sell and might put some proprietary information in their articles that isn't true for general normalization.

Once you have done some studying, you will find that it is EXTREMELY rare for a 1-to-1 relationship to be used. There ARE times, but from first glance, I would say this isn't one of them. Your problem is that you can't create a record for a table on the "wrong" side of a 1-to-1 relationship - and BOTH sides are wrong. The correct solution is that you pick which one is more important, make it the "1" side, and then make the other side a "many" side. Then on that "other side" you make the field in question have a unique index. What that does is (a) a 1/many case includes a 1/0 case i.e. no corresponding record yet. But you CAN have a related record. (b) Because of the unique index, you cannot have two records with the same key. But the RELATIONSHIP (1/many) doesn't need to know that. I'm going to guess that a delivery depends on a tip. So "delivery" should be the "1" side and "tip" should be the many side. And if you don't get a tip, you can still record a delivery.

And I'm not sure about the structure you described anyway. But that was a CONCEPTUAL overview of how to think about the case you described.
 

Users who are viewing this thread

Top Bottom