Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  HOME    TRACKER    Visual Basic

Moving Through Fields In A Recordset VB6 Ole6 Access

Hello. When I choose a title from a lstbox by entering it in a txtbox I want to move through the fields in the database to find the title and have that title appear in a datalst box on another form. What happens is the title, one record on from the one I have chosen appears instead. What am I doing wrong. If I choose the very last title on the list nothing appears at all. Here is the code I have used. Any help would be appreciated. Thank you.

if .fields("title") = lstbooks .list (0) then
.fields("status") = lblissue
end if
loop while .eof = false
end sub

This is the first post I have sent and I realise I should use vb and code to make the code easier to read but I'm not sure exactly how this works. When the script prompt appears what am I supposed to put in there? Sorry and thanks for any help.

View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
Moving Fields In Access
i'm trying to reposition the field that's on a subform but the field and label are defaulting to the first position and not to the position i specified.

Me![tblLTd]![60/5/60].Left = 1.5139 (in form header section)
Me![tblLTd]![60/5/60 label].Left = 1.5139 (in detail section)

What's wrong?

Referencing Recordset Fields In Access Reports
I have to put a report together that includes a lot of decision making. For one filed I just return a value from a function I have written, but another field requires the value one of two fields depending on the value in another field. I can't find a way to do this even with a function. The recodset doesn't seem to be available. What I need is this

Function SetComment(theAns as integer)
     Select Case theAns
          Case is = 1
           Case is = 2
      End Select
End Function

My SQL that draws up the data includes both Com2 and Com6 fields. Any idea how to point to the two Com comments?


The worst thing about wearing glasses is them being on your face.
Nothing is impossible, it just looks that way.

Add Fields In Table Mdb With't Possible
I would like to add new fields in my database YY.mdb


NomChamp_s = "XXX"

Set f = New Field
f.Name = NomChamp_s
f.Type = dbText
f.Size = 10
Data.Recordset.Fields.Append f

But I received an error.

Who can help me?


Filtering A Hierarchical Recordset On Fields In Child Recordset
I am trying to filter a hierarchical recordset based on fields in the parent and child recordsets. I am using an MSHFlexgrid to display the result. The following code works fine:
dim cn as New ADODB.Connection
dim rs as New ADODB.Recordset
cn.Open "Provider=MSDataShape.1;Persist Security Info=False;Location=c:fmp3fmp.mdb;" _
      & "Data Source=fmp3;Initial Catalog=C:FMP3fmp;Data Provider=MSDASQL"
rs.Open "SHAPE {SELECT TransID, Type, Date, Particulars, [From Account] As [From], " _
      & "ToAccount AS [To], Cheque as Chq, Amount1 AS Total FROM [Transaction]} AS Command1 " _
      & "APPEND ({SELECT subAmount AS Amount, subBudget AS Budget, subNotes AS Notes, TransID " _
      & "FROM SubTrans} AS Command2 RELATE 'TransID' TO 'TransID') AS Command2", cn
Set hfg.DataSource = rs

The grid is populated properly with data from both the parent and child recordsets visible. I can filter the recordset and display selected records as follows:
rs.Filter = "Date >= ' & dtpSelectDate.Value & "'"

and this also works fine, provided I am filtering on one of the fields from the parent recordset. However, if I try to filter on one of the fields in the child recordset, as in
rs.Filter = " Budget = 'E3' "

I get an error message saying the field doesn't exist, and sure enough if I debug.print all the fields names, it lists every field in the parent recordset but only "Command2" instead of the individual fields from the child recordset.

Is it possible to filter based on fields in the child recordset, and if so, how do I programatically get hold of those fields?

Edited by - mdicoope on 10/21/2003 4:17:21 AM

Moving Through Fields?
What would be the best approach to putting data on a form that has descriptions or titles, with data under them. And then only be able to cursor through the data fields. These may be far longer then the viewable screen so they would need to scroll also.

I tried playing with grids, and text boxes but when you cursor around there is no control where it goes. I s there a module out there that will work for this? Or is there a way to do it by printing directly to the form and still being able to cursor through certain fields?


