dcount in subforms

[gibsonn]

Hi,

What I want to do is to validate the user's entry in a subform.

Example:

If the user puts in yes to a question such as "Has the cllient had a meeting?" If the user puts in yes, the next line cannot have no entered. However they may wish to change the first line to a no which is fine.

By using dcounts I can undo this, but only on the third row, the second row still thinks that there is only one record in the table. The only way I have found to update this is to use me.refresh which stops the undo command from working.

Any suggestions, please!!!

 

[Randomblink]

HUh?

Ok...
It sounds like you are stumped...
It sounds like you have tried some ideas to make this work...
What it doesn't sound like is that you can explain easily your problem...

I don't EXACTLY understand what you were trying to say...
And hey, that's ok... I usually speak gibberish for a day while debugging code... I think it's part of the programmers disease...

So, let me try to rephrase your question... and answer it...
If I am wrong in my rephrasing, please explain again...

It sounds like you have a form...
It has a subform...
As a user enters data into fields on the subform, you want it to validate that entry based on fields from the form...?

What you want to do is setup some onChange events...
For instance...
Let's say I have a field: ProjectCompletion (Yes / No field)

This field is a checkbox that is checked whenever the Project is completed. It is on a Project Form where I manage my Projects.

Then, I have another field: InvoiceType (Text Box field)

This field is on an Invoice SubForm where I manage Invoices per each Project. This field holds the TYPES of Invoices... For instance, maybe there is a Crew PayCheck Invoice... or a Partial Pay Invoice... or a Final Payment Invoice...

Well, if you set InvoiceType's OnChange event to check the ProjectCompletion field on the Form... Then it would know...

IF the ProjectCompletion = No Then the InvoiceType CANNOT be FinalPayment.
IF the ProjectCompletion = Yes Then the Invoice Type (maybe) Could be the other CrewPayCheck AND/OR Final Payment...

You would check all this on the InvoiceType events... Either OnChange or OnExit would work...

I HOPE that answers your question...

 

[Randomblink]

HUh?

Ok...
I have read your post again, trying to look at it from another angle... It looks like you might be working with a subform in DataSheet View...

If THAT is the case...
Then what you are doing is entering a record in what you call LINE 2... and wanting it to do something based on another record, which you are calling LINE 3...

If that is the case, I just have to understand why you do it this way? Without understanding what end result you are trying to reach, I don't think I can help...

Sorry man...
Good Luck on your situation man...

 

[gibsonn]

Better example, I hope

Thank you very much for spending the time to reply.

I will try and explain again what I am trying to do.
The example below is in a continuous subform

Example:

Has a planning meeting been held? Outcome
Yes sales
No

The example above is what I am trying to prevent. If they enter no in the next row of a continuous subform I would like the record to be undone. This works if I put in another no (third row)by using the code below either in the afterupdate or change event. When I insert a breakpoint at the end of the code and insert me.refresh at the start the variables work how I want them to, but the undo doesn't. If I remove the refresh it works, but only on the third row. Does refresh act as commit so I can't undo the changes?

Dim stincid As Long
Dim lngTotal As Long
Dim lngno As Long
Dim lngyes As Long

stincid = Nz(Me![PLN_INC_ID], 0)
lngTotal = DCount("[pln_inc_id]", "adult_plan", "[pln_inc_id] =" & stincid)
lngyes = DCount("[pln_inc_id]", "adult_plan", "[pln_inc_id] =" & stincid & "and [pln_meet] = 'Yes'")
lngno = DCount("[pln_inc_id]", "adult_plan", "[pln_inc_id] =" & stincid & "and [pln_meet] = 'No'")

If lngTotal = 1 Then
    MsgBox ("No Change")
ElseIf Meet = "No" And lngyes > 0 And lngTotal > 1 Then
    Me.Undo
    Cancel = True
    MsgBox ("worked")
ElseIf Meet = "Yes" And lngno > 0 And lngTotal > 1 Then
    Me.Undo
    Cancel = True
    MsgBox ("worked yes")
End If

Thanks for any help.

 

[Randomblink]

Re: Better example, I hope

If they enter no in the next row of a continuous subform I would like the record to be undone. This works if I put in another no (third row)by using the code below either in the afterupdate or change event. When I insert a breakpoint at the end of the code and insert me.refresh at the start the variables work how I want them to, but the undo doesn't. If I remove the refresh it works, but only on the third row. Does refresh act as commit so I can't undo the changes?

Ok...
Let's see if this helps...
When you say Continuous Subform, I assume you mean that for each Parent_Item (from FORM) you can have unlimited Child_Items (from continuous subforms)...? If this is the case, I am lost, I just can't visualize it...

As for your Me.Refresh code...? The reason you have problems is because:

Me.Refresh SAVES the current record...
Undo cancels a record from saving...

So Me.Refresh means you can't Undo.

Another option...
Instead of Undo... Set those fields to fieldname = ""
This way your Me.Refresh will overwrite the record with BLANK FIELDS... This SHOULD work with the way I am envisioning your db... Feel free to post or send me a copy of your db so I could get a better understanding...

I hope this helps...

 

[Rich]

If you stuck to the original thread we wouldn't all be wasting our time!
http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=61764

 

[Randomblink]

Kinda...

They do look similar gibson...
And whereas I might not have phrased it quite so harshly...
I have to agree with the message...
It's better for you AND for anyone with the same problem if you don't multi-post across several forums with the same problem...
It makes it easier to look through a single post and all the replies, than to dig around this HUGE message board looking for answers...

I myself am guilty of doing this, so don't beat yourself up over it...
Just ask a question in a post, and if it doesn't get answered, keep re-phrasing it until someone gets it right...

Good Luck on your question...
You might try COMPLETELY re-phrasing your question on your original post... OR you might try posting a copy of your database to the post in order for people to SEE what it is you mean... Sometimes that helps...

Good luck tho...
Later...