Improve company productivity with a Business Account.Sign Up

x
?
Solved

Setting default values for a new record

Posted on 2011-09-10
7
Medium Priority
?
357 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 2000 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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 2000 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

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Usually, rounding is performed by some power of 10 - to thousands, hundreds, tens, or integer - or to one, two, or more decimals. But rounding can also be done to a power of two, say, 16 or 64, or 1/32 or 1/1024, even for extreme values.
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 …
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

606 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