How to find duplicate value on unbound form.

[yameen2011]

Hi everyone,
I am running my own school and working on the school database for in which monthly fee vouchers generated, after depositing the fee in the bank by parents/students , bank dispatched the vouchers and scroll to me. After that i have to post that vouchers entry to my database for marking that voucher as paid at my end.
I have to post every voucher entry individually, therefore i am making an unbound form for multiple entries at once.
on that form only voucher number entered on the unbound form and Student_ID, Student_Name, Voucher_Amount fetched from the tbl_voucher.
now everything is fine
But i want to prevent the user to enter the voucher_no twice in the voucher_no field .
fields on the form
Voucher_no, Std_ID, Std_Name, Amount,
Voucher_no1, Std_ID1, Std_Name1, Amount1,
Voucher_no2, Std_ID2, Std_Name2, Amount2,
Voucher_no3, Std_ID3, Std_Name3, Amount3,
Voucher_no4, Std_ID4, Std_Name4, Amount4,
Voucher_no5, Std_ID5, Std_Name5, Amount5,


Sample data for voucher_No field
291,292,293,294,295
Any one guide me that how to compare the Voucher_no,Voucher_no2,Voucher_no3,Voucher_no4,Voucher_no5 fields for finding if there are duplicate values in any voucher_no field and prompt the user on clicking save button and prevent saving.

Sample attached.

thanks and regards.

 

[Uncle Gizmo]

If Voucher_no,Voucher_no2,Voucher_no3,Voucher_no4,Voucher_no5 fields, are as you appear to say "fields" in a table in your database, then your problems are only just beginning!

I have blogged about this error here:-

http://www.niftyaccess.com/excel-in-access/

The explanation is in text and video format.

A free tool is available to help repair your database.



Sent from my SM-G925F using Tapatalk

 

[Uncle Gizmo]

On re-reading your question, I'm wondering if you meant unbound textboxes, not Fields. If that's the case, please explain how you are saving the voucher numbers in to the table.

Sent from my SM-G925F using Tapatalk

 

[yameen2011]

If Voucher_no,Voucher_no2,Voucher_no3,Voucher_no4,Voucher_no5 fields, are as you appear to say "fields" in a table in your database, then your problems are only just beginning!

Thanks for your response,
In the table tbl_Voucher field is Voucher_No only. and the Voucher_no,Voucher_no2,Voucher_no3,Voucher_no4,Voucher_no5 etc... fields are on the unbound form. this form not related to any record source.
i just want to compare values on the form fields Voucher_no,Voucher_no2,Voucher_no3,Voucher_no4,Voucher_no5 fields

i just want to do this ,
if 291 voucher no is entered in the Voucher_no field on the form then user again enter 291 on any other Voucher_no2,Voucher_no3,Voucher_no4,Voucher_no5 field then msgbox will appear that
"duplicate values found in these fields Voucher_no2,Voucher_no3,Voucher_no4,Voucher_no5 fields
"
or voucher no once entered above not allowed to enter again.

one thing more i want to clear you that this done on only unbound form. after checking this i will save detail for all the voucher_no,Voucher_no2,Voucher_no3,Voucher_no4,Voucher_no5 fields to the table with DAO recordset.

i think you reply me from your mobile , hope if you open my sample datbase you will understand my issue.

 

[yameen2011]

On re-reading your question, I'm wondering if you meant unbound textboxes, not Fields. If that's the case, please explain how you are saving the voucher numbers in to the table.

Sent from my SM-G925F using Tapatalk

yes i meant unbound textboxes on unbound form , and i just want to find if there are any macthing voucher no in Voucher_no2,Voucher_no3,Voucher_no4,Voucher_no5 textboxes.
just prompt through msgbox while pressing command button.

 

[Uncle Gizmo]

But it appears from your comments that you are saving the vouchers to multiple fields?

Extract:-
i will save detail for all the voucher_no,Voucher_no2,Voucher_no3,Voucher_no4,Voucher_no5 fields to the table with DAO recordset.

Please explain this process...

Sent from my SM-G925F using Tapatalk

 

[arnelgp]

here take a look.

 

