I use this code to add "number fields" to a table in backend database
What i don't have in this code, is to set the Indexed property to "Yes (No Duplicates)"
Any suggestions on how to accomplish that?
Please assist - Dim Marker As Integer
-
Dim Rtn As Integer
-
Dim s As String
-
Dim dbBackend As DAO.Database
-
Dim tdf As DAO.TableDef
-
Dim fld As DAO.Field
-
Dim prp As DAO.Property
-
Dim strTableName As String
-
Dim strFieldName As String
-
Dim strFieldCaption As String
-
Dim strFieldNameToCheck As String
-
-
On Error GoTo Err_Section
-
Marker = 1
-
strTableName = "ConsumableSTForms"
-
MsgBox "Click OK to add some different fields to the table '" & strTableName & "'.", , "Back End Updater"
-
'* The full path of the back-end database can be set here
-
gstrBackEndDBName = "C:\pisprg\PisDataV2.accdb"
-
'* Open the back-end database
-
If Trim("" & gstrBackEndDBName) = "" Or Dir(gstrBackEndDBName, vbHidden + vbSystem) = "" Then
-
Beep
-
MsgBox "The back end database '" & gstrBackEndDBName & "' does not exist."
-
MsgBox "Try running the first option in the demo. That will set the variable that holds the back-end database name."
-
GoTo Exit_Section
-
End If
-
Set dbBackend = OpenDatabase(gstrBackEndDBName, True, False)
-
'* Check for existence of new field, and only continue if password entered
-
'***************************************************************************
-
strFieldNameToCheck = "NPItemID"
-
'* get tabledef
-
For Each tdf In CurrentDb.TableDefs
-
If tdf.Name = strTableName Then
-
Exit For
-
End If
-
Next tdf
-
If tdf.Name <> strTableName Then
-
MsgBox "Table '" & strTableName & "' not found. Exiting"
-
GoTo Exit_Section
-
End If
-
For Each fld In tdf.Fields
-
If fld.Name = strFieldNameToCheck Then
-
Exit For
-
End If
-
Next fld
-
If fld Is Nothing Then
-
'* OK to proceed
-
Else
-
If fld.Name = strFieldNameToCheck Then
-
'* Found it - might not want to run again
-
If InputBox( _
-
"The new update has been done before.Do not continue. if there is a problem then add the password to continue anyway (the password is '1234').", _
-
"Back End Updater", "") = "1234" Then
-
Else
-
MsgBox "Password invalid. Exiting."
-
GoTo Exit_Section
-
End If
-
Else
-
'* OK to proceed
-
End If
-
End If
-
-
'* Get tabledef
-
For Each tdf In dbBackend.TableDefs
-
If tdf.Name = strTableName Then
-
Exit For
-
End If
-
Next tdf
-
If tdf.Name <> strTableName Then
-
MsgBox "Table '" & strTableName & "' not found. Exiting"
-
GoTo Exit_Section
-
End If
-
-
Marker = 2
-
'***************************************************
-
strFieldName = "NPItemID"
-
On Error Resume Next
-
tdf.Fields.Delete strFieldName '* Delete it if it already exists
-
Err.clear
-
On Error GoTo Err_Section
-
Set fld = tdf.CreateField(strFieldName, dbDouble) '* Create long integer field and set default value
-
With fld
-
'.DefaultValue = ""
-
.DefaultValue = "0"
-
.Required = False
-
-
End With
-
tdf.Fields.Append fld
-
Set prp = fld.CreateProperty("Caption", dbText, "NPItemID")
-
fld.Properties.Append prp
-
Set prp = fld.CreateProperty("Description", dbText, "NPItemID.")
-
fld.Properties.Append prp
-
CurrentDb().Execute "Update " & strTableName & " set " & strFieldName & " = true;", dbSeeChanges + dbFailOnError
-
-
If Not xg_CreateIndex(tdf, strFieldName, False, False, True) Then Beep: GoTo Exit_Section '* Create index for foreign key field
-
-
MsgBox "Fields added successfully."
-
-
Exit_Section:
-
On Error Resume Next
-
On Error GoTo 0
-
Exit Sub
-
Err_Section:
-
Select Case Err
-
Case Else
-
Beep
-
MsgBox "Error (" & Marker & "), object " & Err.Source & ": " & Err.Number & " - " & Err.Description
-
End Select
-
Err.clear
-
Resume Exit_Section
-
3 4422
If i change line 94 to - If Not xg_CreateIndex(tdf, strFieldName, False, False, True) Then Beep: GoTo Exit_Section '* Create index for foreign key field
then i get "Yes(Duplicates OK)"
I can't get "Yes(No duplicates)
i got it right by changing line 94 to - If Not xg_CreateIndex(tdf, strFieldName, False, True, True) Then Beep: GoTo Exit_Section '* Create index for foreign key field
But the table may not have any data in it
NeoPa 32,557
Recognized Expert Moderator MVP
I'm sure you'd find it can make a lot more sense if you were to use Named parameters Neels. Not just for us, but for you too.
Sign in to post your reply or Sign up for a free account.
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...
|
by: Mike |
last post by:
Hi,
I have created a collection of a custom class. Everything works fine (can add these items in a combo from withing a VB.NET application, for instance), but when looking at the collection from a...
|
by: Mike |
last post by:
Hi,
I have created a collection of a custom class. Everything works fine (can add these items in a combo from within a VB.NET application, for instance), but when looking at the collection from a...
|
by: Ioannis Vranos |
last post by:
Fellows there is probably a serious implementation bug of C++/CLI
indexed property in VC++ 2005, it looks like it is implemented the
opposite way than the C++/CLI draft says!
At first the...
|
by: Mike |
last post by:
Hi,
I have created a collection of a custom class. Everything works fine (can add these items in a combo from within a VB.NET application, for instance), but when looking at the collection from a...
| |
by: Altman |
last post by:
I would like to use an indexed property to access my private array of user
controls. If I don't use an indexed property, the property will show up in
my Properties Window during development. Is...
|
by: Ramani Kurella |
last post by:
Hello,
Iam trying to send x.509 certificate of the client to sign a soap message
request and encrypt it with the server's x.509 certificate in a secure
conversation policy based. I somehow...
|
by: Michael Matteson |
last post by:
I have two classes. Class
A and Class B. I give class A 5 properties
int prop1(){}
int prop2(){}
int prop3(){}
int prop4(){}
classB prop5(){}
what i would like to do is to create a 5th...
|
by: Jim Devenish |
last post by:
I wish to copy a table on a SQL Server backend to a table on an Access
(.mdb) front end in the simplest way.
I have the following to get the recordset but am seeking something
easier.
Dim...
|
by: saudamini |
last post by:
i am trying to deserlize XML against the class which has this an indexed property, the deserializer just skips this indexed property, it's not setting it.
I remember reading somewhere XML...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
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,...
|
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...
|
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,...
|
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...
|
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...
| |
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |