How to find duplicate value on unbound form. (1 Viewer)

Y

yameen2011

Registered User.
Local time
Today, 13:53
Joined
Jan 19, 2013
Messages
59
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.
 

Attachments

  • sample.accdb
    608 KB · Views: 99
  • sample.png
    sample.png
    19 KB · Views: 87
Uncle Gizmo

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 10:53
Joined
Jul 9, 2003
Messages
16,278
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

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 10:53
Joined
Jul 9, 2003
Messages
16,278
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
 
Y

yameen2011

Registered User.
Local time
Today, 13:53
Joined
Jan 19, 2013
Messages
59
Uncle Gizmo said:
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!
Click to expand...

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.
 
Y

yameen2011

Registered User.
Local time
Today, 13:53
Joined
Jan 19, 2013
Messages
59
Uncle Gizmo said:
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
Click to expand...

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

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 10:53
Joined
Jul 9, 2003
Messages
16,278
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

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:53
Joined
May 7, 2009
Messages
19,230
here take a look.
 

Attachments

  • sample.zip
    32.1 KB · Views: 75
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:53
Joined
May 7, 2009
Messages
19,230
you're welcome yameen! is you're brother yasar?
 
Y

yameen2011

Registered User.
Local time
Today, 13:53
Joined
Jan 19, 2013
Messages
59
arnelgp said:
here take a look.
Click to expand...


Dear arnelgp,
Can you please help me.


Code: 
        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
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:53
Joined
May 7, 2009
Messages
19,230
is this on the same form?
why did you not send this revised form
so i can incorporate the code.

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, ....
 
Y

yameen2011

Registered User.
Local time
Today, 13:53
Joined
Jan 19, 2013
Messages
59
arnelgp said:
is this on the same form?
why did you not send this revised form
so i can incorporate the code.

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, ....
Click to expand...

attached the sample file
 

Attachments

  • sample.accdb
    508 KB · Views: 85
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:53
Joined
May 7, 2009
Messages
19,230
you may try now
 

Attachments

  • aa_sample(1).zip
    47.5 KB · Views: 71
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:53
Joined
May 7, 2009
Messages
19,230
God bless you too.
 
moke123

moke123

AWF VIP
Local time
Today, 05:53
Joined
Jan 11, 2013
Messages
3,913
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.
 
You must log in or register to reply here.

Similar threads

K
Solved I want to Show and Hide a field in a SubForm depending on the value in a field on Main form.
Replies
4
Views
771
Gasman
Gasman
A
Trying to populate a field in main form based on value from unbound textbox in datasheet subform
2
Replies
21
Views
1,198
Pat Hartman
P
S
Solved How to create Multivalued on an Unbound Combo box
Replies
11
Views
1,284
theDBguy
theDBguy
M
Form to create new entry: how to check uniqueness of value immediately
Replies
9
Views
867
Pat Hartman
P
R
Need to show/hide unbound text on a tabbed subform
Replies
3
Views
609
RGinNC
R

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom