UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Adding Default Value To Numeric And Date Fields On Form Open, Access 2010    
 
   
NaviChandu
post Feb 16 2017, 12:07 PM
Post#1



Posts: 36
Joined: 19-January 17



Hi All,

I use the below query to INSERT DATA into table.

I am yet to define the mandatory fields on the form.

SO want to define the default values for numeric and date fields On form OPEN so that insert query does not end in error.

Other way i thing would be define it at table level. Please suggest how to set the default value when the form is open.

mysql = "INSERT INTO tblAssignment (Proj_ID, Proj_Code, Proj_Name, Proj_Type, Assigned_ToLoc,"
mysql = mysql & "Assignee_Name,Assignee_Onshore_Name,Plan_Start,Plan_End,Delivery_Status,Per
_Complete,Total_Hours,Modified_Date,Assignee_Notes) VALUES ("
mysql = mysql & Me.Proj_ID & ", "
mysql = mysql & " '" & Me.cobproject.Column(1) & "', "
mysql = mysql & "'" & Me.Project_Name & "', "
mysql = mysql & "'" & Me.Proj_Type & "', "
mysql = mysql & "'" & Me.cobLoc & "', "
mysql = mysql & "'" & Me.cobAssignee_Name & "', "
mysql = mysql & "'" & Me.cobOnshore_Name & "', "
mysql = mysql & "#" & Me.Plan_Start & "#, "
mysql = mysql & "#" & Me.Plan_End & "#, "
mysql = mysql & "'" & Me.cobDeliveryStatus & "', "
mysql = mysql & Me.Percentage_Complete & ", "
mysql = mysql & Me.Total_Hours & ", "
mysql = mysql & "#" & Date & "#, "
mysql = mysql & " '" & Me.Notes & "' )"
Debug.Print mysql
DoCmd.RunSQL (mysql)

Regards,
Naveen N
Go to the top of the page
 
theDBguy
post Feb 16 2017, 12:12 PM
Post#2


Access Wiki and Forums Moderator
Posts: 68,695
Joined: 19-June 07
From: SoCal, USA


Hi,

Default values usually apply to "new" records. What you posted seems to add a new record each time the form is opened. What if the user opens the form to merely edit an existing record?

Just curious...

--------------------
Go to the top of the page
 
BruceM
post Feb 16 2017, 01:19 PM
Post#3


UtterAccess VIP
Posts: 6,194
Joined: 24-May 10
From: Downeast Maine


Also, if that code is in the Open event, it may be too soon to work with records. What values exactly do you wish to insert into a new record? The Me prefix indicates the controls and/or fields are from the current form, but which record?
Go to the top of the page
 
NaviChandu
post Feb 16 2017, 01:44 PM
Post#4



Posts: 36
Joined: 19-January 17



Hi,

I am inserting the records for the new records. The primary key is a Assignment_Id.

Before I insert the records to the tables, I want to ensure the numeric and date fields have default value.

Yes even for the existing records I need to ensure they have a value, if the user removes the field value then they have to have a default value.

Please suggest the best way to achieve this

Regards,
naveen N

Go to the top of the page
 
theDBguy
post Feb 16 2017, 01:46 PM
Post#5


Access Wiki and Forums Moderator
Posts: 68,695
Joined: 19-June 07
From: SoCal, USA


Hi,

The "best" or perhaps the easiest way is to put the Default Value at the table level. You can also set the field as Required or use a Validation Rule to make sure it has a value.

Hope it helps...

--------------------
Go to the top of the page
 
BruceM
post Feb 16 2017, 01:53 PM
Post#6


UtterAccess VIP
Posts: 6,194
Joined: 24-May 10
From: Downeast Maine


I agree with DBGuy, but have another question:
QUOTE
I want to ensure the numeric and date fields have default value.

What value do you want them to have?

If you have a default value for a field in table design or for a control on a form, it applies only to new records. You may be using "default value" to mean something a little different than that.
Go to the top of the page
 
NaviChandu
post Feb 16 2017, 02:09 PM
Post#7



Posts: 36
Joined: 19-January 17



Hi Bruce,

Yes, default values for Numeric fields will be 0 (Zero), Start date will be Current date and End date will be 12/31/99.

For an edit, I need to make a Tag as REG and ensure the date fields and numeric values before database updates.
Else user will get an error.

Regards,
naveen N
Go to the top of the page
 
NaviChandu
post Feb 17 2017, 08:45 AM
Post#8



Posts: 36
Joined: 19-January 17



Hi Bruce,

I will set the default value in the table design.

I use the before update process you have explained me in the below post

Visit My Website
Go to the top of the page
 
BruceM
post Feb 17 2017, 10:11 AM
Post#9


UtterAccess VIP
Posts: 6,194
Joined: 24-May 10
From: Downeast Maine


Glad to hear you found an approach that works for you. Keep in mind that there are several options for handling default values and validation. If you find something that works but is not as good as you think it could be, very likely the approach can be modified.

Best of luck with the project.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    20th February 2017 - 04:03 AM