Solved

Date field textbox  bound to dao Help!

Posted on 2001-06-06
19
421 Views
Last Modified: 2010-05-02
This question was posted once but I couldnot get any solution.

I have a form with  textboxes bound to a dao. one textbox is bound to date field. Now if user doesnot

write into the field, and then presses the update buton (i use dao form wizard)then the record is saved

without any problem and the date field shows null. But if the user enters a text into date field but

then realises that he shouldn't be entering it, and then if he deletes whatever he has entered in the field

and then presses update button, dao gives data type mismatch error. 'Not required' is ticked in database
field during design.
what do i do ?

I tried using dao.recordset.fields("date")=null to set the field to null before saving it.
But dao gives me an error saying that, "update without edit or cancelupdate" when i use dao.recordset.update method.
This nulling method works if I use .edit method before the user changes any data. But, I do not have control on it since, when a user changes a textbox, it is the dao that handles the editing, and not me.
I cannot upgrade to ado.
Please help
0
Comment
Question by:shivajivarma
  • 9
  • 9
19 Comments
 
LVL 1

Expert Comment

by:Balshe
ID: 6160800
try This Code


Private Sub Form_Load()
   ' Set the button's CausesValidation property to False. When the user
   ' clicks the button, the Validate event does not occur.
   ' Set the Caption property of the button to "Help".
   With Command1
      .CausesValidation = False
      .Caption = "Help"
   End With

   Show
   With Text1 ' Select text of Text1 and set focus to it.
      .SelLength = Len(Text1.Text)
      .SetFocus
   End With
End Sub

Private Sub Command1_Click()
   ' Give the user help when the button is clicked.
   MsgBox _
   "Text1 must be set to a date." & VbCrLF & _
   "Text2 must be a number less than 10."
End Sub

Private Sub Text1_Validate(KeepFocus As Boolean)
   ' If the value is not a date, keep the focus, unless the user
   ' clicks Help.
   If Not IsDate(Text1.Text) Then
      KeepFocus = True
      MsgBox "Please insert a date in this field.", ,   "Text1"
   End if
End Sub
   
Private Sub Text2_Validate(KeepFocus As Boolean)
   ' If the value is a number larger than 10, keep the focus.
   If Not IsNumeric(Text2.Text) Or Val(Text2.Text) > 10 Then
      KeepFocus = True
MsgBox _
"Please insert a number less than or equal to 10.", , "Text2"
   End If
End Sub

0
 

Author Comment

by:shivajivarma
ID: 6163372
This method will force the user to enter a date. But this has to be avoided. After typing in the date, if the user realizes that he shouldn't have typed it (because of whatever reason), then he must be allowed to save the record with a blank(null?) blank date field. Generally, this is allowed by dao, only if nothing had been entered in the data field.Setting focus is okay, but typing anything , even if it is deleted, would trigger this behaviour.  DAO also would refuse to accept the siuation, where in, a saved date is deleted by the user to make it blank, and the user presses the update button.
0
 
LVL 49

Expert Comment

by:Ryan Chong
ID: 6166391
Hi, Are you using VB6 or VB5? Use the IsDate function to check the Text entered ?
0
 

Author Comment

by:shivajivarma
ID: 6168452
I am using VB6. I can use isdate function, but then, that doesn't help me since my problem is different. When the date is wrong, I can ask the user to coorect it. But, if he makes the textbox empty, since the entered date was wrong, and he doesn't have a new date, then DAO WILL NOT LET ME UPDATE, unless I enter a valid date. At the same time, I need the date column to be emptied.

0
 

Author Comment

by:shivajivarma
ID: 6200960
Any suggestions?
0
 
LVL 49

Expert Comment

by:Ryan Chong
ID: 6200975
Hi Shivajivarma, it's better to understand if you post your code.
0
 

Author Comment

by:shivajivarma
ID: 6205490
I don't use nay code other than data1.recordset.update to update my record. Just like the form created by the dataform wizard in VB. The problem is that, dao doesnot allow nullfields to be saved. I want null fields to be saved using data.recordset.update method.
0
 
LVL 49

Expert Comment

by:Ryan Chong
ID: 6205510
Try to use Data1.Recordset.CancelUpdate when the error occurs.
0
 

Author Comment

by:shivajivarma
ID: 6205853
I do not want to cancelupdate since I nned to allow the user to save the record. Error is flagged because, I am trying to save a null value into a field.

