Enter table record on form, but check value in field in another table. If ok increment checked field

[Jdubya]

Hi everyone



I am new to Access, a beginner. I'm helping a friend with a database for his charity, and although I have mastered the simple things, I am stuck on how to do the following

I have a form where sessions can be added for a client, which update a table called Client sessions, keyed on Client ID/Session ID/Care package ID

When the user adds a record to the Client sessions file, I need a check to be made on another table called Care Package master, keyed on Client ID/Care package ID, that the care package number they enter on the form exists for that client in the Care package master table, and if so that all the sessions in that care package haven't been used (it has 2 fields - Sessions in package and Sessions used). If Client ID/Care package ID combination not in the master table, produce an error message. If all sessions used, produce an error message. If it does exist and sessions available then increment by 1 the Sessions used field in the Care package master.

Thanks for any help.

[moke123]

Before you go any further I would get rid of all the spaces in your table names and field names.
It will save you a lot of future problems.

[Jdubya]

Thanks for the advice

[Gicu]

Once you do that and come back to your original question you might want to reconsider altering your design by removing the "Sessions Completed" and "Care Package Completed" fields as they are not really needed. You can always get the number of sessions per client for each care package using a simple totals query and a dLookup into that or even easier a dCount on the original table. In your client session master form you should have the "CarePackageID" in a combo that has a row source to only show the "available" care packages (the ones existing in the package table for the client and that have a dCount<SessionsInPackage) - no checking or error messages needed if they can only pick the correct ones.

Cheers,

[ssanfu]

Before you go any further I would get rid of all the spaces in your table names and field names.

Also remove spaces in ALL object names: Tables, Queries, Forms, and Reports.
Examples:
tblCarePackageMaster

qryPracticioner

frmClientSessionsMaster

rptPracticionerMaster



Makes coding and creating queries soooo much easier!

[Jdubya]

Thanks for the advice. I actually went ahead and did this after mike123's suggestion ;-)

[Jdubya]

Thank you so much for the advice. I need to investigate how to do a dCount, and will look at setting up the combo box as suggested. Where would I put these though? Sorry if I sound thick, but I'm a complete novice using Access and am only doing this to help a friend. I'm retired and so it's not work related. Thanks in advance Vlad.

[Gicu]

The best would be to create a small sample with your updated db with no sensitive data in it (looks like you already have some dummy data to play with), zip it and upload it here.

The combo would replace the CarePackageID text box on the subform in your first screen shot and the dCount would go in the combo's row source:
SessionsUsed:dCount("*","[CarePackageMaster]","ClientID=" & [ClientID] & " And [CarePackageID] = " & [CarePackageID]) (assumes ClientID and CarePackageID are textboxes on the same subform bound to fields in the CarePackageMaster table).

In this new calculated field's criteria row you enter a similar dLookup:
<dLookup("SessionsInPackage","[CarePackageMaster]","ClientID=" & [ClientID] & " And [CarePackageID] = " & [CarePackageID])

So with these you now ensure that the combo will only display CarePackageIDs that still have available sessions. The last thing is to force the combo to requery its source when it gets the focus, so in its GetFocus event you add this:

Me.cboCarePackageID.Requery

Cheers,
Vlad