reading selected parts of an access 97 database?

[Aaron.W]

i am wanting to bring in 5 of 6 cells of my acess97 database to be listed depending on what number is in the 5th cell. for example - the user will click on a button, and everthing in the cell named "LabNum" containing the number "1" will be listed in a list box. how would i go about doing this? im trying to learn databases, but im not too sure how to do this. any/all help is greatly appreciated! thanks

Aaron

[Rouven Thimm]

I am not sure if I understood your problem correct, so I will first tell you what I think it is: You have a database, each entry consists of 6 fields, one of them is labnum, and now you want the five other fields to be transferred if this field contains a certain value. If so then this could be your code:

DIM DB as Database

'In Access
set DB = CurrentDB

'######### IF YOU NEED to MAKE A SECOND Table
Dim TB as TableDef
'Assign this table-variable
set TB = DB.CreateTableDef ( "Thisismysecondtable" )
'Make the five fields, do so by setting name and type and at the same time adding them to the fields collection each table owns
WITH TB
.Fields.Append .CreateField("F1", dbText)
.Fields.Append .CreateField("F2", dbText)
.Fields.Append .CreateField("F3", dbText)
.Fields.Append .CreateField("F4", dbText)
.Fields.Append .CreateField("F5", dbText)
End With
'Now add the empty table to DB
DB.TableDefs.Append TB

'Now the database has a table for the desired elements

DB.TableDefs.Append

'Now we need two recordset-variables, to work inside the tables, the table consists of records, so the recordset consists of all these records...
Dim roriginal as Recordset
Dim rnew as Recordset
'The roriginal will work in the original table, the rnew will transfer the entries into the new table
'Now they will be assigned to the tables
set roriginal = DB.TableDefs("OriginalTable").OpenRecordset
set rnew = DB.TableDefs("Thisismysecondtable").OpenRecordset

'Move to the first position
roriginal.MoveFirst

'Now, we will go through the original table, always compare the value of this entry for LabNum and then decide - recordcount gives the number of records in a recordset, since the records start with 0, we will work until recordcount-1
DIM counter as long

for counter = 0 to roriginal.RecordCount-1 step 1
'Compare
If roriginal.Fields("LabNum") = DesiredValue then Goto Transfer else Goto DoneThisRecordset

Transfer:
'Now we take each field and transfer it to the other recordset - to do so, we first have to create a new entry in the new recordset, then we will work inside it, and then update this recordset so that the modifications are written to the database
With rnew
.AddNew
.Edit
' ! marks a field name
'I will assume the five fields in the original table carry the names F1 to F5
!F1 = roriginal.Fields("F1")
!F2 = roriginal.Fields("F2")
!F3 = roriginal.Fields("F3")
!F4 = roriginal.Fields("F4")
!F5 = roriginal.Fields("F5")
.Update
'The transfer is complete

DoneThisRecordset:
'to be sure of correct positions, we will always move to the last entry in the new recordset and in the original we will advance to the next entry
rnew.MoveLast
roriginal.MoveNext

next counter





I hope this will in any way help you...

[BrewGuru99]

Now I haven't used the database object in vb for some time now (most of my coding is VBA) but I remember being able to use an sql string in the record source property of the data object. If you're using any other method to grab the recorset, then you're definately able to use sql. If I'm right, and you can use sql, then it's a chinch to grab what you want out of the table.

"SELECT Field1, Field2, Field3, Field4, Field5 FROM MyTable"

Now I'm confused as to what you need execately, but if you wanted to put all five fields into one output result, you would just modify the sql...

"SELECT Field1 & Field2 & Field3 & Field4 & Field5 As AllFields FROM MyTable"

As for placing your data into a listbox... you could just use the .Additem method.

Brewguru99

[vhtml]

Okay, you can do this VERY easily. (c: You need to make sure you have DAO or ADO libraries referenced, and then you construct a simple SQL statement. What you need to know, though, is the the table name and then the fields in there. Let's say you want the table named "MyTable" and the fields: "Field1", "Field2", and "Field3". You only want to select the fields that have "1" in them in "Field1". Your SQL statement would look like:
SELECT [Field1], [Field2], [Field3] FROM [MyTable] WHERE Field1 = '1';
This assumes that Field1 is a string/text field type. If it's a number, then it has to be:
...WHERE Field1 = 1;
Notice there's no ''s on this one! (c; That's how you handle a number/integer/long/etc. field type. (c: That's just giving you a BRIEF SQL lesson. I suggest you read elsewhere for more information. To apply this to your problem, you need to construct an SQL statement like:
SELECT * FROM [YourTable] WHERE [LabNum] = 1;
The * will select ALL the information from the LabNum field where labnum = 1. To apply this to VB code, though, we would use something like:

Dim SQL as string
Dim db as Database
Dim rs as RecordSet
SQL = "SELECT * FROM [YourTable] WHERE [LabNum] = 1;"

on error resume next
set db = OpenDatabase(TheDatabasePathGoesHere)

on error resume next
set rs = db.Execute(SQL);

on error resume next
rs.MoveFirst

Do Until rs.EOF
ListBox1.AddItem rs("LabNum")
rs.MoveNext
Loop




I came up w/ this off the top of my head, so it might need to be modified. But you should get the general jist of it! LOL (c: Well, I hope you have an EXCELLENT day! Bye! <G>

- David Hoyt -
http://vhtml.hypermart.net/