For example, use dataformwizard to create small program with  a data entry form, with 4 fields. (access database) execute the program.(with DAO not ADO)

Employeename : (string) ('field can be null' set in database): Cris
Age : (numeric) :30
dob :(date field) : 1/1/1971
Dateofjoining: (datefield)1/1/1998

Press addrecord button, and enter one record.
Close the form, and reload the form. Now you can see the data you entered.

Now, as a user, try to edit the data. You don't have to press any button to do it since dao allows editing and saving directly.
I change the name to Chris. The previous one was Cris, which was a mistake.  I delete the value entered  in the the dateofjoining cloumn, since the previous one was mistakenly entered. Unfortunately, I don't know the actual date. But at the same time, I cannot leave it as it is, since another guy (probably my manager) will be looking at the data 10 minutes from now to check the list of employees and  giving them incentives on the basis of dateofjoining.
NOW, if I press update key, dao will give me an error, and simply won't let me make the datofjoining empty!

This is a hypothetical situation, typed in just to make you understand that it may be necessary at times to let the user save "blank" or null fields! In this example, there are only 4 fields. But think about a form with 30 fields, where the user corrects 29 fields, and makes  the 30th column blank, since it must be made blank. I simply cannot cancelupdate. I have to let him save all 29+1 fields as he wants!
This behaviour is also exhibited in the case of numeric fields made blank/null. This is a very common behaviour, and almost every data entry form which used dao methods will face this problem. I wonder how programmers deal with this situation.
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 49

Expert Comment

by:Ryan Chong
ID: 6205895
Yup, i'm now better understood. Let me do a testing and tell you the result back soon. Cheers.
0
 

Author Comment

by:shivajivarma
ID: 6205951
Thanks for the prompt reply. This is a very big problem for us, and if you could help, we would be grateful(probably all our life).
0
 
LVL 49

Accepted Solution

by:
Ryan Chong earned 300 total points
ID: 6206060
Hi Shivajivarma, here is an alternative way (as what i always did):

Do not bound the Data Control directly to the TextBoxes, it can definitely avoid the error you face.

____________________________________________________

Here is the Sample:

____________________________________________________

Option Explicit
Dim AddNow As Boolean

Private Sub cmdAdd_Click()
    txtName.Text = ""
    txtAge.Text = ""
    txtDOB.Text = ""
    txtJoin.Text = ""
    AddNow = True
End Sub

Private Sub cmdBrowse_Click(Index As Integer)
    Select Case Index
    Case 0 'First
        Data1.Recordset.MoveFirst
    Case 1 'Previous
        Data1.Recordset.MovePrevious
        If Data1.Recordset.BOF Then Data1.Recordset.MoveFirst
    Case 2 'Next
        Data1.Recordset.MoveNext
        If Data1.Recordset.EOF Then Data1.Recordset.MoveLast
    Case 3 'Last
        Data1.Recordset.MoveLast
    End Select
    ShowRecords
End Sub

Private Sub cmdCancel_Click()
    AddNow = False: ShowRecords
End Sub

Private Sub cmdUpdate_Click()
    If Not IsDate(txtDOB.Text) And Trim$(txtDOB.Text) <> "" Then ShowRecords: Exit Sub
    If Not IsDate(txtJoin.Text) And Trim$(txtJoin.Text) <> "" Then ShowRecords: Exit Sub
   
    If AddNow Then
        Data1.Recordset.AddNew
    Else
        Data1.Recordset.Edit
    End If
   
    Data1.Recordset.Fields!EmployeeName = txtName.Text
    Data1.Recordset.Fields!Age = Val(txtAge.Text)
    If Trim$(txtDOB.Text) <> "" Then Data1.Recordset.Fields!DOB = txtDOB.Text
    If Trim$(txtJoin.Text) <> "" Then Data1.Recordset.Fields!DateOfJoining = txtJoin.Text
   
    Data1.Recordset.Update
    If AddNow Then Data1.Recordset.MoveLast
    ShowRecords
    AddNow = False
End Sub

Private Sub Form_Load()
    AddNow = False
End Sub

