This is my query,ValidateRegistration:
SELECT Registration.RegistrationID, Registration.CustomerID,
Registration.RoomID AS Registration_RoomID, Registration.StartDate,
Registration.EndDate, Room.RoomID AS Room_RoomID, Room.RoomNo, Room.BlockID,
Room.TypeID, Room.PriceID, Room.Register, Room.Notes
FROM Room INNER JOIN Registration ON Room.RoomID = Registration.RoomID;
And this is the code at txtCustomerID:
Private Sub txtCustomerID_BeforeUpdate(Cancel As Integer)
If DCount("*", "[ValidateRegistration]", "CustomerID = '" &
Me.[txtCustomerID] & "' And Register = True") > 0 Then
MsgBox "Room already selected"
Cancel = True 'stop the process
End If
End Sub
It is not working mean it does not track the duplication. Thanks again.
Ofer Cohen said:
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
:
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.
:
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
:
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.
