Problem to close a Form 2

[sanan]

Hi there
I have a little difficulty with my codes When I want to close my form called “sales”.
I have following codes in it’s UnLoad event, and it checks for the value of 2 txtboxes before it closes, And it works;

Private Sub Form_Unload(Cancel As Integer)
If Sd.Value - SC.Value <> 0 Then
MsgBox "Entries are Out of Balance", vbOKOnly, "Inventory 101"
DoCmd.CancelEvent
End If
End Sub

But here is the challenge, There are 2 controls on this form “combo144” (tab Indexed 0), and “InvoiceID100” (Tab Indexed 2), that are sort of important, and neither of them should have a value Null, when the user wants to close this form.
How would you do this?
At present I tried it with codes in LostFocus events of the bove controls, which should take care of this issue for me, But they do not work all the times, Here are those codes;
Private Sub InvoiceID100_LostFocus()
Dim strMsg As String, strTitle As String
Dim intStyle As Integer
If IsNull(Me.InvoiceID100) Or Me.InvoiceID100 = "" Then
strMsg = "You must Enter an Invoice No. A Date Or Check No.."
strTitle = "Invoice Number Required"
intStyle = vbOKOnly
MsgBox strMsg, intStyle, strTitle
Me.ReffrenceID.SetFocus
Me.InvoiceID100.SetFocus
Cancel = True
End If
End Sub


Private Sub Combo144_LostFocus()
Dim strMsg As String
Dim strTitle As String
Dim intStyle As Integer
Dim MyResp As Integer
If (IsNull(Me!Combo144)) Then
strMsg = "You must select a Customer!"
strMsg = strMsg & vbCrLf & "If you want to choose it from the ComboList, press Yes."
strMsg = strMsg & vbCrLf & "Other wise Press No, And Just Enter it"
strMsg = strMsg & vbCrLf & "In Order to close the Form Press Cancel"
strTitle = "Bill To Customer Required"
intStyle = vbYesNoCancel + vbQuestion
MyResp = MsgBox(strMsg, intStyle, strTitle)
If (MyResp = vbNo) Then
Me.Combo144.Value = "Guest"
Me.InvoiceID100.SetFocus
ElseIf (MyResp = vbYes) Then
Me.CompanyName.SetFocus
Me.Combo144.SetFocus
ElseIf (MyResp = vbCancel) Then
DoCmd.Close acForm, "Sales", acSaveNo
End If
End If
End Sub

There are 2 problems with these codes;
1. When the Combo144 is not Null, But InvoiceId100 is null and the user decides to close this forms. The InvoiceId100 msgBox Pops Up for 3 times, But the form closes after the third one, Well the user should not be able to close at all.
2. Also In my combo144 , when the user gets the Bove MsgBox and chooses to push the Cancel Button (The button that should close the form), the form does not closes and the curser gets focused on to next Control.

What are the problems with the above codes?

Best Regards
Sanan

 

[Frink]

Hallo,

In the Form_Unload event procedure, can't you put:

If IsNull(me!Combo144) or IsNull(me!InvoiceId100) then Cancel=True
If Cancel Then Msgbox "You must complete Combo144 and InvoiceId100 before closing this form",vbExclamation

Or it might be better putting the above in the Form_BeforeUpdate event procedure.

This won't let the user close the form without entering something (or en empty string) in the two fields. If you do this however, you should give the users a Cancel button to close the form without changing anything, else they will get stuck and curse your program.

Also, You shouldn't have controls called Combo144, you should give them appropriate names, ideally with a type prefix, ie cboCustomer. That way your code will be easier to understand and modify when it needs modifying in 6 months time.

- Frink

 

[sanan]

Hi frink
Thank you for your comment.
First of all I followed your advice, and I changed my Combo144 Name to comboCustomer, I knew about it, and I wanted to do it before.
Anyhow, Let me explain what is it, that I want to do by this form,
Basically in order to avoid any wrong entries by the Users, Any valid entries must have A customer and Invoice No., Therefore a user should not be able to close this form and save Any kind of data even if any of this control’s has a value of Null.
When a user opens up this form, they must first choose a Customer from my ComboBox (combocustomer), Then Enter any value for the Invoice No. txtBox (InvoiceId100) and then the can fill or not to fill the rest of controls as they choose.
Basically that is it, I have provided most of the codes that I used, basically mostly I used the Lostfocus Events of my controls.

