Solved

I need to require a field in one table to be filled in to allow entries in another table

Posted on 2004-08-10
16
231 Views
Last Modified: 2006-11-17
Hi, I'm using Access 97. I have a Jobs table that has an autonumber Job Number field, a lot of other fields, and a Completion date field. This is only filled in when a job is finished. I also have a Billings table, which has a Job field that links to the Job Number field in Jobs table. I need to set my database up so that you can only enter information in the Billings table if the related record in the Jobs table has a value in the Completion date field. Any ideas? Thanks a lot.
0
Comment
Question by:katerpillar
  • 5
  • 4
  • 4
  • +3
16 Comments
 
LVL 5

Assisted Solution

by:ZenMasterrr
ZenMasterrr earned 50 total points
ID: 11760988
add the tables to the relationship diagram, you can specify this type of thing there...if you need help with this , please ask

zen :))
0
 
LVL 5

Accepted Solution

by:
broesi earned 75 total points
ID: 11761004
Hi,

with Access you can't enforce this requirement on table level, so your only option is to create a form that takes care of checking the completion date field before adding records to the billing table.

Plus, you might want to restrict users from entering data into the tables directly.

One way to implement this would be to create a command button on your Billings-Form. The code behind this button could look like this:

Dim rst as recordset
Dim lngJobID as long

lngJobID=...

set rst=currentdb.recordset("SELECT * FROM tblJobjs where JobID=" & lngJobID)
if not rst.eof then
  If isnull(rst!CompletionDate) then
    msgbox "Job not completed"
  else
    'add to billings table here
  endif


else
   msgbox "No Job found!"
endif

HTH,


Michael
0
 
LVL 5

Expert Comment

by:ZenMasterrr
ID: 11761050
you can do this with relationship diagram, if you have a separate table for the completion date and job number, if the create a relationship between the billing table and table with completion date and job number, and enforce referential integrity
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 26

Expert Comment

by:dannywareham
ID: 11761130
ZenMasterrr is correct - it can be done through relationships.
Much simpler than building a coded form for it (sorry broesi)

:-)
0
 

Author Comment

by:katerpillar
ID: 11761188
Zenmasterrr -

thanks, I like your suggestion best. Sorry to be dumb, but I'll need to link my new table to both of the old ones - should I enforce referential integrity on both links?
0
 
LVL 5

Expert Comment

by:broesi
ID: 11761475
Hm, I cannot imagine how you can set up a relationship between 2 fields that checks the value of a third field?!

Sure, you can define a relationship between the JobID-field of the two tables. This way you can enter billings only for existing jobs. The only way I can imaginge this would be to create a query that filters shows completed jobs only and add a relationship between the billings table and that query. But afaik it is not possible to enforce referential integrity between queries...

Michael
0
 
LVL 5

Expert Comment

by:ZenMasterrr
ID: 11761556
yes katerpillar, you wouldnt want a job number in new completed table that doesn't exist in your main table right ?
0
 

Author Comment

by:katerpillar
ID: 11761645
This is getting too confusing. I think if I were starting from scratch I would design it differently with extra tables as ZenMasterrr suggests, but as it is I think I'll just write in some code. Story of my life. Thanks everyone.
0
 
LVL 5

Expert Comment

by:broesi
ID: 11761652
Here's how I understood the question:

1. Users should not be able to enter billings for non-existing jobs (of course)
2. Users should only be able to enter billings for jobs with a non-empty field 'completion date'

Is that correct?
0
 
LVL 5

Expert Comment

by:broesi
ID: 11761692
Ups, sorry zenmasterrr! I did'nt read your suggestion right: a _separate_ table for completion date/job number will of course allow you to set this up via relationships!

Michael
0
 
LVL 84
ID: 11761775
Although ZenMasterrr's suggestion of a separate CompletionDate table would work, from my point of view I'd approach this differently ... after all, CompetionDate would be a tightly coupled property of it's "parent" entity (Job, Invoice, etc), would be directly dependant on the Primary Key of that entity, and therefore should be stored in the same table ... I realize that the Jet engine could certainly enforce this relationship, but to me this is unnecessary. And, from my perspecitve, this is more along the lines of a Business Rule (a piece of logic that is enforced based on requirements of the user/application/customer) than a matter of referential integrity (which should be used to enforce the relationship between parent and child records, NOT to determine/verify the state of the data).

With that logic, I'm going with broesi on this one ... use logic in your form to enforce this business requirement. If this bit of data is stored in the table with it's rightful entity, then enforced relationships would determine whether a record exists before allowing a "child" record to be added, but a relationship would NOT be able to determine what the value of YourTable.DateCompleted would be ... admittedly, I haven't attempted to set something like this up (simply because I have always assumed this was impossible with Access), but I'd have to see this type of setup ... matter of fact, I'd LOVE to see this type of setup in Access, since it'd make my life a helluva lot easier!!!!

However, either suggestion would work, so whatever you choose would be "doable" ...
0
 
LVL 5

Expert Comment

by:ZenMasterrr
ID: 11761827
no probs michael, i misread a lot too hehehe

Katerpillar, this doesn't need to be painful, create the JobCompleted table, append all JOBIDs,completed_dates from your JobTable where date_completed is not null,  use a form for data input as michael suggests, set the jobcompleted.job_id = job.job_id value, and completed_date datasource to jobcompleted.completed_date, then create the relationships.
0
 
LVL 4

Expert Comment

by:szacks
ID: 11762255
I agree with LSM. Business rules should generally not be enforced by referential integrity.
There are times that you may want to consider breaking a business rule, such as if you made a deal with a client to bill them partially in the middle of a job (such as for a 6 month job, or if you're afraid the client won't pay so you ask for 50% up front). You would still want the job linked to the billing, but you might include an override button or something similar in the form.
0
 
LVL 5

Expert Comment

by:broesi
ID: 11762333
Good point!
0
 

Author Comment

by:katerpillar
ID: 11765171
Szacks - that is a good point, although in this case the jobs are small and so we would not usually bill partially; or if we had to then we would split the job in two. But the main principle is sound and I think I will stick with the code. However, please excuse me if I don't close this question for a while, I'm still not sure which answer to accept. I need to think a little more about ZenMasterrr's suggestion.
0
 

Author Comment

by:katerpillar
ID: 11956102
OK, so I have thought. What I've done is made my billings subform in my main form invisible unless "completion date" is filled in. Deleting a completion date leads to deleting of related billing records (after a prompt, of course!). This probably isn't the most elegant solution but it seems to be working for now. Since I haven't really used anybody's answer I'll split the points.

Thanks everyone!
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

806 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question