Add checkbox problem

[Guest]

Hi, Im beginner in Access and I got problem with my form.

My form (frmRegistration)
-save all the data key in by the user to the table Registration
-also need to tick the Register field at table Room when clicked the button
save

My problem is all the data can be saved successful but I forgot to add the
checkbox on the form at first.
Now when I add the checkbox, it does not function at all. I thought maybe it
is because my form's record source.
This is my form's record source: Registration
How can I change my record source or my form in order to tick the checkbox?

This is my tables:
Registration(RegistrationID,CustomerID,RoomID,Star tDate,EndDate)
Room(RoomID,RoomNo,BlockID,TypeID,PriceID,Register ,Notes)

Im really newbie in Access and could you give a step by step answers for my
understanding.
Thank you.

 

[John W. Vinson]

Hi, Im beginner in Access and I got problem with my form.

My form (frmRegistration)
-save all the data key in by the user to the table Registration
-also need to tick the Register field at table Room when clicked the button
save

My problem is all the data can be saved successful but I forgot to add the
checkbox on the form at first.
Now when I add the checkbox, it does not function at all. I thought maybe it
is because my form's record source.
This is my form's record source: Registration
How can I change my record source or my form in order to tick the checkbox?

This is my tables:
Registration(RegistrationID,CustomerID,RoomID,Star tDate,EndDate)
Room(RoomID,RoomNo,BlockID,TypeID,PriceID,Register ,Notes)

Im really newbie in Access and could you give a step by step answers for my
understanding.
Thank you.

Well... I think that you could make a very good argument that the field
Register in table Rooms should *simply not exist*.

The state of having someone registered in the room is not properly an
attribute of the room. The value of this field doesn't depend on the room
itself; it depends on SOME OTHER table - registration.

You can readily determine whether a room is currently registered by a Query
joining Room to Registration with criteria like

StartDate <= Date() AND (EndDate >= Date() OR EndDate IS NULL)

That said... you cannot just put a checkbox bound to a field in Room onto a
form bound to Registration, since it's not in that table. You will need some
VBA code to run an Update query to update the Room table. But as I say... it's
probably not proper design to have this field in the table *at all*.

John W. Vinson [MVP]

 

[Guest]

Thanks for the quick reply and your recommendation, that make me realize my
design is not in proper way. But if there is no Register field, how can I
make the combobox which can only list all of the room that still empty.
Before this, I set at the combobox's row source as Room.Register Like No.

Im really interested to do like the way you say but I still wanna know if I
still use checkbox to set it as register, can you show me the VBA code to
update the Room table? It is because before this I tried to put some code in
the save button but I got msg "Data type mismatch in criteria expression" and
the code is like:

Dim strSQL As String

strSQL= "UPDATE Room Set Register = True WHERE RoomID=" & Me!RoomID

DoCmd.RunSQL strSQL

Please do not feel that I'm not interested with what you've said. I wish I
can learn both ways. Thank you.

 

[John W. Vinson]

Thanks for the quick reply and your recommendation, that make me realize my
design is not in proper way. But if there is no Register field, how can I
make the combobox which can only list all of the room that still empty.
Before this, I set at the combobox's row source as Room.Register Like No.

LIKE is a String searching operator for searching text fields using wildcards.
If you're going to use the Yes/No field then simply use

SELECT <whatever> FROM ROOM WHERE Register = True;

or even just

SELECT <whatever> FROM ROOM WHERE Register;

If the WHERE clause evaluates to TRUE the record will be retrieved; if it
evaluates to FALSE (or NULL) it won't.

Im really interested to do like the way you say but I still wanna know if I
still use checkbox to set it as register, can you show me the VBA code to
update the Room table? It is because before this I tried to put some code in
the save button but I got msg "Data type mismatch in criteria expression" and
the code is like:

Dim strSQL As String

strSQL= "UPDATE Room Set Register = True WHERE RoomID=" & Me!RoomID

DoCmd.RunSQL strSQL

It sounds like RoomID might be a Text field rather than a Number. If so you
need some syntactically required delimiters:

strSQL= "UPDATE Room Set Register = True WHERE RoomID='" & Me!RoomID & "'"


John W. Vinson [MVP]