Visual Basic :: How To Write A Table Lookup Function In VBA?

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.

Lookup Table Speed
I had always thought that lookup tables were quicker than the built in Trig functions, and this seems to be bourne out be various games programming websites. However, I can not get my implementation to even the same speed as the builtin VB trig functions.

Here is my lookup code:

Code:
Public Function QSin(ByVal Angle As Single) As Single
If bTrigTablesBuilt Then
QSin = tblSin((Angle * TrigAccuracy) 1)
End If
End Function
Angle is in degrees and TrigAccuracy specifies the number of decimal places to use (the value itself is a multiplier to bring the angle value up to number number to index the array).

Can anyone see a way to improve the speed of this?

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

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!

A Lookup Function For Access
I'm really fixing to show my ignorance, but, here goes.

All I want to do is use like a DLookUp function, I think, in access.

What I want to do is return the records from one table which do not have a corresponding record in another table. Where (CAN-FIND(tbl1.asset = tbl2.asset)) = Null (or False or whatever)

FOR EACH RECORD IN tbl1
WHERE NOT CAN-FIND(tbl2.assettag = tbl.assettag).

DISPLAY tbl1 RECORD.

An SQL Statement will work as well.

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

Database Function Lookup With Criteria
In Lotus 123 it is possible to directly pass a criteria in a database lookup function, whilst Excel needs a criteria range as parameter.

I.e. In Lotus 123 if I put @dsum(Salesdatabase;"sales";name=b2) I can copy this formula down a long list of names to show total sales for each person.
In Excel, on the other hand, I must use a criteria range consisting of header name and criteria. This makes it very difficult to copy and paste to a long list of names as I have to use a new criteria range for each person.

Is there an easier way in excel ?

I Need To Decide Which One Is Best Lookup/Database Function
I have an Excel Worksheet whit a lot of data, I was using Vlookup to find the data and retrieve it to a report... (I know that probably in this case using a Pivot table could be the best solution, but the users who this Application is made for, don´t like to work with them... so)...

With all the data & formulas my file has a 2MB size, so I´m trying to make it short, I am tryin to use Database Functions like DSUM or DGET, but i´m having troubles with the criteria, cause it has to be adjacent (I mean the columns...)

So thats why I´m asking which one is the best choise?, and if the Database Functions are, how can I make that the range criteria takes the reference of non adjacent columns...?

I´ll Appreciate any sugestions...
Rick

Lookup Function Screws Up After So Many Entries Into DB
Hey,
I have a database, that has member ID in cloloumn 1 (A) and username in coloumn L...i have a system that lookups the username from coloumn L and returns their user ID from coloumn 1 (i know, i should have put the username in coloumn 1 n donea vlookup but it will screw all my other code up ) this methos works fine for about 5 entries, then it suddenly screws up n returns wrong ID's :S, is there a way of doing the same thing in the VBA instead of the lookup function in excel...it would take waayyyy too long to make it work as a Vlookup ...

Thanks for any help, appretiated,

Ross

Calendar Form Lookup How Code Special Function?
hi .. all VB developers.

i have a problem on lookup from w/calendar. for example form1 i have a 4 textbox 4 command buttons and 4 labels for description the 4 textbox the datafield is set as bithdate,datehired,dateresign,endofcontract. the main problem is when the calendar lookup show when i click the cmdSElECT all textbox of form1 they contains a value which is i want to have a date value only the textbox1 so how can i make a special function for this?

Form1:
Private Sub cmdDtBirth_Click()
   frmcalendar.show
End Sub

Calendar lookup code
this is for textbox1 only how about the other textbox how can i make function. i want to know how to make a special function specially on lookup form it is very important in my project i need help....

Private Sub cmdSelect_Click()
With frmEmp2
      .txtemp(24).Text = frmCalendar.Calendar1.Value
      .lblDtBirth.Caption = Format(frmCalendar.Calendar1.Value, "mmm. dd, yyyy")
End With

Thank You and GOD BLESS!!!!!

How To Write The Backup/copying Process From One Table To Another New Table?
2. Sharing file during Physical Inventory Count :


Server
more than three client computer sharing the PI.exe file in server
Clients connect to the PI.exe in server by clicking on the shortcut of PI.exe from their computer