Moving Fields
Hello guys.

I am building a program for my school. It involves database linking in vb.

I know how to create a field. But the problem is moving a field to another location.

What i tried doing was use the ordinalposition code and add one all the fields and then put that empty field position into the field i wanna move..

but it didnt work for me, and i dont know how to fix it... any help is appretiated

Moving Through Fields
How could I allow users to move through fields (e.g. text boxes) using cursor keys (Left, Down, Up, Right)?

Thanks in advance.

Moving Fields At Run Time

At run-time I want to select a label (or textbox) from within a screen, and move it to somewhere else on the screen by dragging it using the mouse.

1. Is this possible?
2. If so any suggestions or recommended links.

any help would be much appreciated.   

Need Help Moving Through A Recordset
Something strange here. If I use the Recordset object to run the query,

Dim rsRoundTitles As ADODB.Recordset 'RS to hold round titles
Dim cmdRoundTitles As ADODB.Command
Dim sqlRoundtitles As String

Set rsRoundTitles = New ADODB.Recordset

sqlRoundtitles = "SELECT TourDesc, TourID FROM tblTour"
rsRoundTitles.ActiveConnection = Turnover_Connection
rsRoundTitles.CursorLocation = adUseClient
rsRoundTitles.CursorType = adOpenDynamic
rsRoundTitles.LockType = adLockOptimistic
rsRoundTitles.source = sqlRoundtitles
rsRoundTitles.Open Options:=adCmdText

it works fine as long a I have the Locktype as above. I can movefirst, last all day long. But even when I use client side cursors, the command object still tells me it can't go backwards. Sure the Recordset object works as above, but I need the Command object to work for parameter queries. Do I have to set a property in the Command object before using it, or does it refer to all the Recodset objects already set?

Moving Through A Recordset
I created a recordset, and am able to scroll through the recordset by using the movenext property, and then taking the values (Ex: rs(1).value) and then diplaying that equal to the text of the textbox (Ex: text1.text = rs(1).value)

however, i do have about 30 text boxes to do this to.

it works, but seems a little slower than it should be to just move to the next record.

another idea i tried was to set the datsource and datafield name of the text box. this way i can just display the first record in the recordset, but can't see the other records even when i use the movenext property.

so..i guess my question is...
1) Whats the best way to do this that will be the quickest
2) in my 2nd way, is there something i need to do, to beable to scroll through the other records,
3) any other ideas that would help me accomplish this?

Moving In A Recordset
I have a text box that I want to tie to a recordset. How can I do this and then navigate through the recordset?
I have been able to make the textbox display the first field in the recordset, but I can't seem to get it to display the rest of the fields. I have tried to use .movenext, but I must be doing something wrong.
Any suggestions?
Thanks, Stephen

Moving Through A Recordset
I have a problem with .movenext and .moveprevious.  I have a sub named 'display' which displays all the fields from an access database into text boxes on a form.  I have been trying for days now to get 2 buttons to scroll through the records and then run the display sub.
   Most examples I have seen are very simple, i.e:
 if (not rst.eof) then
 end if

This complains about the recordset not being open so I try adding both and "tblContacts", conn (seperatly).  This works but then because the recordset is already open, the display sub tells me so.  So I added "rst.close" before I call the display sub, this gives me no errors but still does not work.
   The display sub works fine as I have it on form_load, here are a few different combinations of code I have tried:

Private Sub cmdNext_Click()
    'On Error Resume Next
    If (Not rst.EOF) Then
    End If
    'With rst
    '    .Open
    '    .MoveNext
    '    .Update
    '    .Close
    '    display
    'End With
    'rst.Open "tblContacts", conn
    'Do While Not rst.EOF
    '    rst.MoveNext
    '    rst.Close
    '    display
End Sub

Another interesting point,  I did at one point have it sort of working, for some reasong when I was clicking the "next" button it was changing the value of a few fields to that of their TabIndex but only those text boxes that I had not entered my own TabIndex for.  There was also no reason for these to change because I was only trying to scroll through the tblContacts (they were on a different table).  This would only work once and although the "Previous" button had near identical code, it made no changes when clicked.
   I beleive I have all the required resources added for this project.

