• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 242
  • Last Modified:

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

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
katerpillar
Asked:
katerpillar
  • 5
  • 4
  • 4
  • +3
2 Solutions
 
ZenMasterrrCommented:
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
 
broesiCommented:
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
 
ZenMasterrrCommented:
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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

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

:-)
0
 
katerpillarAuthor Commented:
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
 
broesiCommented:
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
 
ZenMasterrrCommented:
yes katerpillar, you wouldnt want a job number in new completed table that doesn't exist in your main table right ?
0
 
katerpillarAuthor Commented:
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
 
broesiCommented:
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
 
broesiCommented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
ZenMasterrrCommented:
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
 
szacksCommented:
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
 
broesiCommented:
Good point!
0
 
katerpillarAuthor Commented:
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
 
katerpillarAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 5
  • 4
  • 4
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now