About your suggested codes,
I used the in the beforeUpdate Events, but I encountered all sorts of problem
Then I used the following codes in my Form’s Unload event;

Private Sub Form_Unload(Cancel As Integer)
Dim strMsg As String
Dim MyResp As Integer
strMsg = "Do you really want to close this form, And Lose All the Data?"
strMsg = strMsg & vbCrLf & "If Yes, press Yes."
strMsg = strMsg & vbCrLf & "Other wise Press No, And Continue By Choosing a Custumer!"
If IsNull(Me!ComboCustomer) Or IsNull(Me!InvoiceID100) Then
Cancel = True
MyResp = MsgBox(strMsg, vbYesNo + vbQuestion, "Save Issue")
If MyResp = vbYes Then
DoCmd.RunCommand acCmdUndo
DoCmd.Close acForm, "sales", acSaveNo
Exit Sub
Else
End If
End If
If Sd.Value - SC.Value <> 0 Then
MsgBox "Entries are Out of Balance", vbOKOnly, "Inventory 101"
DoCmd.CancelEvent
End If
End Sub

But a major problem that arose, Is After the user chooses the customer (A must other wise, the user can not move) Then sets focus on InvoiceId100 control, and if the user decides to close at this junction, Above MsgBox Pops Up and if the users decides to close the form and pushes the yes Button, The msgBox of my InvoiceID100 Lostfocus events Pops Up for 2 times before the form closes, Also As you can see I indicated in above codes, that if a user pushes the yes button and decides to close the form, THE DATA SHOULD NOT BE SAVED, BUT AFTER THE FORM CLOSES, The customer info from the previously selected controls is saved in to my Table (tableSales).

Any suggestion, of what is going on, and how to fix it.

Best regards
Sanan

 

[PHV]

Why aren't the customer and the invoice# required in tableSales ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Frink]

Hallo,

If you make the form_Unload procedure just the two lines I suggest (remove everything else) and remove the lost focus events, isn't that enough?

- Frink

 

[sanan]

Hi PHV
Good to see here again.
If I understand your question correctly, You are concerned “I believe” to the Last part of my Comment. (THE DATA SHOULD NOT BE SAVED, BUT AFTER THE FORM CLOSES, The customer info from the previously selected controls is saved in to my Table (tableSales).)

Well, PHV it is actually completely revered, as I explained at the Beginning of my Earlier Comment, Any Valid Entries must have some Values for those 2 Controls.
My Form works fine for most of the times, only when a user chooses a Customer and Then decides to close the form “for any reason” with out entering any value for our InvoiceId100 control, That I am concerned about, and at this situation I would like to Void this Data Entry (Off course, only the Customer Info, Part of it, since he/she did not enter anything in our InvoieId100 control).

I hope I made it clear.
But PHV let me ask you this question in a little more general sense, at the beginning of my earlier comment I explained to frink, that what is it, I try to accomplish with this form, It goes as follow (I am copying from my earlier comment)
“Basically in order to avoid any wrong entries by the Users, Any valid entries must have A customer and Invoice No., Therefore a user should not be able to close this form and save Any kind of data even if one of these controls has a value of Null.
When a user opens up this form, they must first choose a Customer from my ComboBox (combocustomer), Then Enter any value for the Invoice No. txtBox (InvoiceId100) and then they can fill or not to fill the rest of controls as they choose.
Basically that is it”

Basically How would you tackle this form? What events of what controls are the best one to use?


Best Regards
sanan

 

[sanan]

Hi frink
Thank you for the comment.
I sort of knew about using Only the Unload event of the form, and leaving the LostFocus events of the controls untouched.
But I would like to have controls of my users entries on Controls Level itself, and not just on Form Level.
But it seems like at this time, this could be too difficult to achieve, and I decided to leave it for time being.
Therefore I came up with following codes for my form’s Unload event;

