Solved

Update a DataControl

Posted on 2000-03-09
40
179 Views
Last Modified: 2013-12-25
What is wrong in my code? The pgm. save always the Data of my form, if there is no data in the table.
If i answer No to the question and if the table is empty; my pgm. save what i have in my txtBoxes, if there is one or more record, it doesn't save;it is what i want.

Private Sub DataCli_Validate(Action As Integer, Save As Integer)
Dim Rep As Long
Dim X As Integer
If Action = vbDataActionUnload And ajout = True Then

    If TxtCli(1).Text = "" Then
        Save = False
        Exit Sub
    Else
            Rep = MessageBox(0, "Do you want to SAVE?", "Exit", vbYesNo)
            If Rep = vbYes Then
                Save = True
                Exit Sub
            Else
                Save = False
                Exit Sub
            End If
    End If
End If
End Sub

WHEN I HAVE ONE ORE MORE RECORDS IN THE TABLE THE PGM. WORKS FINE.

I WANT TO KNOW HOW DON'T SAVE THE DATA IN THE TXTBOXES WHEN THERE IS NO RECORD IN THE TABLE.

I WANT IF THERE IS NO DATA IN THE TABLE(empty) AND I ANSWER NO TO: Do you want to SAVE? THE DATA SHOULD BE NOT SAVE.

TRY IT BEFORE TO TELL A COMMENT PLEASE.
0
Comment
Question by:dosyl
  • 22
  • 6
  • 5
  • +4
40 Comments
 
LVL 9

Expert Comment

by:Ruchi
Comment Utility
>>>>Action = vbDataActionUnload And ajout = True

What is ajout?
0
 
LVL 43

Expert Comment

by:TimCottee
Comment Utility
Dosyl, do you have a function/sub called MessageBox? Using standard VB this would be

Rep = MsgBox("Do you want to SAVE?",  vbYesNo,"Exit")

Anyway, the problem is that txtCli(1).Text will always = "", it is better to check the length:

IF Len(txtCli(1).Text) = 0 Then

instead should fix your problem.
0
 
LVL 43

Expert Comment

by:TimCottee
Comment Utility
I am going mad, sorry that doesn't help at all and is probably wrong, I was thinking of something else.

I think Ruchi is on the right lines, what is ajout, my guess is that this is not defined in the right scope and therefore will be evaluated as false in the procedure. It should either be declared at the form level or passed as a parameter to the procedure. Or if it is the name of a control, you need to make sure that the default property of the control is a boolean value which is set to true.
0
 
LVL 1

Author Comment

by:dosyl
Comment Utility
Ajout is Add in French. It is a variable to see if another sub is done.
0
 
LVL 1

Author Comment

by:dosyl
Comment Utility
What i don't understand the pgm. run perfectly, but when there is no data in the table, the pgm. save if you answer No.
I checked with F8, Save= -1 to begin after Save= 0 when Save=False.
Another thing i writed in Form_Unload DataCli.Recordset.Close then we go back in DataCli_Validate.
0
 
LVL 9

Expert Comment

by:Ruchi
Comment Utility
Use Or instead.

If Action = vbDataActionUnload And ajout = True Then

to

If Action = vbDataActionUnload Or ajout = True Then

......
I'm still thinking....


0
 
LVL 1

Author Comment

by:dosyl
Comment Utility
If Ajout=True, it is because the user Add a new customer, then i can't put OR because i must to verify if the user Unload the Form and Add a customer.
0
 
LVL 1

Author Comment

by:dosyl
Comment Utility
I found the problem, but i don't know how to solve it.
The problem is: in DataCli_Validate Save=False(it is good); After in form_Unload, i do DataCli.Recordset.Close, there, Save become True and it save my data. How to avoid this?
0
 
LVL 9

Expert Comment

by:Ruchi
Comment Utility
In your form_Unload event,
You try to put Save = False or something like that...
DataCli.Recordset.Close
Set DataCli.Recordset = Nothing

You get it or not? I don't think I make any sense...
0
 
LVL 1

Author Comment

by:dosyl
Comment Utility
I have only this in the Form_Unload to close the recordset.

DataCli.Recordset.Close
Set FrmCli = Nothing
0
 
LVL 9

Expert Comment

by:Ruchi
Comment Utility
Ok... Can you explain more about your problem?

I was thinking if you enter the following code into Form_Unload event.
Private Sub Form_Unload(Cancel As Integer)
  if mblnValidationFailed Then Cancel = True
End Sub

This procedure cancels the Unload event.

