Error on VB page, where do start?

[akhlaq768]

Hi,



I'm desperately seeking your help on this one... my form has stopped working correctly, each time i populate the mandatory fields, and I then save the form, i get the following error message on the Visual Basic page

Private Sub Form_AfterInsert()
Me.txtFirstEntered = Date
Me.txtEnteredBy = Environ("Username")
If Me.txtLeadDivision = "Corporate" Or Me.txtLeadDivision = "Trust-wide" Or Me.txtLeadDivision = "Primary Care" Or Me.txtLeadDivision = "1" Or Me.txtLeadDivision = "2" Or Me.txtLeadDivision = "3" Or Me.txtLeadDivision = "4" Or Me.txtLeadDivision = "5" Or Me.txtLeadDivision = "A" Or Me.txtLeadDivision = "B" Or Me.txtLeadDivision = "C" Or Me.txtLeadDivision = "D" Then
Me.txtNewProjectID = ("CA" & Me.txtLeadDivision & "-" & Format(Me.txtArbitraryNumber, "00000") & "-" & Right(Me.txtFirstEntered, 2))
Else
Me.txtNewProjectID = ("CA" & "0-" & Format(Me.txtArbitraryNumber, "00000") & "-" & Right(Me.txtFirstEntered, 2))
End If
MsgBox ("The code for the audit you just registered is " & Me.txtNewProjectID & ".")
End Sub

I think it may be something to do with txtArbitraryNumber but im not 100% sure or dont know where to start in fixing it

[RuralGuy]

Do you have Option Explicit at the top of your code page? What error message are you getting?

[akhlaq768]

yes i have an Option Explicit on top of the page and the error message i recieve says

Compile error:
Method or data member not found

[RuralGuy]

Which line is highlighted?

[akhlaq768]

ive inserted a screenshot of the error hope you can view it

[RuralGuy]

I would start by *importing* everything into a fresh new db: http://www.btabdevelopment.com/ts/impnew

[Missinglinq]

The error message is saying, in essence, that it cannot find a Control/Field named txtLeadDivision. If there is, in fact, one with that name, your Control named txtLeadDivision may be corrupt, or the Form, or the Database, itself. Importing everything into a new. blank file will resolve the latter two cases, but I'm not sure about the first case, where the Control is corrupt.

If RG's advice doesn't work, you might Delete the txtLeadDivision Control and then re-create it.

This has nothing to do with your problem (unless the txtLeadDivision Control doesn't exist) but when you have a large number of Ifs in an If...Then construct, or a lot of tests, like you do here

If Me.txtLeadDivision = "Corporate" Or Me.txtLeadDivision = "Trust-wide" Or Me.txtLeadDivision = "Primary Care" Or Me.txtLeadDivision = "1" Or Me.txtLeadDivision = "2" Or Me.txtLeadDivision = "3" Or Me.txtLeadDivision = "4" Or Me.txtLeadDivision = "5" Or Me.txtLeadDivision = "A" Or Me.txtLeadDivision = "B" Or Me.txtLeadDivision = "C" Or Me.txtLeadDivision = "D" Then
    Me.txtNewProjectID = ("CA" & Me.txtLeadDivision & "-" & Format(Me.txtArbitraryNumber, "00000") & "-" & Right(Me.txtFirstEntered, 2))
Else
    Me.txtNewProjectID = ("CA" & "0-" & Format(Me.txtArbitraryNumber, "00000") & "-" & Right(Me.txtFirstEntered, 2))
End If

it's probably better to use a Select Case construct:

Select Case txtLeadDivision

Case "Corporate", "Trust-wide", "Primary Care", "1", "2", "3", "4", "5", "A", "B", "C", "D"
  
  Me.txtNewProjectID = ("CA" & Me.txtLeadDivision & "-" & Format(Me.txtArbitraryNumber, "00000") & "-" & Right(Me.txtFirstEntered, 2))

Case Else
  
  Me.txtNewProjectID = ("CA" & "0-" & Format(Me.txtArbitraryNumber, "00000") & "-" & Right(Me.txtFirstEntered, 2))

End Select

It's shorter/cleaner and much easier to read, as you'll see if you copy and paste it into the Form's code window.

Linq ;0)>