OK guys, this is my** last question** to you on Access. I’ve done plenty of research on this one and wrote a script , they only thing is that it only does half the job.
With some help from the internet, i coded up a script that performs a check on duplicate values.
My database has a Room Bookings form which consists of the room name, periods and booking date (BDate) fields. I need to prevent a duplicate on the same date, period and room.
The only problem with my code is that it only does a check on one of the fields e.g. BDate although i select a different period or room it still comes up with an error message saying that booking already exists? This means it is only checking the duplicate value of 1 field and not a combination of fields.
Here is my code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strSearch As String
Dim varKey As Variant
strSearch = "BDate = #" & Me.BDate & "# And Period1 = """ & Me.Period1 & """And Period2 = """ & Me.Period2 & """And Period3 = """ & Me.Period3 & """And Period4 = """ & Me.Period4 & """And Period5 = """ & Me.Period5 & """And Period6 = """ & Me.Period6 & """And Lunch = """ & Me.Lunch & """And After_School = """ & Me.After_School & """"
varKey = DLookup("Booking_ID", "Furtherbookings", strSearch)
If Not IsNull(varKey) Then
If MsgBox("Booking already exists Booking ID: " & varKey & ". Do you wish to continue to create a new record?", vbYesNoCancel) = vbYes Then
Else
Cancel = True
Me.Undo
End If
End If
End Sub
Can any1 identify where i am going wrong?
I’d really appreciate any links to help/guidance/tutorials etc etc to resolve this problem!
I can use a composite key but wont work as data is coming from two tables?
I have a form which has a date, room and periods fields (periods 1-6) each which either contain an X if it isn’t booked and the period number if it is booked.
Do you have allergies to queries, or you like to give yourself the hardest time possible? which one is it chanda? Its so easy if you do it with query. I am confused here, I don’t u/s what you are doing really? your field names are not making any sense to me at all. I will solve it by query if you want me to.
I suggest, change the fields name right now, otherwise later on you wont u/s ur own database.
or .. you can create a query to check if the values entered by the user exist ... on the submit button, create a 'onMouseClick' event to execute the query. Hmmm ... but how would you check the result of the query? You'll have to use VB for that I guess.
Wait, you are taking input from the user and then checking if a duplicate exists? That will be quite irritating for the user ... consider the case where most of the rooms are booked. So if the user wants to find a vacant room, he'll have to go about guessing which room is available? So if there are a total of 10 rooms and 8 are booked then the user will enter the details 8 times (in a worst case scenario). That is a problem bro ... you need to rethink the design ..
I hope I didn't mess things up and got them right?
I know its possible with a simple search query but my end user prefers a msg box to appear when she types in a date and selects the periods and room that she requires to tell her that it is already booked.
omg yes its possible in query, how would it be possible, well create a query, then goto ur form and in events use upload events, so the moment form uploads it will ask you this question or show you how many rooms are available. Aik kaam kero, tell her to hire me I wont charge her much but she would have to share her hotel with me I should give everything for free
since you are not pro at this, I wont write down anything for you to copy it, I will give you lil home work. How about going in query section click on a wizard and look for duplicate query wizard and use your table and fields and try to manage it on ur own. This is just the beginning, hopefully u will make me proud
okay then computers are mine and she can have the room. Then why were you talking about the rooms if its not a hotel just a comp lounge, you are such dumbo.
First thing first, deal with the query, and I will help you create a search engine too :p, that lady whoever is asking you do stuff, she doesn’t know how database works, and thats what i hate in clients, they don’t know anything yet they think they know everything. I love ma boss, he just show me the stuff he wants me to do, we discuss it and then its mine, he has no say in it, unless he wants me to do few more changes
okay now type your sql query here. I wanna see what you actually did in it?
Nos shut up, he is my student rite now this is first in life I am teaching someone something, don’t ruin it gareeb ke pait pe laat na maro yaar, yeh ghareeb mar jae gee:p
you are very cute 
I wont charge her much but she would have to share her hotel with me 
i prefer working with queries than programming vb scripts and have no allergy wots so ever with them at all. 