Solved

Set Default Values in Microsoft Access Table w/ VBA

Posted on 2011-03-12
5
353 Views
Last Modified: 2012-05-11
With a Microsoft Access Form, I have 4 text boxes for the user to enter their Name, Department, Title, and Manager Name.  
Using VBA, I want to take each of those 4 values and set the default values of 4 fields in a table.  The form that opens up is not bound to the table for which I want to set up the default values for.
0
Comment
Question by:vfinato
  • 3
5 Comments
 
LVL 75
ID: 35118819
Form is not bound ?  Just curious why?

Anyway ... you can use DLookup() to do this ... and probably in the Form Load event, or possibly from a New Record button ...

Me.Text1 = DLookup("[Field1]","[YourTableName]", <optionalCriteriaHere>)

mx
0
 
LVL 75
ID: 35118834
OR  ... just


Me.Text1 = "YourDepartmentName"

and so on ... if you just want to hard code these values.  The first post 'looks up' values from a table.

mx
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 35121173
Do you mean you want to set the Default PROPERTY of Fields at table level? If so, you can do this:

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set dbs = CurrentDb
Set tdf = dbs.TableDefs("YourTableDef")
Set fld = tdf.Fields("YourField")

fld.DefaultValue = "Your Value"
0
 

Author Closing Comment

by:vfinato
ID: 35148140
Actually, I am using ADO (which I forgot to mention in the original post).  Here is a snippet of the ADO/SQL code that I used to accomplish this task.

        Dim str1 As String
        Dim strDblQuote1 As String
        Dim strDblQuote2 As String
        strDblQuote1 = """"
        strDblQuote2 = """"

str1 = "ALTER TABLE StartingNumberTable ALTER COLUMN [LeadAuditor] CHAR (50) Default " & strDblQuote1 & strLeadAuditor & strDblQuote1
        cnCon.Execute str1
0
 
LVL 75
ID: 35149890
vfinato
For future reference, next time try to explain a little better what you are trying to do ... and more importantly, please take the time to response to Expert comments.   I took the time to give you two solutions and you gave No response.

mx
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question