Please help me or my boss wont pay me and I will starve :/

Moving Through Recordset
Hi there...

I want to know how to go to a specific record in a recrodset without filtering the rst. For instance, I want to go to the record which ID is 22 in the rstbooks to populate some text boxes...



Moving The Recordset
How do I click on the data within a listbox and get the data control to jump to that recorset position. Can anyone help me.



Moving Thru Recordset
I have accessed the databse in VB using ADO by a recordset. But for some reasons in my code ,


gives the following errors :

Operation not allow in this context.

recordset doesnot support fetching backwards.

Can someone help me ?

Copy Access Fields To Sql Fields
I have the following code:

vb Code:
Sub Command1_Click() Set g_oConn = New ADODB.ConnectionDim SQLStr As StringDim rs As ADODB.Recordset Set rs = New ADODB.Recordset SQLStr = "SELECT * FROM " & lstTables.ListCount     g_oConn.Open g_oSettings.DBConnString    cmd.CommandType = adCmdText    cmd.CommandText = strSQL    cmd.ActiveConnection = g_oConn    Set rs = cmd.Execute()    rs.MoveFirst  Dim rs_dao As DAO.Recordset Set rs_dao = CurrentDb.OpenRecordset("Database.mdb") rs.MoveFirst While Not rs.EOF    rs_dao.AddNew    rs_dao.Field = rs.Fields    rs_dao.Update    rs.MoveNextWend End Sub

Everything should work grand, except for this line:

vb Code:
rs_dao.Field = rs.Fields

How would I go about transferring all the fields and data of an access table to a sql table without using the field names, both tables are designed the same and contain the same fields. I just want to update the data from an mdb to sql.

Thanks in advance.

Max Fields In Recordset
hi folks i just need to know what is the maximum of field a recordset is able to play around with... i'm looking into some mathematical formulas for work and there a bunch of differant variable that changes every week... like the oil density(for the weigt). i have about 400 different fields ....

I'll be among the best soon, very soon!!!

Recordset Fields?
Once in a recordset, how do I change a value of one of the fields?

eg: I want to change an age from 19 to 22


Two Fields As One In Recordset
here's the scenario:
1. in the Employees table i have three fields: EmployeeID, FirstName, LastName
2. i want to create a recordset such that it would have only to member fields:
    EmployeeID: which is equivalent to the original EmployeeID
    FullName: which is just in the format of 'LastName, FirstName' (e.g., Smith, John)

this is my sql statement and i don't think this is the right one:

sql = "Select EmployeeID, LastName & ', ' & FirstName as FullName from Employees Order by LastName & ', ' & FirstName "

help me get the right syntax.

-never mind this it's not working
it turned out it's a mistake on my field naming in the table

Edited by - ykdis on 3/2/2005 10:02:41 PM

Fields In A Recordset
I know how to loop through all the records in a recordset.
But how do I loop through all the FIELDS in a record contained in a recordset. I know all the fields but i want to avoid typing them all in individually?


Moving An Entire Recordset
Is there an easy way to move all of the information from a recordset in one table into another table? I know this is probably a silly question, but I have a table that has somewhere near 150 fields, and want to move all of those fields into a "history" table when they are complete. I am currently using ADO to access the table. Thanks in advance for any help!

Moving Recordset And Counting
I am using the following in my code:

txtCount = 0
If Trim(txtFields(9).Text) <> "" Then txtCount.Text = txtCount.Text + 1
If Trim(txtFields(28).Text) <> "" Then txtCount.Text = txtCount.Text + 1

This works very well in my program and I use it in the Form Load(), now my problem is that txtFields(9) and txtFields(28) are used in a ADO database with Access.

When I use my movelast, movefirst, moveprevious etc.. txtCount still has the same value in, as it is not moving with the recordset. Example txtField(9) has text in and txtField(28) is empty then txtCount = 1.

