Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to change ado record source

Posted on 2002-04-23
17
Medium Priority
?
223 Views
Last Modified: 2008-03-04
I am new to VB and don't know how to handle
the following problem.

I have an access 2000 mdb file and am trying to
create a front end with vb6

I've made the controls on the form as unbound.
There is a txtSearch field on the form. When the
form is loaded, the user has to fill in this
txtSearch field with some Number (71001 etc).

Now my question is how to change the ado Record
Source to select only the records matching the
number entered by the user (in this case 71001)?

For testing, here's the code I am using on the on
click event of a button:

Dim strSql As String
Dim strValue1 As String


strSql = "select * from Expenses WHERE DeptId = txtDeptID"

adoData.RecordSource = strSql
'adoData.Refresh

BindControls  'Function to bind controls

The above code does not give me any error message,
but returns records for all depts.



Thanks in advance.
0
Comment
Question by:sanand
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 3
  • +2
17 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6963568
Assuming that txtSearch is your Department ID and DeptID is a Text field than

strSql = "select * from Expenses WHERE DeptId = '" & txtSearch.Text & "'"

adoData.RecordSource = strSql
adoData.Refresh

Also, please maintain your open questions:
Questions Asked 9
Last 10 Grades Given A A A A B  
Question Grading Record 5 Answers Graded / 5 Answers Received

Anthony
0
 
LVL 18

Expert Comment

by:mdougan
ID: 6963569
This is a common mistake.  You are including the name of your textbox in the select statement rather than the value of the text in the textbox.  If the column DeptID in your database is numeric, then change the sql to this:

strSql = "select * from Expenses WHERE DeptId = " & txtDeptID.Text

If the column DeptID is defined as a Text field in the database, then change it to this

strSql = "select * from Expenses WHERE DeptId = '" & txtDeptID.Text & "'"
0
 
LVL 18

Expert Comment

by:mdougan
ID: 6963570
Anthony, you're too fast on the button ;)
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:sanand
ID: 6963615
Thanks everyone who responded to my question promptly.

But none of these responses resolved the problem.
It still gives me total of 2381 records which should
be only 111. (for the selected department).

Can this be the cause of the problem:
1.  adoData control is bound to a table

I've also created a query in Access mdb file with
a [Enter dept id] as parameter.  But under proeprties
for the adoData control, I cann't find this query
as a record source.  If this can be changed from a
table name to the query name, I think this will
resolve my problem.  I cann't find this query under
recordsource for the ado data control.

Any other idea, please?

TIA
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6963738
Any chance we can see some movement on this open question:

Output query result as Text  truncates data Date: 09/07/2001 09:28AM PST
http://www.experts-exchange.com/jsp/qShow.jsp?ta=msaccess&qid=20180023

Anthony
0
 
LVL 18

Expert Comment

by:mdougan
ID: 6963803
It doesn't matter what you set for the RecordSource of the data control at design time (binding to a table), because you are changing the RecordSource in your code.  However, I noticed that you are not doing a .Refresh, I think that .Refresh was what you had to do in DAO, but in ADO, you might just need to do a .MoveFirst  or, if that doesn't work, try a .Requery, because without something that forces the data control to go back to the database, you'll still have your old recordset there, which sounds like it's your problem.

I'm not sure how to associate a data control with a parameterized query, so, I'd just recommend doing it in the code as you are.  Oh, and always make sure to test with a breakpoint on the line:

strSql = "select * from Expenses WHERE DeptId = " & txtDeptID.Text

Just to verify that txtDeptID does have some value in it.

strSql = "select * from Expenses WHERE DeptId = " & txtDeptID.Text

adoData.RecordSource = strSql
adoData.Refresh
or
adoData.MoveFirst
or
adoData.ReQuery
0
 
LVL 1

Expert Comment

by:alfanhendro
ID: 6964487
Maybe you should try to use ADO recordset object instead of the DataControl. (the recordset is created during the runtime, no control added in the code).

Dim cn as ADODB.Connection
Dim rc as ADODB.Recordset

Private Sub Form_Load()
  Set cn = New Adodb.connection
  set rc = new adodb.recordset
  cn.cursortype = adlockoptimistic
  ...
End sub

Sorry, no time to continue. Can anyone please help to finish this.
0
 

Author Comment

by:sanand
ID: 6966302
This still does not work and gives an type mismatch.

Here is the copy of the code I am using

Dim strSql As Integer
Dim strValue1 As String


strSql = "select * from Expenses WHERE DeptId = " & txtDept

adoData.RecordSource = strSql
adoData.Refresh

BindControls  'Function to bind controls

'Function to Update record numbers for adoData control
UpdateRecordNum

Any other idea, please.

TIA
0
 
LVL 18

Expert Comment

by:mdougan
ID: 6967625
Well, a type mismatch usually means that you are comparing a column that has one type with a value that has another type.  In case of the SQL, since you are not wrapping the txtDept in single quotes, then your database column DeptID HAS TO BE defined as some numeric value (Long Integer probably).  If DeptID is defined as a Text field (as I said in my first comment) then you have to make sure to wrap it in single quotes:

If the column DeptID is defined as a Text field in the database, then change it to this

