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
229 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
 
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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

744 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now