Lookup previous records

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Neil M - 24 Oct 2005 10:20 GMT

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.

Reply to this Message

Chris B - 24 Oct 2005 10:58 GMT

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

>Don't know whether anyone can help with this.
>
[quoted text clipped - 7 lines]
>
>Thanks.

Reply to this Message

Neil M - 24 Oct 2005 11:18 GMT

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

> HI Neil,
> Below is some code from a database that I made, with thanks to others in this
[quoted text clipped - 44 lines]
> >
> >Thanks.

Reply to this Message

Neil M - 24 Oct 2005 11:57 GMT

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
[quoted text clipped - 56 lines]
> > >
> > >Thanks.

Reply to this Message

Chris B - 24 Oct 2005 13:00 GMT

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.
[quoted text clipped - 30 lines]
>> > >
>> > >Thanks.

Reply to this Message

Chris B - 24 Oct 2005 13:19 GMT

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.

>Hi Neil,
>Im still new to this so bear with me,
[quoted text clipped - 19 lines]
>>> > >
>>> > >Thanks.

Reply to this Message

Neil M - 25 Oct 2005 13:36 GMT

OK thanks i'll try that and let you know.

> Another simple way would be to make the field that the [cboTimeIn] is bound
> to in the Table, Indexed,
[quoted text clipped - 24 lines]
> >>> > >
> >>> > >Thanks.

Reply to this Message