Lookup previous records

  • Thread starter Thread starter Neil M
  • Start date Start date
N

Neil M

Don't know whether anyone can help with this.

I have a database that i unput time and date linked to a room.
But I don't want to book the same date/time for the room and I want a
message box to appear stating that it has been double booked.

so for this i need a testing part where it checks the rest of the records
(within a query that only holds records for the particular date and room),
how can i do this please?


Thanks.
 
HI Neil,
Below is some code from a database that I made, with thanks to others in this
forum I was able to get it this far, what this code does is it looks at your
records and if there is a record that matches your entry it warns you and
then takes you that record...
You will need to change my code to match yours,

[Case Number] is the TEXT box on your form and [ POA Details] is the table to
which it pertains.
Place this code in the BeforeUpdate event procedure of the TEXT box
-----------------------
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.Case_Number.Value
stLinkCriteria = "[Case Number]=" & "'" & SID & "'"


If DCount("[Case Number]", "POA Details", stLinkCriteria) > 0 Then

Me.Undo

MsgBox "WARNING " _
& SID & " Already Exists." _
& vbCr & vbCr & "You will now be taken there.", vbInformation _
, "Duplicate Information"

rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing
 
Thanks that helped a lot.
I tweaked the code and got the correct results (I didn't want it to go to
the record, etc..) but Thanks for all your help...

Thanks again,

Neil M


Chris B via AccessMonster.com said:
HI Neil,
Below is some code from a database that I made, with thanks to others in this
forum I was able to get it this far, what this code does is it looks at your
records and if there is a record that matches your entry it warns you and
then takes you that record...
You will need to change my code to match yours,

[Case Number] is the TEXT box on your form and [ POA Details] is the table to
which it pertains.
Place this code in the BeforeUpdate event procedure of the TEXT box
-----------------------
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.Case_Number.Value
stLinkCriteria = "[Case Number]=" & "'" & SID & "'"


If DCount("[Case Number]", "POA Details", stLinkCriteria) > 0 Then

Me.Undo

MsgBox "WARNING " _
& SID & " Already Exists." _
& vbCr & vbCr & "You will now be taken there.", vbInformation _
, "Duplicate Information"

rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing
---------------------------------
Chris

Neil said:
Don't know whether anyone can help with this.

I have a database that i unput time and date linked to a room.
But I don't want to book the same date/time for the room and I want a
message box to appear stating that it has been double booked.

so for this i need a testing part where it checks the rest of the records
(within a query that only holds records for the particular date and room),
how can i do this please?

Thanks.
Click to expand...
Click to expand...
 
actually it didn't work - it displays the messagebox regardless of the
input.
I doesn't seem to be looking up the Table (via the Query) to see if any of
the same times exist on the same room and date

Here's my code, see if I've missed anything;

***** CODE BEGINS *****

Dim SID As String
Dim stLinkCriteria As String
'Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.cboTimeIn.Value
stLinkCriteria = "[cboTimeIn]=" & "'" & SID & "'"

If DCount("[cboTimeIn]", "Qry_RoomSub", stLinkCriteria) > 0 Then
'Me.Undo
MsgBox SID & " - Time already Exists." & vbCr & vbCr & "Please input
a different time to avoid overlapping.", vbInformation, "Duplicate Time
Exists"
End If

'Set rsc = Nothing

***** CODE ENDS *****


Neil M said:
Thanks that helped a lot.
I tweaked the code and got the correct results (I didn't want it to go to
the record, etc..) but Thanks for all your help...

Thanks again,

Neil M


Chris B via AccessMonster.com said:
HI Neil,
Below is some code from a database that I made, with thanks to others in this
forum I was able to get it this far, what this code does is it looks at your
records and if there is a record that matches your entry it warns you and
then takes you that record...
You will need to change my code to match yours,

[Case Number] is the TEXT box on your form and [ POA Details] is the
Click to expand...
table
to
which it pertains.
Place this code in the BeforeUpdate event procedure of the TEXT box
-----------------------
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.Case_Number.Value
stLinkCriteria = "[Case Number]=" & "'" & SID & "'"


If DCount("[Case Number]", "POA Details", stLinkCriteria) > 0 Then

