Lookup Field In Access 97

Access 2000 Database Field Lookup
I am a novice user of Access and am creating a simple contacts database. Is there a way to lookup contacts within the database by simply typing in the selected field?  

For example: let's say i am in a database named Cities.mdb.  Once i open the database the first city listed is Anaheim, but i need to get to the city of Los Angeles' info.  Is there a way to type an "L" for Los Angeles in the field where the city's name is listed and automatically be sent to the "L" section of my database?

I know that was a mouthful.  But any help would be greatly appreciated.  Thank you all in advance.

---

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

---

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

---

Retrieve Data From A Lookup Field
I'm using MS Access as my database. I've set the data type of some of my field in a look_up wizard. I've set the look up wizard to look up in other table and others I've inputted the data in the look up wizard manually. Anyone who knows how to retrieve those data and display it on a listbox or textbox or label?

---

Inserting Data Into A Lookup Field In A Database
Hello all,

I have a database table which has relationships with other tables in the database. Some of these relations are with autonumber fields. I know that when you create a lookup field that is linked to a autonumber field in some other table, the lookup field needs to be defined as Number and the FieldSize needs to be Long Integer.

But now my problem is that I have to import data from an out source into this field using a VB code. What is happening is that I get a type mismatch error because the external data is a string and it doesn't match the long integer variable I have for my table. Is it possible to import this string data into a temporary recordset defined as long integer and then copy it to my table field? If so how can it be done?

Is there any other alternative to solve this problem?

Thanks much,

---

TDBGrid (How Can You Sort On A Lookup Field By Clicking On The Columtitle?)
Hi,
 In my project i am usinf TDBGrid. I am loading data by using ADODC Control, its all working well. i wanna sort that data by clicking on the columntitle.
Plz Help me out.

Thanks in Advance

Lalitha.C

---

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.

---

Access Form Lookup
Hi!

I have an Access Database with a table called MFG.

The Table has a few Columns MFG and PartNumber are 2 of them.

The Table will contain multiple MFGs and Part numbers.

What Im trying to do is, in an Access Form, have 2 combo boxes. One that presents a list of grouped MFGs. That much works so far. What I need now is the Part Number lookup to have only data from the MFG that has been selected in the previous step.

So my table will be something like this:

Code:
MFG Part Number
GoodYear 12345
GoodYear 555-123
Sears 12345
Sears 777789
My mfg lookup only has 2 MFGs to select from. Once the MFG is selected how do is get my PartNumber to run a query based on the MFG combo box. Do I need to do this in a module?

Thanks!

---

Lookup A Query In MS Access
I am building a machine that dispenses disposable items to employees. This is like a venidng machine run by a touch screen computer. The user makes a selection of their desired item and through a series of relays and motors the item is dispensed. A database record is kept of the transaction. This includes who took which item on what time and date. I have a query built in Access to count the current stock of the machine. The machine will contain ten of each item and as transactions occur the item is subtracted from stock. The one issue I am having (big one too) is how to disable a selection when the counter reaches zero. Right now when an item is empty it will just put the stock count into a negative. Thoughts? Here is the basic code but it's very simple:


If Text1(1) = 1 Then
MSComm1.PortOpen = True
MSComm1.Output = "sK0" + Chr(13)
MSComm1.Output = "1sk0" + Chr(13)
MSComm1.PortOpen = False
Load frmUtility
Data1.Recordset.AddNew
txtFields(0).SetFocus
Label1.Caption = "~Please Scan Your ID Card~"

---

Lookup Problems In Access Using SQL
I'm pretty new to all this SQL stuff but I've now run into a brick wall which (no matter how hard I try and get through it), still seems to be stronger than me. I was wondering if you could help...

I'm trying to update some records within an Access table with some information from a MS Excel spreadsheet. However, the information in the Access database is stored in code form so I have to convert the info from Excel into code form first then dump it into the Access table. The conversion table is listed within the Access database.

