Displaying object based on specific record (1 Viewer)

[kooheji]

Hello,
I am having bit of confusion in trying to come up with a code that will assist in completing my database display (for a warehouse rental database).

My aim is to have a form display a layout of the site and overlay an image over a store if its occupied else leave the image off if otherwise.

I have everything in my table plus a checkbox field that states if vacancy is "yes' or "no"; say I have 60 stores to rent and they have specific names (unit = A09) and I already 'drew' the layout on a form (all 60 of them) with renaming the boxes for each unit (Name = A09).

How can I program a code that upon opening the form it will like

If 'Box Name on form' = 'unit name on table' AND 'Vacancy = No' then 'redbox.visible = yes'


thank you.

 

[JHB]

Use a recordset and a loop to cycle through the recordset result.
Recordsource for the recordset could be something like below.

SELECT [unit name] From YourTable Where Vacancy ='No';

 

[kooheji]

Thank you for replying.

I forgot to mention that all 60 records must be on a single form to display the layout plan accordingly.

Earlier I tried to do it in one record with 60 fields; but this will fail when each unit requires to display its address, space, and tenant details.

Is there a work around to display them all records in one form.

Attached is how the form looks like, and each box suppose to be a record.

 

[JHB]

I've made a small sample for you in the attached database.
I think it is much easier as a long explain how to do it.

 

[kooheji]

Thank you very much,

I doubt I'd understand the code (even after going through all day yesterday what on earth recordset and how to loop it mean) but it works exactly how I need it to: many records on one single form.

Thank you again, I appreciate it.

 

[JHB]

Thank you very much,

I doubt I'd understand the code (even after going through all day yesterday what on earth recordset and how to loop it mean) but it works exactly how I need it to: many records on one single form.

Thank you again, I appreciate it.

You're welcome.
Only for clarifying, "many records on one single form." is not the correct term, but you've many objects (boxes) on a form.

By the way, did you read my signature?

 

[kooheji]

Does the code accommodate a combo box because I want to add another option (Vacant, Reserved, Occupied)?

 

[JHB]

If you mean to add a combo box in the table structure, then no, not without changing the code.

 

[kooheji]

If you mean to add a combo box in the table structure, then no, not without changing the code.

Alright? I mean can I use CASE for this or it still requires something else?

(I'm sorry, but I barely can keep up with vba)

 

[JHB]

Try it now, database attached.

 

[kooheji]

Try it now, database attached.

You never disappoint; thank you again.

It successfully integrated to my main database.

 

[JHB]

It successfully integrated to my main database.

Then everything is good, luck with it!