how to set field properties in VBA

I am creating (successfully ) lots of tables with VBA.
I need to be able to set the property called "Required" to False or No.
Is there a command to do this?

Post your answer or comment

comments powered by Disqus
Hello all.
I have a couple of doubts.
first is how to set page margins using VBA code.
second is how to open report in invisiblw mode in design view.

i am using Access 2000

thank you in advance.


How to Suppress a keystroke in VBA.

I have a textbox. When the user hits the plus sign (on the numeric keypad) , I would like to both
(1) Prevent the keystroke from falling AND
(2) Tab the user backward one control in the tab order.

I'm not sure how to do either 1 or 2 as yet. I realize it will be the keypress event or keydown event, but I don't know the syntax.

I am creating a table on the fly, and need for external reasons to format the Double field into STandard with 2 decimals. The code fails with Invalid operation.

What have I missed?

I have used the SetFieldProperty elsewhere on an existing table, and it worked just fine.

	Dim tbl As DAO.TableDef
Dim fld As DAO.Field
Dim fldprop As DAO.Property

    If TableExists("tblKPI") Then
        CurrentDb.TableDefs.Delete ("tblKPI")
    End If
    Set tbl = CurrentDb.CreateTableDef("tblKPI")
    Set fld = tbl.CreateField("KPI", dbText)
    tbl.Fields.Append fld
    Set fld = tbl.CreateField("SOurce", dbText)
    tbl.Fields.Append fld

    Dim KortNavn As String
    Dim myID As Long
    While Not rstSource.EOF
        myID = rstSource!StamdataID
        KortNavn = DLookup("ShortName", "tblBasics", "BasicsID=" & myID)
        Set fld = tbl.CreateField(ShortName, dbDouble)
        SetFieldProperty fld, "Format", dbText, "Standard"
        SetFieldProperty fld, "DecimalPlaces", dbInteger, 4
        tbl.Fields.Append fld


    CurrentDb.TableDefs.Append tbl

Public Sub SetFieldProperty(ByVal fld As DAO.Field, ByVal strPropertyName As String, ByVal iDataType As DAO.DataTypeEnum,
ByVal vValue As Variant)
    Dim prp As DAO.Property

    Set prp = Nothing

    On Error Resume Next
    Set prp = fld.Properties(strPropertyName)
    On Error GoTo 0

    If prp Is Nothing Then
        Set prp = fld.CreateProperty(strPropertyName, iDataType, vValue)
        fld.Properties.Append prp 'this gives Invalid operation
        prp.Value = vValue
    End If
End Sub

Am now creating a form in ms access, but I got stucked...
I would use this form for rather reporting data (not for adding data into the database).The form is linked to one table only (I have imported the database from excel), containing columns like customer name, customer category, month, product name sold, volume sold in qty, volume sold in €....etc.
What I would like to get with the help of this form is the following:
-first I select from eg: customer category X
-in the field, "customer name" only those customerts are visible, which belong to customer category X, ----> I select customer A
-than, in field "product name" only those items are visible, what "customer A" has purchased, and I choose "product 1"
after I set up all filters I wanted in all fields, press the buttom eg "export to excel", and all data (based on the selections were done in previous steps) will be loaded into a new excel file.

What I do not know how to match one field with the other, how to set up the condition if Customer X is filtered, only relevant products will be indicated.
At the moment despite of selecting "Customer category X", all existing customer will be shown in "customer name", not only those ones have customer category X in the table.
I would appreciate any help!

Thanks in advance!

I have had a great deal of difficulty dealing with numbers in access. I was recently entering flexibility numbers of students ie 9.0, 11.5, 4.5, 8.0. I was entering it as text initially, but I had to average these for right and left leg to a single value. I went back and reset data type to number, and set the set the properties to long integer, fixed, with one decimal space. I did a caluculated field in an query to calculate the average. In beginning to enter data again for a new school this am, I noticed that I had 500 kids whose flexibility values had changed, all rounded off to a whole number, with a .0 following all numbers. Could anyone explain how I need to set field properties so I can keep the .5's for accuracy, average the right and left leg, and still be able to sort the numbers Z to A accurately. Many times in the past I have to put a placeholder 0 in front of numbers so they will sort accurately. I am tired of having to renter data, because my settings were wrong, and Access has automatically rounded off.