Private Sub Form_Unload(Cancel As Integer)
Dim strMsg As String
Dim MyResp As Integer
strMsg = "Press Yes, To Close the Form"
strMsg = strMsg & vbCrLf & "Press No, And Continue By Choosing a Custumer!"
If IsNull(Me!ComboCustomer) And IsNull(Me!InvoiceID100) Then
DoCmd.Close
ElseIf IsNull(Me!ComboCustomer) Then
Cancel = True
MyResp = MsgBox(strMsg, vbYesNo + vbQuestion, "Save Issue")
If MyResp = vbYes Then
DoCmd.Close
Else
Me.CompanyName.SetFocus
Me.ComboCustomer.SetFocus
End If
ElseIf IsNull(Me!InvoiceID100) Then
Cancel = True
MyResp = MsgBox(strMsg, vbYesNo + vbQuestion, "Save Issue")
If MyResp = vbYes Then
DoCmd.RunCommand acCmdUndo
DoCmd.Close ‘I also tried this line with this code
‘DoCmd.Close acForm, "sales", acSaveNo’’
‘But the form would not close on the first try and I had to close it twice’
Else
Me.CompanyName.SetFocus
Me.InvoiceID100.SetFocus
End If
End If
If Sd.Value - SC.Value <> 0 Then
MsgBox "Entries are Out of Balance", vbOKOnly, "Inventory 101"
DoCmd.CancelEvent
End If
End Sub

But the major problem is in a situation that a user chooses a customer and decides to close the form for “any reason”, The MsgBox Pops Up and if the user pushes the Yes Button the form closes “as it should”
But here is the problem, (The record Source of my Form “sales” is a query called “sales query” which is made up of Unique Table called “TableSales” and another Table Called “customers”) after closing this form, all the data that were entered to the form which are from the Customer table will be saved to my “Tablesales” and only the data with Control source to “TableSales” will be Ignored.

My objective is at the time of closing the form, when InvoiceID100 or customerId is left null noting to be saved to my “Tablesales” and all the entries to be voided, Including the autoNumber which is the “SalesID” Control

I hope I made it clear; Please let me know your comments on this.

Best Regards
sanan

 

[RoyVidar]

It's rather much text, but I think the essence is:

- > disallow save of the record if two controls bound to two fields does not contain a value.

With that requirement, there are two possible (and reliable) solutions (that I use), both is presented previously in this thread:

PHV - make the two fields required in the table
Frink - use the before update event of the form

"Control validation coding" does not prevent a save, but often provide lots of funny message boxes, events calling events calling events...

My recommandation is to try the suggestions of either PHV or Frink, as the path you are on now will probably just continue to bring surprises. After "solving" one issue, the next will pop up...

Roy-Vidar

 

[sanan]

Hi RoyVidar
Good to see you again.
you are absoulutly correct about the essence of my question.
Sorry about too much text.
And thank you for your reminder of PHV’s technique; I think I completely misunderstood him at first Place.
But about Frink’s technique;
Well I tried the following codes from his suggestion earlier, But No success;

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String
Dim MyResp As Integer
strMsg = "Press Yes, To Close the Form"
strMsg = strMsg & vbCrLf & "Press No, And Continue!"
If IsNull(Me!ComboCustomer) Or IsNull(Me!InvoiceID100) Then
Cancel = True
MyResp = MsgBox(strMsg, vbYesNo + vbQuestion, "Save Issue")
If MyResp = vbYes Then
DoCmd.Close
Else
Me.CompanyName.SetFocus
Me.ComboCustomer.SetFocus
End If
End If
End Sub

Basically right at the time of opening the form as soon as I try to choose some one from my combocustomer, My MsgBox Pops up (which I do not want this, it should give the user a chance to select something and if he/she does not then the MsgBox should pop up) and If I choose the No, the Afterupdate of my ComboBox doesn’t work any more.

Therefore I sort of disregarded this technique, But about the PHV’s tech.
I am a little confuse, Could you explain, how can I make this 2 field required in my table?
Do I have to uncheck the Allow Nulls for these 2 Fields?
If I do, that will create a lot of problems for me.
A reminder the BE is SQL server, and it is not an Access database.

Best Regards
Sanan

 

[sanan]

Hi there
I have one question.
If I have a form which is partially Filled, then I decide that these are all wrong enteries,
How can I undo or erase all my entries (I mean all including the AutoNumber field) by pressing let’s say a Button?
Would it be the following codes?

Private Sub Command170_Click()
DoCmd.RunCommand acCmdUndo
End Sub

But the above codes does not clear the autonumber that was generated.

Best regards
sanan

 

[RoyVidar]

The Access term is Required which can be set to Yes.
I believe the SQL server term is Allow Null, which you can uncheck.

Using either technique, making fields required (disallow nulls) or the before update event of the form, would meen to abandon the control validation coding (as in delete/remove). Else there's a possibility they will interfer with eachother, creating new headaches.

If you are getting the message from the forms before update event after toggling one combo, it means you have code trying to perform a save on the forms current record in that event (or in other events that's triggered by that event - look for, and remove me.requery, me.refresh, me.repaint, me.recalc, docmd.runcommand accmdsaverecord, me.dirty=false...).

A hilarious couple of hours fun can easily be achieved when setting a breakpoint in code (F9 while the cursor is within the first code line of the routine), then single step thru each line (F8) to see what happens when you trigger one event in cases when you've overpopulated event handlers with code (which I think may be the main challenge at current)...

The before update event of the form, I believe, is made available for validation purposes. I would not try to close the form within it. See - point is - when you try to close a form and there are unsaved changes, it will most often try to perform the save, which will invoke the before update event of the form, then you'll act upon it, the focus will return to the closing routine after the before update, and depending on the choise there (me.undo, cancel=true or nothing) it will close (or not - again, dependent on choices made in the before update, and how you deal with it). You'll need to trap it, though, probably a 2501 (cancelled some operation thingie error). Note, there are some anomalities (flaws) when closing forms when required fields are not filled, but if you make them required, the flaw will work the way you need Losing data when you close a form, but nice to know, anyhow.

The search is currently down, so I can't find any samples, but I know I've posted something usable earlier (without control validation, but with before update validation, and not saving when closing). Here's one thread, not directly related, but with some discussion on validation (mentioning several means of such) thread702-868277. One of my samples there, can also be found here faq702-5010 (section 4).

Roy-Vidar

 

[RoyVidar]

I don't know how SQL-server deals with "Autonumber" (increment), but in the Access world, an Autonumber that's used (assigned when one starts inputting data in a record), is used. At the next add, the next number occurs. I thought SQL-server only assigned the increment on save, but as said, I don't know, if that's true, using either the before update or required fields, would then prevent increment.

Me.Undo ' or just have the user press ESC.

Again, with your requirements, use either the before update of the form or required fields in the table. Next alternative, would be unbound forms...

Roy-Vidar

 

[sanan]

Hi RoyVidar
Thanks so much for your replies.
But it seems like I have hit a Major wall, and I just cannot move.
At first I tried your suggestion of Beforeupdate Event, But my major problem is that, I get the Access MsgBox “you can not save this record at this time….”, and also my AterUpdate Event of my ComboCustomer does not work any more, need less to say that I removed all the LostFocus Events of all my controls and basically every other events, and I had only the form’s BeforeUpdate Event at the time of the above experiment.

Also I tried an Unbound form and controls, Then I used the ADO Recordset to transfer my Data from the Controls to their respected field in my tablesales, and every thing seemed to be working fine, until the time that I faced this Link Master Child Field Problem.

One thing, I like to ask at this time; this is about your comment earlier, which goes as follow:
“disallow save of the record if two controls bound to two fields does not contain a value”
The above statement is my challenge, as you know the Record Source of this form is from a Query (View), and each of this 2 field belong to 2 Different tables,
And codes like;
DoCmd.RunCommand acCmdUndo
Only voids part of the Records and fields or records like the AutoNumber does not get voided and remains as a new Record.
How can I Void this AutoNumber when I want to Void my data entry all together?

Also about the making the field required, I do not like it because I keep getting the Access MsgBoxes.

Best regards
sanan

 

[sanan]

Hi RoyVidar
I finally noticed that the problem of my Form’s BeforeUpdate Event was in my ComboBox ComboCustomer Control afterupdate event;

Private Sub ComboCustomer_AfterUpdate()
RunCommand acCmdSaveRecord
Me!ShipName = Me![ComboCustomer].Column(1)
Me!ShipAddress = Me!Address
Me!ShipCity = Me!City
Me!ShipRegion = Me!Region
Me!ShipPostalCode = Me!PostalCode
Me!ShipCountry = Me!Country
Me.InvoiceID100.SetFocus
End Sub

And Basically only in this line;
RunCommand acCmdSaveRecord

As soon as I removed this line, Things are in order so far.
Now only my CmboCustomer does not work properly
(Only the first line Updates; Me!ShipName = Me![ComboCustomer].Column(1))
Any Suggestion why the do not work? And what to do?
and the rest sort of do not work, Then I decided to use the Dlookup in each of my Customer’s controls.
But why any of the following codes do not work?

=DLookUp("[address]","[customers]","[customerID]=form![comboCustomer].value")
=DLookUp("[address]","[customers]","[customerID]=form![Sales]![comboCustomer].column(0)")
=DLookUp("address","TableSales","CustomerID=" & Val([Me]![comboCustomer]))
=DLookUp("[address]","[TableSales]","[CustomerID]=" & Val([Me]![comboCustomer]))
=DLookUp("[address]","[TableSales]","[CustomerID]='" & Val([Me]![comboCustomer]) & "'")

One note the Data Type of CustomerID is nchar.

Best regards
Sanan

 

[RoyVidar]

My knowledge of SQL server is somewhere between zilch and Null;-)