What I could do is export the conversion data from Access to Excel, do a VLOOKUP on the selected cells and import the new fields. However, I thought it would be neater if this process could be done automatically by referring to the conversion table in the Access database to find the correct code and then use this result to populate the correct Access table. I keep getting "Automation errors" after processing the last line in the code below. It doesn't seem to like the WHERE statement. However, if I take out the where statement, it only seems to read the first line in the conversion table. Please help me!

The relevant code is as follows:

Set MakeInactive = New ADODB.Connection
Set XLMovement = Excel.Workbooks.Open("D:Documents and SettingsMabarnettMy DocumentsaccessFeb 04 Movement.xls")
Set XLMoversSheet = XLMovement.Worksheets("Op Unit Movement")

Rownbr = 2

MakeInactive.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:Documents and SettingsMabarnettMy DocumentsaccessDB Authorisations Master v3.mdb;"
MakeInactive.Open
OpUnit = XLMoversSheet.Cells(Rownbr, 4)
OpUnitCodes = "SELECT * FROM [Op Units] WHERE [Op Unit Code] = '" & OpUnit & "'"
OpUnitCode.Open OpUnitCodes, MakeInactive, adOpenDynamic

---

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...

---

Lookup Tables In Access And VB
Here's the scenario:

I am working on VB connecting to an access database

In the access database I have a main table and three lookup tables.
In the main table I have five fields.
1. ID (autonumber)
2. Title (text)
3. AuthorID (number)
4. EditorID (number)
5. PublisherID (number)

The three lookup tables are for the Author, Editor, and Publisher.
Each of them has two fields: ID (number), and Name(Text).
The AuthorID, EditorID, and PublisherID columns on the main table
have been linked/related to the ID column of the respective lookup
table.

In the VB program, I have an ADODC connecting to the main table
and textboxes bound to the ADODC. When I move thru the records
using the ADODC I want the Name(text) to show on the textboxes
and not the ID(number). Of course that's just for the display on the VB.
In the database, the main table should still save the number equivalent
to the displayed text.

I thought of doing this using the Join Table in setting the recordsource
of the ADODC. For instance,
ADODC.Recordsource = "Select * From MAIN Left Join AUTHOR on MAIN.AuthorID = AUTHOR.ID"

But this is only for the Author lookup. Is there a way to form a Join between a main table
and several lookup tables? Or is there a better way to do what I want to accomplish
here?

Any help will be greatly appreciated.

---

Replacing Lookup Fields In MS Access
Why would I want to do that?

Although defining Lookup fields in your tables prevents you having to define Combo Box controls on all your forms, this field display type creates a number of negative issues including: 1) Affecting the performance of your database2) Introducing major development headaches as your project construction progresses. Read the MVPS advice on the topic here: http://www.mvps.org/access/lookupfields.htm


I’ve already used these gremlins in my project!

It’s ok, they can be removed and replaced with regular Textbox display control types, although there are a few effects that you will need to consider if you do this.
1) If you have already based other objects on the output of the Lookup field. e.g. a form with a control based on that field is referred to by another process (macro, VBA procedure).2) If there are form filter operations based on the Lookup field
It’s ok, I want to go ahead with operation doctor!

There are only a couple of ways to skin this pesky puss:1) Manually view each table in your database in design mode, change the properties individually, then save the table.2) Use code to loop though all the tables, zapping the errant fields as it rattles along.

Is she gonna make it doc?

Make a backup! Create a copy of the file before attempting this operation. There may be considerations which we have not covered already. Please let myself or another badged member know via PM if you discover any problems so that we can help others who attempt this later.

Since the properties we’re interested in are peculiar to MS Access only, ADO/ADOX cannot be used to modify the fields for this purpose. The only library which supports the related properties and methods is the “Microsoft DAO Object Library”; you must add this project reference to your application before running the following code.