Private Sub ShowRecords()
    txtName.Text = Data1.Recordset.Fields!EmployeeName
    txtAge.Text = Data1.Recordset.Fields!Age
    txtDOB.Text = "" & Data1.Recordset.Fields!DOB 'Add "" to prevent null error
    txtJoin.Text = "" & Data1.Recordset.Fields!DateOfJoining 'Add "" to prevent null error
End Sub


'Ask me for original file, if needed, email me at ryancys78@yahoo.com . Thanks
0
 
LVL 49

Expert Comment

by:Ryan Chong
ID: 6206107
Hi, In addition:

To limit the text type by user, we can use the Validate event in Text Box, example:

Private Sub txtJoin_Validate(Cancel As Boolean)
    If Not IsDate(txtJoin.Text) And Trim$(txtJoin.Text) <> "" Then Cancel = True
End Sub

'Cheers!
0
 
LVL 49

Expert Comment

by:Ryan Chong
ID: 6206131
Hi, Here is a link i found, take a look: http://graphicsmagician.com/vbcourse/08sql/data2.htm
0
 

Author Comment

by:shivajivarma
ID: 6217298
Dear ryancys,

Thank you for the comments.
I had submitted this comment 2 days back, but I guess it didn't really go.

1. The first method, will definitely work, since you aren't using bound controls. We use similar methods right now, since we are forced to do this. But this is not what I wanted. If I can use bound controls (edit method is auto-invoked by dao) then We will save plenty of code clutter. Think about a form with 80 text boxes, and you will agree that this is a sensible statement.

2. The second link might be helpful, but I could not work on it since I am away from office. Kindly bear with me for 1-2 days.

Thanking you once ag'n.
0
 

Author Comment

by:shivajivarma
ID: 6217302
Dear ryancys,

Thank you for the comments.
I had submitted this comment 2 days back, but I guess it didn't really go.

1. The first method, will definitely work, since you aren't using bound controls. We use similar methods right now, since we are forced to do this. But this is not what I wanted. If I can use bound controls (edit method is auto-invoked by dao) then We will save plenty of code clutter. Think about a form with 80 text boxes, and you will agree that this is a sensible statement.

2. The second link might be helpful, but I could not work on it since I am away from office. Kindly bear with me for 1-2 days.

Thanking you once ag'n.
0
 
LVL 49

Expert Comment

by:Ryan Chong
ID: 6217328
Hi, shivajivarma.

How about using the DTPicker (Date Picker) Control instead of textbox?
0
 

Author Comment

by:shivajivarma
ID: 6223335
Dear  ryancys,
1. Your links were not directly helpful. Actually, if dao had an exposed event between its edit and update methods(both are invoked internally. If there are any datachanged properties then automatically these two are invoked while calling the updaterecord method.) , then it would have solved the whole problem.
But, from your comments, we could finalise a method which would cut down the code clutter by 30%. Kindly comment on the method.
1. If the field is numeric, then in the lost focus event,

if datachanged and trim(textbox)="" then make text box="0"

In update button code,
2. For each date field text box, assign a variable to flag true if (datachanged=true and not isdate(textbox text). If true then, make datachanged property of textbox false.
Use updaterecord method and bookmark
use edit method.

For all those true flagged fields, make fields null.

data1.recordset![datefield1]=null

use update method again.

This fully solves the problem, and takes less code than using only code to update fields.

 You definitely deserve the points, and we thank you once again for your gestures of help.

2. Regarding datepicker control: Do you mean the calender control? Is there a separate control available? We have had setup problems with calender control, and had got a patch for the dependency file. We do not use calender control now since we are a bit apprehensive.
This method may not eliminate the problems, considering the ways the user would want to enter his data.
0
 
LVL 49

Expert Comment

by:Ryan Chong
ID: 6223393
Hi shivajivarma,

Glad can help you a little.

Date picker control is available on VB 6, i just have an alternative idea that to use it to solve your problem. Ya, it is similar to the Calendar Control, but look nicer, it only show the Calendar when we click the drop-down button.

For the first question:

> 1. If the field is numeric, then in the lost focus event,
> if datachanged and trim(textbox)="" then make text box="0"

I had try this similar things before, that is enter some not date characters, then clear it. But this method doesn't work. and for the second:

> For all those true flagged fields, make fields null.
> data1.recordset![datefield1]=null

Will work if it pass the first one.

That is my slight comments and glad can exchange ideas with you. : )

0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

705 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

18 Experts available now in Live!

Get 1:1 Help Now