recordset issue

  • Thread starter Thread starter Joseph Atie
  • Start date Start date
J

Joseph Atie

I have a piece of code that checks a table to see if a record already exists
before it allows a form to pass its data on to the next form in the chain

pretty simple stuff. It works on several other forms, but for some reason
one form keeps throwing errors at me. Im sure its pretty something small but
i cant seem to find what is wrong with the code

HELP PLEASE

Private Sub Command7_Click()
'declaration
Dim fname As String
Dim sname As String
Dim rs As Recordset
Dim rstr As String
Dim answer As Boolean

'initilisation
fname = text1.Value
sname = eType.Value
rstr = "SELECT Users.[First Name], Users.Surname FROM Users WHERE
Users.[First Name]=" & fname & " AND Users.Surname= " & sname & ";"
Set rs = CurrentDb.OpenRecordset(rstr)

'begin data checking
rs.MoveLast
If rs.RecordCount < 1 Then
DoCmd.OpenForm "user_add_3", acNormal, , , acFormAdd, acWindowNormal
DoCmd.Close acForm, "user_add_2", acSaveNo
Forms!user_add_3.Label14.Value = fname
Else
answer = MsgBox("This User is already in the system, Please try again",
vbYesNo, "Existing User")
If answer = True Then
DoCmd.OpenForm "user_add_1", acNormal, , , acFormAdd, acWindowNormal
DoCmd.Close acForm, "user_add_2", acSaveNo
Forms!user_add_3.Label14.Value = fname
Else
DoCmd.OpenForm "main_menu", acNormal, , , acFormAdd, acWindowNormal
DoCmd.Close acForm, "user_add_2", acSaveNo
End If
End If
End Sub
 
HI Joseph Atie,

I suppose first name and surname are strings so that in your sql you must
put them between quotation marks so that your sql should look in this way:

rstr = "SELECT Users.[First Name], Users.Surname FROM Users WHERE
Users.[First Name]=""" & fname & """ AND Users.Surname= """ & sname & """"

HTH Paolo
 
That is not what i want to do, your suggestion would run the following query

SELECT Users.[First Name], Users.Surname FROM Users WHERE Users.[First
Name]= & fname & AND Users.Surname= & sname &

what i want to do is run a query using the paramaters fname & sname, which
as you can see from my code and taken from the fields on the form that runs
the code.

access keeps giving me the error:

too few parameters. expected 2.

Paolo said:
HI Joseph Atie,

I suppose first name and surname are strings so that in your sql you must
put them between quotation marks so that your sql should look in this way:

rstr = "SELECT Users.[First Name], Users.Surname FROM Users WHERE
Users.[First Name]=""" & fname & """ AND Users.Surname= """ & sname & """"

HTH Paolo

Joseph Atie said:
I have a piece of code that checks a table to see if a record already exists
before it allows a form to pass its data on to the next form in the chain

pretty simple stuff. It works on several other forms, but for some reason
one form keeps throwing errors at me. Im sure its pretty something small but
i cant seem to find what is wrong with the code

HELP PLEASE

Private Sub Command7_Click()
'declaration
Dim fname As String
Dim sname As String
Dim rs As Recordset
Dim rstr As String
Dim answer As Boolean

'initilisation
fname = text1.Value
sname = eType.Value
rstr = "SELECT Users.[First Name], Users.Surname FROM Users WHERE
Users.[First Name]=" & fname & " AND Users.Surname= " & sname & ";"
Set rs = CurrentDb.OpenRecordset(rstr)

'begin data checking
rs.MoveLast
If rs.RecordCount < 1 Then
DoCmd.OpenForm "user_add_3", acNormal, , , acFormAdd, acWindowNormal
DoCmd.Close acForm, "user_add_2", acSaveNo
Forms!user_add_3.Label14.Value = fname
Else
answer = MsgBox("This User is already in the system, Please try again",
vbYesNo, "Existing User")
If answer = True Then
DoCmd.OpenForm "user_add_1", acNormal, , , acFormAdd, acWindowNormal
DoCmd.Close acForm, "user_add_2", acSaveNo
Forms!user_add_3.Label14.Value = fname
Else
DoCmd.OpenForm "main_menu", acNormal, , , acFormAdd, acWindowNormal
DoCmd.Close acForm, "user_add_2", acSaveNo
End If
End If
End Sub
Click to expand...
Click to expand...
 
Well, if fname and sname are text parameters, as I said you must enclose them
between quotation marks. The error raised is due to the fact you don't
enclose them between quotation marks. If you display the sql string in a
msgbox with this statement

msgbox rstr

you'll see, using John Doe as an example name
SELECT Users.[First Name], Users.Surname FROM Users WHERE
Users.[First Name]="John" AND Users.Surname= "Doe"

Cheers Paolo

Joseph Atie said:
That is not what i want to do, your suggestion would run the following query

SELECT Users.[First Name], Users.Surname FROM Users WHERE Users.[First
Name]= & fname & AND Users.Surname= & sname &

what i want to do is run a query using the paramaters fname & sname, which
as you can see from my code and taken from the fields on the form that runs
the code.

access keeps giving me the error:

too few parameters. expected 2.

Paolo said:
HI Joseph Atie,

I suppose first name and surname are strings so that in your sql you must
put them between quotation marks so that your sql should look in this way:

rstr = "SELECT Users.[First Name], Users.Surname FROM Users WHERE
Users.[First Name]=""" & fname & """ AND Users.Surname= """ & sname & """"

HTH Paolo

Joseph Atie said:
I have a piece of code that checks a table to see if a record already exists
before it allows a form to pass its data on to the next form in the chain