This procedure can be run from any VB or MS Office application; you need only add the DAO project reference, and then call it from another routine remembering to pass the full file path and name in the single argument.

Good luck!

Code:
'Modifies all non-system tables which have Lookup fields, to display simple textboxes instead
' ARGUMENTS >> DB_PathAndName:= full path and name of database to modify
' DEPENDENCIES >> Project Reference to a 'Microsoft DAO Object Library'

Function Access_TableModifyProperties(DB_PathAndName As String) As Boolean
Dim dbAccess As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim aryProperties As Variant
Dim prpDelete As Variant

'list of DAO extended field properties that can be removed after re-setting the
' 'DisplayControl' property
Const FLD_PROPERTIES As String = "RowSourceType,RowSource,BoundColumn,ColumnCount," _
& "ColumnHeads,ColumnWidths,ListRows,ListWidth,LimitToList"

'the property name which determines the control type for a field
Const PRP_PROPERTY_NAME As String = "DisplayControl"
'Combobox 'DisplayControl' property type value
Const PRP_CONTROL_CBO As Long = 111
'Textbox 'DisplayControl' property type value
Const PRP_CONTROL_TXT As Long = 109

On Error GoTo Error_H

'create an array of properties to delete from the field definitions
aryProperties = Split(FLD_PROPERTIES, ",")

Set dbAccess = DAO.DBEngine.OpenDatabase(DB_PathAndName)
'loop all the tables in the db
For Each tdf In dbAccess.TableDefs

If tdf.Attributes = 0 Then
'loop the fields of the non-system tables
For Each fld In tdf.Fields

'check for fields which display as a ComboBox
If fld.Properties(PRP_PROPERTY_NAME) = PRP_CONTROL_CBO Then
fld.Properties(PRP_PROPERTY_NAME) = PRP_CONTROL_TXT

'remove all dependent properties
With fld.Properties

'ignore any missing properties for the field
On Error Resume Next
For Each prpDelete In aryProperties
.Delete prpDelete
Next prpDelete
'reset the error handler
On Error GoTo Error_H

End With

End If

'jump to this line if the field display control type is not a ComboBox
No_Such_Property:

Next fld
End If

Next tdf
Access_TableModifyProperties = True

Finish:
On Error Resume Next
Erase aryProperties
dbAccess.Close
Set dbAccess = Nothing
Exit Function

Error_H:

'the property does not exist for the current field
If Err.Number = 3270 Then Resume No_Such_Property

'something else went wrong!
Access_TableModifyProperties = False
MsgBox "Error " & Err.Number & ": " & Err.Description _
, vbCritical, "Access_TableModifyProperties"
Resume Finish

End Function

---

Building Access 97 Forms With VBA And SQL Lookup
I'd like some general pointers on this topic for me. What I want to be able to do is have Access draw a form (text input boxes, combo boxes and labels) based on the results of a SQL query. I'm planning to have the possible questions (ie: fields to be displayed on the form) stored on a table and have Access draw the form subject to the type of form chosen (via a SQL query along the lines of SELECT questions FROM questionstable WHERE formtype = complaintform ). The idea of this is to make the form easily updateable - if new fields are needed on a form all I need to do is add a record to the table, rather than manually redesigning the form.

The SQL bit shouldn't present any problems, its the VBA that I need to paint the elements on the form that I'm unsure of.

Is this possible using Access 97 VBA only? Can anyone suggest some good sites (or threads on this site) that I might use as a resource? As I don't even know what I am trying to do is called (!) if anyone can even suggest some keywords for searching on, I'd appreciate it!

Many thanks.

---

Lookup Tables In Access...are They Evil
I was reading on another Database site that in Access, lookup tables used in tables are "evil" and should be avoided at all costs. These lookup tables can also be created using the Lookup Wizard in the table. I haven't seen any negative opinions here, but maybe because no one uses lookup tables and only puts the relationtional ID in the field. Is it because if you are building a huge db, it can make things hairy or what?

