RE: duplication record

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Thanks for the reply. I've never done any query before, so I just select all
the fields from both table Registration and Room. Is it correct?

In addition, you need to link both tables using the field that ia common in
both of them.

Can you post the SQL (query) you have now and the code you used in the
BeforeUpdate event?

When you say it doesn't work do you mean you get an error message or it
doesnt track duplicates?

--
Good Luck
BS"D


"yanz" wrote:

Thanks for the reply. I've never done any query before, so I just select all
the fields from both table Registration and Room. Is it correct?

After that, I put the code at before update but it is not working. For your
information, to validate another fields at the same form is not null, I've
put all the code at on click event. Is it because of the on click event, the
before update event is not working or I make any other mistakes?
The txtCustomerID at the form also is been passed from another form using
OpenArgs, I just inform if it have any connection to the 'not working' matter.

Sorry for those questions, I'm a newbie and the questions asked could be
ridiculous and thank you again.

"Ofer Cohen" wrote:

First create a query that link both tables, so it will return the Register
for each customer

On the before update event of the customer text box add the code that check
for duplicates in query above

If DCount("*","[QueryName]","CustomerID = " & Me.[CustomerID] & " And
Register = True") >0 Then
MsgBox "Room already selected"
Cancel = True 'stop the process
End If

If the CustomerId is a string type field you need to change the criteria
adding single quote

If DCount("*","[QueryName]","CustomerID = '" & Me.[CustomerID] & "' And
Register = True") >0 Then
MsgBox "Room already selected"
Cancel = True 'stop the process
End If

--
Good Luck
BS"D


"yanz" wrote:

Hi,
This is my tables:

Registration(RegistrationID,CustomerID,RoomID,StartDate,EndDate)
Room(RoomID,RoomNo,BlockID,TypeID,PriceID,Register,Notes)

The form will saved all the record into table Registration.
How can I prevent duplication of the record in my form?
I want the form to validate if the same Registration.CustomerID with
Room.Register=True, the record can't be saved and message will be appeared.
I appreciate any guidance, thank you.


.

Relevant Pages

  • RE: Stopping new record creation
    ... Use the form's Before Update event. ... data and if found can cancel the update: ... MsgBox "Customer Not Found" ... MsgBox "Order Date Requored" ...
    (microsoft.public.access.modulesdaovba)
  • Re: Need validation rule to pevent duplicating a customer number
    ... I create the customer number myself, ... letters of the last name and first 2 letters of the first name, ... digits open for duplicates. ... Private Sub txtCustomerNumber_BeforeUpdate ...
    (microsoft.public.access.gettingstarted)
  • Re: Need validation rule to pevent duplicating a customer number
    ... "BruceM" wrote: ... I create the customer number myself, ... letters of the last name and first 2 letters of the first name, ... digits open for duplicates. ...
    (microsoft.public.access.gettingstarted)
  • Re: Deleting Anything Duplicate
    ... One with the Customer ID's and their ... For, create a totals query, i.e. depress the Total button on the toolbar ... ClientID and OrderDate, but a different OrderID. ... customer numbers and now we have duplicates that are hard to find and we ...
    (microsoft.public.access.queries)
  • Re: Duplicates
    ... If you want Access to check for duplicates, the spelling of all fields would ... Is there a chance you could USB? ... visually determine that a customer already existed (and select that ... As an aside, when your customer Smith moves to 2 Avenue Road, or across ...
    (microsoft.public.access.gettingstarted)