Sub DCount updating some records but not others

[MrTickle]

Hi there

I have a database that takes accommodation bookings. I have a form for bookings that adds details to the 'bookings' table and a sub-form for reservations within that booking that adds records to the 'Room Reservations' table.

For every booking there is a finite number of rooms reserved, so I am limiting the amount of room reservations that can be made. To achieve this, I have a procedure that counts the number of records in 'Room Reservations' and returns the result for comparison against the amount of rooms reserved, as follows:

Private Sub Number_of_Rooms_AfterUpdate()
Dim RmTkn As Integer
Dim CurrentRecord As Integer

CurrentRecord = [Booking Reference]

RmTkn = DCount("[Status]", _
"[Room Reservations]", _
"[Room Reservations]![Booking Reference] = CurrentRecord And [Status] = 'Booked'")

[Rooms Taken] = RmTkn

This works for the first few bookings but then returns a value of '0' for the rest. Can anyone shed any light on this little problem?

Thanks in advance

Andy

 

[pbaldy]

I'm surprised it works at all. You need to concatenate the variable into the string:

http://www.mvps.org/access/general/gen0018.htm

 

[AccessMSSQL]

You are using CurrentRecord incorrectly. This only returns the record number (not the field). Use this:

RmTkn = DCount("[Status]", _
"[Room Reservations]", _
"[Room Reservations]![Booking Reference] = " & me.[Booking Reference] & " And [Status] = 'Booked'")

 

[MrTickle]

You are using CurrentRecord incorrectly. This only returns the record number (not the field). Use this:

RmTkn = DCount("[Status]", _
"[Room Reservations]", _
"[Room Reservations]![Booking Reference] = " & me.[Booking Reference] & " And [Status] = 'Booked'")

Thank you so much, this works a treat!

Andy