By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,790 Members | 1,409 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,790 IT Pros & Developers. It's quick & easy.

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

P: 8
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub TTLex_DblClick(Cancel As Integer)
  2.  
  3. Dim stDocName As String
  4. Dim stLinkCriteria As String
  5.  
  6. stDocName = "frmExpense"
  7. stLinkCriteria = "[CONTAINER]='" & Me![CONTAINER] & "'"
  8.  
  9. DoCmd.OpenForm stDocName, , , stLinkCriteria, , acDialog
  10.  
  11. Exit_cmdDetails_Click:
  12. Exit Sub
  13.  
  14. Err_cmdDetails_Click:
  15. MsgBox Err.Description
  16. Resume Exit_cmdDetails_Click
  17.  
  18. End Sub
  19.  
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.  
  3. If Me.NewRecord Then
  4. [CONTAINER].DefaultValue = Forms![frmExport]![CONTAINER]
  5. End If
  6.  
  7. End Sub
  8.  
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.
May 8 '12 #1

✓ answered by 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.

Expand|Select|Wrap|Line Numbers
  1. 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.

Share this Question
Share on Google+
20 Replies


TheSmileyCoder
Expert Mod 100+
P: 2,321
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.
May 8 '12 #2

P: 8
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?
May 8 '12 #3

NeoPa
Expert Mod 15k+
P: 31,418
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].
May 9 '12 #4

NeoPa
Expert Mod 15k+
P: 31,418
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 :
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Open(Cancel As Integer)
  2.     Me.txtContainer.DefaultValue = Forms!frmExport.txtContainer
  3. End Sub
or even :
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Open(Cancel As Integer)
  2.     Me.txtContainer.DefaultValue = Split(Me.Filter, "'")(1)
  3. End Sub
There is always the danger of a mis-reference if the control is named the same as the field.
May 9 '12 #5

100+
P: 759
Your code:
Expand|Select|Wrap|Line Numbers
  1. If Me.NewRecord Then
  2.     [CONTAINER].DefaultValue = Forms![frmExport]![CONTAINER]
  3. 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.
May 9 '12 #6

P: 8
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Open(Cancel As Integer)
  2.  
  3.     If Me.NewRecord Then
  4.     Me.txtCONTAINER.DefaultValue = Forms!frmExport.txtCONTAINER
  5.     End If
  6.  
  7. End Sub
  8.  
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.
May 9 '12 #7

TheSmileyCoder
Expert Mod 100+
P: 2,321
I suggest you try to read out the defaultvalue after its been set, to see what happens.
A simple messagebox should provide more information:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Open(Cancel As Integer)
  2.   If Me.NewRecord Then
  3.     Me.txtCONTAINER.DefaultValue = Forms!frmExport.txtCONTAINER
  4.     Msgbox "Default Value set to:" & Me.txtContainer.DefaultValue
  5.   End If
  6. End Sub
  7.  
May 9 '12 #8

P: 8
Unfortunately nothing new - no #Name? error, but also blank in the required field.
May 9 '12 #9

TheSmileyCoder
Expert Mod 100+
P: 2,321
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?
May 9 '12 #10

P: 8
That is the thing - the messagebox did not return anything - no message displayed.
May 9 '12 #11

NeoPa
Expert Mod 15k+
P: 31,418
alekseiaiteno:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Open(Cancel As Integer)
  2.  
  3.     If Me.NewRecord Then
  4.     Me.txtCONTAINER.DefaultValue = Forms!frmExport.txtCONTAINER
  5.     End If
  6.  
  7. 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 :
Expand|Select|Wrap|Line Numbers
  1. Msgbox "Default Value set to:" & Me.txtContainer.DefaultValue
But not in the If block it was suggested in ;-)
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Open(Cancel As Integer)
  2.     Me.txtCONTAINER.DefaultValue = Forms!frmExport.txtCONTAINER
  3.     Call MsgBox("Default Value set to:" & Me.txtCONTAINER.DefaultValue)
  4. End Sub
@Mihail
I'm afraid you do misunderstand something in this case, as what you say is not correct.
May 9 '12 #12

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

I have updated the code in the frmExpense:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2.  
  3.     If Me.NewRecord Then
  4.     Me.txtCONTAINER = Forms!frmExport.txtCONTAINER
  5.     End If
  6.  
  7. End Sub
  8.  
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.
May 9 '12 #13

100+
P: 759
Indeed.
But this will force the user to fill all required fields. How you implement the ESCAPE option ?
May 9 '12 #14

NeoPa
Expert Mod 15k+
P: 31,418
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?
May 9 '12 #15

P: 8
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
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Open(Cancel As Integer)
  2.     Me.txtCONTAINER.DefaultValue = Forms!frmExport.txtCONTAINER
  3.     Call MsgBox("Default Value set to:" & Me.txtCONTAINER.DefaultValue)
  4. 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.
May 10 '12 #16

TheSmileyCoder
Expert Mod 100+
P: 2,321
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.

Expand|Select|Wrap|Line Numbers
  1. 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.
May 10 '12 #17

NeoPa
Expert Mod 15k+
P: 31,418
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.
May 10 '12 #18

P: 8
IT WORKS!
the code you advised above
Expand|Select|Wrap|Line Numbers
  1. 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!
May 11 '12 #19

TheSmileyCoder
Expert Mod 100+
P: 2,321
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.
May 11 '12 #20

NeoPa
Expert Mod 15k+
P: 31,418
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.
May 11 '12 #21

Post your reply

Sign in to post your reply or Sign up for a free account.