Me.Undo

MsgBox "WARNING " _
& SID & " Already Exists." _
& vbCr & vbCr & "You will now be taken there.", vbInformation _
, "Duplicate Information"

rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing
---------------------------------
Chris

Neil said:
Don't know whether anyone can help with this.

I have a database that i unput time and date linked to a room.
But I don't want to book the same date/time for the room and I want a
message box to appear stating that it has been double booked.

so for this i need a testing part where it checks the rest of the records
(within a query that only holds records for the particular date and room),
how can i do this please?

Thanks.
Click to expand...
Click to expand...
Click to expand...
 
Hi Neil,
Im still new to this so bear with me,
Id try changing the "Qry_RoomSub" portion of the code to the actual name of
your Table, i dont think it would work with a Query.

You could also try just using:-
**********Code Start******************
If Not IsNull(DLookUp("[Field name to lookup in on table]", [NAME OF YOUR
TABLE], _
"[Field name to lookup in on table] = '" & Me![cboTimeIn]& "'") Then
MsgBox "This Time already exists, Please input another time to avoid
overlapping", vbOKOnly
Cancel = True
End If
**********Code End****************
Let me know if it works out,
Chris

Neil said:
actually it didn't work - it displays the messagebox regardless of the
input.
I doesn't seem to be looking up the Table (via the Query) to see if any of
the same times exist on the same room and date

Here's my code, see if I've missed anything;

***** CODE BEGINS *****

Dim SID As String
Dim stLinkCriteria As String
'Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.cboTimeIn.Value
stLinkCriteria = "[cboTimeIn]=" & "'" & SID & "'"

If DCount("[cboTimeIn]", "Qry_RoomSub", stLinkCriteria) > 0 Then
'Me.Undo
MsgBox SID & " - Time already Exists." & vbCr & vbCr & "Please input
a different time to avoid overlapping.", vbInformation, "Duplicate Time
Exists"
End If

'Set rsc = Nothing

***** CODE ENDS *****
Thanks that helped a lot.
I tweaked the code and got the correct results (I didn't want it to go to
Click to expand...
[quoted text clipped - 56 lines]
Click to expand...
 
Another simple way would be to make the field that the [cboTimeIn] is bound
to in the Table, Indexed,
ie In the Design view of your table, find the field for cboTimeIn and where
it says INDEXED put Yes-No Duplicates.


Chris said:
Hi Neil,
Im still new to this so bear with me,
Id try changing the "Qry_RoomSub" portion of the code to the actual name of
your Table, i dont think it would work with a Query.

You could also try just using:-
**********Code Start******************
If Not IsNull(DLookUp("[Field name to lookup in on table]", [NAME OF YOUR
TABLE], _
"[Field name to lookup in on table] = '" & Me![cboTimeIn]& "'") Then
MsgBox "This Time already exists, Please input another time to avoid
overlapping", vbOKOnly
Cancel = True
End If
**********Code End****************
Let me know if it works out,
Chris
actually it didn't work - it displays the messagebox regardless of the
input.
Click to expand...
[quoted text clipped - 30 lines]
Click to expand...
 
OK thanks i'll try that and let you know.


Chris B via AccessMonster.com said:
Another simple way would be to make the field that the [cboTimeIn] is bound
to in the Table, Indexed,
ie In the Design view of your table, find the field for cboTimeIn and where
it says INDEXED put Yes-No Duplicates.


Chris said:
Hi Neil,
Im still new to this so bear with me,
Id try changing the "Qry_RoomSub" portion of the code to the actual name of
your Table, i dont think it would work with a Query.

You could also try just using:-
**********Code Start******************
If Not IsNull(DLookUp("[Field name to lookup in on table]", [NAME OF YOUR
TABLE], _
"[Field name to lookup in on table] = '" & Me![cboTimeIn]& "'") Then
MsgBox "This Time already exists, Please input another time to avoid
overlapping", vbOKOnly
Cancel = True
End If
**********Code End****************
Let me know if it works out,
Chris
actually it didn't work - it displays the messagebox regardless of the
input.
Click to expand...
[quoted text clipped - 30 lines]
Thanks.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top