473,574 Members | 17,716 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Change Table Field Lookup Properties in Code?

Hello All,

Is it possible to change table field lookup properties in code?

I've been able to change other field properties in code, however so far
no luck with field lookup properties. What I've done for test purposes
is use a text input file for the table field lookup properties. I
thought that I'd start first by just changing the 'Display Control'
property. Thanks to Allen Browne for some ideals per

See code and input below. Hopefully wrapping won't mess everything up.

Any ideals? Thanks!

=============== ======>Begin Code>========== =============== ==========>
Sub OpenTxtTblPropF ile2()
Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const TristateUseDefa ult = -2, TristateTrue = -1, TristateFalse = 0
Dim fs, f, ts, s
Dim lngNbrRec As Long, lngLnLength As Long, lngTilde1 As Long, _
lngTilde2 As Long, lngTilde3 As Long, lngTilde4 As Long, _
lngTilde5 As Long, lngTilde6 As Long, lngTilde7 As Long, _
lngTilde8 As Long, lngTilde9 As Long, lngTilde10 As Long, _
lngTilde11 As Long
Dim strLn As String, strTbl As String, strFld As String, _
strDispCntl As String, strRwSrcTyp As String, strRwSrc As
String, _
strBndCol As String, strColCnt As String, strColHds As String, _
strColWidths As String, strLstRw As String, strLstWdth As
String, _
strLmtToLst As String

Dim strErrMsg As String

Dim db As DAO.Database
Dim tbl As DAO.TableDef
Dim fld As DAO.Field
Dim prp As DAO.Property