I have code that I would like to set a variable in an open form from another open form. I get an error when using the code pasted below:

"Application Defined or Object Defined Error.."

Can someone tell me if you can set a variable dim'd in another form and if so what the syntax is?

This part of the code is where it is erring out:

frm.str_entry_type = str_fra_val_proc_frm

I want the variable that is dim'd in the frm_pr_proc_sel_req to be set to the value of the variable in the current open form (named frm_entry_type)


Dim frm As Form
Set frm = Forms("frm_pr_proc_sel_req")
Dim str_msg_1 As String
Dim str_msg_2 As String
Dim str_fra_val As String
Dim str_fra_val_proc_frm As String 'for use in setting the value of the form: frm_pr_proc_sel_req varible so can decide if requester wants se or pr record..

If Me.fra_entry_type.Value = 1 Then
str_fra_val = "Profile Entry"
str_fra_val_proc_frm = "pr"
ElseIf Me.fra_entry_type.Value = 2 Then
str_fra_val = "Special Exception Entry"
str_fra_val_proc_frm = "se"
End If

str_msg_1 = "You have selected " & vbCrLf & vbCrLf & UCase(str_fra_val) & vbCrLf & vbCrLf _
& "as the Entry Type. If This is correct, click YES. Otherwise click NO and reselect"

str_msg_2 = "Please re-enter a new entry type"

If fcn_confirm(str_msg_1) = True Then
frm.str_entry_type = str_fra_val_proc_frm
MsgBox str_msg_2
Me.fra_entry_type = Null
End If

Function GetColumnNames(strObjectName As String, Optional ShowDetails As Boolean = False) As String 'Returns the names of all columns in the specified table or query
'If ShowDetails = False, the return value is like so:
' ID;Column1;Column2;
'If ShowDetails = True, the return value is like so:
' ID;4;4;Column1;10;100;Column2;4;4;
' In this case,the columns represent the following data:
' Column name ; DAO datatype constant:
' (dbLong = 4, dbText = 10, etc) ; Max bytes per column
With WizHook
WizHook.Key = 51488399 'This is an internal Wizhook key.
'It only needs to be given once per
'user session, but most Wizhook
'functions won't work without it.
If ShowDetails Then
GetColumnNames = .GetInfoForColumns(strObjectName) 'where strObjectName is the table name passed to the function
GetColumnNames = .GetColumns(strObjectName) 'where strObjectName is the table name passed to the function
End If
End With
MsgBox GetColumnNames
End Function

Note: I found this code posted on the web so use at your own risk. There are also other methods to get field names in a table versus the above method.

WizHook is an internal undocumented library for Access wizards in Access 2000 and later.

For more info:

I need to Set Properties in combo box in the code behind(VBA) and get the key field value in VBA. I have created a combo box in my form and set the properties as follows.

Combo87.ControlSource = "CriteriaValue1"
Combo1.ColumnWidths = "0,1"
Combo1.RowSourceType = "Table/Query"
Combo1.RowSource = " SELECT DISTINCT ComponentData.Criteria_1_Code, Criteria.tCriteriaName " + _
" FROM Criteria " + _
" WHERE Criteria.nIndicatorId= " + CStr(cmbIndicator.Value) + " "

I wanted to send a parameter value to my query which i couldn't do it in the design view combo box property' RowSourse'. So i did that in the code behind and it displays the values of "tCriteriaName" in the combo box.

i want to display "tCriteriaName (Text Field Type) " in the combo box but when the record is adding to the database value should be "Criteria_1_Code (Number Field Type)" of the "tCriteriaName (Text Field Type) ".

