What is wrong with my codes, any suggestions 2

[sanan]

Hi Every Body
At first let me explain the situation a bit.
I have a form. Upon Opening the Form, In order to make my users to select an option in my comboBox (Indexed value is 0), I have the following Codes:

Private Sub Combo144_LostFocus()
Dim strMsg As String, strTitle As String
Dim intStyle As Integer
If IsNull(Me!Combo144) Then
strMsg = "You must pick a Customer"
strTitle = "Bill To Customer Required"
intStyle = vbOKOnly
MsgBox strMsg, intStyle, strTitle
Me.CompanyName.SetFocus
Me.Combo144.SetFocus
Else
Me.InvoiceNo.SetFocus
End If
End Sub

Which sort of works, Otherwise If the user does not pick a value from my comboBox He /She cannot go any Further and the MsgBox Pups up.

But here is the problem; the user should be able to close the form, even if they have not
Pick any value from my comboBox Therefore I came up with the following codes Which They do not work and my Msgbox Pups up 3 times:

Private Sub Form_Close()
If IsNull(Me!Combo144) Then
Me!Combo144.Enabled = False
DoCmd.RunCommand acCmdUndo
DoCmd.Close acForm, "Sales"
End If
End Sub
I tried the above in my Onload Event No Success, also I used Me.Combo144 rather than Me!Combo144 But still no success.
Here is the code I Have on Opening my form if it helps any:

Private Sub Form_Open(Cancel As Integer)
Forms!sales.UniqueTable = "Tablesales"
DoCmd.GoToRecord , , acNewRec

Me.Combo144.SetFocus
End Sub

Could you tell me, What are wrong with my coding?
Basically I think the problem should be in my Close Event coding.

Best regads
sanan

 

[MichaelRed]

once focus is on the combo, any (all) attempts to leave w/o a customer selection instantiates the Msg box, which (in Turn) resets the focus back toye olde comboe boxe.

A way our of the "VICIOUS" circle would be to include a dummy "customer" (EXIT CUSTOMER). When this customer is selected, just close the form.





MichaelRed

 

[Zygor]

You could make all fields except your combo locked = true and enabled = false.

Then once they make a choice, make all enabled = true and locked = false.

 

[sanan]

Hi there
I will try both approch, and let you know.
But MichaelRed could you explain your technique just a little more, This Dumy customer where should I make it or what is it? Is it a TextBox or what?

best regards
Sanan

 

[SiJP]

Alternatively, have the message box as a Yes / No option:

(Psedo code)

If combo is null...

If Msgbox("Are you sure you wish to continue without selecting a customer?", vbQuestion + vbYesNo, "Customer Selection") = vbNo Then
' Don't allow the user to move from that field.
End If


I tend to find if you don't allow a user to do something like the above, they tend to get fustrated and find reasons to complain about your app.

------------------------
Hit any User to continue

 

[sanan]

Hi there
I tried MichaelRed’s suggestion I could reduce the number of Pup up of MsgBox from 3 to 1, when I am closing my forms, But I still get the MsgBox at least once.
Please let me know your Opinion on it.

About the Zygor ‘s technique.
I am not quite sure at what Controls or Events I have to set the properties of my Controls.
I know how to set the properties of all my controls:

Dim i As Integer
For i = 0 To sales.Controls.Count - 1
Sales.Controls(i).Enabled = False
Sales.Controls(i).Locked = True
Next i
Some thing like the above, But I do not know at what events, Please Explain more.

Best regards,
Sanan

 

[Zygor]