Set fs = CreateObject("S cripting.FileSy stemObject")
Set f = fs.GetFile("X:\ My Documents\FileR ef\Access\SQL Access to SQL
Server\Export\N WLookupProperti es.txt")
Set ts = f.OpenAsTextStr eam(ForReading, TristateUseDefa ult)

Set db = CurrentDb()

Do While ts.AtEndOfStrea m <> True
lngNbrRec = lngNbrRec + 1
strLn = ts.ReadLine
lngLnLength = Len(strLn)
lngTilde1 = 0
lngTilde2 = 0
lngTilde3 = 0
lngTilde4 = 0
lngTilde5 = 0
lngTilde6 = 0
lngTilde7 = 0
lngTilde8 = 0
lngTilde9 = 0
lngTilde10 = 0
lngTilde11 = 0
lngTilde1 = InStr(1, strLn, Chr(126), vbTextCompare)
lngTilde2 = InStr(lngTilde1 + 1, strLn, Chr(126), vbTextCompare)
lngTilde3 = InStr(lngTilde2 + 1, strLn, Chr(126), vbTextCompare)
lngTilde4 = InStr(lngTilde3 + 1, strLn, Chr(126), vbTextCompare)
lngTilde5 = InStr(lngTilde4 + 1, strLn, Chr(126), vbTextCompare)
lngTilde6 = InStr(lngTilde5 + 1, strLn, Chr(126), vbTextCompare)
lngTilde7 = InStr(lngTilde6 + 1, strLn, Chr(126), vbTextCompare)
lngTilde8 = InStr(lngTilde7 + 1, strLn, Chr(126), vbTextCompare)
lngTilde9 = InStr(lngTilde8 + 1, strLn, Chr(126), vbTextCompare)
lngTilde10 = InStr(lngTilde9 + 1, strLn, Chr(126),
lngTilde11 = InStr(lngTilde1 0 + 1, strLn, Chr(126),

' Debug.Print strLn
' Debug.Print "Line Length: "; lngLnLength
' Debug.Print "1st, 2nd, 3rd, 4th Tilde Postn: "; lngTilde1 & " "
& _
' lngTilde2 & " " & lngTilde3 & " " & lngTilde4
' Debug.Print "5th, 6th, 7th, 8th Tilde Postn: "; lngTilde5 & " "
& _
' lngTilde6 & " " & lngTilde7 & " " & lngTilde8
' Debug.Print "9th, 10th, 11th Tilde Postn: "; lngTilde9 & " " &
' lngTilde10 & " " & lngTilde11

strTbl = Mid(strLn, 2, lngTilde1 - 3)
strFld = Mid(strLn, lngTilde1 + 2, lngTilde2 - lngTilde1 - 3)
strDispCntl = Mid(strLn, lngTilde2 + 2, lngTilde3 - lngTilde2 -
strRwSrcTyp = Mid(strLn, lngTilde3 + 2, lngTilde4 - lngTilde3 -
strRwSrc = Mid(strLn, lngTilde4 + 2, lngTilde5 - lngTilde4 - 3)
strBndCol = Mid(strLn, lngTilde5 + 2, lngTilde6 - lngTilde5 - 3)
strColCnt = Mid(strLn, lngTilde6 + 2, lngTilde7 - lngTilde6 - 3)
strColHds = Mid(strLn, lngTilde7 + 2, lngTilde8 - lngTilde7 - 3)
strColWidths = Mid(strLn, lngTilde8 + 2, lngTilde9 - lngTilde8 -
strLstRw = Mid(strLn, lngTilde9 + 2, lngTilde10 - lngTilde9 - 3)
strLstWdth = Mid(strLn, lngTilde10 + 2, lngTilde11 - lngTilde10
- 3)
strLmtToLst = Mid(strLn, lngTilde11 + 2, lngLnLength -
lngTilde11 - 2)

' Debug.Print strLn
Debug.Print "Table: "; strTbl
Debug.Print "Field: "; strFld
Debug.Print "Display Control: "; strDispCntl
' Debug.Print "Row Source Type: "; strRwSrcTyp
' Debug.Print "Row Source: "; strRwSrc
' Debug.Print "Bound Column: "; strBndCol
' Debug.Print "Column Count: "; strColCnt
' Debug.Print "Column Heads: "; strColHds
' Debug.Print "Column Widths: "; strColWidths
' Debug.Print "Lists Rows: "; strLstRw
' Debug.Print "List Width: "; strLstWdth
' Debug.Print "Limit To List: "; strLmtToLst
Set tbl = db.TableDefs(st rTbl)
Set fld = tbl.Fields(strF ld)
Call SetPropertyDAO( fld, "DisplayControl ", dbText, strDispCntl,
Debug.Print lngNbrRec


Set prp = Nothing
Set fld = Nothing
Set tbl = Nothing
Set db = Nothing
Application.Ref reshDatabaseWin dow
End Sub
Function SetPropertyDAO( obj As Object, strPropertyName As String, _
intType As Integer, varValue As Variant, _
Optional strErrMsg As String) As Boolean
On Error GoTo ErrHandler
'Purpose: Set a property for an object, creating if necessary.
'Arguments: obj = the object whose property should be set.
' strPropertyName = the name of the property to set.
' intType = the type of property (needed for creating)
' varValue = the value to set this property to.
' strErrMsg = string to append any error message to.

If HasProperty(obj , strPropertyName ) Then
obj.Properties( strPropertyName ) = varValue
obj.Properties. Append obj.CreatePrope rty(strProperty Name,
intType, varValue)
End If

SetPropertyDAO = True

Exit Function

strErrMsg = strErrMsg & obj.name & "." & strPropertyName & " not set
to " & _
varValue & ". Error " & Err.Number & " - " & Err.Description &
Resume ExitHandler
End Function
Public Function HasProperty(obj As Object, strPropName As String) _
As Boolean
'Purpose: Return true if the object has the property.
Dim varDummy As Variant

On Error Resume Next
varDummy = obj.Properties( strPropName)
HasProperty = (Err.Number = 0)
End Function
=============== ======<End Code<========== =============== ============<
=============== ======>Text File Input>========= =============== ======>
"Employees"~"Ti tleOfCourtesy"~ "Combo Box"~"Value
List"~"Dr.;Mr.; Miss;Mrs.;Ms."~ "1"~"1"~"No"~"" ~"8"~"Auto"~"No "
"Employees"~"Re portsTo"~"Combo Box"~"Table/Query"~"SELECT
Employees.Emplo yeeID, [LastName] & ", " & [FirstName] AS Name FROM
Employees ORDER BY Employees.LastN ame, Employees.First Name;
"~"1"~"2"~"No"~ "0""~"8"~"Auto" ~"Yes"
"Order Details"~"Produ ctID"~"Combo Box"~"Table/Query"~"SELECT
[ProductID], [ProductName] FROM Products ORDER BY [ProductName];
"~"1"~"2"~"No"~ "0""~"8"~"Auto" ~"Yes"
"Orders"~"Custo merID"~"Combo Box"~"Table/Query"~"SELECT [CustomerID],
[CompanyName] FROM Customers ORDER BY [CompanyName];
"~"1"~"2"~"No"~ "0""~"8"~"Auto" ~"Yes"
"Orders"~"Emplo yeeID"~"Combo Box"~"Table/Query"~"SELECT
Employees.Emplo yeeID, [LastName] & ", " & [FirstName] AS Name FROM
Employees ORDER BY Employees.LastN ame, Employees.First Name;
"~"1"~"2"~"No"~ "0""~"8"~"Auto" ~"Yes"
"Orders"~"ShipV ia"~"Combo Box"~"Table/Query"~"SELECT [ShipperID],
[CompanyName] FROM Shippers ORDER BY [CompanyName];
"~"1"~"2"~"No"~ "0""~"8"~"Auto" ~"Yes"
"Products"~"Sup plierID"~"Combo Box"~"Table/Query"~"SELECT [SupplierID],
[CompanyName] FROM Suppliers ORDER BY [CompanyName];
"~"1"~"2"~"No"~ "0""~"8"~"Auto" ~"Yes"
"Products"~"Cat egoryID"~"Combo Box"~"Table/Query"~"SELECT [CategoryID],
[CategoryName] FROM Categories ORDER BY [CategoryName];
"~"1"~"2"~"No"~ "0""~"8"~"Auto" ~"Yes"
=============== ======<Text File Input<========= =============== ======<

Greg Strong
Mar 30 '06 #1
2 7971
"Greg Strong" wrote
Is it possible to change table field lookup
properties in code?

My advice to you is, as you are capable of writing code, you should not be
using Lookup Fields. They are helpful only to novice end users, in datasheet
view. When those novices advance just a little to using queries, we are
going to hear from them in the newsgroups, because those Lookup Fields will
turn out to cause more trouble than they were ever worth to the novice in
his/her datasheet days.

The Boys and Girls in Redmond, in their wisdom, implemented this feature
with violates relational design principles. Violating relational design
principles in what is supposed to be a relational database is A Very Bad

Larry Linson
Microsoft Access MVP

Mar 30 '06 #2
On Thu, 30 Mar 2006 05:18:08 GMT, "Larry Linson" <bo*****@localh ost.not>
The Boys and Girls in Redmond, in their wisdom, implemented this feature
with violates relational design principles. Violating relational design
principles in what is supposed to be a relational database is A Very Bad


To be honest I was just trying to complete rebuilding NorthWind tables
from scratch. I use a working copy of NW for test purposes. I started
with DDL in code, and had to make some other changes in code that was
not capable in DDL. The last piece was to setup the lookup properties. I
suppose I could just import the tables from a fresh copy of NW. I
believe the lookups would exist this way.



Greg Strong
Mar 30 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

by: Vladimir | last post by:
Hello, I have a table in MS Access database. It has one field (with BYTE datatype) that has several properties set in Lookup tab of table Design View. Display Control = Combo Box. Row Source Type = Value List. Row Source = "1; "Above"; 2; "Below"; 3; "Equal"". When I try to SELECT <field> FROM <table> in my C++ application through ADO, I...
by: Old Timer | last post by:
I wish to type in a number in my "Code" field, for instance 1060, I then wish the number 1060 to trigger an event that will fill in the next field (township field) For instance, 1060 brings up and fills in name "Spencer Tp". I have 30 townships,2 cities, 6 villages and identifiers consisting 38 separate code numbers assigned by our county...
by: Randy | last post by:
Access= 2002 I'm NOT a Programmer, but I have used VB in the past to do some things ( Spaghetti Code King) so I have some understanding of Coding I need to replace a text field (teacher) in Table (attendance) with a lookup fileld. the lookup field will point to a newly created table (teachers) the original DB was never intended to do...
by: sparks | last post by:
I was copying fields from one table to another. IF the var name starts with milk I change it to egg and create it in the destination table. It works fine but I want to copy the description as well. Short version :) For Each fld In tdf.Fields pos = InStr(fld.Name, "milk") If pos > 0 Then
by: jbsfe | last post by:
I have "Spilt" my database and the "lookup" and "seek" methods that previously worked, no longer do. I have learnd from reviewing the posts that the "lookup" and "Seek" methods cannot be used on linked tables and that you cannot set an "index" for a linked table. What I don't know how to do, is revise my code to complete the same tasks as...
by: Senna_Rettop | last post by:
Hello, I'm new at Access and ran into a problem. I have a table with a field for customer's names. I want to make a lookup field out of the names by linking it to a table that holds all the customer's names, adresses, phone numbers, ect. I made a form with a name field and would like to be able to lookup the name, click it and the have that...
by: Ryan | last post by:
I have a form that contains a field that is a Foreign Key (UserID) I want this control to have a "label" appearance but I want it to act as a lookup table (SelectedValue = FKID, SelectedText = "Joe Blow"). Using a ComboBox control is the only way I've found to do this (and its actually really easy) but I haven't found a way to disable...
by: Martin Arvidsson | last post by:
Hi! I have used the SqlCommand and DataReader to read data to a DataSet This dataset is the used to set the properties; DataMember and DataSource of the DataGridView. Now what i want to do is to go to a specific row of the DataGridView. I have tried to use a BindingSource to find the value and the issue a position, but it doesn't work
by: Paul H | last post by:
Suppose I have a table called tblPeople and I want a field to illustrate whether each person prefers cats or dogs. I could do it one of three ways. 1. A plain text field Create a text field in tblPeople called PreferredPet. 2. A lookup field that stores text values. Create a text field in tblPeople called PreferredPetID and use it to...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.