Visual Basic :: Lookup Table Speed

Table Lookup
Hi

I'm having trouble with a table lookup. I have a relationship between 2 tables, boats and classe. When I add a new boat I want to select the class it belongs to. I'm using a datacombo but can't get it to display the class names.

If I use the ADO Control it works but not when I try to code it:

Private Sub DataCombo1_Click(Area As Integer)

Dim db As Connection
Set db = New Connection
db.CursorLocation = adUseClient
db.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:WinsysVisual BasicMy ProgramsVB6 Sailing Boat ProjectBoat Race.mdb;"

Dim rsClass As ADODB.Recordset
Set rsClass = New ADODB.Recordset
rsClass.Open "Select classid,classname from classes", db, adOpenStatic, adLockOptimistic

With DataCombo1
Set .DataSource = adoPrimaryRS
Set .RowSource = rsTest
.ListField = ClassName
.DataField = classid
.BoundColumn = classid
End With

Can somebody tell me how to get this working.

Cheers

Lookup Table In VB
I am making a program in VB with the following objects:

- 1 text box
- 1 command button
- 3 labels
- label 1 = Error Number
- label 2 = Error Description
- label 3 = Error fixes

Ok, what i want is for the user to type a number i.e. 680 and then press the command button and my program will look through the database(system1.mdb) which has three fields:

- Error Number
- Error Description
- Error fixes

I would like the three fields too be displayed in the appropriate labels.

Any ideas?

Carl

How To Use Lookup Table?
I have 2 table.In one of them is a field with a code number.In another is same fields and another is names that related by them.
If I want show one of that names in a text box ,how can I do it?
I have two Adodc that connect to this tables.

Makin A Lookup Table
Hi, i'm tryin to make something that is like in help files, the index part, where u type a letter it narrows down. How would I do that w/ a dblist and a textbox?

Lookup Field In Table
Hi,

I'm creating a database aplication in Access and I'm thinking of using a table to hold some database parameters in it.

Things such as directory path for files to be uploaded, which Dial-Up Networking Connection to use when dialing up etc.

My question is how, in code, do I lookup the field and assign it to a variable?

e.g.

Code:

strPhoneNumber = Value from Phone Number field
Hope I've made myself clear?

Regards

1-Table Lookup 2-Backing Up
I have two questions.

1 - My code finds a string in a table's first column and brings matching values from the other columns of the matched record. Now my database grows bigger and I need to group specific info on separate tables. I will change my code so that the code would first search the table name (which would be selected in a combobox by the user), then search its first column for an entry matching a textbox again filled in by the user.
Can you advise me how a table is searched ?

2 - My application depends on some databases placed in the application's paths. The app regularly compacts, repairs and backs up the databases. If the original is corrupted or accidentally deleted by the user then this created back up is brought back in to the app path and used thereafter. Where do you recommend me to keep these backups ? In Windows directory, Temp directory ? Where ?

Thank you

Lookup An Access Table
Hi,

I am using a DAO Connection from VB to an Access database. I wanted to know if there was an easy way to lookup a single value - in the same kind of way that Dlookup works in Access

e.g.
value = DLookup("[COSTING_COMPONENT_ID]", "COSTING_COMPONENTS_LKP_TBL", "COMPONENT_TYPE='Salaried Positions'")

instead of having to write a an SQL string and open the recordset etc.

Cheers and thanks
VW...

SQL Question For Lookup Table.
Say for instance you have the following:

table:
create dbo.tbl_Users_Provinces
  [ProvinceID] INT NOT NULL,
  [Province] NVARCHAR(50) NOT NULL,
  CONSTRAINT PK_tbl_Users_ProvinceID PRIMARY KEY CLUSTERED ( ProvinceID)
)

This table will be a lookup table and will contain all the values

1 QUEBEC
.
.
.
N ONTARIO

Now, given the following user's table:

