#Name? Error on Form (1 Viewer)

[Lkwdmntr]

Hi Guys,

I added a new field "birthdate" to an existing table and form. When I go to add a new record using the form, the birthdate field fills in with "#Name?". I am stumped! Does anyone have any idea why this may be happening?

 

[bob fitz]

what is the record source of your form

 

[Lkwdmntr]

The Users table and the birthdate field from that table is the control source. On the form the field is txtBirthdate.

 

[bob fitz]

The birthdate field from my table. On the form the field is txtBirthdate.

That doesn't really answer my question

 

[Lkwdmntr]

That doesn't really answer my question

I edited my answer. The source is the actual user's table, and the control source of the birthdate field is the birthdate from the table.

 

[LarryE]

You need to add that field to the RecordSource of the form so it can be bound.

 

[theDBguy]

Can you post a sample db with test data to demonstrate the problem?

 

[Lkwdmntr]

You need to add that field to the RecordSource of the form so it can be bound.

It is bound. In the properties section of the form, birthdate is used for the control source

Can you post a sample db with test data to demonstrate the problem?

I can. When you open the database. If you open the main menu and click "add user", you will see the problem. When you go to type in the first name, the birthdate field fills with #Name?. I have a meeting to go to now, so I will check back later this evening. Thanks for helping with this. It really stumped me.

 

[arnelgp]

When you go to type in the first name, the birthdate field fills with #Name?

it doesn't occur to me when i tested your form.

 

[The_Doc_Man]

Looks to me like when you click Add User you launch your Users form to a new record for which some of the fields will be nulls because you just created a new empty record. Not sure why other fields don't do the same thing, but it might be format differences.

EDIT: I see that Arnel tried it and it didn't happen for him. I cranked up my anti-virus and tried it myself, and it didn't do that for me either.

So I was using Ac2010. What version of Access are you using? @arnelgp - what version of Access were you using when you did your test?

 

[theDBguy]

It is bound. In the properties section of the form, birthdate is used for the control source

I can. When you open the database. If you open the main menu and click "add user", you will see the problem. When you go to type in the first name, the birthdate field fills with #Name?. I have a meeting to go to now, so I will check back later this evening. Thanks for helping with this. It really stumped me.

Thanks for posting a sample DB. When I opened the Main Menu form and clicked Add New User, I tried to enter a First Name, but the #Name? error never showed up. Not when I was still entering a first name and also not when I tabbed out of it after I'm done.

Edit: Looks like I forgot to hit the "Post reply" button. Sorry for the delay and the duplicate info.

 

[Lkwdmntr]

Thanks guys. I'm not sure what happened. I must have messed around with something to fix it. Wish I knew what it was. I am having trouble with field validation. I'm used to using code to make up rules with an after-insert event, but I thought I could use validation rules with validation text to warn the person entering data when something was wrong or missing. I put in some validation rules on the fields that have asterisks and are required. I tested it and nothing happened. It did nothing, it didn't even save the record. Any help would be appreciated.

 

[theDBguy]

Thanks guys. I'm not sure what happened. I must have messed around with something to fix it. Wish I knew what it was. I am having trouble with field validation. I'm used to using code to make up rules with an after-insert event, but I thought I could use validation rules with validation text to warn the person entering data when something was wrong or missing. I put in some validation rules on the fields that have asterisks and are required. I tested it and nothing happened. It did nothing, it didn't even save the record. Any help would be appreciated.

I prefer doing data entry validation using the Form's BeforeUpdate event.

 

[smtazulislam]

For Validation Date, You can use it.

<=Now()

User can't enter future a date.

>=Now()

User can't enter Past a date.

I put in some validation rules on the fields that have asterisks and are required. I tested it and nothing happened.

I using for validation Field this Function

Private Function ValidateRecord() As Boolean
Dim strVR As String
    strVR = ""   
    'Validate the form
    If IsNothing(Me![EmployeeName]) Then strVR = "You must enter Employee Name" & vbCrLf
    If IsNothing(Me![cboStatus]) Then strVR= strVR & "You must select a Employment Status" & vbCrLf
    If Not IsDate(Me![EmploymentDate]) Then strVR = strVR & "You must enter the Date Employment Started" & vbCrLf
    
    If Len(strVR ) <> 0 Then
        MsgBox "The following entry errors have found" & vbCrLf & strVR , vbCritical + vbYesNo, "Invalid Entry"
        ValidateRecord = False
    Else
        ValidateRecord = True
    End If
HandleExit:
    Exit Function
End Function

Then its called in your SAVE or UPDATE button

Private Sub cmdSave_Click()

    If (IsNull(ValidateRecord)) Then
       Form.SetFocus
    End If
    If ValidateRecord <> 0 Then
       MsgBox "The entered data has been saved successfully" & vbCrLf & _
        "" & vbCrLf & _
        "You can choose others tab", vbInformation + vbOKOnly, "Saved Successful"
        DoCmd.GoToRecord , "", acNewRec
        'DoCmd.RunCommand acCmdSaveRecord
    End If
End Sub

 

[Lkwdmntr]

This information is great and much appreciated, but I was wanting to know why the validation rules and validation text weren't working and if one of the fields were empty, it didn't even save the record. Why would that happen?

 

[theDBguy]

This information is great and much appreciated, but I was wanting to know why the validation rules and validation text weren't working and if one of the fields were empty, it didn't even save the record. Why would that happen?

Do you have another sample db showing this problem?

 

[Pat Hartman]

@smtazulislam Validation code belongs in the FORM's BeforeUpdate event or under some conditions in the control's BeforeUpdate event. If your user doesn't use your save button, the bad data will be saved. Remember, Access also saves records when it thinks it should whether you issued a save command or not.

The form's BeforeUpdate event is the LAST event to run before a record gets saved. Think of it as the flapper at the bottom of the funnel. If the flapper is closed, the record does NOT get saved. If the flapper is open, the record gets saved. It's as simple as that. It doesn't matter what caused the record save, your validation code runs. Period. When you use other events, you need code in multiple events and you can still leave cracks. So why oh why would you not want to use the event that the Access designers designed specifically to handle validation?

It's hard for people who have never used a lower level language where you had to do everything yourself to recognize that Access is running a lot of its own code to process a form. The form itself is a class. The class code takes care of rendering the form and filling it with data and transferring the data to the table to be saved, etc. The events associated with controls and the form are hooks in the class module code so they can drop into your code to do something custom at specific points. The events are NOT RANDOM. They all have meaning and are intended for specific purposes. You will never have full control over a form no matter how much code you write if you don't put your code in the events designed to handle it. The form's BeforeUpdate event is the most important event in the entire collection of events. If you understand this ONE event, you can prevent saving invalid records.

You might want to watch this video I made with Uncle. There is a second edition coming soon where we talk about how validating using the control's events can still allow bad data to be saved which is why I always use the Form's BeforeUpdate event. It CANNOT be bypassed unless you pull the plug and in that case, the record isn't going to be saved anyway.

Bad Data is Bad for Business - Pat & Tony - Video 1

Bad Data is Bad for Business - Pat & Tony - Video 1

screencast-o-matic.com