Solved

Setting default values for a new record

Posted on 2011-09-10
7
349 Views
Last Modified: 2013-11-27
Great Day, I am creating a job order for an automotive repair shop. When I open an estimate to review it, all of the data has populated the form properly; however, when I create a new estimate, customer name and address and customer vehicle make, model and year, I expected to automatically populate the appropriate fields, but they did not.

Do I need to code the default value for those fields or is there a way for access to automatically do it when a new record is detected?

Thanks in advance,

David
0
Comment
Question by:yddadsjd95
  • 4
  • 3
7 Comments
 

Author Comment

by:yddadsjd95
ID: 36516290
Okay, I've discovered that since I am at a new record, the customer data that I was expecting to be able to load, is not available on the form, so I'll have to get it elsewhere. I suspect that I can pull it from the form that loaded the Job Order, so I'll play with it and give you an update.
0
 

Author Comment

by:yddadsjd95
ID: 36516367
I am unable to get a default value to load when the form opens to a new record. This is the code that I used to test one field

If Me.NewRecord Then
        'Set the defaults
        Forms!FrmJob!frmJobSub.Form!txtStreetAddress.DefaultValue = " Forms!   frmCustomerContactData!StreetAddress" _

End If

I am attempting to use the data that is in the form (frmCustomerContactData) that opened the Estimate/Work Order (frmJobOrder). When I check the Immediate Window, there is a value for txtStreetAddress; however, nothing loads on the form when it opens.

Thanks in advance for your assistance.

r/David

If Me.NewRecord Then
        'Set the defaults
        Forms!FrmJob!frmJobSub.Form!txtStreetAddress.DefaultValue = " Forms!   frmCustomerContactData!StreetAddress" _

End If

Open in new window

0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 36516659
First, lets clear up the terminology....

Technically, a default value is a value that is always there when a New record is created.
(See the help files on "Default Value")

(It can be made to be a "default value" in other circumstances, but this is another discussion)


For example, if 95% of all of your customers will be form Nevada, then "Navada" could be the default value for the "State".

Creating a new work order for the same customer merely requires you to save the CustomerID.
(a strict "default value" is not heeded here)

When the WorkOrder for the existing customer is displayed on the form, simply do something roughly like this in the Form's declaration section:

Private lngpvtCustID As Long

Then, on a button called "New Work Order for existing Customer" put code like this:

    'Store the custID in the
    lngpvtCustID = Me.CustomerID
    DoCmd.GoToRecord , , acNewRec

Finally on the current event of the form do something like this:

'If new record and there is a customer selected
If Me.NewRecord And Not IsNull(lngpvtCustID) Then
    'Look up the cust fields
    Me.txtCustName = DLookup("CustName", "CustomerTable", "CustID=" & lngpvtCustID)
    Me.txtCustAddress = DLookup("CustName", "CustomerTable", "CustID=" & lngpvtCustID)
    '...etc
    'Save these entries
    DoCmd.RunCommand acCmdSaveRecord
    'Clear the variable
    lngpvtCustID = Null
end if


I am sure that based on your experience here you can get this working in your database...


JeffCoachman
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 500 total points
ID: 36516670
...small tweak:

'If new record and there is a customer selected
If Me.NewRecord And Not IsNull(lngpvtCustID) Then
    me.txtCustID=lngpvtCustID
    'Look up the cust fields
    Me.txtCustName = DLookup("CustName", "CustomerTable", "CustID=" & lngpvtCustID)
    Me.txtCustAddress = DLookup("CustName", "CustomerTable", "CustID=" & lngpvtCustID)
    '...etc
    'Save these entries
    DoCmd.RunCommand acCmdSaveRecord
    'Clear the variable
    lngpvtCustID = Null
end if

0
 

Author Comment

by:yddadsjd95
ID: 36516941
Thanks Jeff, I'm working on it.
0
 

Author Closing Comment

by:yddadsjd95
ID: 36522793
Thank you Jeff, I worked on this for quite some time before I was able to get it to work. Thanks a million. However, now I am running into another issue where when I open the form Access is asking for a parameter, which I will address under a new question.

Have a great day and an even finer day tomorrow.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36524176
ok
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
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…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

773 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