I would leave the combo box properties as is. Each other field (that you don't want the user to use until they fill in the combo) I would go to the properties and set the enabled to no and locked to yes.

In the after update field of the combo box, I would set them to enabled = yes and locked = no, if the combo box is not null or blank or = " "....

Something like that.

If you use your routine, it may error as labels (if they're considered controls) don't have enabled or locked properties and it may crash. You can get around that by adding a line something like if .controltype = "textbox" then... or something like that . (untested)

 

[MichaelRed]

What "MsgBox" do you get? What the the revised code lok like? What does the ComboBox Soource list look like?



MichaelRed

 

[sanan]

Hi SiJP
From your suggestion, I came up with following codes:

Private Sub Combo144_LostFocus()
Dim strMsg As String, strTitle As String
Dim intStyle As Integer
If IsNull(Me!Combo144) And Me.Check164.Value = 0 Then
strMsg = "You must pick a value from the Bill To list."
strTitle = "Bill To Customer Required"
intStyle = vbQuestion + vbYesNo
MsgBox strMsg, intStyle, strTitle
If MsgBox(strMsg, inStyle, strTitle, , "customer selection") = vbNo Then
Me.CompanyName.SetFocus
Me.Combo144.SetFocus
End If
Else
Me.InvoiceNo.SetFocus
End If
End Sub

But it is not working for me, previously I could stop my user from going to next control (invoiceNo), but with above I cannot even stop them from going to next control.
(Could you tell me what is wrong with the above code)
Off course my original Problem was not this issue.
My problem is at the time of closing my form when nothing is selected in my Combo144, I get that MsgBox, which I do not want it; otherwise a User should be able to close the form even If they have not choose any thing.

About MichaelRed Technique
Hi MichaelRed, here is what I did
Since I did not want to use or introduce any Dummy Customer, I should mention that I tried that Before using a “” for a Customer and I set the default value of my Combo144 this “” value, If I remember correctly I had some kind of Table related problem.
Therefore I made a checkbox it’s visible property equals False and here are my codes and events on this issue:
Dim strMsg As String, strTitle As String
Dim intStyle As Integer
If IsNull(Me!Combo144) And Me.Check164 = 0 Then
strMsg = "Are You sure, that you do now want to choose any Customer."
strTitle = "Bill To Customer Required"
intStyle = vbOKOnly
MsgBox strMsg, intStyle, strTitle
Me.CompanyName.SetFocus
Me.Combo144.SetFocus
Else
Me.Check164.Value = 1
Me.InvoiceNo.SetFocus
End If
End Sub
Then I have a Addnew reord Button which I added the check box to it:
Private Sub Command162_Click()

If Me!Sd <> Me!SC Then
MsgBox "Entries are out of Balance", vbOKOnly, "Inventory101"

Else
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.GoToRecord , , acNewRec
Me.Check164.Value = 0
Me.Combo144.SetFocus
End If
End Sub

Now Everything works O.K. Except on situation when the user upon opening a form decides to close it without choosing any Customer from my combo144, Only at this situation I get my MsgBox only once.
So some how, onclosing I have to set the value of my Chekbox to 1.

About Zygor’s technique
Again my problem is not stoping the users from moving to next control, It is about closing my form with out getting my msgBox.

Best regards,
Sanan

 

[MichaelRed]

I have (quasi) re-created some version of your form and table, entered a VERY few dummy records and (semi-minimmally) modified your code. The items modified fall into two categories:

1[tab]naming convention employed (primarily for MY benifit)
2[tab]modified the MsgBox to permit options (necessary) and revise language (my benefit)

I added the simplistic form "frmCloseOrders". It includes ONLY a single command button which includes only the MsgBox activation and the close method to apply to the "Main" form.

Since Your code was a bit bare, I needed to name this form and arbitrarily chose "frmOrders".

There appear to be two real issues with what you were doing:
A[tab]It is not acceptable to Ms. A. to close a form from within itself.

B[tab]You provide no mechanisim for the User to 'correct' their failure to select from the ComboBox list

other 'issues' include attempting to set the focus to other controls. This may be O.K. but I do not follow the rationale, as it 'seems like' just a long way 'round the barn to the objective of closing the form.

Private Sub cboCust_LostFocus()

    Dim strMsg As String
    Dim strTitle As String
    Dim intStyle As Integer
    Dim MyResp As Integer
    
    If (IsNull(Me!cboCust)) Then
        strMsg = "Are You sure, you do not want to choose a Customer."
        strMsg = strMsg & vbCrLf & "Failure to Select a Customer"
        strMsg = strMsg & vbCrLf & "Will force the closing of this Form"
        strTitle = "Bill To Customer Required"
        intStyle = vbYesNo + vbQuestion
        MyResp = MsgBox(strMsg, intStyle, strTitle)
        If (MyResp <> vbYes) Then
            Me.txtCompany.SetFocus
         Else
            DoCmd.OpenForm "frmCloseOrders"
        End If
    End If

End Sub
'Then I have a Addnew reord Button which I added the check box to it:
Private Sub cmdNewRec_Click()

    Dim strMsg As String
    Dim strTitle As String
    Dim intStyle As Integer
    Dim MyResp As Integer

    If Me!SD <> Me!SC Then
        strMsg = "Entries are out of Balance!"
        strMsg = strMsg & "Do You Wish to Correct this?"
        intStyle = vbYesNo + vbQuestion
        strTitle = "Inventory Issue"
        MyResp = MsgBox(strMsg, vbOKOnly, strTitle)
        If (MyResp <> vbNo) Then
            Me!txtSC.SetFocus
        Else
        DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
        DoCmd.GoToRecord , , acNewRec
        Me.cboCust.Value = 0
        Me.cboCust.SetFocus
    End If

End Sub

Private Sub cmdCloseOrders_Click()

    Dim strMsg As String
    Dim strTitle As String
    Dim intStyle As Integer

    strMsg = "Closing frmOrders. Please Close this form"
    strMsg = strMsg & "to continue other operations"
    intStyle = vbOKOnly
    strTitle = "Closing Orders Form"
    MsgBox strMsg, intStyle, strTitle

    DoCmd.Close acForm, "frmOrders", acSaveNo

End Sub

MichaelRed

 

[sanan]

Hi MichaelRed
Thanks so much for all your help and advice, Let me go through them, and I will let you know.
But it appears to be allright.

Best regards
sanan

 

[sanan]

Hi MichaelRed
Thanks again for your reply.
I tried your codes; I had some success with it, and some Problems.
Major problem was that the little form “frmcloseorder” was popping up at the time of closing and at some other event too.
Is there Any way to avoid that, some thing like hiding the form.
How is it? I tried these codes in OnOpen event;
Forms!frmcloseorder.visiable=False
But, I had no success.
Anyhow, I changed your codes a little And basically deleted the Extra Form too.
Here are the codes:

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.ShipName.SetFocus
ElseIf (MyResp = vbYes) Then
Me.CompanyName.SetFocus
Me.Combo144.SetFocus
Else
DoCmd.Close acForm, [sales], acSaveNo
End If
End If
End Sub

It is working, and basically satisfies my needs at this time.
But I have few question on it;
In above as you can see I have the following;
Me.Combo144.Value = "Guest"
But I rather to have some thing like this;
Combo144.DataItem (1) Or Me. Combo144.DataItem (1)
But neither works, Why?
Another issue When our MsgBox appears, If I just use the close Button of the MsgBox, It acts like if I had pushed the No Button.
Why is it?
Is there ant way to fix that?

Best regards
Sanan

 

[MichaelRed]

Confession. I am not following your changes. Why was the code polaced in the form open event?

Your original code had it in the lost focus event of the combo box, anf the revised code also appears to place it there. Lost the concept of what you are discussing here.

I am not aware that there is a property / method ".DataItem" associated with the Ms.A. combobox control. Perhaps you meant to refere to ".ItemData"? If so, the ubiquitous {F1} (aka HELP) has a useful explination.

MsgBox is -at times- somewhat complex. The value returned in ambigious circumstances depends on several factors, including the defaultbutton (which I did not "program") so it defaults to zero (0) which i think returns the first button in the "intStyle" specification. Again, the ubiquitous {F1} has some useful (if arcane) reading on the subject.

Re the MsgBox "popping up" on 'other occassions, I have no idea, if it is really happening 'randomly'. The (sample) code I provided would (COULD) only ever trigger the MsgBox from through the "Loft_Focus" event of the combobox itself. Other 'code' or user manipulation may, of course, set focus to it and subsquent activity instantiate the LostFocus event. One common issue in this arena is the "Tab Order" of the forms' controls and the tabStop property of the individual control. Again, the ubiquitous {F1} coud prove useful.





MichaelRed

 

[sanan]

Hi MichaelRed
Thanks again for all your good advices.
I am a little confused about our extra Form, where do I have to place the following codes;
Private Sub cmdCloseOrders_Click()
Dim strMsg As String
Dim strTitle As String
Dim intStyle As Integer
strMsg = "Closing frmOrders. Please Close this form"
strMsg = strMsg & "to continue other operations"
intStyle = vbOKOnly
strTitle = "Closing Orders Form"
MsgBox strMsg, intStyle, strTitle
DoCmd.Close acForm, "frmOrders", acSaveNo
End Sub
Do I have to place the in our comboBox Lostfocus Event?

I also have another related problem, There is another TextBox control in my Form by name of InvoiceNo which actually has index no. 1 and the user must enter a Value for it, Therefore I basically placed exact coding as our comboBox Lostfocus event in it’s lostfocus Event and it is not working, And the user can basically move away from control with out entering any thing and my MsgBox does not pop up, Here are the codes;

Private Sub InvoiceNo_LostFocus()
Dim strMsg As String, strTitle As String
Dim intStyle As Integer
If IsNull(Me.InvoiceNo) Or Me.InvoiceNo = "" Then
strMsg = "You must Enter an Invoice No."
strTitle = "Invoice Number Required"
intStyle = vbOKOnly
MsgBox strMsg, intStyle, strTitle
Me.ReffrenceID.SetFocus
Me.InvoiceNo.SetFocus
End If
End Sub

Why is it?

Best regards
Sanan

 

[MichaelRed]

re the first. You noted that you had 'discarded' the form, so the code shoulod be irrelevant. If using hte form, hts code it the "OnClick" event procedure of the command button (note the specific NAME) of the control.

The second routine is not te same as the comboboxs' LostFocus event.

In a more general sense, where you have multiple requirements for your records, the set would normally be handled in a V&V (Verification and Validation) procedure. Commonly, ALL controls, requiring specific entries would include a simple call to the V&V routine. It would then check each control for required entries, posting an error msg and setting focus to the FIRST control where the V&V finds a discrepancy. These routines can get to be somewhat elaborate, and are generally beyonf the scope of what I am able to discuss in these fora. I can only suggest that you consider some more professional help from local source(s).




MichaelRed

 

[dRahme]

A Combo Box, A Close Button

On form load, Set focus on the Combo Box

'If the Combo Box is Used:

Private Sub CboEntry_AfterUpdate()

If Not IsNull(Me!CboEntry) Then
Me.Form1.Visible = True 'Or whatever
End If

End Sub


On Lost Focus of the combo box, check for Null

Private Sub CboEntry_LostFocus()
Dim Answer
Dim Response
Dim Closetheform
Dim Continue

'Check for Entry

If IsNull(Me!CboEntry) Then

Answer = MsgBox("Either Enter Something or Close the Form. Do you wish to close the form?", vbYesNo)

End If

'If yes, Set focus on the Close Form Button

If Answer = vbYes Then
GoTo Closetheform
End If

'If no, Set focus back to the Combo Box

If Answer = vbNo Then
MsgBox "Enter something in the combo box"
GoTo Continue
End If


Continue: Me.CboEntry.SetFocus
End

Closetheform:

Me.CmdClose.SetFocus 'Close Button


End Sub

 

[sanan]

Hi There
MichaelRed, And dRahme Thanks so much for your comments.
But what is wrong for the following codes for my InvoiceNo TextBox control LostFocus, which are identical to our ComBobox Control;
Private Sub InvoiceNo_LostFocus()
Dim strMsg As String, strTitle As String
Dim intStyle As Integer
If IsNull(Me.InvoiceNo) Or Me.InvoiceNo = "" Then
strMsg = "You must Enter an Invoice No."
strTitle = "Invoice Number Required"
intStyle = vbOKOnly
MsgBox strMsg, intStyle, strTitle
Me.ReffrenceID.SetFocus
Me.InvoiceNo.SetFocus
End If
End Sub
It is not working And I just can not get my MsgBox Poping Up Like before, A user must enter a value here and shouldnot be able to move on at all if the do not enter any value and MsgBox should warn them, But it is not acting.

Best regards,
Sanan

 

[dRahme]

Hi, I copied your code out and tried it. It works fine for me. You might want to ensure you are setting focus on the invoice text box. Can't lose focus if it is not set. Or, check the invoice proerties and ensure there is an [Event Procedure] in the Lost Focus event.

 

[PHV]

And is by chance InvoiceNo numeric instead of text ?

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

 

[MichaelRed]

A 'common' issue with some Ms. A. apps arises because the the controls are named with the same name as the source field. Then Ms. A. gets 'confused' regarding which object is being addressed. Check the (name & control source) properties of the controls and their control sources. Also check the name property of the associated label.





MichaelRed