I don't know if this makes sense to you.
0
 
LVL 1

Author Comment

by:dosyl
Comment Utility
The pgm. works fine. In DataCli_Validate when save=False, the pgm. doesn't save; when Save=True the pgm. save.
The problem is: when the Table is empty the pgm. save the data even if i answer No to the question.

I don't want to Cancel the Unload event.
Do a test:
Put a txtBox and DataControl on a Form; try to put in the Data_Validate :
If Action = vbDataActionUnload then
Save=False
endif
Put in the  form_Unload :
Data1.Recordset.Close.

You will have the same error, if your table is empty, the data will be save anyway.
0
 
LVL 1

Author Comment

by:dosyl
Comment Utility
I forgot 2 cmdButton to put too.
1. cmdAdd
Data1.Recordset.AddNew
2. cmdSuppress
    Data1.Recordset.Delete
    Data1.Recordset.MoveNext
End If
0
 
LVL 69

Accepted Solution

by:
Éric Moreau earned 100 total points
Comment Utility
Hi Dosyl.

I did a small test and there is a way that data didn't save. Look at this code:
Private Sub DataCli_Validate(Action As Integer, Save As Integer)
Dim Rep As Long
Dim X As Integer
'If Action = vbDataActionUnload And ajout = True Then
If Action = vbDataActionUnload Then

    If txtCli(1).Text = "" Then
        Save = False
        Exit Sub
    Else
            Rep = MsgBox("Do you want to SAVE?", vbYesNo)
            If Rep = vbYes Then
                Save = True
                Exit Sub
            Else
                txtCli(1).DataChanged = False
                Save = False
                Exit Sub
            End If
    End If
End If
End Sub

I have added only one line (txtCli(1).DataChanged = False) and I think it will correct your problem.
0
 
LVL 1

Author Comment

by:dosyl
Comment Utility
emoreau, with txtCli(1).dataChanged i have this error now:
Execution Error 3426
This method was cancelled by an assiciated object.
0
 
LVL 1

Author Comment

by:dosyl
Comment Utility
Adjusted points to 100
0
 
LVL 9

Expert Comment

by:Ruchi
Comment Utility
An article about "Cancelled by an Associated Object.(3426)"

http://support.microsoft.com/support/kb/articles/Q189/8/51.ASP?LNG=ENG&SA=ALLKB&FR=0
0
 
LVL 4

Expert Comment

by:gcs001
Comment Utility
Hi dosyl,
Use the Cancel method before closing the Recordset.

Data1.Recordset.Cancel

This will ensure that the .AddNew is not done when closing the recordset!

HTH

Regards,
Grant.
0
 
LVL 1

Author Comment

by:dosyl
Comment Utility
gcs001 ,
It doesn't work, i have the error message: execution error 3251.
0
 
LVL 9

Expert Comment

by:Ruchi
Comment Utility
Please post your whole code here. Thanks.
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 4

Expert Comment

by:gcs001
Comment Utility
Are you certain your data is being saved in the DataCli_Validate event?

You should perhaps step through your code and at the same time check your database table for the exact line of code that is eventually saving the record.

If you post more of your code here perhaps we could be of more assistance.

- Grant.
0
 
LVL 1

Author Comment

by:dosyl
Comment Utility
Dim Ajout As Boolean

Private Sub CmdAdd_Click()
If DataCli.Recordset.EOF = False And DataCli.Recordset.BOF = False Then
    DataCli.Recordset.MoveLast
End If
DataCli.Recordset.AddNew
Ajout = True
End Sub

Private Sub CmdSupp_Click()
 If MsgBox("Do you want to suppress this Customer?", vbYesNo, "ATTENTION !") = vbYes Then
    DataCli.Recordset.Delete
    DataCli.Recordset.MoveNext
End If

End Sub

Private Sub DataCli_Validate(Action As Integer, Save As Integer)
Dim Rep As Long
Dim X As Integer
'On vérifie s'il y a des données d'ajoutées avant de fermer le programme
'avec le bouton dans le coin droit de la fenêtre.
If Action = vbDataActionUnload And Ajout = True Then
'On vérifie s 'il y a des données d'entrées.
    If Text1.Text = "" Then
        Save = False
        Exit Sub
    Else
    Rep = MsgBox("Do you want to save this customer's data?", vbYesNo, "Exit")
        If Rep = vbYes Then
            Save = True
            Exit Sub
        Else
            Save = False
            Exit Sub
        End If
    End If
End If
End Sub