Now if I move the recordset and txtField(9) has text and txtField(28) has text then txtCount should =2. But its not

Any help anyone please

Moving Records From One Recordset To Another
This is probably a dumb question but is it possible to move a single record from one recordset ot a different one.

I know I can do it by using multiple variables but I have 16 different parts to the record. I really don't want to have all that code running if I don't have to.

Thanks ahead of time,

DataCombo Box Not Moving Through Recordset

I am new to database programming in vb6. I have table which have 2 fields: Cus_code and Cus_name. I have successfully populated the data combo boxes: combo_cuscode and combo_cusname. But what i want to do now is to move through the recordset using datacombo boxes. For example: If i select customer code from combo_cuscode, it should go to that recordset, so that the other datacombo box combo_cusname display the correct customer name for the customer code i selected in the combo_cuscode. What happens now is that the data in the current recordset is trying to change when i select a entry from the combo box and generate error as it is the primary key field. Please help me out to solve my issue with the data combo box.

Thanking you,


Ordering By Two Fields In An Ado Recordset
I have no problem ordering my recordset by one field but how do I do it with two?!?

Adding Fields To ADO Recordset...
Hello All,
Somewhere I heard that fields can be added on the fly to an ADO recordset.

Is this true??

If so, how is it done??

Thanx In Advance,

Comparing Fields In Same Recordset

Could anyone tell me how I would write an SQL statement to retrieve records based on matching data two common fields (from different records) in the same recordset? For instance:

Select * from table where anyrecordfield1 = anyrecordfield4

then perform functions on the two records based on their common field names to achieve a single record?


Temporary Fields In A Recordset

Is it possible to make a temporary field in a adodb recordset that is grouped?

I need to calculate the values of fieldA * FieldB then i need to sort by the new field and return the maxium.

Is this possible or am i barking up the wrong tree?

Any thoughts would be good

Thanks in advance

Recordset Fields Question
I know you can use the following :

strExample = datTest.recordset.fields(1)

The problem with that is that if I add to the database the fields(1) or fields(27), etc. may change as the table grows/shrinks.
Can you refrence a field by its name? Something like fields(lastname)?
The field names are not likely to change even if the table grows.



Recordset Fields Properties?

How can I find the property of a
certain field with this use of a recordset?

For Each item In Rs.Fields
backup = backup & item & ","
I would like to know if the field is text or numeric
so I can add ' around the text fields (and dates...)

I know that I can check if field data is numeric but this is not the issue.

Using Variable Fields In Recordset
How can I refer to a fields in a recordet using a variable
soemthing like rs!variable. Is this possible

ADO Recordset.Find For 2 Fields
I have two fields to find a recordset. But unfortunately it doesn't work at all. Can anyone help in this regard?


The following works fine
RS.FIND "FIELD2 = '" & VALUE & "'"

But I want something like this
RS.FIND "FIELD1 = '" & VALUE1 & "' AND FIELD2 = '" & VALUE2 & "'"


Excluding Fields From A Recordset?

I need help excluding certain fields from a Recordset.The Remaining fields are used with in a loop,I just want to use the excluded fields once,how can i do this?

Thanks in advance

Retrieving Recordset Fields

I'm retrieving values from a table with 15 fields, but when I try to display field number 10 - 15 nothing get displayed on my page, though the fields are not null in the db table.

Here is my code


Set rsDefault = Server.CreateObject("ADODB.Recordset")

Set rsDefault = dbConn.Execute("SELECT * USERS")

Do while not rsDefault.EOF
Response.Write rsDefault(10)
Response.Write rsDefault(11)
Response.Write rsDefault(12)
Response.Write rsDefault(13)


Please help I don't know what could be the problem.

Extra Fields In A Recordset
I'm pulling a ado recordset from a SQL Server table and a need an extra field to sort the records within. Can I add this extra field to the recordset after I pull my data or does it have to be added to the table I'm pulling from in the database?

Adding Fields To An ADO Recordset ?

Is it possible to alter the structure of an ADO recordset ? Using FoxPro, I can use a statement such as :

ALTER TABLE tempdirectors ADD COLUMN rfname c(20)

to add a new field to a table.

Any comparable command in VB ?

Thanks for any info.

Set Textboxes With Fields From A Recordset
hello everyone,

i was just wondering if anyone knew a quick way to set a bunch of textboxes to with their corresponding field in a recordset. for example:

textboxes named txtSN, txtPN, txtUN would correspond to fields named SN, PN, UN in a recordset.

at the moment i am typing each line as so and it is veryinefficient since i have over 100 fields:

txtSN.Text = rst!SN

thanks in advance, PJ

Using Strings In Recordset Fields
I am trying to create a function which allows a user to enter a fields name in a function call routine

form1 code :-

Private Sub Button1_click()
call anyfield(field1)
End sub

Function in Module1 :-

Function anyfield (f as field)


form1.list1.additem rs.fields(f).value


End Function

how do i get the field name entered into the brackets ?
form1.list1.additem rs.fields(user entered field name from form1).value

i have all the other database code written ok

thanks in advance

Accessing Fields In A Recordset
I'm running a query, and placing the data in the fields that are returned in the recordset into label caption. However, in my code below, the query is correct in syntax and logic, yet nothing is showing in the labels. Any advice as to why this is?...Below is my code, and once again, thanks for any help in advance:

Dim conn As adodb.Connection
Dim rec As adodb.Recordset
Dim dbpath As String
'lblCurrentSemester.Caption = "YO"

lblCurrentSemester.Caption = currentsemester
Dim sql1 As String

Set conn = New adodb.Connection
Set rec = New adodb.Recordset

dbpath = "C:StudentInformationSystem.mdb"
sql1 = "select studentgrade.departmentcode,studentgrade.coursenumb er,coursetitle, grade From course, studentgrade Where studentgrade.departmentcode = course.departmentcode and studentgrade.coursenumber = course.coursenumber and studentgrade.semestercode = '& currentsemester & ' and studentgrade.student_number = '& id &'"
rec.Open sql1, conn, adOpenForwardOnly
Dim test As String

Do While rec.EOF = False
test = rec(0).Fields(0)

lblcourse1dc.Caption = rec(0)
lblCourse1CN.Caption = rec(1)
lblcourse1title.Caption = rec(2)
lblcourse1grade.Caption = rec(3)

Accessing Fields From A Recordset
I'm running Access 97, and have a query that's set up like:

SELECT R1!ID, R1![Part Number], R1![Quantity], R1![BackOrder], R3![Type] FROM...

I'm doing a join of 3 tables and pulling out the information I need.  Now, my question is, what is the syntax to access the fields of each tuple in the recordset?  They all are unique names, so I tried rstRecordset![Part Number] for instance, but it gave me an Item Not Found in Collection error.  Next I tried rstRecordset!R1![Part Number] but didn't do me much good either.

I got the information out by typing rstRecordset.Fields(1) and everything worked fine for me, but I'm wondering what the syntax is for accessing the fields by name.

Adding Fields To A Recordset
I have successfully created an ADO recordset from an Access table. This recordset contains fields in it from the table. Now I want to add some fields to the recordset. How do I do this? I searched on 'recordset' and 'append' and didn't see anything (at least nothing obvious) on how to add fields to a recordset. Can anyone post an example, a link, or an explanation of how to add fields (columns) to a recordset?


Recordset With Fields Added
I am trying to create a recordset from an Access table and add columns to it. Then I want to update the columns in my program. I've never done this before. It's not working. It's probably something simple.

To create the recordset I use:

strSQL = "SELECT Aptno,Custnam,Custno,Days,Delinstr,EntryDate,Houseno,Offcarr,Posinroute,Routeno,Streetnam, _
                 Null as RecType, '0' as NumPage, Null as Laststreetnam _
                 from Customers WHERE Routeno = '1000'"
    adoPrimaryRS.Open strSQL, db, adOpenStatic, adLockOptimistic

I'm not sure about this but there is no error. The new fields, the ones not in the original database, that I'm trying to create are RecType, NumPage, and Laststreetnam.

