If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Dlookup - double booking
Help please!!!!!
I am building a data base for booking audiovisual equipment. My table structure is the following. tblUserInfo ID_PR (Primary Key, auto number) Name Last Name tblLoanInfo LoanDate Equipment StartTime EndTime ID_SC(Primary Key, auto number) ID_PR (number) tblEquipment Equipment (store equipment names) tblStartTime StartTime (stores available Start Times, date/time field) tblReturnTime ReturnTime (stores available Return Times, date/time field) My problem is that when I have a booking lets say for today 1/26/2010, DVD-01, 8:00 am, to 10:00am, I should not be able to book that same DVD-01 at 9:00, to 10:00. I have done the Allen Browne clashing events query and I see my double booking (that query worked). I have read about using the DLookup in the form instead of the clashing query to check if the record can be booked. This is where I am now. I can't get the DLookup to work. I have been on this for days and need some help. Can any one help me get the right DLookup structure so that I wont be able to book and equipment. Thank you. |
#2
|
|||
|
|||
Dlookup - double booking
comments in-line below
"cadillac" u57797@uwe wrote in message news:a2b450dc77e89@uwe... Help please!!!!! I am building a data base for booking audiovisual equipment. My table structure is the following. tblUserInfo ID_PR (Primary Key, auto number) Name Last Name "Name" is a reserved word in Access. Use something else to avoid confusing (both) yourself and Access. tblLoanInfo LoanDate Equipment If this "points back to" the tblEquipment, how do you know which "plow" you're referring to? Is it the one that's been in and out of repair 100 times since you got it, or the brand new one, still under warranty? StartTime EndTime ID_SC(Primary Key, auto number) If this is an Autonumber field in this table, what does "SC" stand for? I'd think a primary key for a tblLoanInfo would be something more like "LoanID" (or as you have, "ID_Loan") ID_PR (number) Is this your foreign key, pointing back at the person who took out the equipment? tblEquipment Equipment (store equipment names) A bit risky, using "equipment name" as a unique identifier ... this is the primary key, right? If you don't have one, you need one! tblStartTime StartTime (stores available Start Times, date/time field) tblReturnTime ReturnTime (stores available Return Times, date/time field) My problem is that when I have a booking lets say for today 1/26/2010, DVD-01, 8:00 am, to 10:00am, I should not be able to book that same DVD-01 at 9:00, to 10:00. I have done the Allen Browne clashing events query and I see my double booking (that query worked). I have read about using the DLookup in the form instead of the clashing query to check if the record can be booked. This is where I am now. I can't get the DLookup to work. I have been on this for days and need some help. Can any one help me get the right DLookup structure so that I wont be able to book and equipment. You didn't post the "DLookup" structure you used, so it's a bit hard to offer an idea of what needs to change. "I can't get the DLookup to work" doesn't give us much information to go on. Thank you. More info, please... Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. |
#3
|
|||
|
|||
Dlookup - double booking
Thanks for your reply. I wrote to fast an left some stuff out, hope this
clears it better. tblUserInfo PrimayID (Primary Key, auto number) FirstName LastName tblLoanInfo LoanDate Equipment StartTime EndTime SecondayID(Primary Key, auto number) PrimaryID (number) tblEquipment Equipment (store equipment names, Primary Key) tblStartTime StartTime (stores available Start Times, date/time field, PrimaryKey) tblReturnTime ReturnTime (stores available Return Times, date/time field, PrimaryKey) The dlookup structure is where am lost, I was using some examples from this site but did not get it working. Jeff Boyce wrote: comments in-line below Help please!!!!! [quoted text clipped - 5 lines] Name Last Name "Name" is a reserved word in Access. Use something else to avoid confusing (both) yourself and Access. tblLoanInfo LoanDate Equipment If this "points back to" the tblEquipment, how do you know which "plow" you're referring to? Is it the one that's been in and out of repair 100 times since you got it, or the brand new one, still under warranty? StartTime EndTime ID_SC(Primary Key, auto number) If this is an Autonumber field in this table, what does "SC" stand for? I'd think a primary key for a tblLoanInfo would be something more like "LoanID" (or as you have, "ID_Loan") ID_PR (number) Is this your foreign key, pointing back at the person who took out the equipment? tblEquipment Equipment (store equipment names) A bit risky, using "equipment name" as a unique identifier ... this is the primary key, right? If you don't have one, you need one! tblStartTime StartTime (stores available Start Times, date/time field) [quoted text clipped - 16 lines] structure so that I wont be able to book and equipment. You didn't post the "DLookup" structure you used, so it's a bit hard to offer an idea of what needs to change. "I can't get the DLookup to work" doesn't give us much information to go on. Thank you. More info, please... Regards Jeff Boyce Microsoft Access MVP |
#4
|
|||
|
|||
Dlookup - double booking
We still haven't seen the expression you're using for your DLookup().
Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "cadillac" u57797@uwe wrote in message news:a2b543fe2cfb2@uwe... Thanks for your reply. I wrote to fast an left some stuff out, hope this clears it better. tblUserInfo PrimayID (Primary Key, auto number) FirstName LastName tblLoanInfo LoanDate Equipment StartTime EndTime SecondayID(Primary Key, auto number) PrimaryID (number) tblEquipment Equipment (store equipment names, Primary Key) tblStartTime StartTime (stores available Start Times, date/time field, PrimaryKey) tblReturnTime ReturnTime (stores available Return Times, date/time field, PrimaryKey) The dlookup structure is where am lost, I was using some examples from this site but did not get it working. Jeff Boyce wrote: comments in-line below Help please!!!!! [quoted text clipped - 5 lines] Name Last Name "Name" is a reserved word in Access. Use something else to avoid confusing (both) yourself and Access. tblLoanInfo LoanDate Equipment If this "points back to" the tblEquipment, how do you know which "plow" you're referring to? Is it the one that's been in and out of repair 100 times since you got it, or the brand new one, still under warranty? StartTime EndTime ID_SC(Primary Key, auto number) If this is an Autonumber field in this table, what does "SC" stand for? I'd think a primary key for a tblLoanInfo would be something more like "LoanID" (or as you have, "ID_Loan") ID_PR (number) Is this your foreign key, pointing back at the person who took out the equipment? tblEquipment Equipment (store equipment names) A bit risky, using "equipment name" as a unique identifier ... this is the primary key, right? If you don't have one, you need one! tblStartTime StartTime (stores available Start Times, date/time field) [quoted text clipped - 16 lines] structure so that I wont be able to book and equipment. You didn't post the "DLookup" structure you used, so it's a bit hard to offer an idea of what needs to change. "I can't get the DLookup to work" doesn't give us much information to go on. Thank you. More info, please... Regards Jeff Boyce Microsoft Access MVP |
#5
|
|||
|
|||
Dlookup - double booking
sorry about that...
these are two examples I have been trying to figure out. Private Sub Returntime_AfterUpdate() If Not IsNull(DLookup("[EquipmentID]", "tblloaninfo", "[Starttime] = #" & Me. Returntime & "#AND [Returntime] = #" & Me.StartTime & "#")) Then MsgBox Me.EquipmentID & " is booked for the selected time, please select another Equipment or time!", vbOKOnly Else DoCmd.RunCommand acCmdSaveRecord End If End Sub and I also found this one and gave it a try Private Sub Returntime_AfterUpdate() Dim strWhere As String Dim varResult As Variant Dim strMsg As String Const strcJetDateTime = "\#mm\/dd\/yyyy hh\:nn\:ss\#" If ((Me.StartTime = Me.StartTime.OldValue) And (Me.Returntime = Me. Returntime.OldValue) And (Me.EquipmentID = Me.EqipmentID.OldValue)) Or IsNull (Me.StartTime) Or IsNull(Me.Returntime) Or IsNull(Me.EquipmentID) Then 'do nothing Else strWhere = "(StartTime " & Format(Me.Returntime, strcJetDateTime) & ")" AND " & Format(Me.StartTime, strcJetDateTime) & " ReturnTime) AND (EquipmentID = " & Me.EquipmentID & ")" Debug.Print strWhere varResult = DLookup("SecondaryID", "tblloaninfo", strWhere) If Not IsNull(varResult) Then strMsg = "Clashes with booking # " & varResult & vbCrLf & "Continue anyway?" If MsgBox(strMsg, vbYesNo + vbDefaultButton2, "Double-booked") vbYes Then Cancel = True End If End If End If End Sub I have not been able to get none of them to work. thank you for you help... Jeff Boyce wrote: We still haven't seen the expression you're using for your DLookup(). Regards Jeff Boyce Microsoft Access MVP Thanks for your reply. I wrote to fast an left some stuff out, hope this clears it better. [quoted text clipped - 84 lines] Jeff Boyce Microsoft Access MVP |
Thread Tools | |
Display Modes | |
|
|