strSql = "select * from Expenses WHERE DeptId = '" & txtDeptID.Text & "'"

Make sure that your adodc has an Active connection (so, make sure you've set something for the ADODC Connection property, then .Refresh should work (perhaps with a .MoveFirst followin)
0
 

Author Comment

by:sanand
ID: 7165752
Dear mdougan:

I am in a similar propbelm in another case where
I'm using this code:

Public Sub ConnectTDNum()

Dim strSql As String

strSql = "SELECT * FROM tblTD WHERE fldTdNum = ' " & Me.txtSearch.Text & "'"
'strSql = "select * from Expenses WHERE DeptId = '" & txtDeptID.Text & "'"
adoTD.RecordSource = strSql
adoTD.Refresh

End Sub

Here, I am getting 2 error messages:
First one: adoTD:
           Syntax error in From clause.

Second: Runtime Error:
        Method 'Refresh' of object IADODC fails


In your last suggestion, you advised to me make adodc an active connection.  Would you please, make corrections in the above code to make it work?  I am not sure what I am doing wrong here.

TIA
0
 
LVL 18

Accepted Solution

by:
mdougan earned 200 total points
ID: 7166019
Well, both errors are pointing to a problem with the SQL statement.  If you get a message that there is a problem with the FROM statement, then you have to verify that the table mentioned in the FROM clause is actually a table in your database.  If you verify that you have such a table tblTD in your database, and that your user ID has authority to Select from this table, then it could be that the error is just misleading, and the problem is further down in your SQL.  I can see one problem is that you have a space between the first single-quote and the search text.  So, let's say that your txtSearch.Text has "Bob" in it.  Your strSQL would look like:

SELECT * FROM tblTD WHERE fldTdNum = ' Bob'

This shouldn't cause a SQL error, but you most likely wont find any matches.

Now, I notice that the field's name is called fldTdNum, if this field is defined in the database as a Numeric data type, then as my previous comments said, you cannot have the single quotes.  So, if this is a numeric field as defined in the DB, change your SQL to this:

strSql = "SELECT * FROM tblTD WHERE fldTdNum = " & Me.txtSearch.Text
adoTD.RecordSource = strSql
adoTD.Refresh

I always find it helpful to put a breakpoint on some line after forming the strSQL and then I print the strSQL in the Immediate window just to see what it looks like.  If you had done that, you would have seen that the single-quote is out of place.  You might discover that your txtSearch.Text didn't have any value in it, and then you would be trying to search on an invalid value.

If the SQL looks good to you, then you should copy the SQL from the Immediate window and go into the Database, in Access you'd create a new query, then go to view the SQL and paste your SQL there and try to run it.  In SQL Server, you'd go into ISQLW and try to run it there.  Once you verify that your SQL works, then you can start to debug the rest of the code.

I still say that stringing your variables (like txtSearch.text) together with your other SQL is going to open you up to problems if the text holds any single quotes in it, like O'Brian.  The best way around that is to use an ADO Command object and to append ADO Parameter objects to it.
0
 

Author Comment

by:sanand
ID: 7169900
Dear mdougan;

This still does not work. Error message says
Syntax error in From clause.

Let me give you a little layoutof my form:

I am creating a FE in Vb6 for an A2k mdb.

I have a form with adoTD as ado control and all the
fields' datasource is bound to adoTD. Datafield,
dataformat, and datamember properties are left blank.
A Sub bindControls binds the fields programmatically.

adoTD is tied to the database and the table tblTD

The form has a txtSearch box to search for fldTDNum
(txt format) in the table.

The form also has 2 command buttons: cmdSearch and
cmdShowAll.

Now when the form opens, it displays all the records
in the table.

This is what I want:
When I want to search for fldTdNum 000005, and click on
cmdSearch button, it should display all records matching
this fldTdNum only.  But when I click on cmdShowAll, it
should display all the records in the table.

Would you please send me a complete sample code,
if possible; explaining how to programmatically change
the recordsource for an ado control

Your assistance is highly appreciated.

Thanks
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 7660736
Hi sanand,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will ask a Community Support Moderator to:

    Accept mdougan's comment(s) as an answer.

sanand, if you think your question was not answered at all or if you need help, just post a new comment here; Community Support will help you.  DO NOT accept this comment as an answer.

EXPERTS: If you disagree with that recommendation, please post an explanatory comment.
==========
DanRollins -- EE database cleanup volunteer
0
 

Author Comment

by:sanand
ID: 7662481
Happy New Year to All Experts!

As you can see my questions was not answered,
instead of using unbound form, I resolved this
by using a bound form.

Now the question may be deleted.

Thanks for reminding me.

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7662699
Dan,

I agree with you.  Despite repeated and very detailed messages my mdougan, the questioner could only repeat "still does not work".

Anthony
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 7663769
sanand,
Are you certain that mdougan's thoughtful and extensive comments were no help to you in solving this problem?  I think that it would be an honorable thing to award him an A for his efforts.  What do you think?

-- Dan  
0
 

Author Comment

by:sanand
ID: 7663951
DanRollins,

I did not mean that it did not help me at all, but it
gave me some error message.  I'm learning from this
Expert's community and everybody is very generous and helpful.  I'd be happy to award him an "A".

Thanks


0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question