CREATE TABLE dbo.tbl_Users]
(
    [UserID] [int] IDENTITY (1, 1) NOT NULL ,
    [UserName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [FirstName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [LastName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [StateProvinceCityID] [int] NOT NULL ,

                CONSTRAINT [PK_tbl_Users] PRIMARY KEY NONCLUSTERED
    (
        [UserID]
    ) ON [PRIMARY] ,
                CONSTRAINT [FK_tbl_Users_tbl_Users_Provinces] FOREIGN KEY
    (
        [ProvinceID]
    ) REFERENCES [tbl_Users_Provinces] (
        [ProvinceID]
    )
) ON [PRIMARY]
GO

Now Say I want to do a select statement and on userid or FirstName. and retrieve all the values including the province name and not a the index value for that province.
How would you approach this. Now, things have change for lookup tables.
There are many possibilities. Which one is the most logical one and why? If my question is too long then just help me on the query I will analyze the reasons.

Let's share our knowledge together!!!
Matt Cupryk
514-685-0449

Alternative For A Lookup Table
good morning,

I was designing a database for a payroll system and have come up with a database fully normalized. however, I noticed that I was able to create a reasonable number of lookup tables. If i wish to remove some of them (maybe to reduce the number of size of the database) are there any alternatives? or is it way better leaving them behind?

follow up question-- Do I still need to make a look-up table for a GENDER Field (e.g., Male, Female)?

Please help me with this.
Any reply is greatly appreciated!
thanks and GOD BLESS!

---==+ Fear is what keeps us from doing things we know we can't... but we can. +==--- - zhylax

Range Value - Table Lookup
Hi, Guys.

I have range value in Ms Access Table. When I type a value in textbox, in another textbox will be dispalyed the value that has been convert to character.

Let's the table is like this:

ValMin-----ValMax-------Char

---0----------50-------------E--
--51---------60-------------D--
--61---------70-------------C--
--71---------80-------------B--
--81---------100-----------A--

When I type value in txtValue "75", the value of chararater in txtChar will be 'B'.

Here is the code :

Private Sub Form_Activate()
Dim sqlstring
sqlstring = "select Char from TblValue where Valmin >= " & txtValue.Text & " and Valmax <= " & txtValue.Text

Data2.RecordSource = sqlstring
Data2.Refresh
End Sub

Private Sub txtValue_Change()
Dim sqlstring
sqlstring = "select Char from TblValue where Valmin >= " & txtValue.Text & " and Valmax <= " & txtValue.Text

Data2.RecordSource = sqlstring
Data2.Refresh
End Sub

Thank you.

/sukarso

Create Lookup Table With DAO
I have a table named "MAIN". I want to create using code (DAO), another table with one field that will lookup (via Lookup field)its values from the "MAIN" table. So in the end I will have 2 tables, one will be "MAIN" and the other will have one column that will lookup its values from the "MAIN" table.

Code Problem {lookup Table}
ok im writing a sports day program for my school by using a excel spreadsheet. lets say im looking at the 100 m race and for every time which is achieved by a competitor he is rewarded with points.

now i can do it by writing all the possible times with the corresponding points into a spreadsheet, which would mean i would have to write alot of If - Then statements such as:

If 12 > Text1 > 11 Then
MsgBox Cell(1, 1)
End If

this would then have to be repeated for every single range of times.
Is there any way to make ranges in excel and then by using VB searching these?

such that you have a speadsheet like this:

x > 20 | 10
20 > x > 18 | 20
18 > x > 16 | 30
.
.
10 > x | 1000

is there anyway in excel to make ranges and then by VB finding these?

thank you

How To Write A Table Lookup Function In VBA?
Hi,

I want to write a VBA function that will search through a two-dimensional table until it finds an element specified by a given row index and a column index, both of which will be passed in as parameters. The row header would have 6 elements, with values (say) apple,orange,mango,pear,grape,melon. The index passed in would be "grape", for example. I then want to search this row until it finds "grape", and return the column number associated with that element, in this case 5. I would search through the column header following a similar logic (i.e. find the row in which the column index resides). If this index were 14 (say), then I could use a Range object to get the value in column 5 and column 14, and return this value from the function.

I know how to do this in the spreadsheet using worksheet functions (a combination of MATCH & OFFSET), but I'd like to do it using VBA as I think its clearer and more transparent (and I need to use it in several places). I initially tried using the worksheetfunction method to allow me to use MATCH & OFFSET, but I didn't get anywhere with it. In any case, this method is not ideal, as it isn't clear. Can anyone suggest anything?

Table Design And Lookup In Queries
Hi,

This is probably a dumb question but I have three tables, tblCustomers, tblOrders, and tblCreditCards. tblCustomers links to tblOrders by CustomerID, tblOrders to tblCreditCards by OrderID. An ori l order would have values in all three tables. Now a second order placed by a customer already has customer info and uses the same credit card. I add a new order to tblorders. Is there a way in a query using the Dlookup function to lookup the creditcard even though there are several orders for one cusomter. I tried to use orderid but this didn;t work because they original orderid is obviously different from the second. And also make the first creditcard number the default for additional orders by the same customer.

Thanks

Information About The Lookup Table In MS Access
Dear VB users,

Can somebody give me a source about how to find information of the "LookUp".
You can find the "lookUp" by pressing "Design table", second tab.

Nice regards,

Michelle.

Number Prefix Lookup Table In MDB
Hi all,

I'm trying to get my VB6 app to look up prefixes for a phone number out of
an MDB file along with an associated price etc.

For example the phone number could be 9802xxxx, and the MDB file will have
the record 9802 with an associated price.

I am used to connecting to databases via ADO, but my problem is that the MDB
file may contain prefixes from 1 to 5 chars in length and i'm not sure how
to get VB to find the best match.

I'm assuming it can be done with a very large nested IF statement, is there
a better way?

Cheers,
John

Using Or Not Using Bound Control For Lookup Table
Sould i use bound controls to connect to a DB? I realize it if faster to code but is if efficient use of DB resources? Does the connection stay open the whole time the DataCombo is active?

I found this code in a FAQ
Code:
Private Sub Form_Load()
  Dim oRS as New ADODB.Recordset
  Dim strSQL as String

  strSQL = "SELECT PubID, Name FROM Publishers"
  oRS.Open strSQL, goConn, adOpenForwardOnly
  Set DataCombo1.RowSource = oRS
  DataCombo1.ListField = oRS.Fields("Name").Name
  DataCombo1.BoundColumn = oRS.Fields("PubID").Name
End Sub

The goConn is the connection to the database via a global connection which is one way of having a connection to a database which is shown in the attached Demo

Basically what is the benefit of this line of code:
DataCombo1.BoundColumn = oRS.Fields("PubID").Name

Here is my issue. I would like to be able to pull all the Departments from a DB table and display them. When the user selects a department the department ID is stored in Employee DB when user registers. This will allow me to query results by department.

What i was planning to try is using a regular combo box, reading the Departments from the DB and placing them in the combo box. Which is better? Teh bound control or the latter? Thanks!

Commission Table Lookup / Calculation
Using Access 2000, I'm creating a small app that calculates commission. The commission is tiered such that commission/unit rises after a threshhold. I'm trying to determine the most efficient way to determine a commission rate without hard coding the values. (The rates and threshholds will be stored in a table which will need to be changed from time to time). I was hoping somebody could inform me of a more effiecient way than I'm doing it currently -- this way works but I've got 12 levels of rates, threshholds and hence potentially 12 levels of nested ifs.

CommissionTable

Level Threshold Rate
  1 20 $50
  2 30 $75
  3 40 $100

dim strSQL as string
dim dblSalesUnits as double
dim Rate1, Rate2, Rate3, CommisionRate, CommisionTotal as currency
dim Level1, Level2, Level3 as single

strSQL = select * from commissiontable

'Assign appropriate values to rate and level variables from recordset info

If dblSalesUnits >= Level1 Then
      If dblSalesUnits >= Level2 Then
           If dblSalesUnits >= Level3 Then
           CommissionRate = Rate3
           End If
      Else CommissionRate = Rate2
      End If
Else CommissionRate = Rate1
End If

CommissionTotal = CommisionRate*SalesUnits

Thanks in advance for any help/suggestions.

Dcount On Form With Lookup Of Datepart On Second Table
ok Ive a report which gets info filled in form one table but my counts for that report are based on another table.

The table with the counts is called "ViewQsTotals"
Fields are:

QsNumber MonthRequested YearRequested RequestsYTD
06001 5 2004 3
06002 5 2004 3
06003 1 2004 2
06003 2 2004 1

Now I tried the folowing:

=DCount("[QSNumber]",[ViewQsTotals],[MonthRequested] =' & Month(GetDate()) & '' AND [YearRequested] = '" & Year(GetDate()) & "'")

so basically I need the count of the qsnumber(from a txtbox on my report)
for the current month and year from the above table

My Report is a continuous form so the qsnumber will be constantly changing

any Ideas....??

Need Help Creating A Lookup Table For Nasty Equation
I know this is a relatively simple question, but I'm a complete newbie so here goes:

I'm building a 2-D intermolecular force simulation, using a Lennard-Jones type interaction between the molecules, which is a really nasty equation.

http://polymer.bu.edu/Wasser/robert/work/img20.gif

I tried implementing it in place of my current simple spring interaction:
force = (spring constant) * (distance)

and I got all kinds of terrible errors. I think building a lookup table at the beginning of each loop might be a good way to go, but I've never done it before so I'm wondering if anyone could give me a quick & dirty description of how I should go about it. I'm thinking probably having an array with 2 columns (one being the distance, other being force) and looking up force by looking for the distance x between the 2 particles in question. The reason for building a new lookup table at the beginning of each new timestep is that I'd like to vary a couple of the variables in the equation as time progresses.

So does anyone have any experience building/using lookup tables? If you want to see what I've got so far you can check this out:
lennard_jones17.rar

I built an excel graph showing how Lennard Jones works here:
force3.xls

Number Prefix Lookup In A Really Big Sql Server Table
Hi all,

I'm trying to get my VB6 app to look up prefixes for a phone number in a sql server table with an associated price etc...

For example the phone number could be 9802xxxx, and the sql table will have
the record 9802 with an associated price.
it can be 98021 if phone number look like this 98021xxx.i must find the longuest one

I am used to connecting to databases via ADO, and this select statement in a stored procedure :

Code:

SELECT TOP 1 Prefix AS Prefix , price ,step,price_step From MyBigTable WHERE ( @numBerDestination like Prefix + '%' ) ORDER BY LEN(Prefix) DESC
'Prefix' is indexed.

my table contains about 1 million prefix ...
but my problem is that this operation takes too long (2s per operation)

Is there
a better way to do this ?

thanks

Errors While Connecting Lookup Table With Combo Box
I ma getting an invalid authorization error when linking my lookup from SQL server to combo box

the code is

Private Sub SetupADO()

Dim adoParm As ADODB.Parameter
Dim strSQLServer As String

'On Error GoTo SetupADO_Err

Set acnMarkets = New ADODB.Connection
strSQLServer = "sfdaqsql1"
With acnMarkets
.ConnectionString = "Provider=sqloledb;Data Source=sfdaqsql1;Persist Security Info=False"
.CursorLocation = adUseClient
.Open
End With

Set arsMarkets = Nothing

Set acmMarkets = New ADODB.Command
With acmMarkets
.ActiveConnection = acnMarkets
.CommandType = adCmdText
.CommandText = "SELECT market_id,market_name FROM market_lookup where market_id=?"
Set adoParm = .CreateParameter("market_id", adChar, adParamInput, 1, "x")
.Parameters.Append adoParm
End With
Set arsMarkets = Nothing

Set acmMarketsInfo = New ADODB.Command
With acmMarketsInfo
.ActiveConnection = acnMarkets
.CommandType = adCmdText
.CommandText = "SELECT market_id,market_name, market, state FROM market_lookup where market_id=?"
Set adoParm = .CreateParameter("market_id", adChar, adParamInput, 1, "x")
.Parameters.Append adoParm
End With

Exit Sub
'SetupADO_Err:
' Call ShowErrors
End Sub

Thanks
Help appreciated

Tabbed Form Vs. Subform Table Lookup
Good Morning everyone,

Just some quick clarifying questions r/t the differences between tabs and subforms.

We have a form that is pretty similar to a typical address book contact form. Most of the information on the form [frmPerson] is pulled from a cooresponding table [tblPerson]. However, some of the information for text boxes needs to be pulled from other tables [tblProfessional], [tblAgencyContacts], etc. and is dependent on the type of contact [tblDisciplineID].

We initially set up the form to use tabs to represent the different types of contacts. We are currently working within the tab Professional that uses a list box to allow the user to select the agency they want to lookup for there current contact. For example if I work for Company ABC and Company XYZ they both show up in the list box. The user then selects Company ABC, and the corresponding text boxes for that person and company should populate. The problem that we are seeing is that, it doesn't appear there is an easy way to associate the text boxes that are to be populated with a table different than the one the [frmPeron] is associated with [tblPerson]. So here are the questions...

1. Can tabs be used (easily) to lookup the information in a different table (i.e. [tblProfessional]) and populate text fields? I realize we could use combo boxes, but this seems like messy programming.

2. Should we use subforms instead of tabs? Obviously, subform will link directly to a different table. My only initial consideration here is trying to minimize the number of forms we have. I assume we could get the subform to change using a case statement that is dependendent on the [DisciplineID].

Many Thanks!

Adding An Entry Into A Lookup Table Using Ado Data Combo Box
I am using 2 ado controls, one for the main table books and another to lookup the authors name from his id .While adding a new book by a new author what can be done to first add this author into the lookup table and then accept the entry into the book table without the user seeing another form?

regards
Venu

ExcelSpreadsheet Vs. DB Table (SPEED)
I work with excel files a lot via VB6 applications and was wondering if I can speed up my code by making a subtle change.

Normally, I open up the excel file, then select the right worksheet, and then loop through the rows and cols and extract the data
Just like so: myData=xlSheet.Cells(row,col)

I was wondering if it would make things faster if I was to get the whole worksheet into a temporary DB table.
I could then do "SELECT * FROM TEMPTABLE" and work with an ADODB.Recordset instead of the excel file.
Then myData=rs(x)

First Question: Do you think that would improve processing speed? My suspicion is that it will because Excel tends to be pretty slow.

Next Question: Since I may change to SQL Server later, I am interested in how to convert my Excel sheet into a temp table without any "MS Access specific" methods. Is this an easy task? Any examples?

Best Regards,

Edit by Moderator:
Please post Excel questions, in the Excel forum.

Thank you.

Table Update Speed Problem
Hi All ,
I need an answer.I have tried everything in the Book to speed up updates to My MSAccess SQL table.Indexes , and transactions . You can see the Big SQL statement (lots of Join Statements) in the "Where " clause.

i loop through the recordset returned by this query and for each record update some other table.(see the While Loop enveloped in transactions and i use the DBSQLPASSTHROUGH option for each update.This option does not load the Jet database Engine and i think what is does is to directly invoke functions implemented in the ODBC Driver--- in other words sidesteps one layer).

Then Why in the world does this update take time.It is very slow.Even the application,seems to hang.Conceptually , I understand that for each value to be updated in the
table a radom access is made to that record(records may be in non-Sequential locations).But then what is the solution??????????????????????????????????????????????????/

'******************************SEE the CODE BELOW**********************************

str1 = "SELECT D.CustGrp, D.CustCombo ,D.MktArea,D.OrderSize, D.ProdClass as Prod, P.RET_PRICE AS retail,((DB.MDemand + DB.SDemand )* P.RET_PRICE * (PT.EP_DISCNT/100) * (PT.EP_MAX_DAY/100)) as EPD " & _
"FROM DemandData AS D, ProdInfo AS P ,detailedBreakdowns as DB ,PAYTERMS as PT " & _
"Where d.ProdClass = p.PROD_CLASS and d.CustCombo = DB.CustCombo and d.OrderSize = DB.OrderSize and d.Mktarea = DB.MarketArea and PT.Cust_Group = D.CustGrp;"

Set rsEPD = cnxn.OpenRecordset(str1, , dbSQLPassThrough)
sum = 0

While (rsEPD.EOF <> True)

str2 = "Update detailedBreakDowns as D " & _
"Set EPmtDiscount = " & rsEPD!EPD & " " & _
"Where D.CustCombo = " & rsEPD!CustCombo & " and D.OrderSize = " & rsEPD!OrderSize & " and D.MarketArea = " & rsEPD!MktArea & " "

cnxn.Execute str2, dbSQLPassThrough

sum = sum + rsEPD!EPD

rsEPD.MoveNext

Wend

rsEPD.Close

How To Improve Speed Of Table Update?
I have a problem: I am updating a DBASE3 table records from an arraylist that I create in an app.
Let me start from beginning. I take a name field for each record, and make a userID out of it, then stick it into listArray along with the old userID that exists in one of the recrord fields. so list array looks like this:

{temprec}
{custkey}
newuserID1
olduserID1
newuserID2
olduserID2
newuserID3
olduserID3
newuserID4
olduserID4
...5
...5

I use ODBC to open the table and read it through, then I close connection (array list is filled and it is in the same order the Table is).

Then I open up ODBC connection again and update each record with my newuserID in the appropriate field.
The problem is that the update part of the program is very slow! I am using a test table which has 4200 records, and it takes 4-5 minutes to run (i will need to run this app. on recordsets 100K+. I know that string DIMs and arraylist are not the most efficient way to do things, but without the update part of the program it runs in 5-7 seconds which includes going through table, making newuserID, filling arraylist, writing to test.txt. I guess I know where the problem lies, just dont know how to fix it; I am running update sql statement for each record, and I think this is the problem. I am pretty new to vb.net and really not aware of any other way to do this. Is there a better, more efficient way?

sample code for the update part.

dbf_connection2.Open()
        While myreader.Read

            '*******************************************
            If ((myreader(15).ToString).Length = 0) Then
                'if name is blank skip to next record
                GoTo EndOfLoop
            End If
            Try
                cField = myreader(15).ToString
                custkey = cField.Substring(39, 15)
            Catch e As System.Exception
                System.Console.WriteLine("ERROR #5 " + e.Message)
            End Try

            temprec = tempRecord.Item(count)
            temprec = temprec.Substring(0, 7)
            'PrintLine(1, tempRecord.Item(count))
            custkey = tempRecord.Item(count + 1)
            custkey = custkey.Substring(0, 15)
            'PrintLine(1, tempRecord.Item(count + 1))

            Try

                update_command.CommandText = "UPDATE ins SET rec= '" + temprec + "' WHERE RIGHT(LEFT(C,54), 15) ='" + custkey + "' "
                update_command.Connection = dbf_connection2

                update_command.ExecuteNonQuery()

                System.Console.WriteLine("[" + reccount.ToString + "]" + "[" + temprec + "]")

            Catch e As Exception
                System.Console.WriteLine("ERROR #6 " + e.Message)
                End

            End Try

            count = count + 2
            reccount = reccount + 1

EndOfLoop:
        End While
        dbf_connection2.Close()

Need To Put Info From Form &"lookup&" Fields Into Main Table
I have a request to create a series of forms in Access that provide succeeding bits of information back to a main table.  Each row of the main table describes a preventive maintenance activity from request to scheduling.

The first form (InputForm) would include three lookup fields (dept, ext, pager) from tblNamesSite based on field "name".  The content of the three linked fields must be included in the MainTable to be available thereafter.

The second form (ScopeForm) would look up "WorkOrderNumber" based on "PreventiveMaintIDNumber" or "Work RequestNumber" from an Oracle database.  These three fields must also be populated in the table and called up on the approval forms. And so on, and so on.....

I researched this problem and found only one possible solution (aside from the obvious, it should be on SQL server) and found the update or edit/update function.  Is this a viable solution?  What is the proper syntax? Should I initiate a batch event or trigger from the form?

Thanks,

Imjay

I Need To Speed Up An Append Process From Txts Files To Acces Table
Hi everybody,

It's a VB5 app.

I need to speed up an append process from txts files to an access 97 table.

Right now It's doing like this:

where:

- txtvalue is an array of string and the dimension = destination-table.fields.count-1
- listtxtfiles has the pathname of the txts files to be appended

      Dim strglen As Integer
      Dim posinstrg As Integer

      For i = 0 To listtxtfiles.ListCount() - 1

          DoEvents

          Open listtxtfiles.List(i) For Input Access Read As #1

          txthewholeline = Trim(txthewholeline)

          Do While Not EOF(1)

             DoEvents

             Line Input #1, txthewholeline

             If Trim(txthewholeline) <> "" Then

                strglen = Length(txthewholeline)

                If strglen > 0 Then

                   j = 0

                   For posinstrg = 1 To strglen

                       If Mid(txthewholeline, posinstrg, 1) <> "," Then

                          flakedline = flakedline & Mid(txthewholeline, posinstrg, 1)

                       Else

                          txtvalue(j) = flakedline

                          j = j + 1

                          If j > txtdef.Fields.Count - 1 Then

                             Exit For

                          End If

                          txtvalue(j) = 0

                          flakedline = ""

                       End If

                   Next posinstrg

                End If

                'add a new record to the table

                Call AddNewRecord(txtvalue())

             End If

          Loop

          Close #1

       Next i

---------------------

thank,

Jaigon

Module Speed VS Class Speed?
Would I get any speed change from moving code from a module into a class or viceversa? I've written some reuseable code for other projects and currently they are laid out in a bunch of subs in a module. If I convert them to a class would the execution speed be the same?

And what I then convert the class to a .DLL and access the .DLL via code. Any speed change there?!?!

Trivial question maybe, but we're talking 1000's of lines of code. Thanks for your thoughts.

Speed UP Coding Speed
I want to use faster way to code my ASP web pages like code in VB.
One way is using DLL, cool but a few servers allow custom DLL to register on server.
My question are

1.What is IIS Application, is it type of Dll?
2.Is it possible to write Function in VB IDE in Class or module and use in ASP.

Or any better idea!

UI Speed Vs Language Speed
One of the factors that impressed most about vb is how smooth, clean, and fast UI's designed in it are. Seems that even novice coders can create applications whose UI's are sports cars compared to other languages like java who's UI's are reminicent of the horse and buggy days. Why is the UI soo much faster in VB than in other languages like java. Is it just the controls (windgets?) they use or is there a real performance difference between the languages themselves? I'd be really interested to see timing differnces of really simple operations performed many times in different languages, like adding, subtracting, and string handling, ect.. Between Java, C++, C#, VB6, and VB.net. Does anyone know where I can find any of these benchmarks?

DNS MX Lookup
Hi,

I searched the forum, but I didn't find any solution for my problem. I need to find the smtp server and to send mail. The send mail part is ok, but the mx lookup is a big problem for me. I found controls, ocx-s and dll-s, but I need to do this without them. Only VB and mswinsck.ocx... For example I want to send email to: user@emailportal.com. The App has to find the smtp (f.E. smtp.emailportal.com) and to send my email...

Is it possible?

Thanks in advance

Lookup
Hey I have a command button that saves information from four text boxes and saves them into Notepad. The file is saved as whatever is in the first text box. Is there a way I can have a command button to look these files that have been saved?

Here is the code I use to save to notepad

Private Sub cmdFinish_Click()
Dim strCid As String 'set variable
Dim strCname As String 'set variable
Dim strTno As String 'set variable
Dim strApt As String 'set variable

strCid = txtCustomerID.Text 'define variable
strCname = txtCustomerName.Text 'define variable
strTno = txtTelephoneNo.Text 'define variable
strApt = txtAppointment.Text 'define variable

Open ("A:" & strCid & ".txt") For Append As #1
Write #1, "Customer ID", strCid, "Customer Name", strCname, "Customer Telephone No", strTno, "Appointment date and time", strApt 'write the info to the file.
Close #1 'closes the file after it's done writing.
txtCustomerID = ""
txtCustomerName = ""
txtTelephoneNo = ""
txtAppointment = ""
Form1.Show
Me.Hide
End Sub

Thanks in advance

G

Lookup
I'm trying to identify when a value exist in both columns. I'm using the formula:

=LOOKUP(M2,N2:N95)

The ex.

m n lookup cell
1 abc xxx
2 bcd xxx
3 cde xxx
4 def a

I'm looking in n to find an exact match, but the cell formula returns the value a. How do I retrieve abc only

Lookup
I have data in column A (bank names) and values in column B(routing numbers to those banks). I want to make a search box where the user enters either/or part or the whole bank name or the routing number and based on what they enter I would like to return the value under what they searched for. Either that or have whatever they begin searching for scroll down the list. For example if my list of banks included BALTIMORE, CHICAGO, DALLAS and the user began entering CHI it would highlight Chicago since that is the closest match. Thanks.

Lookup
I can use your help, please!!

I have a column of dates I need to match to dates on a calendar (actually only Fridays on the calendar), when the column date matches the Friday-calendar date I need to mark the calendar date.

VB With IP Lookup
Hello Big Brothers

I think its batter to Post New Thrade instade of old one for this IP Lookup

see my attachment
IPList.zip

In this excel file there is 'A' column with IP so I want to store Location and State of thet IP in column 'E'

you can see lots of information in first row of this worksheet , but its OK if you help me to get only City and Country

I collect it with use of www.DnsStuff.com manually but when i do it programatically it ban my IP by opening their site so is there other way

Thanks in Advance for your Valuable Help

Web Lookup
am trying to get my database to go to one of 4 web sites when a check box is checked. I want to be able to lookup information depending on information in a form and one of the tables that the form uses for information.
The table has these fields.
Type
Make
Model
ManufUnitId
Here is my code so far, which works only for one web site no matter what manufacturer is found.
Can anyone help me with what I am doing wrong?

Private Sub WarrantychkLbl_AfterUpdate()

Dim Manufacturer As String
Dim ManufId As String

If Me.Warranty = -1 Then

ManufId = DLookup("[ManufUnitID]", "Equipment", "[Make] & ' ' & [Model] = Equipment")

Manufacturer = DLookup("Make", "equipment", Me.ManuModelNumber = ManufId)

If Manufacturer = "IBM" Then Shell ("C:Program FilesInternet Exploreriexplore.exe http://www-307.ibm.com/pc/support/site.wss/warranty /warranty.vm"), vbMaximizedFocus Else
If Manufacturer = "HP" Then Shell ("C:Program FilesInternet Exploreriexplore.exe http://h20000.www2.hp.com/bizsupport/TechSupport/Wa rrantySingleLookup.jsp?prodSeriesId22924&prodTyp eId!5348"), vbMaximizedFocus Else
If Manufacturer = "COMPAQ" Then Shell ("C:Program FilesInternet Exploreriexplore.exe http://h20000.www2.hp.com/bizsupport/TechSupport/Wa rrantySingleLookup.jsp?prodSeriesId22924&prodTyp eId!5348"), vbMaximizedFocus Else
If Manufacturer = "DELL" Then Shell ("C:Program FilesInternet Exploreriexplore.exe http://h20000.www2.hp.com/bizsupport/TechSupport/Wa rrantySingleLookup.jsp?prodSeriesId22924&prodTyp eId!5348"), vbMaximizedFocus
End If

End Sub
am trying to get my database to go to one of 4 web sites when a check box is checked. I want to be able to lookup information depending on information in a form and one of the tables that the form uses for information.
The table has these fields.
Type
Make
Model
ManufUnitId
Here is my code so far, which works only for one web site no matter what manufacturer is found.
Can anyone help me with what I am doing wrong?

Private Sub WarrantychkLbl_AfterUpdate()

Dim Manufacturer As String
Dim ManufId As String

If Me.Warranty = -1 Then

ManufId = DLookup("[ManufUnitID]", "Equipment", "[Make] & ' ' & [Model] = Equipment")

Manufacturer = DLookup("Make", "equipment", Me.ManuModelNumber = ManufId)

If Manufacturer = "IBM" Then Shell ("C:Program FilesInternet Exploreriexplore.exe http://www-307.ibm.com/pc/support/site.wss/warranty /warranty.vm"), vbMaximizedFocus Else
If Manufacturer = "HP" Then Shell ("C:Program FilesInternet Exploreriexplore.exe http://h20000.www2.hp.com/bizsupport/TechSupport/Wa rrantySingleLookup.jsp?prodSeriesId22924&prodTyp eId!5348"), vbMaximizedFocus Else
If Manufacturer = "COMPAQ" Then Shell ("C:Program FilesInternet Exploreriexplore.exe http://h20000.www2.hp.com/bizsupport/TechSupport/Wa rrantySingleLookup.jsp?prodSeriesId22924&prodTyp eId!5348"), vbMaximizedFocus Else
If Manufacturer = "DELL" Then Shell ("C:Program FilesInternet Exploreriexplore.exe http://h20000.www2.hp.com/bizsupport/TechSupport/Wa rrantySingleLookup.jsp?prodSeriesId22924&prodTyp eId!5348"), vbMaximizedFocus
End If

End Sub

Row Value Lookup
Hello all,

I have a pretty simple one I think. But it's been stumping me. I have a dynamic list of unique names on my Excel sheet. And in my code I want to search that list for a fixed name and have it return the row number it found it at. How could I write that into my code?

Thanks,
Adam

Dns Lookup
I need send a dns lookup request to a dns server which is not the system default dns server,
does anyone know how it's done?

DNS Lookup
I'm looking for an ActiveX-Control to make a reverse DNS lookup from ASP-pages. I've tried ActiveDNS but I can't register it. Any tips?

DNS Lookup?
I want to do a DNS lookup but I want to only use the Local LAN DNS server. If no record is found on that server then I do not want to query other servers. Anyone know how to do this?

File Name Lookup
I hope this is the right place to post this problem I'm having with VB.

I have this macro that I have created in excel that generates a file in a specific format with a certain content.

The macro works by creating an empty file and then populates it by switching back to the spreasheet where to takes all the required information in raw format before copying it to the file where I can do some formatting and other things.

Here's the problem... when switching back to the spreadsheet, the code specifies which spreadsheet to open. In other words, the file name is hard coded. The problem comes when the name of the spreadsheet changes (versions and drafts and so on...) I have to keep updating the code to pick up the right spreadsheet file name.

Would anyone be able to tell me if there's a way to change my macro to look at the filename of the spreasheet?

I can't provide any code right now but I'll try to post an extract later on this week

Thanks in advance

Excel Lookup
I have a whole table with data... and i want to lookup if the table has the word price and if it does then i want to indicate that in another cell.. How would i go about doing that...w ould i have to use VB?

Lookup A Value In A Field
In Access 2000 I have a one table that needs to lookup the value in another table and perform a calculation.

I have a table called BenefitPercent that has just one field, Percent, that holds a percent value that is used in a calculation. This value is used in the Salary table to calculate the benefits for the employee by calculating, the Salary.TotalSalary * BenefitPercent.Percent. These tables have no relationship to one another, I just want the calculation to lookup the percent and perform the calculation. I just want the user to enter the percent in the BenefitPercent table, not the Salary table. I created a text box in the Salary form with the following calculation but I get #Name? when I open the form.

=[txtSalTotalSalary]*[BenefitPercent]![Percent]

Does anyone have any suggestions?

Thanks

Using Lookup In EXCEL
Please help this beginner !

I have an excel sheet with 8 columns and about 1000 rows.

Third column gives the month of an event.
Fourth Column gives the name of the person
Fifth column gives the nature of the event

I want to read each row, one after the other, till the end of the file and select all rows that has March or April as the month in third column and copy those rows satisfying this condition (i.e. month=March or April) to another sheet in the same file.

The result I expect is to have this second sheet to contain all events took place in March and April. I need to sort this new sheet based on the month. (i.e. All MARCH events will be listed and then APRIL events). Within each month's entries, I would like to get the "nature of the event" column sorted alphabetically.

I got lost in using VLOOKUP command.

Could someone help this beginner, please?