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

 

[sanan]

Hi There
I would like to Thank you all, for all your Excellent Helps and advices.
Finally I had it to work, I do not know how, because at first I tried all your suggestions, I tried to rename my TextBox, I changed the DataType to nchar (The same as my ComboBox) Nothing seemed to be working, But some how suddenly it worked, therefore I do not known what was wrong with it.

I have another related problem in the same TextBox (InvoiceID),
I would like to stop the users to enter a duplicate value in my InvoiceID textbox, I came up with the following codes, But again it is not working,
Any suggestion, (Like What events? or the codes itself) why it is not working;

Private Sub InvoiceID100_AfterUpdate()
Dim error_fld As String
error_fld = Me.InvoiceID100.Value
ErrHandler:
Error ([-2147217900])
MsgBox "Invoice ID Has Been Issued Already"
End Sub

Also as you know the Record Source of my form (sales) is a query (view) named “Sales Query” and it has a unique Table by name of “Tablesales”, But Some times (More than usual off course) that I open up my Form (sales) my form’s Unique Table becomes deleted, which requires that I use the form’s Property >Data>Unique Table and set it back to it’s original value “table sales”. (What could be the cause of it?)
Therefore I came up with following Button, which I called it “refresh” and in its On_Click Event I have the following codes;

Private Sub Command166_Click()
Forms!sales.UniqueTable = "Tablesales"
End Sub
The above used to work for me before, But it is not working now, could you tell me what is wrong with it?

Best regards
Sanan

 

[PHV]

stop the users to enter a duplicate value in my InvoiceID textbox
In the BeforeUpdate event procedure of the TextBox call the DLookUp function and play with the Cancel parameter if a match is found.

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
Thank you for your advice.
I tried the following codes from your suggestion, But no Success as of yet.
Could you tell me, what is it that I am doing wrong.
I actually used 2 different technique or approaches;
Tech. 1

Private Sub InvoiceID100_BeforeUpdate(Cancel As Integer)
Dim Msg As String, Style As Integer, Title As String
Dim DL As String, Criteria As String
DL = vbNewLine & vbNewLine
Criteria = "[InvoiceID100] = " & Me.InvoiceID100.Value
If Not IsNull(DLookup("[InvoiceID100]", Criteria)) Then
Msg = "InvoiceId100 Has Been Issued Allready, Try Another One"
Style = vbCritical + vbOKOnly
Title = "Duplicate Invoice ID Error! . . ."
MsgBox Msg, Style, Title
Cancel = True
End If
End sub

· Tech. 2

Private Sub InvoiceID100_BeforeUpdate(Cancel As Integer)
For I = 1 To DCount("*", "TableSales")
If DLookup("[InvoiceID100]", "tablesales", "me.newrecord") = DLookup("[InvoiceID100]", "tablesales", "me.salesID=I") Then
Msg = "InvoiceID Has Been Issued Allready, Try Another One"
Style = vbCritical + vbOKOnly
Title = "Duplicate Invoice ID Error! . . ."
MsgBox Msg, Style, Title
Cancel = True
End If
Next
End Sub


Best regards
Sanan

 

[PHV]

Private Sub InvoiceID100_BeforeUpdate(Cancel As Integer)
Dim Msg As String, Style As Integer, Title As String
Dim DL As String, Criteria As String
DL = vbNewLine & vbNewLine
Criteria = "[InvoiceID100] = " & Me.InvoiceID100.Value
If Not IsNull(DLookup("[InvoiceID100]", [highlight]"[table name]"[/highlight], Criteria)) Then
Msg = "InvoiceId100 Has Been Issued Allready, Try Another One"
Style = vbCritical + vbOKOnly
Title = "Duplicate Invoice ID Error! . . ."
MsgBox Msg, Style, Title
Cancel = True
End If
End sub

If InvoiceID100 is not of type numeric in the table then:
Criteria = "[InvoiceID100] = [highlight]'[/highlight]" & Me.InvoiceID100.Value[highlight] & "'"[/highlight]

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
Thanks again for your reply.
I tried your codes,
But I still have no success; actually I tried many different version of it, but no success.
Here are the codes, that I tried;

Private Sub InvoiceID100_BeforeUpdate(Cancel As Integer)
Dim Msg As String, Style As Integer, Title As String
Dim DL As String, Criteria As String
DL = vbNewLine & vbNewLine
Criteria = "[InvoiceID100] = " & Me.InvoiceID100.Value Or
Criteria = "[InvoiceID100] = ' " & Me.InvoiceID100.Value & " ' "

If Not IsNull(DLookup("[InvoiceID100]", "[TableSales]", "[Criteria]")) Then Or
If Not IsNull(DLookup("[InvoiceID100]", "[TableSales]", "Criteria")) Then
If Not IsNull(DLookup("[InvoiceID100]", "[TableSales]", Criteria)) Then

Msg = "InvoiceId Has Been Issued Allready, Try Another One"
Style = vbCritical + vbOKOnly
Title = "Duplicate Invoice ID Error! . . ."
MsgBox Msg, Style, Title
Cancel = True
End If
End Sub

But no success,
The Data Type of InvoiceID100 was originally nchar,
I tried numeric with the first selection of above Criteria, No success
I should mention, that the InvoiceId100 Lostfocus events (My previous question)
Works fine for both nchar and Numeric data type selection, and the MsgBox pops up,
But for the case of Data type to be text, The LostFocus events works a little strange and msgBox Pops Up even if I have entered some value for it.

Best regards
Sanan