#Name? error in Access Form when trying to add new record

[alekseiaiteno]

Hello all!

Have researched the issue all day to to avail, therefore turning to your help for the solution.

I have a Form A which is accessed via another Form B only (i.e. not possible for user to go directly to a Form A and add values).
So when the user clicks on a button in the Form B he is taken to the Form A.
In case Form A has a record (CONTAINER) correspondent to key record in Form B, then the correct record is filtered based on the code:

Private Sub TTLex_DblClick(Cancel As Integer)

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmExpense"
stLinkCriteria = "[CONTAINER]='" & Me![CONTAINER] & "'"

DoCmd.OpenForm stDocName, , , stLinkCriteria, , acDialog

Exit_cmdDetails_Click:
Exit Sub

Err_cmdDetails_Click:
MsgBox Err.Description
Resume Exit_cmdDetails_Click

End Sub

However if there is no correspondent record in Form A there is a code on the Current state to create a new record showing the correspondent record name from Form B:

Private Sub Form_Current()

If Me.NewRecord Then
[CONTAINER].DefaultValue = Forms![frmExport]![CONTAINER]
End If

End Sub

The first part where there are correspondent records works fine. The trouble starts when no correspondent record is found - Form A shows #Name? instead of a record name from Form B.

I have checked Control Source, Label names - everything is in order. Don't know where else to look.

Your help will be much appreciated.

[TheSmileyCoder]

First of, its a bad idea to use the same name for both the field of the table and the control on your form. A common approach is to prefix the control name with a tb or txt for textbox.

Start by fixing that, and let us know if it helps.

[alekseiaiteno]

don't know what exactly yu are referring to as I have 2 Forms and the CONTAINER in both refers to a record in the initial table, so I really cannot change the one of these as then it would not work.

Perhaps you could explain more precisely or some more info needed from my side?

[NeoPa]

It looks like the controls on both [Form A] (which appears to be [frmExpense] - It's actually clearer if the name you use in the explanation matches the name in the code) and [Form B] ([frmExport]) for the field [CONTAINER] are also named [CONTAINER]. Access defaults to that approach, which sux large, but it's still good advice to use [txtContainer] as a name for controls if the bound field is named [CONTAINER].

[NeoPa]

Your code doesn't do much wrong to be fair, though I'd suggest putting the DefaultValue code in the Form_Open() event procedure rather than the Form_Current() one.

Something like :

Private Sub Form_Open(Cancel As Integer)
    Me.txtContainer.DefaultValue = Forms!frmExport.txtContainer
End Sub

or even :

Private Sub Form_Open(Cancel As Integer)
    Me.txtContainer.DefaultValue = Split(Me.Filter, "'")(1)
End Sub

There is always the danger of a mis-reference if the control is named the same as the field.

[Mihail]

Your code:

If Me.NewRecord Then
    [CONTAINER].DefaultValue = Forms![frmExport]![CONTAINER]
End If

And yours explanation:
The trouble starts when no correspondent record is found - Form A shows #Name? instead of a record name from Form B.

If I not misunderstand something, Forms![frmExport]![CONTAINER] do not exist in this case. So, Access say to you that he (Access) has no value to working with in order to establish the [CONTAINER].DefaultValue.

[alekseiaiteno]

Hello NeoPa,

thank you for the explanation.
I have now changed the name to txtCONTAINER in both forms and inserted the code you advised to Open state of frmExpense. That yields the same result with the #Name? error.

I also tried to leave bits of my previous code in:

Private Sub Form_Open(Cancel As Integer)

    If Me.NewRecord Then
    Me.txtCONTAINER.DefaultValue = Forms!frmExport.txtCONTAINER
    End If
    
End Sub

and the #Name issue goes away in frmExpense and shows a blank field instead, which is incorrect as I need it to show the correspondent field from frmExport.

Will try to play around, and will appreciate your further advice.

[TheSmileyCoder]

I suggest you try to read out the defaultvalue after its been set, to see what happens.
A simple messagebox should provide more information:

Private Sub Form_Open(Cancel As Integer)
  If Me.NewRecord Then
    Me.txtCONTAINER.DefaultValue = Forms!frmExport.txtCONTAINER
    Msgbox "Default Value set to:" & Me.txtContainer.DefaultValue
  End If
End Sub

[alekseiaiteno]

Unfortunately nothing new - no #Name? error, but also blank in the required field.

[TheSmileyCoder]

The messagebox was never intended as a solution, but as part of a debug procedure. However if you don't inform us of the results then we will rapidly go nowhere. What result did the messagebox produce on your screen?

[alekseiaiteno]

That is the thing - the messagebox did not return anything - no message displayed.

[NeoPa]

alekseiaiteno:

Private Sub Form_Open(Cancel As Integer)

    If Me.NewRecord Then
    Me.txtCONTAINER.DefaultValue = Forms!frmExport.txtCONTAINER
    End If

End Sub

This shows a fundamental misunderstandin g of the issue Aleksei. The DefaultValue property pertains to the form, and all records thereon. It is set when the form opens before you ever get to positioning the pointer to a new record. Think about that for a second.

I must say I agree with Smiley here, in that your reported experiences point to a problem with the [txtCONTAINER] value from the other form. Is [frmExport] even still open at this stage? I expect it would be if it called for this form ([frmExpense]) to be opened. You need to let us know what you see when using Smiley's suggestion :

Msgbox "Default Value set to:" & Me.txtContainer.DefaultValue

But not in the If block it was suggested in ;-)

Private Sub Form_Open(Cancel As Integer)
    Me.txtCONTAINER.DefaultValue = Forms!frmExport.txtCONTAINER
    Call MsgBox("Default Value set to:" & Me.txtCONTAINER.DefaultValue)
End Sub

@Mihail
I'm afraid you do misunderstand something in this case, as what you say is not correct.

[alekseiaiteno]

I think I may have found the workaround in a slightly different way.

I have updated the code in the frmExpense:

Private Sub Form_Load()
    
    If Me.NewRecord Then
    Me.txtCONTAINER = Forms!frmExport.txtCONTAINER
    End If
    
End Sub

Notice I've changed the Form_Open to Form_Load. And the DefaultValue is gone.
This creates a new record and puts the data in the CONTAINER record on frmExpense when the blank field is double clicked.

[Mihail]

Indeed.
But this will force the user to fill all required fields. How you implement the ESCAPE option ?

[NeoPa]

As Mihail says, this is a flawed approach that will automatically dirty the buffer on entry to the form. DefaultValue was absolutely the correct way to go. Don't give up just because understanding the complexities is difficult, otherwise you'll be just as lost and confused next time you have to deal with any similar issue.

What you've seen makes perfect sense. Your code was designed wrongly, so the results were other than you expected. That is a good thing, not a bad one. It tells you to look deeper at the actual logic, and to understand it more fully before coding.

Did you try the code I suggested in post #12? If so, what did you see in the message box?