#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 misunderstanding 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?

[alekseiaiteno]

Mihail, NeoPa, I'm back to try what you suggest as you definitely have more savvy that I do.
Not quite sure what "this will force the user to fill all required fields. How you implement the ESCAPE option" and "that will automatically dirty the buffer on entry to the form" mean :) but let's leave this aside.

I have added the code you suggested above

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

and what I get is a message box with the correct CONTAINER value, but then it goes to the frmExpense and the [Container] field has #Name? error in it.

[TheSmileyCoder]

About the ESCAPE option
Imagine the following scenario, you have a form for say students, and another form for adding Parents of students. Imagine that the form for adding parents is not a subform, but a form that you open from your students form, and during that process INSTEAD of setting the default value of the student ID field to match the student form, you set the value of the field. The form is now DIRTY (meaning a unsaved New/Edit is in progress). If the user presses ESC key the record will be undone, and the value of the student ID field will return to null. If you have not handled that case, the user can now enter a parent, that will NOT be tied to a student ID, which I hope you can see and understand why that would be bad. However, if you use the DefaultValue approach the above scenario cannot happen, because even if the user undo, and then create a record the student ID field will be properly filled out.

I created a test db, and tried to recreate your issue. Only when it became apparant that this particular error only occurs for TEXT fields. (note the different between a textbox which can show many differnt types of data, and TEXT which is a datatype in access.)

The problem is that say your textbox txtContainer bound to the field container, contains the string "test".

Now the code runs fine, and sets the defaultvalue of the textbox on frmExpense to test. However when the form loads, access thinks that Test is a function, or variable, and then complains (The #Name?) that it can't find anything by that name.

The solution is to tell access that its dealing with a string. The following code accomplishes that by adding single quotes around the value of frmExport.txtContainer when adding it to the defaultvalue field in frmExpense.

Me.txtContainer.DefaultValue = "'" & Forms!frmExport.txtContainer & "'"

Once you have tested it and you find it to work, you can off course remove the msgbox line, it was only ever there to help debug.

[NeoPa]

AlexeiAiteno:
and what I get is a message box with the correct CONTAINER value, but then it goes to the frmExpense and the [Container] field has #Name? error in it.

Can you post exactly what it said Alexei. It may also help to indicate at this point, what the type of the field CONTAINER is in your table. Numeric, textual and date type fields all behave differently in particular circumstances.

[alekseiaiteno]

IT WORKS!
the code you advised above

Me.txtContainer.DefaultValue = "'" & Forms!frmExport.txtContainer & "'"

seems to do the requested operation.

As for the Msg box it said previously: Default value is set to:AAAA1234567
With the new code it adds single quote marks around the AAAA1234567

CONTAINER is a text field (what I now understand is a string in Access terms).
I still have a lot to learn and your help and comments are very appreciated!

[TheSmileyCoder]

Good to hear that its finally working. Keep in mind for your next question that we can only work with the information we get. The more accurately you describe your situation, and for example the EXACT result from a msgbox will help us to not only answer faster, but more precisely.

A text field and a string is not exactly the same. A text field is bound to a table, and once written to, it has a physical location on a diskdrive. A text field can only contain as many charecters as you have set its size to (maximum 255), whereas a string can contain alot more, I believe its limited to 2.147.000 or so, but it may be larger. A string is contained in memory and as such is lost when it goes out of program scope.

[NeoPa]

The .DefaultValue property can take references as well as literal values. Therefore, it is necessary to differentiate between a literal text string and a text string which is the name of an item (which would be a reference to that item). When entering such a value in Design View, Access automatically adds quotes for you. When doing it by VBA code, you need to add these yourself for string values, and add hashes (#) for date values. I hope this makes what is happening, and why, clearer for you.