Solved

Setting default values for a new record

Posted on 2011-09-10
7
347 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 500 total points
Comment Utility
...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
Comment Utility
Thanks Jeff, I'm working on it.
0
 

Author Closing Comment

by:yddadsjd95
Comment Utility
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
Comment Utility
ok
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

771 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

10 Experts available now in Live!

Get 1:1 Help Now