Access can't find field |1

[Volts]

Ok heres the code behind the button to add a record it checks all the fields are populated correctly and if one field is unique (UserName) it lets the user know this record has been added. If not it tells them were they went wrong. No compile errors brilliant I thought

However on clicking this button i get a error saying Access can't find field |1 in your expression. I've done a search and am really straining my head at this one

Private Sub SaveRecord_Click()
Dim errmsg As String

On Error GoTo Err_SaveRecord_Click
'Check the validity of the data entered into the fields.

'No blank values for user name.
    If IsNull([User Name]) Then
        MsgBox "You must enter a value for User Name.", vbExclamation, "NCC Visits"
        DoCmd.CancelEvent
        [Surname].SetFocus
        [User Name].SetFocus
'User name must start with a "u".
    ElseIf Left$([User Name], 1) <> "u" Then
        MsgBox "The user name must start with a 'u' or 'v'.", vbExclamation, "NCC Visits"
        DoCmd.CancelEvent
        [Surname].SetFocus
        [User Name].SetFocus
'No blank values for First Name.
    ElseIf IsNull([First Name]) Then
        MsgBox "You must enter a value for First Name.", vbExclamation, "NCC Visits"
        DoCmd.CancelEvent
        [First Name].SetFocus
'No blank values for Surname.
    ElseIf IsNull(Surname) Then
        MsgBox "You must enter a value for Surname.", vbExclamation, "NCC Visits"
        DoCmd.CancelEvent
        Surname.SetFocus
'No blank values for Address
    ElseIf IsNull([Address]) Then
        MsgBox "You must enter a value for the address.", vbExclamation, "NCC Visits"
        DoCmd.CancelEvent
        [Address].SetFocus
'No blank values for Town
    ElseIf IsNull(Town) Then
        MsgBox "You must enter a value for the Town.", vbExclamation, "NCC Visits"
        DoCmd.CancelEvent
        Town.SetFocus
'No blank values for County
    ElseIf IsNull(County) Then
        MsgBox "You must enter a value for county.", vbExclamation, "NCC Visits"
        DoCmd.CancelEvent
        County.SetFocus
'No blank values for Post Code
    ElseIf IsNull([Post Code]) Then
        MsgBox "You must enter a value for the post code.", vbExclamation, "NCC Visits"
        DoCmd.CancelEvent
        [Post Code].SetFocus
'No blank values for Date of Birth
    ElseIf IsNull([Date of Birth]) Then
        MsgBox "You must enter a value for Date of Birth.", vbExclamation, "NCC Visits"
        DoCmd.CancelEvent
        [Date of Birth].SetFocus
'No blank values for Date Enrolled
    ElseIf IsNull([Date Enrolled]) Then
        MsgBox "You must enter a value for the enrollment date.", vbExclamation, "NCC Visits"
        DoCmd.CancelEvent
        [Date Enrolled].SetFocus
'No blank values for ethnicity
    ElseIf [Ethnicity] = 0 Then
        MsgBox "You must indicate Ethnicity.", vbExclamation, "NCC Visits"
        DoCmd.CancelEvent
        [Ethnicity].SetFocus
'No non gender people!
    ElseIf [Male/Female] = 0 Then
        MsgBox "You must indicate gender.", vbExclamation, "NCC Visits"
        DoCmd.CancelEvent
    Else
'Save the record and let the user know.
        DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
        MsgBox "New user entered successfully.", vbInformation, "NCC Visits"
        DoCmd.Close
    End If
        
Exit_SaveRecord_Click:
    Exit Sub

Err_SaveRecord_Click:

'User name is already in use.
    If Err.Number = 3022 Then
        MsgBox "That user name is in use, please select another.", vbExclamation, "NCC Visits"
        [User Name].SetFocus
 Else
