×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

VB SQL - Fieldname,etc - simple PROBLEM

VB SQL - Fieldname,etc - simple PROBLEM

VB SQL - Fieldname,etc - simple PROBLEM

(OP)
VB SQL - Fieldname,etc - simple PROBLEM

Having trouble with what should be a ridiculously easy small, brief routine in VB. Any insight, would be really helpful.

All Im trying to do is have VB pull data from two databases, two different ways;


database (1)

    Simple table, with entries such as (strucuture of db);

P    101    102    103    104    105
-------------------------------------------------------------------
1    1286    2847    5288    2299    3300
2    3101    3024    3503    3024    2305
3    3016    3074    3058    3029    8310
4    3111    3142    3135    3214    3315
5    3116    3417    3518    2319    9400


The above is the table. It is only a static lookup table, no writing or updating will occur, where column 'P' is the primary key field.

VB generates a number through a different internal process, this number will exist in the table within database (1).  I want VB to return the field name the number
is found in, along with the primary key field number.  I won't know which field the number will be in, (unless I were to open the database manually and scan
all the fields).  The field names in this table are numeric, that is to say in the above table;

Field Name 1 = 101, Field Name 2 = 102, Field Name 3 = 103, Field Name 4 = 104, etc....

So the VB app generates say the number '3214', I want VB to search the entire table for the number 314, returning the fieldname it is found in and the corresponding
primaryukey index number, so the correct answer would be;

3214 = FieldName ; " 104 " and Primarykey Index number = 4

Then I want the values, "104, 4" stored in a variable and stuck in a text box or label.

Thats its for database (1).

So here is what is driving me buggy;

My constraint is I'm using VB 4.0 professional version.  Yes I know its old, but due to my current circumstances I am unable to upgrade. The database used is Access MDB, the Jet version, 3.0.

To do the above should be really simple, I've tried all kinds of different approaches.  I've poured through the massive VB tips and tricks HLP file, the huge
VB BUGS hlp, the online standard HLP, various textbooks, and I can't find any clear guidelines that I can grok to figure out how to implement this.
I'm probably just missing something obvious, I can be a bit dense and slow sometimes but usually can there by plodding.  I've been plodding on this one for a while, and have made some progress but am just simply stuck at the moment.

I've tried using recordsets, the data control, snapshots, dynasets, SQL, etc.... often get really weird big error reports.  Checked the VB error dox, and found there was a lot of misleading, erroneous bungles reported on the use and intermixing of all these....  Syntax, methods, properties that are reported to work a certain way, don't.

Ideally, I'd just like to use a recordset, pass through an SQL statement and get the data I want.  I've tried using 'findfirst', seek, etc.... I'm open to these instead of SQL if need be, IF they will work. I can't get seek to do what I want.

Talked with a guy I know who's an SQL jockey ( I'm not), he tells me I need to know the fieldname in advance if I want to pull the number back with its field name,
I thought using a Select * (all) would let me just pick the whole table, wanting the routine to just automatically search all fields until it finds the value Im after and then tells me the field name, is this not possible?  Do I have to specify each and every field name explicitly to search through, if so that seems a bit frustrating.....

Could someone show me some code that will perform this basic operation, please?



For database (2)

    Simple table, with entries such as (strucuture of db);


LOOK
UP
CODE#        Text 1        Text 2        Text 3
--------------------------------------------------------------------------------------------
1286        Text Entry 1    Text Entry 2    Text Entry 3
3101        Text Entry 1    Text Entry 2    Text Entry 3
3016        Text Entry 1    Text Entry 2    Text Entry 3
3111        Text Entry 1    Text Entry 2    Text Entry 3
3116        Text Entry 1    Text Entry 2    Text Entry 3



This table has only four fields.  Field one is "LOOK UP CODE #", Field two is Text 1, Field three is Text 2, Field four is Text 3

The VB app generates a look up code #.  It then pass this number to the database table, the corresponding text entries found
in fields Text 1,Text 2,Text 3, are returned.  Each text entry is stored in a different variable, and each variable is displayed to a different
textbox, i.e.


var1 = Text1.Text Entry 1
var2 = Text2.Text Entry 2
var3 = Text3.Text Entry 3

textbox1.text = var1
textbox1.text = var2
textbox1.text = var3

Ideally, where ever SQL can be used in conjunction with a recordset is appealing, since its the fastest, but I am most concerned about functionality, just getting this to work without bursting anymore blood vessels in my head, if using seek or some other approach does the trick Im open to this.

I've included some of the code I've been tweaing with below, but its not very pretty and it doesnt do what I describe above, really.... just shows some of the stuff I've been trying .... included as a reference to give an idea as to where I'm at....


Thanks for any responses.... I know this sounds ridiculous but I've been working on this for quite a while, not making much headway.....

Best regards


P












 








Private Sub Command8_Click()

    Dim MyDatabase As Database
    Dim MyRecordset As Recordset, MyField As Field
    Dim MySQL As String, I As Integer
    Dim MyTable As Recordset
    
Set MyDatabase = Workspaces(0).OpenDatabase("C:\TEST.MDB")
Set MyRecordset = MyDatabase.OpenRecordset("TABLENAME", dbOpenTable)  ' Open table.
      MyRecordset.Index = "PrimaryKey"               ' Define current index.
      MyRecordset.Seek "=", "12"                        ' Seek record.
      If MyRecordset.NoMatch Then
         
         MsgBox "match was not found"

      Else
         MsgBox "match was found"
      End If

    For I = 0 To MyRecordset.Fields.Count - 1
        Set MyField = MyRecordset.Fields(I)

Debug.Print MyField.Name    ' Print field name.
        Debug.Print MyField.SourceTable ' Print original table name.
        Debug.Print MyField.Value ' Print original table name.
        Debug.Print "name of column is ;"; MyField.SourceField ' Print original field name.
MyRecordset.Index = "PrimaryKey"
MyRecordset.MoveNext

Next I
    MySQL = "SELECT * FROM ninesv WHERE 3 = 12"  'here, 3,4 represnt field, (column names)
For I = 0 To MyRecordset.Fields.Count - 1
        Set MyField = MyRecordset.Fields(I)
        Debug.Print MyField.Name    ' Print field name.
        Debug.Print "FIELD NAME ;"; MyField.Name   ' Print field name.
                Debug.Print MyField.SourceTable ' Print original table name.
                Debug.Print "SourceField ;"; MyField.SourceField ' Print original field name.
                Debug.Print MyField.SourceField ' Print original field name.
Next I

End Sub

RE: VB SQL - Fieldname,etc - simple PROBLEM

Hi P,

The short answer is no, you don't have to supply all the field names to search - that's the good news, the bad news is you'll have to loop through the entire recordset and every field to find what you need:

CODE

  Dim rs As DAO.Recordset
  Dim fld As DAO.Field
  
  Set rs = CurrentDb.OpenRecordset("Table1", dbOpenTable)
  
  rs.MoveFirst
  
  Do While Not rs.EOF
  
    For Each fld In rs.Fields
      If fld.Value = "3214" Then
        Debug.Print "Found it in field:" & fld.Name
        Exit For    ' Quit looping throuhg the fields.
        rs.MoveLast ' Quit looping through the recordset.
      End If
    Next fld
    rs.MoveNext
    
  Loop

Certainly not the most elegant solution but it'll get you where you want to go.  The other option is read the tables field names, and create an SQL statement using each field name in the criteria:

CODE

SELECT * FROM [Table1] WHERE [101] = 3214 OR [102] = 3214 OR [103] = 3214 ....

This can get ugly and you're probably better off just looping through the entire recordset to get your lookup value.

HTH
Todd

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources