i have been using adodc for searching for a client's details using the client number, but it would give me inexpected errors and bomb out of my programme. i have then resorted to searching using sql. the following code gives me this error:::

no value given for one or more required parameters

this error is pointed at line 7 in the following code.

the code thats producing this error is:::

Private Sub cmdcnsearch_Click()
sql = "Select companyname,companysize,country,city from cooperate where CSTR(accnumber)='" & txtcnsearch.Text & "'"
Set conn = New Connection
Set r = New Recordset
conn.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Subscriptions Project\subscriptions.mdb"
With r
.Open sql, conn, adOpenKeyset, adLockOptimistic, adCmdText
If .EOF Then
MsgBox "Record not found", vbExclamation
txtcnsearch.SelStart = 0
txtcnsearch.SelLength = Len(txtcnsearch.Text)
txtcnsearch.SetFocus
Exit Sub
'to populate the field
Else
   txtccname = r!CompanyName
   txtcsize.Text = r!companysize
   txtcountry.Text = r!country
   txtcity.Text = r!city
    r.Close
    
End If
End With
End Sub

can you help me please!!!

since im the one who posted this thread let me just add on something.
i have tried a different method for searching and its now telling me another different error. the error is::
object variable or with block variable not set

the code thats giving this error is:::::

Dim uid As Variant
uid = txtcnamesearch
 If uid = "" Then Exit Sub
r.Find "membernumber=" & uid
If r.EOF = True Then
    mymessage = MsgBox("Account does not exist, Please create account first", vbCritical, "Account Not Found")
    txtcnamesearch = ""
    txtcnamesearch.SetFocus
    r.MoveFirst
Else
    txtccname = r("companyname")
    txtcsize = r("companysize")
    txtccountry = r("country")     
End If

since im the one who posted this thread let me just add on something.
i have tried a different method for searching and its now telling me another different error. the error is::
object variable or with block variable not set

the code thats giving this error is:::::

Dim uid As Variant
uid = txtcnamesearch
 If uid = "" Then Exit Sub
r.Find "membernumber=" & uid
If r.EOF = True Then
    mymessage = MsgBox("Account does not exist, Please create account first", vbCritical, "Account Not Found")
    txtcnamesearch = ""
    txtcnamesearch.SetFocus
    r.MoveFirst
Else
    txtccname = r("companyname")
    txtcsize = r("companysize")
    txtccountry = r("country")     
End If

sorry but i am using VB 6 and cant access the file u sent me...think its PHP and cant link it with VB...please i ned to search using VB thanx for your help

Try placing brackets around CSTR(accnumber) so it looks like this:

[CSTR(accnumber)]

I'm assuming it's the name of a field.

i have been using adodc for searching for a client's details using the client number, but it would give me inexpected errors and bomb out of my programme. i have then resorted to searching using sql. the following code gives me this error:::

no value given for one or more required parameters

this error is pointed at line 7 in the following code.

the code thats producing this error is:::

Private Sub cmdcnsearch_Click()
sql = "Select companyname,companysize,country,city from cooperate where CSTR(accnumber)='" & txtcnsearch.Text & "'"
Set conn = New Connection
Set r = New Recordset
conn.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Subscriptions Project\subscriptions.mdb"
With r
.Open sql, conn, adOpenKeyset, adLockOptimistic, adCmdText
If .EOF Then
MsgBox "Record not found", vbExclamation
txtcnsearch.SelStart = 0
txtcnsearch.SelLength = Len(txtcnsearch.Text)
txtcnsearch.SetFocus
Exit Sub
'to populate the field
Else
   txtccname = r!CompanyName
   txtcsize.Text = r!companysize
   txtcountry.Text = r!country
   txtcity.Text = r!city
    r.Close
    
End If
End With
End Sub

can you help me please!!!

Follow my comment lines within your code...

Private Sub cmdcnsearch_Click()
'dim sql as string

sql = "Select companyname,companysize,country,city from cooperate where CSTR(accnumber)='" & txtcnsearch.Text & "'"
'sql = "Select companyname,companysize,country,city from 'cooperate where accnumber=" & "'" & txtcnsearch.Text & "'"
'remove the cstr for the value you are looking for is in a field, which 'is the value that will be returned.

Set conn = New Connection

Set r = New Recordset
conn.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Subscriptions Project\subscriptions.mdb"
With r
.Open sql, conn, adOpenKeyset, adLockOptimistic, adCmdText
If .EOF Then
MsgBox "Record not found", vbExclamation
txtcnsearch.SelStart = 0
txtcnsearch.SelLength = Len(txtcnsearch.Text)
txtcnsearch.SetFocus
Exit Sub
'to populate the field
Else
   txtccname = r!CompanyName
   txtcsize.Text = r!companysize
   txtcountry.Text = r!country
   txtcity.Text = r!city
    r.Close
    
End If
End With
End Sub

Hope this solves your problem. With your second error, is your connection referenced?

when i make the changes it gives me a whole new error...its now saying

syntax error in querry. Incomplete query close

and its pointing to the following section of my code....

With r
.Open sql, conn, adOpenKeyset, adLockOptimistic, adCmdText

so what could be causing that.

wheh i make the chaanges it gives me a different error.....now it is saying...

syntax error in querry.Incomplete querry close!

and this error is highlighting the following

wheh i make the chaanges it gives me a different error.....now it is saying...

syntax error in querry.Incomplete querry close!

and this error is highlighting the following

Copy and paste the code into your app.

Option Explicit

Private WithEvents conn As ADODB.Connection
Private WithEvents rs As ADODB.Recordset

Private Sub cmdcnsearch_Click()

Dim sql As String
sql = "SELECT companyname, companysize, country, city FROM cooperate WHERE accnumber = " & "'" & txtcnsearch.Text & "'"

Set conn = New ADODB.Connection
conn.CursorLocation = adUseClient

conn.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Subscriptions Project\subscriptions.mdb"

Set rs = New ADODB.Recordset

rs.Open sql, conn, adOpenStatic, adLockOptimistic

If rs.EOF = True Then
    MsgBox "Record not found", vbExclamation
    txtcnsearch.SelStart = 0
    txtcnsearch.SelLength = Len(txtcnsearch.Text)
    txtcnsearch.SetFocus
    
    Exit Sub
        Else
    txtccname = rs!CompanyName
    txtcsize.Text = rs!companysize
    txtcountry.Text = rs!country
    txtcity.Text = rs!city
    rs.Close
End If
End Sub
This question has already been answered. Start a new discussion instead.