Curious

---

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.

---

Access 2000 Lookup Tables
Hi, I wonder if someone can help.  I've created an Access database for a hardware audit at our place of work, and would like to put a VB front end on it.
My main asset table uses lookup tables for things such as processor type, processor speed, memory etc...But when I create a form in VB using the data type to look at my "asset" table, the values come back with the bound "autonumber" from the lookup table, not the 2nd column with the description.

Think that makes sense!! Does anyone have any ideas?

Many Thanks in advance
Luke

---

How To Modify MDB Field Length And ACCESS Field Value Limits Using ADO VB6 Coding.
Hi.

How to modify MDB field length and ACCESS field value limits using ADO VB6 coding.

Where to find an example?

---

Access Select Qry - Join Number Field To Text Field
Hey

I have a select query with 2 tables which I want to inner join table1 field1 data type is number with table2 field1 data type text.

I've tried CInt(table2.field1) in SQL but doesn't like this:

SELECT table1.field1, table2.field1,
FROM table1 LEFT JOIN table2 ON table1.field1 = CInt([table2].[field1])


Any ideas folks?

Cheers,
Mudz

---

Call The Memo Field In Access From Text Field In VB????
Hello
I am running a SQL query in VB to search for anything in the [comments] field defined as Memo in Access database (not text field).

When I run search, I got the error "type mismatch"....

Here is the code:

"WHERE (([DAILY WORK].[Comments]) like ' * " & [txtCommentSearch.Text] & "* ')" & _

(The above code all in one line)


I tried [txtCommentSearch.Memo] but still not working. The txtCommentSearch is the field in VB that I want users to enter any keywords to search.

Can anyone help me, please? Is the wildcard right in VB statement?
I tried to define [txtCommentSearch.text] as variant but still not work. What I done wrong?

Thanks.
Bill

---

Count Field Incorrect Error/ Access 2K ? In Field Name
I have an Access 2K database with a field named [Shipped?]

When I try to do the following.

rs.open "Select * from [FO#/Main] Where [Shipped?] = False

I get the following error.

[Microsoft][ODBC Microsoft Access Driver] Count field incorecct

I assume it has something to do with the question mark.

I do not want to change the name of this field. That would require a lot of rewriting of code and editing many forms in the Access program.

Is there around this error?

I have not found anything in the MS knowledge base to help me with this.

Someone please help me.

Thanks

---

Write A VB Field Entry To An Access Table Field
I have a field called lstQuotedQty on my VB form of which I would like the data input by the user into it to go into a field in an Access table called tblStock and the 3rd field in the table called fldSold...

The code seems to run fine, looks at my A: as though searching for the Access Database, but when I look at the Stock table of course nothing new populates the fldSold as I hoped. This is my code: Also note the user may populate the lstQuotedQty with anywhere from 1-to-an infinite number of different items sold.
I only have fldProductNo, fldPrice, fldAmount, fldSold in my tblStock, and fldSold is the only one I want to populate with this data, I'm not sure how large a zip file we are allowed to upload, but will try to attach the whole thing...I am still in the baby stages here, and everything is pointing to the A: if anyone tests this or has opinions to share I'd appreciate it...

Private Sub Add_Item2_Click()

'==================================
Dim adoDSN As ADODB.Connection
Dim strSQL As String
Dim strConString As String
Dim i%

    strConString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
                    & App.Path & "Inventory.mdb" & ";Persist Security Info=False"
    Set adoDSN = New ADODB.Connection
    adoDSN.Open strConString
    For i = 0 To lstQuotedQty.ListCount - 2
        strSQL = "Insert Into tblStock (fldSold)"
        adoDSN.Execute strSQL
    Next i
    adoDSN.Close
    Set adoDSN = Nothing

End Sub

---