when im going to add the record the system won't allow the record to be added in the database, becos of the invalid field type.

at the moment im stuck with this thing. so can anyone pls help me and give me an answer....? lt wud be really appreciated.

This is a very basic thing however I have not used VBA in 5 years. I have an access form that i would like to use a command button on to set the sheet property from editable and uneditable by clicking the button. I feel like this should only be a few lines of code. any help would be much appreciated.

Is anyone out there able to tell me how to change the order of fields in a table structure using VBA?

I'm inserting a field into a table, but it inserts at the end, and I then want to move it to being the first field.
I know that I can re-arrange everything with a query, but would prefer to modify the table.




I'd like to create a table using VBA where one of the fields is a number datataype, the fieldsize property is double.

	.Fields.Append .CreateField("LENGTH", dbDouble)

However, I'd like tthat that, when values are inserted in this tablefield, 7 decimal places are displayed. I know you can set it manual in the design view of the table (in the tab general - decimal places), but i'd like to do this automatically when creating the table?

In the help I found under DecimalPlaces Property:
"Note You can set this property for text boxes and combo boxes by using the control's property sheet and for table fields by using the table's property sheet. You can also set this property in the Field Properties property sheet in query Design view"

Does that means it can't be set while creating the table?

Thanks for any help!

What I am trying to do is create a VBA Function which takes a form as a parameter and then sets certain properties on that form, which Im thinking could either be run on the Form Open event or when actually opening the form (so creating another function which performs the property updates and then opens the form).
So something like CSS for Forms (for anyone who isn't familiar with CSS, it is a way for web developers to set style properties for their web pages and if they make one change to the CSS all of the pages incorporate that change).

I have been looking into it and from what I have found the only way of doing this is have the code which opens the form open the form in design view, make the appropriate changes, save it and then open the form normally.
Although this would work, it does mean that the Access Database cannot be made into an MDE as once it is in MDE form you cannot open forms in design view.

I was wondering whether anyone knows of anyway that it could be done so that it would allow for the Access Project to be made into an MDE.

Hi all,

My project requires writing a VBA program to update our customer database structure, including changing the "Allow Zero Length" property of a column and also change Input Mask for that column to "PASSWORD".

I know how to set the "Allow Zero length" property for a column but I have researched everywhere but could not find a way to set the "Input Mask" property for that column.

Here is my code:

Set objDB = CreateObject("ADODB.Connection")
objDB.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & installFolder & "MyDB.mdb;"

Dim cat As Catalog
Dim tbl As Table
Set cat = CreateObject("ADOX.Catalog")
Set cat.ActiveConnection = objDB

Set tbl = cat.Tables("MyTB")
tbl.Columns("Mycol").Properties("Jet OLEDB:Allow Zero Length") = True
tbl.Columns("Mycol").Properties("Input Mask") = "PASSWORD"

Set tbl = Nothing
Set cat = Nothing

Set objDB = Nothing

The tbl.Columns("Mycol").Properties("Input Mask") = "PASSWORD" does not work when I run the program to update my db.

My question is how to set that column (currently type Text)'s Input mask to "PASSWORD" programmatically?

Any help would be very appreciated!


Maybe it's the day's 'brain drain', but I need to set a criteria in a query whereby it will select answers in a field that are a specific number of characters in length.
i.e., answers that are 5 digits long (without knowing any of the digits)


Is there a way (macro, maybe) to set the cursor position of a date field (that uses an input mask) when my users click on it?

When a user tabs through the fields, she is automatically placed in the first position of the __/__/____ field. However, when a user clicks in the date text box, she is placed where ever she clicked (which is usually not on the far left side of the field).

Any suggestions (other than telling all users to use their Tab buttons instead of their mice) would be appreciated. Thanks!

See attached.
How do I get the enlarged buttons to set Yes/No in a table? Attached Files Enlarged CheckBox Sample1.mdb (352.0 KB, 2 views) Reply With Quote 12-13-2010, 10:53 AM #2 weekend00 I may not be right Windows XP Access 2003 Join Date Aug 2010 Posts 1,296 you may use toggle button for Yes/No field.

if you like checkbox, you may setup a hidden checkbox for the real value. showing an enlarged textbox which looks like a checkbox. in the click event of this textbox, check/uncheck the hidden checkbox as well as change the apperance of textbox.

I already created my query table and I would like to know on how to set a query field [Section] in a combo box in my form. The combo box will used to filter records (based on Section) in my tblEmployee and be able to print that filtered data using my report form.

Please help.

Can anyone tell my how to set the QUOTED_IDENTIFIER property for a SQL Server 2008 R2 to OFF from MS Access using VBA code. I'm using ADODB to pass SQL string to SQL Server. Thanks, Eddie


I have a report that is based on a dynamic query. The query gets it's parameters from a unbounded form. I would like to get "descriptive" text field from my combo box into the report not the stored value in the combo box.

The combobox has TypeID and TypeName columns and TypeID is the stored field. I want to see the TypeName field in my report.

I tried to put a textbox in my report and in the control source i put:
= Forms![frmName]![ControlSource].Text

but this didnt work

Any ideas on how i can do this?

Please help

i have run into the problem where I want to change parameters of a report in vba but it states I need to open the report first, which is no good.

at the moment I am just wanting to do the following

Dim r As Report
Set r = Reports("myReport")

but this only works when the report is open, is there a way to do this without opening the report.

i am actaully wanting to change the RecordSource parameter eventually, in vba without having to open the report.


How to highlight fields in report in access 2007 that contain date based on dates in other fields
e.g. if the current date is in the field is larger by 4 days from other dates in other fields then change the text or background of the current field into red in the report, thanks

How to read the properties of a menu control programatically. Suppose I having a Menu named "Reports" and "Activities","Process" as controls under that menu. How to read the 'tag' properties of that controls. I reckon those controls are called 'custom pop-up controls' in VBA. It is better if someone shows the basics of handling menus. Thank you.

I'm new to access and am currently using access 2003.
I would want to know how to link 2 tables using vba code.
Also, i would like to be able to recognize part of the data i would fill in my form and be able to let access automatically fill 2 more fields within the same table.

for example, if i wrote a place like brossard in city field, i would like access to write montreal and quebec in 2 other fields.

I hope i was clear enough

Thank You,

Hi Forum,

Have had issues with Null's in VBA code and wonder if the following can be used to avoid some of these occurrences.

This Code finds a matching record in a table.

	Private Sub ADPK_AfterUpdate()

    Dim DB As Database, TData As Recordset
    Dim ADPK As Integer
    ADPK = Me.ADPK
        Set DB = DBEngine.Workspaces(0).Databases(0)
        Set TData = DB.OpenRecordset("TblPointsTraded", DB_OPEN_TABLE)
    TData.Index = "MemberADPK"
                TData.Seek "=", ADPK
                If Not TData.NoMatch Then
                    MsgBox TData![MemberADPK]
                End If
End Sub

Can we put this, or similar, ahead of the next code so that if a record exits, the 2nd code is actioned and if no record exits, then GetMemPointsTraded = 0.

	Public Function GetMemPointsTraded(RecordID As Variant) As Long
            'RecordID is the Control Value on the Form or Report
            Dim rst As DAO.Recordset
            Dim sqlString As String
              'Points Earned Single Loan on Report or Form
            sqlString = "SELECT Sum(ClubPointsTraded) AS Traded " & _
                "FROM TblPointsTraded " & _
                "WHERE (((ADPK)=" & RecordID & "));"
    Set rst = CurrentDb.OpenRecordset(sqlString)

    If rst.RecordCount  0 Then
         'Assign SQL result to Variable
         GetMemPointsTraded = rst!Traded
         GetMemPointsTraded = 0
    End If

    Set rst = Nothing
End Function

Appreciate any guidance

Not finding an answer? Try a Google search.