Error 3601-Please help

[MN]

I have a query below run OK but when I insert into a form then i got an
Runtime-error 3601 Expect 2.

SELECT * FROM dbo_A
WHERE Soundex(lname)=soundex([forms]![FrmMain]![lName]) And
Soundex(Fname)=soundex([forms]![FrmMain]![FName]);
- Soundex function is from Allen Browne website.
Thanks you in advance.

 

[Dirk Goldgar]

I have a query below run OK but when I insert into a form then i got an
Runtime-error 3601 Expect 2.

SELECT * FROM dbo_A
WHERE Soundex(lname)=soundex([forms]![FrmMain]![lName]) And
Soundex(Fname)=soundex([forms]![FrmMain]![FName]);
- Soundex function is from Allen Browne website.
Thanks you in advance.

What do you mean by "insert into a form"? What you describe sounds like
what happens when you use the DAO OpenRecordset method on a SQL string that
has form references (or any other name that needs the Access application to
evaluate it). If you are using code for this, please post your code.

 

[MN]

Yeah-Here is the code in the form:
Dim strSql As String
strSql = "SELECT * " & vbCrLf & _
"FROM dbo_A " & vbCrLf & _
"WHERE Soundex(lname)=soundex([forms]![FrmMain]![lName]) And
Soundex(Fname)=soundex([forms]![FrmMain]![FName]);"
Thanks you for reply.

I have a query below run OK but when I insert into a form then i got an
Runtime-error 3601 Expect 2.

SELECT * FROM dbo_A
WHERE Soundex(lname)=soundex([forms]![FrmMain]![lName]) And
Soundex(Fname)=soundex([forms]![FrmMain]![FName]);
- Soundex function is from Allen Browne website.
Thanks you in advance.

What do you mean by "insert into a form"? What you describe sounds like
what happens when you use the DAO OpenRecordset method on a SQL string that
has form references (or any other name that needs the Access application to
evaluate it). If you are using code for this, please post your code.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Yeah-Here is the code in the form:
Dim strSql As String
strSql = "SELECT * " & vbCrLf & _
"FROM dbo_A " & vbCrLf & _
"WHERE Soundex(lname)=soundex([forms]![FrmMain]![lName]) And
Soundex(Fname)=soundex([forms]![FrmMain]![FName]);"

There has to be more than that, since that code can't in itself raise the
error you posted. I'll bet you have code farther down in that procedure
that opens a recordset on that SQL string; something like this:

Set rs = CurrentDb.OpenRecordset(strSQL)

or (using ADO),

rs.Open strSQL

If my guess is right, your problem can be averted by building the values of
the form controls directly into the SQL string as quoted literals, like
this:

strSql = _
"SELECT * FROM dbo_A " & _
"WHERE Soundex(lname)=soundex(" & _
Chr(34) & Forms!FrmMain!lName & Chr(34) & _
") And Soundex(Fname)=soundex(" & _
Chr(34) & Forms!FrmMain!FName & Chr(34) & _
");"

In fact, it's quite likely that you can embed the soundex-converted versions
of the form controls into the string, instead of the control values
themselves. So this might work even better:

strSql = _
"SELECT * FROM dbo_A " & _
"WHERE Soundex(lname)='" & _
Soundex(Forms!FrmMain!lName) & _
"' And Soundex(Fname)='" &
Soundex(Forms!FrmMain!FName) & _
"');"

 

[MN]

Oh men - you are so smart !
I used second of your post. No error, but now how can I carry over whole 20
records to open another form and let the user select which one is they need
to edit?
Thanks you thanks you ...

Yeah-Here is the code in the form:
Dim strSql As String
strSql = "SELECT * " & vbCrLf & _
"FROM dbo_A " & vbCrLf & _
"WHERE Soundex(lname)=soundex([forms]![FrmMain]![lName]) And
Soundex(Fname)=soundex([forms]![FrmMain]![FName]);"

There has to be more than that, since that code can't in itself raise the
error you posted. I'll bet you have code farther down in that procedure
that opens a recordset on that SQL string; something like this:

Set rs = CurrentDb.OpenRecordset(strSQL)

or (using ADO),

rs.Open strSQL

If my guess is right, your problem can be averted by building the values of
the form controls directly into the SQL string as quoted literals, like
this:

strSql = _
"SELECT * FROM dbo_A " & _
"WHERE Soundex(lname)=soundex(" & _
Chr(34) & Forms!FrmMain!lName & Chr(34) & _
") And Soundex(Fname)=soundex(" & _
Chr(34) & Forms!FrmMain!FName & Chr(34) & _
");"

In fact, it's quite likely that you can embed the soundex-converted versions
of the form controls into the string, instead of the control values
themselves. So this might work even better:

strSql = _
"SELECT * FROM dbo_A " & _
"WHERE Soundex(lname)='" & _
Soundex(Forms!FrmMain!lName) & _
"' And Soundex(Fname)='" &
Soundex(Forms!FrmMain!FName) & _
"');"


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Oh men - you are so smart !
I used second of your post. No error, but now how can I carry over whole
20
records to open another form and let the user select which one is they
need
to edit?

Unfortunately, I'm not smart enough to understand what you mean by that.
Could you explain in more detail?

Thanks you thanks you ...

You're welcome.

 

[MN]

Dirk, I am sorry-While I am hungry up to answer you I forgot it
My purpose is: I open form1 with a data already have is dbo_temp let the
user search by Soundex() firstname and last name between table dbo_temp and
dbo_customer.
If search Soundex() found records
-They want to open another form to looking for the right one (by go to
every single record) . After they found the one they need to edit then they
edit
- Else if it's not they want to open another form to ADD new record for
table customer.
Table dbo_temp and table dbo_Customer have no link between it. After create
a new customer system will assign new customerID for it.
Again Thanks a ton!

 

[Dirk Goldgar]

Dirk, I am sorry-While I am hungry up to answer you I forgot it
My purpose is: I open form1 with a data already have is dbo_temp let the
user search by Soundex() firstname and last name between table dbo_temp
and
dbo_customer.
If search Soundex() found records
-They want to open another form to looking for the right one (by go to
every single record) . After they found the one they need to edit then
they
edit
- Else if it's not they want to open another form to ADD new record for
table customer.
Table dbo_temp and table dbo_Customer have no link between it. After
create
a new customer system will assign new customerID for it.

It's still a little obscure to me, I'm sorry to say, but I might approach it
something like this:

Once you have determined that you have records that may match, use
DoCmd.OpenForm to open your Customer form with a where-condition argument
that is the same as the criterion used in the SQL statement we arrived at
earlier. Something like this:

DoCmd.OpenForm "frmCustomers", _
WhereCondition:="Soundex(lname)='" & _
Soundex(Forms!FrmMain!lName) & _
"' And Soundex(Fname)='" &
Soundex(Forms!FrmMain!FName) & "'"

That is assuming that you want the user to be work directly with the
Customer records. I'm not clear on what you're doing with dbo_Temp, so I
don't know if you want them to be working with a form based on that table
instead.