Populating An Access Table Field With Output From VB Field
I have a field called lstQuotedQty on my VB form of which I would like the data input by the user into it to go into a field in an Access table called tblStock and the 3rd field in the table called fldSold...

The code seems to run fine, looks at my A: as though searching for the Access Database, but when I look at the Stock table of course nothing new populates the fldSold as I hoped. This is my code: Also note the user may populate the lstQuotedQty with anywhere from 1-to-an infinite number of different items sold.
I only have fldProductNo, fldPrice, fldAmount, fldSold in my tblStock, and fldSold is the only one I want to populate with this data, I'm not sure how large a zip file we are allowed to upload, but will try to attach the whole thing...I am still in the baby stages here, and everything is pointing to the A: if anyone tests this or has opinions to share I'd appreciate it...

Private Sub Add_Item2_Click()

'==================================
Dim adoDSN As ADODB.Connection
Dim strSQL As String
Dim strConString As String
Dim i%

    strConString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
                    & App.Path & "Inventory.mdb" & ";Persist Security Info=False"
    Set adoDSN = New ADODB.Connection
    adoDSN.Open strConString
    For i = 0 To lstQuotedQty.ListCount - 2
        strSQL = "Insert Into tblStock (fldSold)"
        adoDSN.Execute strSQL
    Next i
    adoDSN.Close
    Set adoDSN = Nothing

End Sub

---

Fill A Dbf Or Access Table Field With A Recordset Field
Hello,
I need to fill out a dbf field with values from a recordset I get through a query.
The target dbf has a "variable" field which have to be filled with values from a recordset.
Any help will be appreciated.
Hayk

---

Field Type... Get Avg() ...of An Access Text Field
i have an access DB that has a text column of "integers" how can i use the avg function or average them with out individually convertiong the values??? any ideas

---

How Using Access VBA To Open .xls As (lookup) Then Open .doc And Merge?
In my Access application I need to open an Excel file as a lookup table. I then need to compare the Access fields with the columns and rows in the spreadsheet. If all the fields match all the cells then the last cell in the row is the name of the Word doc to open.
Once the doc is open Access fields are the merged into the document. This must repeat for each record selected.

GIVEN:
Access database is complete
Excel lookup matrix is complete
Word files are complete with merge stoppers

I need to know how to write VBA as explained above.

Any help, links or other words of wisdom?

---

Access Field
I need to add a new field to an access database table but receive this error when I try to edit it

table 'tblnew' is a linked table with some properties that can't be modified.

Do I need to build a new table (if possible) and rename it to this one or is there a way around this.

---

OLE Field In Access
I try to retrieve path from documents in OLE fields (Access 2000).

Every document is linked.

I can set with VB new documents with this code:

Form_frmMain.OLE1.OLETypeAllowed = acOLELinked
Form_frmMain.OLE1.SourceDoc = "c: est.doc"
Form_frmMain.OLE1.Action = acOLECreateLink

but I cannot get a value in "SourceDoc". The value is "".
This value look to be used only to add documents.

How can I get the path. I have many documents to update and I cannot do it manually.

Thanks in advance for help.
Olivier Monney

---

Access ID Field
I am converting a database from Filemaker Pro 5 to Microsoft Access and I am going to use it with SQL Server. I have 2 fields, First Name and Surname. I want a field ID Code to take the value of the first 4 letters or the first name and the first 3 letters of the surname...does anyone know how this is done. It is probably done really simply ah?

---

How Can I Access This Field
I have an MDB File with 3 fields: ID, Title, And Link

