Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Set Default Values in Microsoft Access Table w/ VBA

Posted on 2011-03-12
5
Medium Priority
?
370 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

688 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