Text validation

[associates]

Hi,

I have two question. I have a form that allows user to add new data into database. The form also checks whether the data entered by user is valid meaning no existence in the database. My first question is how to make the cursor point back to the textbox where the invalid data is without going to the next textbox.

Here is my code:

Private Sub Creditor_Code_Exit(Cancel As Integer)

If DCount("[Creditor_Code]", "Creditors", "[Creditor_Code]='" & Me.Creditor_Code & "'") > 0 Then
MsgBox "Sorry, " & Me.Creditor_Code & " already exist. Try a different code."
Me.Undo
'Me.Creditor_Code.setfocus ' this doesn't work
'me.Creditor_Code.tabindex = 0 ' this doesn't work either
End If
End Sub

My second question is that is there an easy way to check if the Creditor_Code textbox value has changed or not. The reason is this if i click on the edit button to edit a creditor info, I'd be prompted with a dialog popup message that says sorry POP already exist. Try a different code. The Creditor_Code_Exit sub is invoked when it shouldn't be.

SOrry for making this too long. I hope i can explain it well.

Thank you in advance and look forward to hearing from you

 

[ZmrAbdulla]

Private Sub Creditor_Code_Exit(Cancel As Integer)
    If DCount("[Creditor_Code]", "Creditors", "[Creditor_Code]='" & Me.Creditor_Code & "'") > 0 Then
       MsgBox "Sorry, " & Me.Creditor_Code & " already exist. Try a different code."
       Me.Undo
       Me.AnotherControl.SetFocus
       Me.Creditor_Code.SetFocus
    End If
End Sub

for the second question.. look for the following
BeforeUpdate Event & Dirty Event
controls and form have both events


________________________________________________________
Zameer Abdulla
Help to find Missing people
There’s a world of difference between editorials and advertorials

 

[PHV]

Use the BeforeUpdate event procedure instead of the Exit one:
Private Sub Creditor_Code_BeforeUpdate(Cancel As Integer)
If DCount("Creditor_Code", "Creditors", "Creditor_Code='" & Me.Creditor_Code & "'") > 0 Then
MsgBox "Sorry, " & Me.Creditor_Code & " already exist. Try a different code."
Me.Undo
Cancel = True
End If
End Sub

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

 

[associates]

Yes, it works now. Thank you to both PHV and Zameer Abdulla for your helpful answer.

However, if i may ask another question. on one of the form, i have a listbox that has 5 columns. The first column is invoice_no, followed by invoice_date, job_no, amount, and status. The value of amount is obtained by summing up invoice_amount1, invoice_amount2,...3,...4 and so on from the table for a particular job. Because there is no such field as amount in the table, i use the expression builder in the SQL statement:Query builder. and i got it right.

However, my question is because the amount has to be in currency format with 2 decimal points. How do i format this? I have tried with the field properties but didnot help me.

thank you in advance for your help

 

[ZmrAbdulla]

right click on the grid of the field while you are at the query designer and bring properties, set formatting there.
==================================
Right click on different places in the designview brings different menus. try it.
================================
for showing on the form you can format it from the form designer view.

________________________________________________________
Zameer Abdulla
Help to find Missing people
There’s a world of difference between editorials and advertorials

 

[associates]

Hi, Zameer Abdulla

Thank for your reply.

Zameeer, I did as directed by you that is right click on the grid of the field which has got the expression in it at the query designer and select properties, set formatting to be currency, then close the query. Change the form from design view to form view. Nothing happened. If i go back to the query builder again, right click on that grid to bring up the properties, the formatting is none. This is very strange. i just close in a way that i usually close it.

Right click on different places in the designview brings different menus. try it.
for showing on the form you can format it from the form designer view.

SOrry Zameer, I don't quite get these. Could you explain a bit more.

Thank you Zameer

 

[PHV]

You may use the CCur function in your expression.

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

 

[ZmrAbdulla]

right-click on
1) table shown on top
2) empty area on top
3) field name
etc..

Change the format in form too

Also consider PHV's suggestion of "CCur".

________________________________________________________
Zameer Abdulla
Help to find Missing people
There’s a world of difference between editorials and advertorials

 

[associates]

Thanks to you both again, Zameer and PHV

hmmm... it did not work with CCur.

Here is what i put into the expression builder.

Expr1: CCur([Invoice_Amount1]+[Invoice_Amount2]+[Invoice_Amount3]+[Invoice_Amount4]+[Invoice_Amount5]+[Invoice_Amount6])+(([Invoice_Amount1]+[Invoice_Amount2]+[Invoice_Amount3]+[Invoice_Amount4]+[Invoice_Amount5]+[Invoice_Amount6])*0.1)

Perhaps, i should tell you this again that this field of amount that is to be shown on the listbox on a form does NOT exist on the table. In another word, there is no total amount on the table. I just produce this totalamount on the fly by adding all them up as shown above in the expression builder and get it appear on the listbox. In the table, i have Invoice_Amount1, Invoice_Amount2, Invoice_Amount3, Invoice_Amount4, Invoice_Amount5, Invoice_Amount6. And they are all in currency format.

Thank you once again and look forward to hearing from you

 

[ZmrAbdulla]

I can get this working

Expr1: (([Invoice_Amount1]+[Invoice_Amount2]+[Invoice_Amount3]+[Invoice_Amount4])*0.1)

I don't know why not for you. It is showing digits after the decimal point.

________________________________________________________
Zameer Abdulla
Help to find Missing people
There’s a world of difference between editorials and advertorials

 

[associates]

Sorry, it works now. I have a closing bracket but missed the opening bracket. Thank you, Zameer Abdulla