and i got a problem here (I think it's Easy for you )


VB Code:
Private Sub cmdGoName_Click()If rs.State = 1 Then rs.CloseTitletxt = cmbAIOName.Textrs.Open "Select * From Links Where Title= '" & Titletxt & "'", conn, adOpenStatic, adLockReadOnly 'Here is My ErrorGoLink = rs!linkwbrShare.Navigate GoLinkEnd Sub


The Problem is:
If the title Has ( ' ) e.g: vbForum's Experts. i will get an Error.

Hope you understand me

---

Access OLE Field And VB
using my data control, how can i add a picture to my access? e.g. i have these two fields:
data1.recoredset("MembaName")=text1.text
data1.recoredset("MembaPict")=picture1.picture
the second line is not working. the picture filed is an OLE data type in my access file.please, am not using any control bounded to the data control

---

Cannot Access One Field
Hello people,
I have this problom where I am not able to access a field of a table.
Here is my code:

Code:Public Sub FillAuctionTemp()
  
    Dim rsFAT As ADODB.Recordset
    Set rsFAT = New ADODB.Recordset
    
    gstrSQL = "SELECT * FROM tblProducts, tblAuctionsInfo "
    gstrSQL = gstrSQL & "WHERE tblProducts.fldAuctionID = tblAuctionsInfo.fldAuctionID AND fldStatusID = 5"
    
    With rsFAT
        .ActiveConnection = deConn.cnOffice
        .CursorLocation = adUseClient
        .CursorType = adOpenForwardOnly
        .Source = gstrSQL
        .Open
            Do While Not .EOF
                Call AddAuctionTemp((!tblAuctionsInfo.fldAuctionID), (!fldAuctionDate), (!fldAuctionNumber))
                .MoveNext
            Loop
        .Close
    End With
    
End Sub

My problom is in the first parameter of the call. It won't work. I tried to move the "!" but it keeps giving me errors.
The field fldAuctionID is in 2 tables so I know that I have to specify witch one. But I can't figure out how.
Anyone has an idea ?

Thanks in advance.

---

MS Access YES/NO Field
Hi,

I have a YES/NO (checkbox) field in a table in MS Access 2002. when i output the table to excel and the values of the YES/NO field automatically turn to True/False. Is there anyway to keep it as YES/NO? I understand i can have it display as a text box, yes/no, and true/fasle in access table but not it excel. How come its NOT automatically displayed as YES/NO in excel when its data type is YES/NO?

For Example:

this is how it looks in Access:

          Approved
    [X]
    [ ]
    [ ]
    [X]
    [ ]
    [ ]

this is how it looks after outputting into Excel:

             Approved            
    True
    False
    False
    True
    False
    False

this is what i would like to have it as:
            
           Approved            
    Yes
    No
    No
    Yes
    No
    No

---

If Exist A Field In Access
I'm tring to check if exist a field in a db access.
I only need to know if the field "progr" is present on the table......i'm lost!!!!

---

Vb To Update A Field In Access
hey guys
i am using the following code in access to update a field:

Private Sub MakeLoan_Click()
If OnLoan.Value = True Then
MsgBox ("Sorry, Selected Book Is Already On Loan. Please Enter A Different Book Number")
Else: OnLoan.Value = True And MsgBox("Loan Processed! Click 'New Loan' To Continue")
End If

End Sub

The problem is with the line
Else: OnLoan.Value = True And MsgBox("Loan Processed! Click 'New Loan' To Continue"),
but more specifically i think it may be to do with OnLoan.Value = True

I am getting an error saying the field cannot be updated.. Any ideas?

WHat I am trying to do is if the field OnLoan (which is a checkbox) is false I want to update it to true

can anyone see where i have gone wrong?

Thanks In Advance

LEIGH DUFFY

---

Send A Value To Access Field - Possible?
Hi friends,

I am using Word and with VBA I have inserted a userform with a cmdbutton.I need to check whether I can sent a value to a field in an already existing Access table

For example in userform1 when clicking commandbutton1:
I need to check whether the user did a BOLD formatting, if yes sent 1 mark to field named Question 1,

the same for question 2, question 3 etc.

Can anyone be of some help to me pls, Newbie in vb. Thanks Loads.

---

MS Access: DAO / Memo Field
In the Help files I came across this:

Memo
Lengthy text or combinations of text and numbers.
Up to 65,535 characters. (If the Memo field is manipulated through DAO and only text and numbers [not binary data] will be stored in it, then the size of the Memo field is limited by the size of the database.)

So, I have string with 96k characters in it.
The Memo field has like a 65k limit (or close to it).
I NEED to store this IN a Memo Field (unless someone can tell me what other kind of field would hold this?).

How would I do that using DAO?
It says Manipulated through DAO?
I THOUGHT that would mean just a simple DAO.Recordset OPEN rst!FieldName = HUGESTRING or something like that...

Confused? Help if you can?

---

MS Access Field Description
In MS Access, while in the "Design View" of a Table, you have the option to place a note or description for each Field.

Does anyone know if there is a way to have VB look up that value and place it in a Text Box or Label at runtime?

I'm using VB6, ADO & the MS Jet Engine.

Thanks in advance.

GB

---

Renaming A Field In Access
Does anyone know how to rename a field in Access.

---

Determine Field In Access
I need to open an existing Access database and determine the number and the name of the fields.

Is there a method for this?

---

Reducing A Field In Access Through VB
I have a datalist which contains a number of items. Pressions a button called "reduce" is intended to log a few details from the user and then reduce the quantity of a field by 1. Therefore, as an example:

A user selects the entry "HP01" in the datalist. They then press reduce. This takes them to a form where they enter the date, their name and the department they work in. The quantity of the entry that the the datalist selection relates to is now reduced by 1 and their details have been written to a text file.


Code:
Dim strDate As String
Dim strEmployee As String
Dim strDepartment As String
Dim strAddLine As String
Dim strTemp As String

Dim strListItem As String
Dim strQuant As Double

strDate = frmReduce.txtDate.Text
strEmployee = frmReduce.txtEmp.Text
strDepartment = frmReduce.txtDep.Text

strQuant = frmFront.txtQuantity.Text

strListItem = frmFront.lstSupply.SelectedItem

MsgBox strListItem

If strDate <> "" And strEmployee <> "" And strDepartment <> "" Then

frmFront.adoList2.Recordset.Move strListItem
frmFront.adoList2.Recordset!Supply = strQuant - 1
frmFront.adoList2.Recordset.Update
strAddLine = strDate & " / " & strEmployee & " / " & strDepartment
Open "cartridge_log.txt" For Input As #1
strTemp = Input(LOF(1), 1)
Close #1
Open "cartridge_log.txt" For Output As #1
Print #1, strAddLine
Print #1, strTemp
Close #1

frmReduce.Visible = False
frmFront.Visible = True
End If

For whatever reason at home on my copy of Visual Basic the details are written to the text file yet the entry is not reduced and no errors occur. At college I receive the error message "Can't find project or library" relating to the Input command..

What's going on? Anyone able to help? How do I make the database field reduce?

---

Adding New Field In To Access
Hi All

We have a VB program which was written originally in VB5 and then we upgraded to VB6. Our original database was designed a few years ago in Access version 2. It only contains 5 fields. We use a data control to link to our Access file.

My question is:

Through our program is it possible to extend this to 6 fields by using a command code to insert another field which can also be incorporated in the program being already used?

The reason I ask this is that among the people that use the program, some have Access on and some do not.

Any help would be appreciated.

Thanks

Steve

---

200 KB Is Too Much For MEMO Field (MS Access)
Hi!

I have a strange problem with Access database. I tried to paste about 200 KB of text in Memo field, but I got error "Text is too long to be edited". Isn't MEMO field supposed to hold up to 2 GB of data?

Any ideas?

Bostjan

---

Access Field Name Questions
Hello,
I have Access DB and defined field names are combination of lowercases and uppercases (ie. EmployeeLastName). Is it possible to write the simple procedure in VBA to replace all the field names in DB with uppercase letters.
Any help would be greatly appreciated.

---

Access DB Boolean Field
It's has been so long since i created a DAO Table field, can somone refresh me on creating a boolen field. I mean i know how to create one but forgot how to format the "Yes/No". When i create a the field i list 0 or 1, i don't want that, i want the check box.

Thanks.


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

Thank you.

---

Problem With Access Field From VB
I have a very small Access DB, one can download it from http://www.odoms.net/DB, it's called TRIT.mdb
When attempting to query a certain field, the match field, I get an error from VB. If I do the very same query directly in Access, it works fine. I can query all of the other columns just fine from VB. I deleted the field, and inserted a new one and populated it, but still same problem.

Here is a small snippet of my code, which is exactly like the code I used to query the other columns. If I simply change the field name in this sub to another one in the db, it works fine. As you can see I have commented out more complicated queries, and am just doing a very simple one. At my wit's end. Thanks in advance!


Code:
Public Sub PopulateMatchList()

With TRITEst
.cboMatch.Clear
'strsql = "select distinct match from TRIT where model= '" & strModel & _
'"' and rating = '" & strRating & "' and noshafts = " & strShafts
'strsql = "select distinct match from TRIT where model='TITAN 130' and " & _
'"rating='18001S' and noshafts=1"
strsql = "select distinct match from TRIT"
If ExecQRY(strsql, RS) = 0 Then

Do Until RS.EOF

If IsNull(RS.Fields(0)) Then
.cboMatch.AddItem "null"
Else
.cboMatch.AddItem RS.Fields(0)
End If

RS.MoveNext
Loop
RS.Close
.cboMatch.ListIndex = 0
End If
End With

End Sub

Edit by mkoslof: Added vb tags, thanks

---

Add Field To Table In Access!
Hi there

I need to add a field to an Access Table with ADO or ADOX and I can't seem to figure it out. I've been messing with examples found on MSDN, but they're all geared twords adding tables. I can't find anything on adding a field to an existing table.

If someone knows hoe to do this please advise. I'll post some code, but none of it works anyway...


Thanks for looking

Max.


Code:
Private Sub Command1_Click()
Dim myField As String
myField = "Test"

Dim rs As ADODB.Connection, Cat As ADOX.Catalog, objTable As ADOX.Table, Col As ADOX.Column
Set rs = New ADODB.Connection
Set Cat = New ADOX.Catalog
Set objTable = New ADOX.Table
Set Col = New ADOX.Column


rs.Open sConn
Set Cat.ActiveConnection = rs
Col.Name = "Test"
objTable.Columns.Append Col, adInteger

not sure why I posted that. None of it works or is even close. Maybe just to show I've been trying different things and reading different examples.


Cheers!

Edit: I have adInteger as a type in my example, what I actually need is whatever's equivalent to "Text" in Access.

---

SQL For Access - Where Field Is/is Not EMPTY
How can I query where a field is either empty or not empty?

I would normally do...


VB Code:
recordset.Source = "SELECT * FROM table WHERE [FIELD] = 'xyz';"


but obviously in this case i want to do it so it'll bring back the records if the field is zero length and another for if it is not zero length. Anyone know how to do this?

thanks.

---

How Can I Add The Data Of One Field In Access
hello? can someone help me about my codes?
i have an access database
i use loop to retrieve the data from access to flexgrid
my question is how can i add the value of the field?
im very thankful if someone could help me fix this out..
here is my code:
VB Code:
Dim db As DatabaseDim rs As Recordset Private Sub Form_Load()Set db = OpenDatabase(App.Path + "database.mdb")Set rs = db.OpenRecordset("select * from tblsample")ms.TextMatrix(0, 1) = "value"While Not rs.EOFx = x + 1ms.Rows = ms.Rows + 1ms.TextMatrix(x, 1) = rs!Value     rs.MoveNextWendms.TextMatrix(6, 0) = "Total" ' i want to put the total value of data hereEnd Sub

---