Dlookup - double booking

[cadillac]

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.

 

[Jeff Boyce]

comments in-line below

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.

 

[cadillac]

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.

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

 

[Jeff Boyce]

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.

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.

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

 

[cadillac]

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

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]