In stead of doing those domain aggregates, I'd consider opening a recordset. On a small test here, I was able to use the ADO .Find method both with and without text delimiters on nchar. However when opening a filtered recordset or using the DLookup text delimiters where required.

Note - in controlsources, drop off the Me reference, it's only used in code.

So the equivalence of

[tt]=DLookUp("[address]","[TableSales]","[CustomerID]='" & [comboCustomer] & "'")[/tt]

worked as controlsource of a control on my setup. As did:

[tt]dim rs as adodb.recordset
set rs=currentproject.connection.execute("select * from sometable " & _
"where somefield ='" & val(me!cboCombo) & "'",,adcmdtext)
me!txtSomeControl.value = rs.fields("somefield").value[/tt]

Roy-Vidar

 

[sanan]

Hi RoyVidar
I tried the following codes in the ContrlSource of a txtBox Named “PostalCode”, and the table that we would like to extract Data from is called “Customers”;

set rs=currentproject.connection.execute("select * from Customers " & _
"CustomerID ='" & val(me!ComboCustomer) & "'",,adcmdtext)
me! PostalCode.value = rs.fields("PostalCode ").value

But No success, Also I tried the same code in the AfterUpdate event of my ComboCustomer ComboBox, which it’s field name is called CustomerID and it’s data type is nchar, and it is not Numeric. Other wise the value of my Combcustomer are something like; HXJK, MMIO, IBMI, KLMN, and so on.
And No success as of yet;

Private Sub ComboCustomer_AfterUpdate()
Me!ShipName = Me![ComboCustomer].Column(1)
Set rs = CurrentProject.Connection.Execute("select * from Customers " & _
"CustomerID ='" & Val(Me!ComboCustomer) & "'", , adCmdText)
Me!City.Value = rs.Fields("city").Value

Me.InvoiceID100.SetFocus
End Sub

Best regards
Sanan

 

[PHV]

Set rs = CurrentProject.Connection.Execute("select * from Customers " & _
"WHERE CustomerID='" & Me!ComboCustomer & "'", , adCmdText)


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[sanan]

Hi PHV, RoyVidar
I tried the following, but still no Success;

Private Sub ComboCustomer_AfterUpdate()
Me!ShipName = Me![ComboCustomer].Column(1)
Set rs = CurrentProject.Connection.Execute("select * from Customers " & _
"WHERE CustomerID='" & Form!ComboCustomer.Column(0) & "'", , adCmdText)
Me!City.Value = rs.Fields("city").Value
End Sub

The following line was tried for different versions of it, but still no success;
"WHERE CustomerID='" & Form!ComboCustomer.Column(0) & "'", , adCmdText)
I tried these;
"WHERE CustomerID='" & me!ComboCustomer.Column(0) & "'", , adCmdText)
"WHERE CustomerID='" & me!ComboCustomer & "'", , adCmdText)

Also I tried the followings in the ControlSource of txtBox called city;
Set rs = CurrentProject.Connection.Execute("select * from Customers " & _
"WHERE CustomerID='" & me!ComboCustomer.Column(0) & "'", , adCmdText)
Me!City.Value = rs.Fields("city").Value
But No success, Could you verify for me that in What control, and what Event I should try the above codes, I am a little confused about it.

I should mention, that the following finally worked, which is s sort of different version of PHV’s latest reply;
This is off course a simple “DLookup” Fuction;
=DLookUp("address","customers","CustomerID='" & [Form]![comboCustomer].[column](0) & "'")


Best Regards
Sanan