Proper Database Design--Help

[CatahoulaHound]

First I want to say thanks for all the invaluable assistance offered here. OK-I have a simple database that I use to register members. I have a "FamilyID" table to track the families, a "PlayerID" table to track Players, a "SportsRegistration" table to track what sports the kids sign up for and a "Payments" table. Now I need to add a (flag) someplace that will trigger if we have a returned check from the previous year or somebody did not turn their equipment back in. My question is what is the best/proper method for doing this. Should I just put another field (TagAlert) in the "FamilyID" or "PlayerID" table and tie it to a returned check/equipment table?? Im not sure which way to go on this. Any suggestions would be very helpful and greatly appreciated.
Thanks
Pat

 

[ghubbell]

I suspect it is the Player who should be flagged if he/she didn't pay or return the equipment unless you want to write off the whole family (what if the family has 6 children) for the error. A tag/alert field is a decent idea and another table of players notes/dates tied to the playerID might tell you why and when and ...how many times. Gord
ghubbell@total.net

 

[CatahoulaHound]

Hi Gord:
Thanks for the feedback. I will most likely tie the "tblReturnedCheck to the whole family as they are the ones that are passed the bad paper and we don't want them to be able to register and pay with another check (which may bounce again) until they have made good on the first. Here is my question. On my main registration form, family names are selected from a combo box which populates the other fields on the form, (address, phone, mom-dad names etc.) I suppose that I can have this "TagAlert" field also appear on the form however not being a programmer, I don't know the coding that would look at, and evaluate, that field and based on what is there STOP the registration process and redirect to another form which either shows the record in the "tblRetrunedCheck" or "tblEquipment" table. Can you give me some guidance here?
Thanks
Pat

 

[ghubbell]

I have developed similar to meet ISO standards and if the customer is baaad....bang...everything stops. Here's a simple method:
To your combo add another column to include the tagalert. I'll assume tagalert checked (true or yes) is bad.
On the after update event of the combo add something like:
If me.[MyComboName].Column(4)= True then
MsgBox "This Family is bad. Do not continue with.(and so on).",vbCritical,"Bad Family Notification"
End if
I chose column 4 as example. Combos columns are zero based which means the first column you have #1 is actually (0). If this is your IDNumber you probably don't see it. Then you have Name (1), Address(2), Mom & Dad (3) and so on. You can add in the tagAlert column anywhere, just recall to add 1 to the column count of the combo and set this new column's width to 0 if you don't want to see it or whatever if you do. Start with this and advise if you're ok. We can go further if need be.

Gord
ghubbell@total.net

 

[CatahoulaHound]

Thanks again Gord. I will be working on this tonight and this weekend. I'm sure I will need help with the rest of the coding after the MsgBox, however, I want to try to work through it first. Again, thanks so much for lending your expertise to novices such as myself.
Pat

 

[CatahoulaHound]

OK--Im tired of beating my head against the wall. If it's not asking too much Gord, could you plz give me the code that we discussed above:
If me.[Combo69].Column(6)= True then
MsgBox "This Family is bad. Do not continue with.(and so
on).",vbCritical,"Bad Family Notification"
End if

Also, on the AftrUpdate event I already have
Sub Combo69_AfterUpdate()

Me.RecordsetClone.FindFirst "[FamilyID] = " & Me![Combo69]
Me.Bookmark = Me.RecordsetClone.Bookmark

End Sub

Where would I put the If statement. I keep getting an error about if block??
Thanks Again
Pat

 

[ghubbell]

Pat: Try this:
Sub Combo69_AfterUpdate()
Me.RecordsetClone.FindFirst "[FamilyID] = " & Me![Combo69]
Me.Bookmark = Me.RecordsetClone.Bookmark
If me.Combo69.Column(6)= True then
MsgBox "This Family is bad. Do not continue with.(and so on).",vbCritical,"Bad Family Notification"
End if

End Sub
Please advise results Gord
ghubbell@total.net

 

[CatahoulaHound]

Hi GORD.
We used the program today to register about 1000 kids and everything worked well. Here is the code that I used to bring up a form that had ALL of the returned checks in the file. Since I was the only one using it today, this is not a problem. However, I would like to tell the "ReturnedCheckFRM" to open to the BAD-FAMLY FamilyID.
Thanks So Much for all your help.
Pat


Sub Combo69_AfterUpdate()
Me.RecordsetClone.FindFirst "[FamilyID] = " & Me![Combo69]
Me.Bookmark = Me.RecordsetClone.Bookmark
If Me.Combo69.Column(4) = True Then
DoCmd.CancelEvent
DoCmd.OpenForm "ReturnedCheckFRM", acNormal

MsgBox "THERE IS A FLAG ON THIS FAMILY FILE. REGISTRATION CAN NOT CONTINUE UNTIL THIS FLAG IS CLEARED.", vbCritical, "Bad Family Notification"
End If

End Sub

 

[ghubbell]

Pat:
Remove DoCmd.CancelEvent and change DoCmd.OpenForm "ReturnedCheckFRM" to:
DoCmd.OpenForm "ReturnedCheckFRM",,, "[FamilyID] = " & Me![Combo69]

Now if you want to get nasty and delete the registration record, add this right after:

Me.AllowDeletions = True 'in case you don't allow deletes on this form (optional)
DoCmd.RunCommand acCmdSelectRecord 'will select this record
DoCmd.SetWarnings False 'will bypass warning about...
DoCmd.RunCommand acCmdDeleteRecord 'deleting the record
DoCmd.SetWarnings True 'then will restore your warnings for the future
Me.AllowDeletions = False 'reset from above (optional)

Give it a shot! Glad to see so far so good!
Gord
ghubbell@total.net