We help IT Professionals succeed at work.

MS Access -  Error 2447: There is an invalid use of the .(dot) or ! operator or invalid parentheses

WS
WS asked
on
Hi,

Attach is a sample DB. frmFirst is the first to open and then click Edit Record where Source field click that go to Search Source and then click on Add Source there the error is.

In Add Record it is working but in Edit Record is the one where ahead it gives error, any help how to resolve this?

Thank you.
Wsm93--1-.accdb
Comment
Watch Question

Ryan ChongSoftware Team Lead

Commented:
Me.txtAddRec is bound to
=[Forms]![Table1]![txtAdd]

Open in new window

(you can find that in Control Source)
and there's an error returned with

#Name?

Open in new window


you got to resolve that first.
Ryan ChongSoftware Team Lead

Commented:
it seems that you're replicating your forms...

one quick resolution is to change:
If Me.txtAddRec = "Cap" Then

Open in new window


to:
If Me.txtAddRec1 = "Cap" Then

Open in new window

WSStudent

Author

Commented:
how to resolve this #Name? things?

I am not able to solve this, is there a way to solve this?
WSStudent

Author

Commented:
it seems that you're replicating your forms...

one quick resolution is to change:
If Me.txtAddRec = "Cap" Then

Select all
 
Open in new window

to:
If Me.txtAddRec1 = "Cap" Then


both are different textboxes, if Source Search form open from add record it will pass value back to add record form and if it open from edit record form it will pass value back to edit record, that's the logic behind this.
Ryan ChongSoftware Team Lead

Commented:
how to resolve this #Name? things?

I am not able to solve this, is there a way to solve this?
you getting the error because you're referring to [Forms]![Table1]![txtAdd] while the fact that [Forms]![Table1] is yet not opened
WSStudent

Author

Commented:
is there a way to code that if the form is not open it can go to else condition or something?

i try the code that if Me.txtAddRec = "#Name?" then
do nothing
...
but that's also not working.
Software Team Lead
Commented:
perhaps a custom function can handle that.

create a Module and add function below:

Function handleAddRec(FormName As String, TextBox As String, d As Variant)
    If CurrentProject.AllForms(FormName).IsLoaded = True Then
        handleAddRec = Forms(FormName).Controls(TextBox).Value
    Else
        handleAddRec = d
    End If
End Function

Open in new window


and then for textbox: txtAddRec, set it's Control Source to:

=handleAddRec("Table1","txtAdd","yourDefaultValue")

Open in new window

WSStudent

Author

Commented:
Can you try implementing this in the sample db i have provided? because i am getting a lot of error and my db starts crashing with this function.
Ryan ChongSoftware Team Lead
Commented:
working fine with me.

pls use attached.
Wsm93--1_b.accdb
WSStudent

Author

Commented:
it is working for Add Record but it is not working for Edit Record in the sample DB you provided.i try the same in edit record but it's not working,any idea?
Ryan ChongSoftware Team Lead

Commented:
how you defined as "not working"?

I tried the Edit Record button and at least it get rid of that #Name? error.
WSStudent

Author

Commented:
#Name? error is not there but it is not passing value also, instead #Name? , yourDefaultValue is there, this is what i mean not working like the edit record is still not passing value back.
Ryan ChongSoftware Team Lead

Commented:
just do a message out and see what you getting here?
...
MsgBox Me.txtAddRec

If Me.txtAddRec = "Cap" Then
...

Open in new window

WSStudent

Author

Commented:
Ryan, it's not about txtAddRec as that is working fine , it's about txtAddRec1 which is not working.
Ryan ChongSoftware Team Lead
Commented:
it's about txtAddRec1 which is not working
do you mean you get #Name? error for txtAddRec1?

if yes, use the similar method:

for txtAddRec1, set it's Control Source to:

=handleAddRec("Table1","txtAdd1","yourDefaultValue")
WSStudent

Author

Commented:
Yes exactly.i use the similar method as you can see in image 3, but Add Source button is not working as  by not working i mean it;s not passing value back to edit form, any idea why? neither now it's not showing any error also.

It would be helpful if you can make this work in the sample DB attach, then i will implement that in my operational database.
IMAGE1.png
IMAGE2.png
IMAGE3.png
Ryan ChongSoftware Team Lead

Commented:
check your codes if below is correct...

Private Sub Command167_Click()

If Me.txtAddRec = "Cap" Then
    Forms!Table1!Source = Me.txtCap
    DoCmd.Close
ElseIf Me.txtAddRec1 = "CapEdit" Then
    Forms!Table1_1!Source = Me.txtCap1
    DoCmd.Close
End If

End Sub

Open in new window


>> ElseIf Me.txtAddRec1 = "CapEdit" Then
but value of txtAddRec1 is Cap1

hence nothing will be executed.
WSStudent

Author

Commented:
Thank you Ryan, you were right i was writing the wrong text "CapEdit", it started working in my database, thank you again.