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

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
LVL 1
WSStudentAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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 ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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

WSStudentAuthor Commented:
how to resolve this #Name? things?

I am not able to solve this, is there a way to solve this?
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

WSStudentAuthor 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 ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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
WSStudentAuthor 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.
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
WSStudentAuthor 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 ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
working fine with me.

pls use attached.
Wsm93--1_b.accdb
WSStudentAuthor 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 ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
how you defined as "not working"?

I tried the Edit Record button and at least it get rid of that #Name? error.
WSStudentAuthor 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 ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
just do a message out and see what you getting here?
...
MsgBox Me.txtAddRec

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

Open in new window

WSStudentAuthor Commented:
Ryan, it's not about txtAddRec as that is working fine , it's about txtAddRec1 which is not working.
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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")
WSStudentAuthor 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 ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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.
WSStudentAuthor Commented:
Thank you Ryan, you were right i was writing the wrong text "CapEdit", it started working in my database, thank you again.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.