[yameen2011]

here take a look.

thanks for the help.
this is the solution that i want.
thanks dear.
you are the boss.

 

[arnelgp]

you're welcome yameen! is you're brother yasar?

 

[yameen2011]

here take a look.

Dear arnelgp,
Can you please help me.

        Dim db As DAO.Database
        Dim rst As DAO.Recordset
            Set db = CurrentDb()
            Set rst = db.OpenRecordset("tbl_Voucher", dbOpenDynaset)
    
                rst.FindLast "Voucher_No =" & Voucher_No
                rst.Edit
                rst!Paid_Amount = Me.Amount
                rst!Paid_Date = Date
                rst!IS_PAid = "Y"
                rst.Update
    
      MsgBox "Voucher marked Paid" & Chr(13) & _
            "Successfully.", vbInformation, "MAHI-DBS * Alert"
            Me.Voucher_No = ""
            Me.Std_ID = ""
            Me.Std_Name = ""
            Me.Amount = ""
            Me.IS_PAid = ""

this code is for edit a record in the table for marking as paid and enter paid_date, amount in the table for a single voucher no
please guide me how to setup that function (you used in your attachment) with above code. for the all voucher_no textboxes on the form

 

[yameen2011]

is you're brother yasar?

can't understand?

 

[arnelgp]

is this on the same form?
why did you not send this revised form
so i can incorporate the code.

    Dim i As Integer
    Dim ctl As Access.TextBox
    For i = 0 To 9
	Set ctl = Me.Detail.Controls("Voucher_No" & i)
	' check if there is a Voucher Number entered to this textbox
	If Trim(ctl.Value & "") <> "" Then
		CurrentDb.Execute "Update tbl_Voucher Set " & _
			"Paid_Amount = Nz(Paid_Amount, 0) + " & _
				Nz(Me.Detail.Controls("Amount" & i).Value, 0) & ", " & _
			"Paid_Date = " & Date() & ", " & _
			"Is_Paid = """" & "Y" & """" & " " & _
			"Where Voucher_No = " & ctl.Value
		ctl.Value = Null
		Me.Detail.Controls("Std_Id" & i).Value = Null
		Me.Detail.Controls("Std_Name" & i).Value = Null
		Me.Detail.Controls("Amount" & i).Value=Null
		Me.Detail.Controls("Is_Paid" & i).Value = Null
	End If
    Next

your textbox name should be consistent, with Suffix 0 up to 9, eg:
Amount0, Amount1, ....
Is_Paid0, Is_Paid1, ....

 

[yameen2011]

is this on the same form?
why did you not send this revised form
so i can incorporate the code.

    Dim i As Integer
    Dim ctl As Access.TextBox
    For i = 0 To 9
	Set ctl = Me.Detail.Controls("Voucher_No" & i)
	' check if there is a Voucher Number entered to this textbox
	If Trim(ctl.Value & "") <> "" Then
		CurrentDb.Execute "Update tbl_Voucher Set " & _
			"Paid_Amount = Nz(Paid_Amount, 0) + " & _
				Nz(Me.Detail.Controls("Amount" & i).Value, 0) & ", " & _
			"Paid_Date = " & Date() & ", " & _
			"Is_Paid = """" & "Y" & """" & " " & _
			"Where Voucher_No = " & ctl.Value
		ctl.Value = Null
		Me.Detail.Controls("Std_Id" & i).Value = Null
		Me.Detail.Controls("Std_Name" & i).Value = Null
		Me.Detail.Controls("Amount" & i).Value=Null
		Me.Detail.Controls("Is_Paid" & i).Value = Null
	End If
    Next

your textbox name should be consistent, with Suffix 0 up to 9, eg:
Amount0, Amount1, ....
Is_Paid0, Is_Paid1, ....

attached the sample file

 

[arnelgp]

you may try now

 

[yameen2011]

you may try now

it works for me as i want.
Thank you very much for your support.
GOD BLESS YOU.

 

[arnelgp]

God bless you too.

 

[moke123]

just a suggestion but it would appear to me that you would want to structure your data more like a ledger db. If you google around you can find a data model to follow.