'Error that I haven't thought of
        errmsg = "An unusual error has occurred. Please make a note of this description." + Chr(13) + Err.Description + Chr(13) + Str(Err.Number)
        MsgBox errmsg, vbExclamation, "NCC Visits"
    End If
    
    Resume Exit_SaveRecord_Click
End Sub

Thanks in anticipation

Chris

 

[spikepl]

Comment out

On Error GoTo Err_SaveRecord_Click

because it is just plain silly to debug with a blindfold on, and say which line gave the error.

Update:

Proper test for field for whether or not it contains text is

If Len (Me.myTextFieldName & vbNullString) > 0 ' then there is something in the field

because a textfield that is cleared by the user does not necessarily revert to Null but sometimes to "" - zero length string.

 

[pbaldy]

Temporarily comment out the "On Error..." line and run the code. When it errors, you can click on Debug and it will tell you what line is throwing the error. If you don't spot the problem, post back here with what line it is.

Also, I would not cancel the event, I'd simply structure the code in such a way that it only does the save if no tests were failed, or exit with

GoTo Exit_SaveRecord_Click

 

[Volts]

Many Thanks for your helpful comments. By commentating out the line On Err I have managed to debug and fix most of the code. and it is now checking for null values throughout

Now there is a check for unique values in the UserName if this is not met it throws up a MsgBox asking for a different username combination This is something i hoped would be captured using this bit of code

Instead i get the standard Access Runtime error 3022 dialogue box.

Err_SaveRecord_Click:

'User name is already in use.
    If Err.Number = 3022 Then
        MsgBox "That user name is in use, please select another.", vbExclamation, "NCC Visits"
        [User Name].SetFocus
 Else
'Error that I haven't thought of
        errmsg = "An unusual error has occurred. Please make a note of this description in the log book." + Chr(13) + Err.Description + Chr(13) + Str(Err.Number)
        MsgBox errmsg, vbExclamation, "NCC Visits"
    End If
    
    Resume Exit_SaveRecord_Click
End Sub

I've noticed this falls under another Err On Click does it need to be included above the code for the Save and let user know statement instead (Shown in post 1)

 

[spikepl]

WHich access version are you using? Because you are using some pretty archaic
commands.

Also, your user-name predicament is unclear. Which code checks for it and generates what error with which text? And your current solution does not work for this? What does it do?

 

[vbaInet]

Also, I would not cancel the event, I'd simply structure the code in such a way that it only does the save if no tests were failed, or exit with

Following pbaldy's advice of re-structuring the code:

'All validation - blank and beginning char
    If IsNull(Me.[User Name]) Then
        strName = "User Name"
        strMsg = "You must enter a value for " & strName & "."
    ElseIf Left(Me.[User Name], 1) <> "u" Then
        strName = "User Name"
        strMsg = "The user name must start with a 'u' or 'v'."
    ElseIf IsNull(Me.[First Name]) Then
        strName = "First Name"
        strMsg = "You must enter a value for " & strName & "."
    End If
    
    If strName <> vbNullString Then
        [COLOR="blue"]Cancel = True[/COLOR]
        MsgBox strMsg, vbExclamation, "NCC Visits"
        Me.Controls(strName).SetFocus
    End If

... but this needs to go in the Before Update event of the form.

For your 3022 error (please include the full error message next time) which reads "The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship.", you should be able to trap that error in the form's On Error event and act accordingly.

Or, in the Before Update event of the form (as indicated above), perform a check on the table to ascertain whether the record exists using the DLookup() or DCount() function. If it does set Cancel = True.

Also note the "Me." reference in the code above and avoid spaces in field/control names.

 

[Volts]

Apologies for the long wait for a response and thanks for all the tips and fixes. The database is now live. Admittedly the code was harvested from the existing original database which threw a wobbly and my fixes holding it together (cue many frowns). That database was built in 2005 long before my involvement. Ideally i need to sit down and rebuilt from the bottom using proper and correct calls//methods etc. all of which i'd need to learn