Function Error: "You enetered an expression that has no value"

[SnaveBelac]

I have been using a prticular function on a form to show or hide subforms based on data on the form.

This particular function has been working perfectly for months !

I was just adding a button to lock all the controls on a form to prevnet non administrators from editing the content and BAM - my other (apparently) unrelated function goed belly up woth the error - "You entered an expression that has no value"

At first I thought it was trying to access the AddressID prior to the form completely loading, hence the Do-Loop

This has got me completely stumped - Any help would be very much aappreciated...code is below:

Private Sub Form_Current()
  Dim varExist As Variant
  
    Do
    Loop Until Application.SysCmd(acSysCmdGetObjectState, acForm, Me.Name) = acObjStateOpen
  
[b]***ERROR OCCURS ON THIS LINE***[/b]
  If Not IsNull(Me!AddressID.Value) And Me!AddressID.Value <> "" Then
[b]***ERROR OCCURS ON THIS LINE***[/b]
    varExist = DLookup("RateDetailID", "tbl_RateDetail", "RD_BillID=" & Me!AddressID)
    If IsNull(varExist) Then
      Me!lblNoRates.Visible = True
      Me!sub_RateDetail.Visible = False
    Else
      Me!lblNoRates.Visible = False
      Me!sub_RateDetail.Visible = True
    End If
    varExist = DLookup("JobID", "tbl_Job", "J_SiteID=" & Me!AddressID)
    If IsNull(varExist) Then
      Me!lblNoSite.Visible = True
      Me!sub_JobSite.Visible = False
    Else
      Me!lblNoSite.Visible = False
      Me!sub_JobSite.Visible = True
    End If
    varExist = DLookup("E_EquipmentID", "tbl_Equipment", "E_AddressID=" & Me!AddressID)
    If IsNull(varExist) Then
      Me!lblNoEquipment.Visible = True
      Me!sub_Equipment.Visible = False
    Else
      Me!lblNoEquipment.Visible = False
      Me!sub_Equipment.Visible = True
    End If
    varExist = DLookup("JobID", "tbl_Job", "J_BillID=" & Me!AddressID)
    If IsNull(varExist) Then
      Me!lblNoBill.Visible = True
      Me!sub_JobBill.Visible = False
    Else
      Me!lblNoBill.Visible = False
      Me!sub_JobBill.Visible = True
    End If
  End If
End Sub

Thanks in advance

----------------------------
SnaveBelac - Adventurer
----------------------------

 

[Crowley16]

looks like it doesn't like the AddressID.value bit...

are u sure u're typing the correct thing in? Access thinks that AddressID doesn't contain a value, like a command button...

 

[SnaveBelac]

Like I say - It has been working for months - I havn't changed a thing. It is defintely a text box.

I was using another routine to either lock/enable/unlock/disable the control when it went wrong - could that have something to with it ?

Thanks for the speedy response



----------------------------
SnaveBelac - Adventurer
----------------------------

 

[Crowley16]

ahh...

well, check that the control is enabled, and unlocked...

Also, this textbox, I'm assuming that it is on the form that you are trying to access it from right? not on a subform or parent...

 

[SnaveBelac]

Have double checked - its enabled, unlocked and definitely on the form.

The name hasn't changed. I have reset the control properties to be enabled-Yes and Locked-No and resaved, just to be sure.

Is there anything else that could cause it to have trouble locating it like this ?

Thanks again - I am losing hair fast here. I bet it is something really really simple I am missing !!

----------------------------
SnaveBelac - Adventurer
----------------------------

 

[Crowley16]

eh, what about "Me.Address" instead of "Me!Address.Value"

The value is default so I don't usually put it in, and I usually use the '.' instead of '!' for various reasons...

 

[SnaveBelac]

Made no difference, same error, same place I am afraid !!

I had added and removed the ".value" to try and get it to work but, as expected, it made no difference.


----------------------------
SnaveBelac - Adventurer
----------------------------

 

[Crowley16]

try putting Msgbox me.address infront of the if statement...
see what that gives you?

and if it gives you the error, try
me.forms.controls("Address").value
or something like that, not sure about the exact syntax...

keep trying different thing until msgbox gives you the correct value

 

[SnaveBelac]

It seems to wokring again now ! The forms allow additions and allow deletions properties had been disabled - I think this had something to do with it but I dont know what.

FYI:

Forms("formname").Controls("controlname")

Thanks for the help



----------------------------
SnaveBelac - Adventurer
----------------------------

 

[Crowley16]

yeah, I had a similar problem a while back, for some reason access just wouldn't recognise a control on the form unless it was specified explicitly.

I've never found out why it suddenly did that though...