combo box value in the beforeinsert event

[gibsonn]

I have a beforeinsert event on a sub form and am trying to do some validation on it. Basically when a user clicks a value on a combo box within the sub form it hits the code below in the beforeinsert event. However, "Meet" (combo box value) is null when I clicked on No. What am I doing wrong?????

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

stincid = Nz(Forms!Adult_outcomes.Adult_Outcomes_SF!INC_ID, 0)
lngTotal = (DCount("[pln_inc_id]", "adult_plan", "[pln_inc_id] =" & stincid) + 1)
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 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

 

[Tim L]

Is "Meet" for the convenience of viewing here or the actual code you use?

If it is the actual code shouldn't the combo box value be accessed something like this:

• me.cboMeet.value ?

Where cboMeet is the control name of the combo box. Also, I've foudn that sometimes when working with subforms that you may need to reference the form fully;

• Form_frmName.cboMeet.value

Although of course it could be something else...

Tim

 

[gibsonn]

Thanks very much for replying.

I still have a problem with the value of the combobox coming back as null when I have clicked on a value, even when I added in your suggestion. The revised code is below. Any help greatly received.

Dim stincid As Long
Dim lngTotal As Long
Dim lngno As Long
Dim lngyes As Long
Dim x As String

Meet1 = Forms!adult_outcomes!Adult_Outcomes_SF.Form.Controls!Adult_Plan_SF.Form.Controls!Meet.Value
stincid = Nz(Forms!adult_outcomes.Adult_Outcomes_SF!INC_ID, 0)
lngTotal = (DCount("[pln_inc_id]", "adult_plan", "[pln_inc_id] =" & stincid) + 1)
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 Meet1 = "No" And lngyes > 0 And lngTotal > 1 Then
Me.Undo
Cancel = True
MsgBox ("worked")
ElseIf Meet1 = "Yes" And lngno > 0 And lngTotal > 1 Then
Me.Undo
Cancel = True
MsgBox ("worked yes")
End If

 

[Tim L]

Do you not have to Dim Meet1 before assigning a value to it?

 

[gibsonn]

sorry, read x as meet1 I was messing around with it. even when I put in meet1 in a dim command I am still getting invalid use of null. It's basically not remembering the value in the combo box that I click on. However, it does remeber it when I use afterinsert.

 

[Tim L]

What about using the AfterUpdate event of the combo box?

 

[Rich]

I don't think you can use the before insert event to validate a control on the subform, use the BeforeUpdate event of the subform

 

[gibsonn]

Started off using afterupdate on the combo box, however the dcounts do not work correctly on the second row of a combo box. It still thinks that there is one row in the database and when I use me.refresh the me.undo fails

 

[Tim L]

Perhaps you could put (a) hidden text box(es) on the form that references the column(s) of the comboBox then use those as the source?

 

[Rich]

Me.Refresh is forcing the record to save, you cannot therefore undo the record.
Like I said before, use the BeforeUpdate Event of the SubForm

 

[gibsonn]

Thanks for your replies. However, the beforeupdate works ok on the second row of the subform, but when I try and change the first row the validation kicks in again when it shouldn't as this is ok. the beforeinsert event would work if I could somehow capture the value of the combobox when I select it

 

[Tim L]

I think that a little bit more information about the design might be useful to me if I am to comment any further, although I may already be at the limit of my expertise...

Is there a reason why Rich's suggestion won't work?

 

[gibsonn]

I think the reason rich's suggestion didn't work is because I am using an update event, so when I update anything it triggers the validation - even when I don't want it to. It does this because I am counting the number of rows in the database and it doesn't realise that is two rows until the third row in the sub form.

 

[Rich]

You can't undo the previous record it's already been saved when you enter a new one.

And please stick to ONE post on the same topic
http://www.access-programmers.co.uk/forums/showthread.php?s=&postid=256570#post256570