Private Sub Form_Unload(Cancel As Integer)
DataCli.Recordset.Close
End Sub
0
 
LVL 69

Expert Comment

by:Éric Moreau
Comment Utility
Don't you need something to reset you Ajout flag to False once saved?
0
 
LVL 69

Expert Comment

by:Éric Moreau
Comment Utility

I just retry your code with 2 small modification and it works well:

1) Reset your flag, you should use this:
Private Sub DataCli_Reposition()
    Ajout = False
End Sub

2) Don't close the recordset. It will automatically close on the form unload. Normally I say to close what you open but in your case, you use the bound mode so there is small differences!
0
 
LVL 1

Author Comment

by:dosyl
Comment Utility
emoreau, remove the flag Ajout if it annoys you; you will have the same result.
0
 
LVL 1

Author Comment

by:dosyl
Comment Utility
Is a Recordset of a dataControl close itself always when the form unloads? If it is this; my problem is solved, because i believed we must always to close a recordset to do not have the error message.(... is open by another processus).
0
 
LVL 69

Expert Comment

by:Éric Moreau
Comment Utility
If your data control is on a form that is being unload, the recordset will be close automatically because all controls on that form will be released from memory.
0
 
LVL 1

Author Comment

by:dosyl
Comment Utility
I think : if the user only minimize the form the recordset will be not close and i use the same table in others forms, it is for this i must to close it. If nobody don't give me a better answer, i'll must to remove my dataControl and use DAO like:
Dim dbCli as Database
Dim RstCli as Recordset

But i'd prefer to take a dataControl to avoid code.
0
 
LVL 69

Expert Comment

by:Éric Moreau
Comment Utility
You can't be bound with a DAO Recordset.
0
 
LVL 1

Author Comment

by:dosyl
Comment Utility
I think : if the user only minimize the form the recordset will be not close and i use the same table in others forms, it is for this i must to close it. If nobody don't give me a better answer, i'll must to remove my dataControl and use DAO like:
Dim dbCli as Database
Dim RstCli as Recordset

But i'd prefer to take a dataControl to avoid code.
0
 
LVL 1

Author Comment

by:dosyl
Comment Utility
emoreau, i know.
0
 
LVL 32

Expert Comment

by:bhess1
Comment Utility
Slight change.  Try this code:

Private Sub Form_Unload(Cancel As Integer)
If DataCli.RecordSet.EditMode <> dbEditNone Then
  DataCli.RecordSet.CancelUpdate
End If
DataCli.Recordset.Close
End Sub


0
 
LVL 14

Expert Comment

by:wsh2
Comment Utility
Dosyl writes:
"Private Sub DataCli_Validate(Action As Integer, Save As Integer)"

Make Save a module / global level variable -or- make DataCli_Validate a Function returning the Save value. When you use ByRef, variable Scope becomes everything, and a b*tch to debug. Whenever possible use ByVal referencing, so that VB scope problems do NOT become a problem, as obviously they are here.
0
 
LVL 1

Author Comment

by:dosyl
Comment Utility
wsh2
Thank's to want help me.
If i write byval i have a compile error: the declaration doesn't correspond to the event ot to the sub with the same name.
0
 
LVL 14

Expert Comment

by:wsh2
Comment Utility
Ooops.. <sheepish grin>.. read the question wrong.. <gulp>. As it is after 5pm here.. gotta go home.. and get back to you later.. <smile>
0
 
LVL 1

Author Comment

by:dosyl
Comment Utility
Edited text of question.
0
 
LVL 1

Author Comment

by:dosyl
Comment Utility
Edited text of question.
0
 
LVL 1

Author Comment

by:dosyl
Comment Utility
Edited text of question.
0
 
LVL 1

Author Comment

by:dosyl
Comment Utility
Edited text of question.
0
 
LVL 1

Author Comment

by:dosyl
Comment Utility
Excellent, i don't know why i didn't understand before. Here is my code:

Private Sub DataCli_Validate(Action As Integer, Save As Integer)
Dim Rep As Long
Dim X As Integer

If Action = vbDataActionUnload And Ajout = True Then

    If Text1.Text = "" Then
        Text1.DataChanged = False
    Else
    Rep = MsgBox("Do you want to save this customer's data?", vbYesNo, "Exit")
        If Rep = vbYes Then
            Text1.DataChanged = True
        Else
            Text1.DataChanged = False
        End If
    End If
End If
End Sub

Thank's a thousand time
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

762 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

7 Experts available now in Live!

Get 1:1 Help Now