pretty simple stuff. It works on several other forms, but for some reason
one form keeps throwing errors at me. Im sure its pretty something small but
i cant seem to find what is wrong with the code

HELP PLEASE

Private Sub Command7_Click()
'declaration
Dim fname As String
Dim sname As String
Dim rs As Recordset
Dim rstr As String
Dim answer As Boolean

'initilisation
fname = text1.Value
sname = eType.Value
rstr = "SELECT Users.[First Name], Users.Surname FROM Users WHERE
Users.[First Name]=" & fname & " AND Users.Surname= " & sname & ";"
Set rs = CurrentDb.OpenRecordset(rstr)

'begin data checking
rs.MoveLast
If rs.RecordCount < 1 Then
DoCmd.OpenForm "user_add_3", acNormal, , , acFormAdd, acWindowNormal
DoCmd.Close acForm, "user_add_2", acSaveNo
Forms!user_add_3.Label14.Value = fname
Else
answer = MsgBox("This User is already in the system, Please try again",
vbYesNo, "Existing User")
If answer = True Then
DoCmd.OpenForm "user_add_1", acNormal, , , acFormAdd, acWindowNormal
DoCmd.Close acForm, "user_add_2", acSaveNo
Forms!user_add_3.Label14.Value = fname
Else
DoCmd.OpenForm "main_menu", acNormal, , , acFormAdd, acWindowNormal
DoCmd.Close acForm, "user_add_2", acSaveNo
End If
End If
End Sub
Click to expand...
Click to expand...
Click to expand...
 
but they are not text parameters they are variables, string variables that
are collected from a form

if i put quotation marks arround the variable the query would look for &
fname & instead of John

correct?

if i was to run your code through a msgbox i get this

SELECT Users.[First Name], Users.Surname FROM Users WHERE Users.[First
Name]= & fname & AND Users.Surname= & sname &

if how ever i run my code that i originally posted through msgbox I get

SELECT Users.[First Name], Users.Surname FROM Users WHERE Users.[First
Name]= John AND Users.Surname= Doe

As stated above the error i am getting is

too few parameters. expected 2. but the sql string has 2 parameters???

Paolo said:
Well, if fname and sname are text parameters, as I said you must enclose them
between quotation marks. The error raised is due to the fact you don't
enclose them between quotation marks. If you display the sql string in a
msgbox with this statement

msgbox rstr

you'll see, using John Doe as an example name
SELECT Users.[First Name], Users.Surname FROM Users WHERE
Users.[First Name]="John" AND Users.Surname= "Doe"

Cheers Paolo

Joseph Atie said:
That is not what i want to do, your suggestion would run the following query

SELECT Users.[First Name], Users.Surname FROM Users WHERE Users.[First
Name]= & fname & AND Users.Surname= & sname &

what i want to do is run a query using the paramaters fname & sname, which
as you can see from my code and taken from the fields on the form that runs
the code.

access keeps giving me the error:

too few parameters. expected 2.

Paolo said:
HI Joseph Atie,

I suppose first name and surname are strings so that in your sql you must
put them between quotation marks so that your sql should look in this way:

rstr = "SELECT Users.[First Name], Users.Surname FROM Users WHERE
Users.[First Name]=""" & fname & """ AND Users.Surname= """ & sname & """"

HTH Paolo

:

I have a piece of code that checks a table to see if a record already exists
before it allows a form to pass its data on to the next form in the chain

pretty simple stuff. It works on several other forms, but for some reason
one form keeps throwing errors at me. Im sure its pretty something small but
i cant seem to find what is wrong with the code

HELP PLEASE

Private Sub Command7_Click()
'declaration
Dim fname As String
Dim sname As String
Dim rs As Recordset
Dim rstr As String
Dim answer As Boolean

'initilisation
fname = text1.Value
sname = eType.Value
rstr = "SELECT Users.[First Name], Users.Surname FROM Users WHERE
Users.[First Name]=" & fname & " AND Users.Surname= " & sname & ";"
Set rs = CurrentDb.OpenRecordset(rstr)

'begin data checking
rs.MoveLast
If rs.RecordCount < 1 Then
DoCmd.OpenForm "user_add_3", acNormal, , , acFormAdd, acWindowNormal
DoCmd.Close acForm, "user_add_2", acSaveNo
Forms!user_add_3.Label14.Value = fname
Else
answer = MsgBox("This User is already in the system, Please try again",
vbYesNo, "Existing User")
If answer = True Then
DoCmd.OpenForm "user_add_1", acNormal, , , acFormAdd, acWindowNormal
DoCmd.Close acForm, "user_add_2", acSaveNo
Forms!user_add_3.Label14.Value = fname
Else
DoCmd.OpenForm "main_menu", acNormal, , , acFormAdd, acWindowNormal
DoCmd.Close acForm, "user_add_2", acSaveNo
End If
End If
End Sub
Click to expand...
Click to expand...
Click to expand...
 
but they are not text parameters they are variables, string variables that
are collected from a form

if i put quotation marks arround the variable the query would look for &
fname & instead of John

correct?
Click to expand...

Incorrect. Paolo suggested:
rstr = "SELECT Users.[First Name], Users.Surname FROM Users WHERE
Users.[First Name]=""" & fname & """ AND Users.Surname= """ & sname & """"
Click to expand...
Click to expand...
Click to expand...

This has THREE quotemarks before the variables fname and sname, and FOUR
afterward. Two consecutive doublequotes embedded in a string delimited by
doublequotes will insert one literal doublequote character into the resulting
string. Paolo's suggestion will create a string

Users.[First Name]="John" AND Users.Surname= "Doe"

Did you actually try the posted code?
 
Back
Top