Solved

How to change ado record source

Posted on 2002-04-23
17
214 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
  • 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
 

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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
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 50 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

744 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now