3. PI.mdb database file backup ( tag_master ) :

    Problem in backup:
     * When backup the whole "PI.mdb" database at server site, some user may lock the "PI.mdb" database
        at their client computer, eventhough they have already log out from PI system.
     *    So, lot of data loss after backup . Example, report show 100 item(tag) have been entered before backup,
        but, report show only 50 item after backup.
     *    So, every hours, all the users must restart their client computer before backup activities carried out at
        server. It would be really terrible. Because the backup need to be done every hour in one whole day

    To solve this problem:
     *    User do not need to restart their computer or logout from PI system. They just need to stop the
        the data entry and saving activities only.
     *    Admin just need to inform users stop the data entry and saving activities, and click on the PI system
        to backup automatically. But this time, we want to backup only the tag_master table instead of the
        full PI.mdb database. The concept is just the same as when user key in qty and click "save" to update
        tag_master database

    tag_master table

    The backup process is:
                 *    At the first time, Backup_1 and Backup_2 table are blank
                 *    After 1 hour, copy all the contents in tag_master table into Backup_1
                    table
        ( First hour backup )      *    After second hour, copy all the contents in Backup_1 table into Backup_2
Backup_1 table                    table, then clear all the contents in Backup_1. After that
                     copy all the contents in tag_master table into Backup_1
                    This is to ensure during the copy processing from tag_master table
                    to Backup_1, the Backup_1 table will not loss
                 *    So, at second hour, we have first hour backup in Backup_2 table and
                    second hour backup in Backup_1 table
                 *    At third hour, similarly, clear the contents in Backup_2 table,
                     copy all the contents in Backup_1 table into Backup_2 table, then
Backup_2 table        (Second hour backup)    clear the contents in Backup_1 and copy all the contents from
                    tag_master table into Backup_1
                 *    So, at third hour, we have second hour backup in Backup_2 table and
                     third hour backup in Backup_1 table.

            The programming logic for backup on every hour:
             *    Clear Backup_2 table
              *    Copy the contents in Backup_1 table into Backup_2 table
             *    Clear the Backup_1 table
             *    Copy the contents in tag_master table into Backup_1 table

    Problems:
         How to write the backup/copying process from one table to another new table?
        How to delete all the contents in 1 table?

Please help me if you know how is the backup process coding.
Do you know which reference book/website having this type of backup process procedure, If yes, please inform me.
If you have better idea on this backup problem, please recommend me.
Thanks for your helping, If you do not have any ideas on this problem, I also want to say thanks to you
I am very sorry for any disturb and incovenience caused.

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

Who To Write Outside Of A Table?
hi, once i've created a table from visual basic in a word document, how do i move the insertion point outside of the table?

this is the code i am using

Set w = New Word.Application
w.Documents.Add
w.ActiveDocument.Activate
w.Selection.Tables.Add w.Selection.Range, 9, 2

how do i start writing outside of the table (so that i could create other tables)?

thanks

Write Table In ASP
Here is a pretty table that will display information from a recordset.
Width is the width of the table, Records is the name of the recordset. Chow4now

Function WriteTable(Records, Width)
Dim s
Dim fld
Dim ColWidth

s = "<table border='1' width='" & Width & "' cellspacing='0' cellpadding='0'><b><font face='Arial' size='2'>"

'***write the header
'calulate the width of the fields
ColWidth = Width / Records.Fields.Count

s = s & "<tr>"

For Each fld In Records.Fields
s = s & "<td width='" & CStr(ColWidth) & "' bgcolor='#00FFFF' align='center'>" & fld.Name & "</td>"
Next

s = s & "</tr>"

'write the rows
Do Until Records.EOF
s = s & "<tr>"

For Each fld In Records.Fields

If IsNull(fld.Value) Then
s = s & "<td width='" & CStr(ColWidth) & "' bgcolor='#C0C0C0'> </td>"
Else
s = s & "<td width='" & CStr(ColWidth) & "' bgcolor='#C0C0C0'>" & CStr(fld.Value) & "</td>"
End If

Next

s = s & "</tr>"
Records.MoveNext
Loop

s = s & "</font></b></table>"

WriteTable = s

End Function

Write Fields Into Table
Hi,
I am trying to write some fields into the tblTransactions. I enter the new data into frmTransactions and then it should uptdate the table with the new entered data when I click the save cmdButton. Below is what I have tried but it doesn't update the fields.

'open connection
cnBank.Open
rsTransaction.Open "tblTransactions", cnBank, adOpenDynamic, adLockOptimistic
With rsTransaction
.AddNew
![CustomerID] = glngId
![Date] = Format$(lblDispDate.Caption, "dd/mm/yyyy")
![TransactionType] = "w"
![TransactionAmount] = txtTransactionAmount.Text & ""
.Update
.Close
End With
Set rsTransaction = Nothing

