Solved

Set Default Values in Microsoft Access Table w/ VBA

Posted on 2011-03-12
5
345 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

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
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

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
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
Comment Utility
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
Comment Utility
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

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

762 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now