When I do:

    adoPrimaryRS.Fields("NumPage").Value = 1

I get this error message:

    Multiple-step operation generated errors. Check each status value.

I do not get this error when I try to update one of the fields that was already in the table. I've played with it a bit (taken out .Value, put quotes around the 1) but I always get this same error.

Did I specify something wrong?


How To Find The Fields In The Recordset
hello friend,
                  im using sql server as a backend &VB6 as designer.i have 2 database in which i have same field names in the database.In the 1st database name stats & 2nd database name statsdcn.

1.i want to compare the two database
2.i want to get the fields from the database2 which contain all the fields by matching the data from the database1.
eg dcn is the field in database2,.iwant to fetch all the field detailswhich has same field(dcn) from database1.
3.i want to automate the query without entering field.


Adding Fields To ADO Recordset
I'm trying to write a code that would add a new field to an Access table, which is opened in my aplication as ADO Recordset. The recodset is tied to and displayed in DataGrid. Basically, I want to add a new column to the DataGrid (i.e. field into the underlying table) with current date in the column heading and be able to enter info into that column.
My recordset is declared as rst
I've tried this:

pstrDate = Str(Date)
rst.Fields.Append pstrDate, adVarChar, 10

and I get error: "The operation requested by the application is allowed in this context."

Thanks for any help,

Fields Object Of Recordset
To access the value of a field object(of record set object) the following methods can be used.


I wanted to know which method is mostly used by programmers and why ?


Moving The Recordset To A Specific Record?
Hey all,

I have a table with a primary key, and the last column of the table stores numbers.

what i want to know how to do is, if i wanted to edit the last column of any particular record, how would I tell the recordset to move to that record by using the primary key as the criteria?

ie. If i wanted to edit the last column (called "seats"), for the record with primary key number 145, how would i do it?

Dim rs As Recordset
Dim Db As Database

Set Db = OpenDatabase(App.Path & "db1.mdb")
Set rs = Db.OpenRecordset("Flights")

rs. ?????

rs!seats = blablablabla

the question marks are where i dont kno wat to do

Moving The Recordset To A Flexgrid Selection.
I am using the data control to display the fields of an employee database on a msflexgrid. This works fine, however I want the records I select to display in text boxes. For example the name fields are lname (last name), fname (first name), and mname (middle name). When the form runs all of the fields, and entries come up on the msflexgrid but I can't figure out how to make the texboxes update when I click on one of the names on the flexgrid. I would like to click on the last name on the flexgrid and have text1, text2, and text3 show the first, middle, and last name of the record I clicked on. I can get the specific cell I click on to display in a text box but I can't get each field of the selection to come up in other texboxes.
text1.text = msflexgrid1.text

I have searched for code examples for this and have been unsuccessful so far. Any help would be greatly appreciated.

Can't Find Shape Recordset Fields
I could have sworn that I asked this question before... I used to know how to do this but I forgot!

My question is... how do I access the ADO recordset field properties of a heirarchial (shape) recordset?

For example:
Tier 1: 4 Fields
Tier 2: 20 Fields

When I do the debugger, it only shows me tier 1.
I know that the field values for tier 2 can be accessed... I have forgotten the syntax to get at it.

Anyone know?

Dealing With Recordset Fields Dynamically
I think the hard part is going to be trying to explain this.
I have a database file that is going to populate a spreadsheet. In the event that the database file changes, I want to be able to deal with that in the VB program. Basically, I don't want to go back into the program and add, or remove a field coming from the recordset and recompile the executable. I'm not exactally sure I'm posting this in the right spot, but I figured it wouldn't hurt to start here.
The only thing I can think of is using the field index of the recordset in a loop and watching for errors.

On error goto ErrorHandler
X = 1

Do While Not Rs.eof

Y = 1
Z = 0

Do While or For Loop
Cell(X,Y) = rs.fields(Z)
Y = Y + 1
Z = Z + 1
Loop or Move Next

X = X + 1

I'm basically brainstorming right now and I'm looking for a better solution. Any ideas?

Copyright 2005-08, All rights reserved