Your help is greatly appreciated
Andonny

VB Can't Write To Foxpro Table???
I have been seeking info on how to write to a foxpro database table.
I was referenced to a microsoft article that had good code but stated that when opening a foxpro table directly ,the table becomes "read only".
This is crazy !!!!!!!!
Is there no way I can write to a foxpro table in VB???
Thanks.
danny.
article address: http://support.microsoft.com/support.../q161/3/07.asp

TO WRITE INTO EXCEL FROM A SQL TABLE
Hi Guys,

I have not worked much using excel through VB. I was using Crystalreports but some time users asking me data into excel sheet. Hence Iwant to generate excel sheet for the code which I passed through VB.

In the excel sheet, I want to add Summary's at the top of the Header andI need to marked the header with Color. If any one has any sample code,please let me know.

Regards
Arun

Write Data From One Table To Another
> hi
>
> I have a table called [monthlydata] to keep all monthly records. it fills
> up frequently. so it's taking more time when retriving data. Table has a
> field called [ValueMonth] with date format(MM/DD/YYYY). I need to search
> the [valuemonth] field every time the progrm starts and extract all the
> records with the value for this field has expired two months for the
> current system date and write to a another table. So it keeps data only
> for past two months.please help with codes.this is bit urgent .
>
> regards
>
> dhanuja
>
>
This email and any attachment hereto is for authorised use by the addressee
only. It may contain confidential or priviledged information and/or
proprietary material. If you are not the intended receipient , this email
and/or any information it contains should not be copied, disclosed,
retained or used by you or any other party, and the email and all its
contents should be promptly deleted fully from your system and the sender
informed. Thank you.

Using ODBC To Write To A Table
Hi,
I am using an OBDC link from one program to write info to an access database, the link is fine - to test this I use the first database to write the info I need, then open up access and look at the table and the correct info is there.
The problem comes when I have the form open and info is written to the table the only way I can get it to refresh is to use a "refresh" button, is there any way I can refresh with out the button ?
I have tried with the timer but I'd perfer if possible to update as soon as info writen to table.
Please help

How Do I Write Data To A Table Using Vb6?
I am new to vb6 and am not sure how to write data to a table using vb6 and an access 97 database. I have a label and a text box, I need to take the caption of the label and write that to a field called Field1, and take the text of the text box and write that to field2. The table name is called Items_Data. Any suggestions would greatly be a appreciated. I have already set the connection string, but that is all I have.

Edited by - John2B on 6/29/2004 1:11:31 PM

VB5 Code To Write To A SQL 2000 Table
I have an urgent issue at my work-place!!!
     We are going through an EDI Implementation and need to insert into a Customer Address Table before we can bring the EDI data into the ERP package.
It was suggested to write a VB code to do so. I am not familiar with VB so I need some help.
The VB code has to be able to accept 22 parameters (i.e. Customer_ID, Customer_Name, Address1, Address2, Address3, City, State, .... ect) to push to the Table.
I have tested my Insert statement in SQL Query Analyzer and everything looks fine, but I need the VB code to do this for me. Here is my Insert / Values statement:

INSERT INTO "dbo"."CUST_ADDRESS"  ( "CUSTOMER_ID", "NAME", "ADDR_1", "ADDR_2", "ADDR_3", "CITY", "STATE", "ZIPCODE",

"COUNTRY", "DEF_SLS_TAX_GRP_ID", "TAX_EXEMPT", "SHIPTO_ID", "ORDER_FILL_RATE", "GENERATE_ASN",

"HOLD_TRANSFER_ASN", "CUSTOMS_DOC_PRINT",  "ACCEPT_830", "ACCEPT_862", "CONSOL_SHIP_LINE",

"PALLET_DETAILS_REQ", "GENERATE_WSA", "HOLD_TRANSFER_WSA" ) VALUES ( 'A200500', 'LTest', 'LAddress Test1',

'LAddress Test2', 'LAddress Test3', 'Carrollton', 'TX', '75006','USA' , NULL, 'N', '2118624474',  0.000000, 'D', 'D', 'D', 'D', 'D', 'N', 'N', 'D', 'D')

RESULT: 0

Any help on this would make my day, it is an extremely urgent issue.
I have no Idea on how to accept these 22 parameters that will be pushed
over to the desired VB code, nor do I know if anything else is required
before I use the Insert / Values statement.

        Thanks in advance,