field comparison not working in On Update event 1

[cdck]

I have a subform for which the data source is:

SELECT dbo_GroupUsers.UID, dbo_GroupUsers.GrpID, dbo_GroupUsers.GrpLev, dbo_Groups.GrpLevSt, dbo_Groups.GrpLevEn FROM dbo_GroupUsers RIGHT JOIN dbo_Groups ON dbo_GroupUsers.GrpID=dbo_Groups.GrpID;

It resides on the main Groups form, tied to the master group record through GrpID.

I am able to edit most of the subform fields with no issue.

GrpLevSt and GrpLevEn, which are pulled from the master Group record via the join above, are actual fields which are not visible on the form. Their purpose is just to check the value entered in GrpLev and verify that it is not outside the limits set for that group. I added After Update coding to the GrpLev field to perform the comparison, and this is where the issue appears. The code:

    If Me.txtGrpLev.Value < Me.txtGrpLevSt.Value Then
        MsgBox ("The minimum level in this group is " & Me.txtGrpLevSt.Value)
        Me.txtGrpLev.Value = Me.txtGrpLevSt.Value
    ElseIf Me.txtGrpLev.Value > Me.txtGrpLevEn.Value Then
        MsgBox ("The maximum level in this group is " & Me.txtGrpLevEn.Value)
        Me.txtGrpLev.Value = Me.txtGrpLevEn.Value
    End If

When I set the value of GrpLev to a value which would violate the constraints, I get an error which states:

The expression After Update you entered as the event property setting produced the following error: A problem occurred while Microsoft Office Access was communication with the OLE server or ActiveX Control.

*The expression may not result in the name of a macro, the name of a user-defined function, or [Event Procedure].
*There may have been an error evaluating the function, event, or macro.

I get the same error when I attempt the comparison on the fields (Me.txtGrpLevSt.Value) or on the data (Me.GrpLevSt or Me.GrpLevSt.Value). Does anyone have any insight?

Cheryl dc Kern

 

[lameid]

You know you are violating a constraint...

I guess the question is what is your issue or what do you want to do instead or what do you expect to happen?

 

[cdck]

Actually, it does not matter whether I violate the constraint or not. Even if the number placed in the field falls within my desired parameters, it still throws the error, just for trying to check it.

As you can see in the code above, If the number is below the start level, it should put up a message box saying you can't do that, and reset the value of the field to the start level. If it is above the end level, same deal, but with the end level. If it is within the range, nothing happens.

I believe I have given all the detail above.

Cheryl dc Kern

 

[lameid]

I see you meant when the value fails your code check.... with table names like dbo_Groups I inferred SQL server and assumed SQL table contraints....

I'm going to have to go with the fact that you are trying to update the outside of outer join... Sometimes things get hinky with linked ODBC tables. Also if like I think below, you already have that record open in the main form, you will likely have problems.

It appears that your subform is just showing data for dbo_GroupUsers. It would also seem that dbo_Groups is the obvious record source for the parent/main form. Why not just validate off the parent forms values and use dbo_GroupUsers as your recordsource?

 

[cdck]

Yes! Perfect. I removed the level fields form the subform and directed the comparison to the fields on the main form, and it now works perfectly.

New Code:

    Dim LevSt As Integer, LevEn As Integer
    
    LevSt = [Forms]![Group Information]![GrpLevSt]
    LevEn = [Forms]![Group Information]![GrpLevEn]

    If Me.txtGrpLev.Value < LevSt Then
        MsgBox ("The minimum level in this group is " & LevSt)
        Me.txtGrpLev.Value = LevSt
    ElseIf Me.txtGrpLev.Value > LevEn Then
        MsgBox ("The maximum level in this group is " & LevEn)
        Me.txtGrpLev.Value = LevEn
    End If

Thanks!

Cheryl dc Kern

 

[lameid]

I'm not sure I have the syntax exactly right cold but you might also try...

Dim LevSt As Integer, LevEn As Integer        
LevSt = Me.Parent.Form![GrpLevSt]    
LevEn = Me.Parent.Form![GrpLevEn]

Then if you ever need to use the subform on a different mainform, it should just work.

 

[cdck]

Oh, and just to confirm, the background tables are, indeed, stored on SQL Server.

Cheryl dc Kern

 

[cdck]

I just tried the coding suggestion, and it worked as stated. Thanks again, lameid!

Cheryl dc Kern