Best practice to store/manipulate Excel spreadsheet cells

[Alp Bekisoglu]

Hi Duane,

Thanks for the code. Got actually four more questions, if I may ask.
1- As you have already seen the floorplans, any advice on how can I show the
diagonal cut-off ?
2- Is it possible to write a sub or function to set the values of any
clicked "cell" to the field value on an open form? Or do I have to write the
code Me.controlname = Forms!open_form.field_name under each control's
OnClick event?
3- Embarrased to ask but will anyway; can you just instruct me on the X and
Y coordinates (rowYval and seatXval) I've got 44 rows and many seats for
each row (up to 91). I know they are twips (I hope) but am a bit confused.
Both height & width for each box on my form is set to 0.432cm for now but
I'm planning to shrink them a little more but on the report I am not sure
what size should I use to fit the entire Hall A Ground Floor on A4 landscape
mode.
4- Could it be possible to omit boxes (defined by/or content is say zero)
for the aisles in the report and form?

Thanks in advance and I hope I'm not running your patience down.

Alp

If you replace the sasComment field with a numeric field, you would need
to remove two pairs of quotes around ctl.Value:

Function UpdateSeating()
'
'bunch of snipped code
'
strSQL = "INSERT INTO tblSeatAssignments (sasEvnID, sasSeaID,
sasComment) " & _
"Values(" & Me.cboEvnID & ", " & lngSeaID & "," & ctl.Value & ")"
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
Set ctl = Nothing
'
'bunch of snipped code
'
End Function

--
Duane Hookom
MS Access MVP
--

Hi Duane,

I have tried your sample, then added my own table and copied the form to
work with the new table but came to a halt. It started giving me error
messages and refused to open.

Anyway, I then tied the table on your form and found out that it only
accepts typed-in input and mainly text (no numbers). I am still trying to
get it to work the way I want: i.e. a form is preopened in the background
providing the company_id and I would like to pass on this company_id into
these locations(seats in your example) preferably by mouse-click. Thus I
tried the OnClick event to pass this number. The number IS transferred
but the UpdateSeating function errs out. If I don't run the function,
then I don't get the company_id recorded. But if I do the same with say
company name, then no errors!

I know it must have something to do with """" s since text requires them
and numbers don't but still fighting it. Or could it be the
Screen.ActiveControl?

Alp

After reviewing your floor plan, I would still attempt to implement this
method. I would create temp tables for each of the different sections
and then use subforms and subreports to display the sections.

--
Duane Hookom
MS Access MVP
--

Hi Duane,

I've d/l'd the file and will try and see how I can modify it for my
purpose. Thanks for the sample and the guidance.

Alp

I created a demo for another similar question. You can download the
sample file from www.access.hookom.net/samples/seating.zip.

You basically describe the available seating locations in a couple
tables. You can then make reservations for events. I think this sample
could be easily modified to meet your needs.

--
Duane Hookom
MS Access MVP
--

Hi Alp,

Thanks for the information. I feel I'm beginning to understand the
situation - though if there's an easy answer I don't know it. I'll
ask
some fellow MVPs: probably one of them has had a similar problem
before
and will be able to suggest something.

The 255-field limit isn't a barrier: the way to handle this would be
to
have a "tall narrow" table, in which each record represents one
"cell",
with fields identifying the cell, which company it's booked to (a 1:M
relationship with your table of companies) and so on.

On Mon, 27 Mar 2006 16:52:27 +0300, "Alp Bekisoglu"
<alp@access_ng.com>
wrote:


Hi John,

OK some answers to your questions:
1) Actually, part of an event managment database.
2) The actual building footprint is a rectangle BUT with a sort of
cut-off
corner at one side. sort of like:
__________72
| \
| 44
| \
|___________| 21 here
91
72 cells at top and 91 cells at the bottom with a height of 44 cells.
Ofcourse there are quite a few "cells" that correspond to the
aisles/alleys
in between.
The location(s) are actual spreadsheet cells arranged in a way to
look like
squares (rowheight 15, column width 2.14) since you can't actually
tell
excel to have squares properly...

I had "tried to try" (!) over the weekend to do this solely in Access
and
almost gave up completely since 255 field limitation/table is there.

Sort of "no luck" situation I guess...

Thanks for your help and ideas and am willing to hear any further
suggestions if you're not fed up yet.


Alp

Hi Alp,

I'm not sure how I'd tackle this. Here are a couple of questions
that
would probably affect my decision.

1) Is this going to be an application in its own right, keeping
track of
the areas that have been reserved, or part of a larger database
application (e.g. an event management system)?

2) Are the "floor plans" simple rectangles, or are they actual
floor
plans and hence may have irregular shapes?

A) If it's an application in its own right, I'd think in terms of
building it as an Excel application (with a custom user interface,
worksheet protection and so on to prevent users interfering with
the
layout of the worksheet(s)). Data would be stored either in hidden
worksheets or in a Jet database (.mdb file) managed by the Excel
VBA
code.

B) If it's a module in a database application and the "floor plans"
are
simple rectangles, I'd probably use a table with one record for
each
unit on the plan (each 1m square, in your example) and fields to
show
the coordinates (location) and status of each square.

The user interface could be a continuous form bound to a temporary
table
(one field for each 1m unit on the X axis, one record for each unit
on
the Y axis), with code to run queries to fill the temporary table
from
the main table as the form opens, and to update the main table as
the
user clicks on the controls on the form. Alternatively you could
use a
Flexgrid ActiveX control (and I'm sure there are other ways).

C) Finally, if the thing is a module in a larger application _and_
you
have to handle odd-shaped floor plans: the approach at (B) above is
still possible, though it's more work to store and display an
irregular
floor plan. Or you could use Excel to display the sheets and
provide the
user interface.


On Sat, 25 Mar 2006 13:28:46 +0200, "Alp Bekisoglu"
<alp@access_ng.com>
wrote:


Hi John and sorry for my late response.

I do see your point with excel worksheets. As I tried to explain
briefly
in
my initial post, the main issue is the need to work with a "floor
plan"
which has pre-defined enclosures (so to speak) of 1 sq meters and
indicate
(coloring was the first thing that came to mind) it has been
reserved. It
would be nicer if the routine could also count the cells (again
thinking
in
excel I think) to yield the total sq meters.

What else would you recommend to accomplish this within Access? I
just
couldn't find a solution yet.

Thanks in advance.

Alp

Hi Alp,

What you describe is fraught with danger, because there's no way
to
ensure that the cell references you create in the database will
remain
valid: it's so easy to modify worksheets and workbooks. So if
data
integrity is an issue, you'd probably be better off storing the
data
that is now in Excel workbooks in database tables, and exporting
it to
Excel if or when needed.

But if you do have to use Excel, the first step is to learn
enough about
programming Excel to make it do the Excel part of what you
describe
(running a macro when the user clicks on a cell, changing
colours,
getting the cell reference into a suitable format and appending
it to an
Access table, and so on).

Time enough after that to think about the interaction between
Access and
Excel.

On Wed, 22 Mar 2006 11:45:05 +0200, "Alp Bekisoglu"
<alp@access_ng.com>
wrote:


Hi Experts,

A2000, multiuser, FE/BE database. I would like to design a form
with the
intention:
When a backend record's (a company record) form is opened
- OnClick event of a button to open a specific Excel file
(preferably a
specific tab as well)
- Store in db the mouse selected cell coordinates (together with
the tab
name)
- If possible give an option to change the fill color of the
selected
cells

Moving to another record (company) and hitting the same button,
the same
shall apply but the previously recorded cells should show as
unavailable
(color filled).

In short, I would like to make a sort of "visual" space
reservation sub
application for an event where printing of the spreadsheet will
also be
involved.

Any guidance, suggestion, a starting point, ... is most welcome.

Thanks in advance.

Alp

 

[Duane Hookom]

Answers in-line:

--
Duane Hookom
MS Access MVP
--

Hi Duane,

Thanks for the code. Got actually four more questions, if I may ask.
1- As you have already seen the floorplans, any advice on how can I show
the diagonal cut-off ?

Since my solution works with rectangle blocks of seats, I would keep a table
of "unavailable" seats in a table to append when scheduling for an event.
You could then use conditional formating or whatever to hide or lock them.

2- Is it possible to write a sub or function to set the values of any
clicked "cell" to the field value on an open form? Or do I have to write
the code Me.controlname = Forms!open_form.field_name under each control's
OnClick event?

You could use code like in the UpdateSeating() function to insert a value
from a control elsewhere on the form.

3- Embarrased to ask but will anyway; can you just instruct me on the X
and Y coordinates (rowYval and seatXval) I've got 44 rows and many seats
for each row (up to 91). I know they are twips (I hope) but am a bit
confused. Both height & width for each box on my form is set to 0.432cm
for now but I'm planning to shrink them a little more but on the report I
am not sure what size should I use to fit the entire Hall A Ground Floor
on A4 landscape mode.

The default scale in the report is twips. You may want to copy your
floorplan on graph paper or whatever to "picture" your exact sizes and
positions.

4- Could it be possible to omit boxes (defined by/or content is say zero)
for the aisles in the report and form?

See my answer to question 1. My original solution had some aisles defined.

Thanks in advance and I hope I'm not running your patience down.

Alp

If you replace the sasComment field with a numeric field, you would need
to remove two pairs of quotes around ctl.Value:

Function UpdateSeating()
'
'bunch of snipped code
'
strSQL = "INSERT INTO tblSeatAssignments (sasEvnID, sasSeaID,
sasComment) " & _
"Values(" & Me.cboEvnID & ", " & lngSeaID & "," & ctl.Value & ")"
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
Set ctl = Nothing
'
'bunch of snipped code
'
End Function

--
Duane Hookom
MS Access MVP
--

Hi Duane,

I have tried your sample, then added my own table and copied the form to
work with the new table but came to a halt. It started giving me error
messages and refused to open.

Anyway, I then tied the table on your form and found out that it only
accepts typed-in input and mainly text (no numbers). I am still trying
to get it to work the way I want: i.e. a form is preopened in the
background providing the company_id and I would like to pass on this
company_id into these locations(seats in your example) preferably by
mouse-click. Thus I tried the OnClick event to pass this number. The
number IS transferred but the UpdateSeating function errs out. If I
don't run the function, then I don't get the company_id recorded. But if
I do the same with say company name, then no errors!

I know it must have something to do with """" s since text requires them
and numbers don't but still fighting it. Or could it be the
Screen.ActiveControl?

Alp

After reviewing your floor plan, I would still attempt to implement
this method. I would create temp tables for each of the different
sections and then use subforms and subreports to display the sections.

--
Duane Hookom
MS Access MVP
--

Hi Duane,

I've d/l'd the file and will try and see how I can modify it for my
purpose. Thanks for the sample and the guidance.

Alp

I created a demo for another similar question. You can download the
sample file from www.access.hookom.net/samples/seating.zip.

You basically describe the available seating locations in a couple
tables. You can then make reservations for events. I think this
sample could be easily modified to meet your needs.

--
Duane Hookom
MS Access MVP
--

Hi Alp,

Thanks for the information. I feel I'm beginning to understand the
situation - though if there's an easy answer I don't know it. I'll
ask
some fellow MVPs: probably one of them has had a similar problem
before
and will be able to suggest something.

The 255-field limit isn't a barrier: the way to handle this would be
to
have a "tall narrow" table, in which each record represents one
"cell",
with fields identifying the cell, which company it's booked to (a
1:M
relationship with your table of companies) and so on.

On Mon, 27 Mar 2006 16:52:27 +0300, "Alp Bekisoglu"
<alp@access_ng.com>
wrote:


Hi John,

OK some answers to your questions:
1) Actually, part of an event managment database.
2) The actual building footprint is a rectangle BUT with a sort of
cut-off
corner at one side. sort of like:
__________72
| \
| 44
| \
|___________| 21 here
91
72 cells at top and 91 cells at the bottom with a height of 44
cells.
Ofcourse there are quite a few "cells" that correspond to the
aisles/alleys
in between.
The location(s) are actual spreadsheet cells arranged in a way to
look like
squares (rowheight 15, column width 2.14) since you can't actually
tell
excel to have squares properly...

I had "tried to try" (!) over the weekend to do this solely in
Access and
almost gave up completely since 255 field limitation/table is there.

Sort of "no luck" situation I guess...

Thanks for your help and ideas and am willing to hear any further
suggestions if you're not fed up yet.


Alp

Hi Alp,

I'm not sure how I'd tackle this. Here are a couple of questions
that
would probably affect my decision.

1) Is this going to be an application in its own right, keeping
track of
the areas that have been reserved, or part of a larger database
application (e.g. an event management system)?

2) Are the "floor plans" simple rectangles, or are they actual
floor
plans and hence may have irregular shapes?

A) If it's an application in its own right, I'd think in terms of
building it as an Excel application (with a custom user interface,
worksheet protection and so on to prevent users interfering with
the
layout of the worksheet(s)). Data would be stored either in hidden
worksheets or in a Jet database (.mdb file) managed by the Excel
VBA
code.

B) If it's a module in a database application and the "floor
plans" are
simple rectangles, I'd probably use a table with one record for
each
unit on the plan (each 1m square, in your example) and fields to
show
the coordinates (location) and status of each square.

The user interface could be a continuous form bound to a temporary
table
(one field for each 1m unit on the X axis, one record for each
unit on
the Y axis), with code to run queries to fill the temporary table
from
the main table as the form opens, and to update the main table as
the
user clicks on the controls on the form. Alternatively you could
use a
Flexgrid ActiveX control (and I'm sure there are other ways).

C) Finally, if the thing is a module in a larger application _and_
you
have to handle odd-shaped floor plans: the approach at (B) above
is
still possible, though it's more work to store and display an
irregular
floor plan. Or you could use Excel to display the sheets and
provide the
user interface.


On Sat, 25 Mar 2006 13:28:46 +0200, "Alp Bekisoglu"
<alp@access_ng.com>
wrote:


Hi John and sorry for my late response.

I do see your point with excel worksheets. As I tried to explain
briefly
in
my initial post, the main issue is the need to work with a "floor
plan"
which has pre-defined enclosures (so to speak) of 1 sq meters and
indicate
(coloring was the first thing that came to mind) it has been
reserved. It
would be nicer if the routine could also count the cells (again
thinking
in
excel I think) to yield the total sq meters.

What else would you recommend to accomplish this within Access? I
just
couldn't find a solution yet.

Thanks in advance.

Alp

Hi Alp,

What you describe is fraught with danger, because there's no way
to
ensure that the cell references you create in the database will
remain
valid: it's so easy to modify worksheets and workbooks. So if
data
integrity is an issue, you'd probably be better off storing the
data
that is now in Excel workbooks in database tables, and exporting
it to
Excel if or when needed.

But if you do have to use Excel, the first step is to learn
enough about
programming Excel to make it do the Excel part of what you
describe
(running a macro when the user clicks on a cell, changing
colours,
getting the cell reference into a suitable format and appending
it to an
Access table, and so on).

Time enough after that to think about the interaction between
Access and
Excel.

On Wed, 22 Mar 2006 11:45:05 +0200, "Alp Bekisoglu"
<alp@access_ng.com>
wrote:


Hi Experts,

A2000, multiuser, FE/BE database. I would like to design a form
with the
intention:
When a backend record's (a company record) form is opened
- OnClick event of a button to open a specific Excel file
(preferably a
specific tab as well)
- Store in db the mouse selected cell coordinates (together with
the tab
name)
- If possible give an option to change the fill color of the
selected
cells

Moving to another record (company) and hitting the same button,
the same
shall apply but the previously recorded cells should show as
unavailable
(color filled).

In short, I would like to make a sort of "visual" space
reservation sub
application for an event where printing of the spreadsheet will
also be
involved.

Any guidance, suggestion, a starting point, ... is most welcome.

Thanks in advance.

Alp

 

[Alp Bekisoglu]

Thanks a lot Duane for your patience and the response. Looks like I'm
getting somewhere. Not solved yet, but getting close. The minute I resolve
why the input works only upto and including J but no further, I think I can
say I'm almost done since the rest will be hiding/formatting.

Sincerely,

Alp

Answers in-line:

--
Duane Hookom
MS Access MVP
--

Hi Duane,

Thanks for the code. Got actually four more questions, if I may ask.
1- As you have already seen the floorplans, any advice on how can I show
the diagonal cut-off ?

Since my solution works with rectangle blocks of seats, I would keep a
table of "unavailable" seats in a table to append when scheduling for an
event. You could then use conditional formating or whatever to hide or
lock them.

2- Is it possible to write a sub or function to set the values of any
clicked "cell" to the field value on an open form? Or do I have to write
the code Me.controlname = Forms!open_form.field_name under each control's
OnClick event?

You could use code like in the UpdateSeating() function to insert a value
from a control elsewhere on the form.

3- Embarrased to ask but will anyway; can you just instruct me on the X
and Y coordinates (rowYval and seatXval) I've got 44 rows and many seats
for each row (up to 91). I know they are twips (I hope) but am a bit
confused. Both height & width for each box on my form is set to 0.432cm
for now but I'm planning to shrink them a little more but on the report I
am not sure what size should I use to fit the entire Hall A Ground Floor
on A4 landscape mode.

The default scale in the report is twips. You may want to copy your
floorplan on graph paper or whatever to "picture" your exact sizes and
positions.

4- Could it be possible to omit boxes (defined by/or content is say zero)
for the aisles in the report and form?

See my answer to question 1. My original solution had some aisles defined.

Thanks in advance and I hope I'm not running your patience down.

Alp

If you replace the sasComment field with a numeric field, you would need
to remove two pairs of quotes around ctl.Value:

Function UpdateSeating()
'
'bunch of snipped code
'
strSQL = "INSERT INTO tblSeatAssignments (sasEvnID, sasSeaID,
sasComment) " & _
"Values(" & Me.cboEvnID & ", " & lngSeaID & "," & ctl.Value & ")"
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
Set ctl = Nothing
'
'bunch of snipped code
'
End Function

--
Duane Hookom
MS Access MVP
--

Hi Duane,

I have tried your sample, then added my own table and copied the form
to work with the new table but came to a halt. It started giving me
error messages and refused to open.

Anyway, I then tied the table on your form and found out that it only
accepts typed-in input and mainly text (no numbers). I am still trying
to get it to work the way I want: i.e. a form is preopened in the
background providing the company_id and I would like to pass on this
company_id into these locations(seats in your example) preferably by
mouse-click. Thus I tried the OnClick event to pass this number. The
number IS transferred but the UpdateSeating function errs out. If I
don't run the function, then I don't get the company_id recorded. But
if I do the same with say company name, then no errors!

I know it must have something to do with """" s since text requires
them and numbers don't but still fighting it. Or could it be the
Screen.ActiveControl?

Alp

After reviewing your floor plan, I would still attempt to implement
this method. I would create temp tables for each of the different
sections and then use subforms and subreports to display the sections.

--
Duane Hookom
MS Access MVP
--

Hi Duane,

I've d/l'd the file and will try and see how I can modify it for my
purpose. Thanks for the sample and the guidance.

Alp

I created a demo for another similar question. You can download the
sample file from www.access.hookom.net/samples/seating.zip.

You basically describe the available seating locations in a couple
tables. You can then make reservations for events. I think this
sample could be easily modified to meet your needs.

--
Duane Hookom
MS Access MVP
--

Hi Alp,

Thanks for the information. I feel I'm beginning to understand the
situation - though if there's an easy answer I don't know it. I'll
ask
some fellow MVPs: probably one of them has had a similar problem
before
and will be able to suggest something.

The 255-field limit isn't a barrier: the way to handle this would
be to
have a "tall narrow" table, in which each record represents one
"cell",
with fields identifying the cell, which company it's booked to (a
1:M
relationship with your table of companies) and so on.

On Mon, 27 Mar 2006 16:52:27 +0300, "Alp Bekisoglu"
<alp@access_ng.com>
wrote:


Hi John,

OK some answers to your questions:
1) Actually, part of an event managment database.
2) The actual building footprint is a rectangle BUT with a sort of
cut-off
corner at one side. sort of like:
__________72
| \
| 44
| \
|___________| 21 here
91
72 cells at top and 91 cells at the bottom with a height of 44
cells.
Ofcourse there are quite a few "cells" that correspond to the
aisles/alleys
in between.
The location(s) are actual spreadsheet cells arranged in a way to
look like
squares (rowheight 15, column width 2.14) since you can't actually
tell
excel to have squares properly...

I had "tried to try" (!) over the weekend to do this solely in
Access and
almost gave up completely since 255 field limitation/table is
there.

Sort of "no luck" situation I guess...

Thanks for your help and ideas and am willing to hear any further
suggestions if you're not fed up yet.


Alp

Hi Alp,

I'm not sure how I'd tackle this. Here are a couple of questions
that
would probably affect my decision.

1) Is this going to be an application in its own right, keeping
track of
the areas that have been reserved, or part of a larger database
application (e.g. an event management system)?

2) Are the "floor plans" simple rectangles, or are they actual
floor
plans and hence may have irregular shapes?

A) If it's an application in its own right, I'd think in terms of
building it as an Excel application (with a custom user
interface,
worksheet protection and so on to prevent users interfering with
the
layout of the worksheet(s)). Data would be stored either in
hidden
worksheets or in a Jet database (.mdb file) managed by the Excel
VBA
code.

B) If it's a module in a database application and the "floor
plans" are
simple rectangles, I'd probably use a table with one record for
each
unit on the plan (each 1m square, in your example) and fields to
show
the coordinates (location) and status of each square.

The user interface could be a continuous form bound to a
temporary table
(one field for each 1m unit on the X axis, one record for each
unit on
the Y axis), with code to run queries to fill the temporary table
from
the main table as the form opens, and to update the main table as
the
user clicks on the controls on the form. Alternatively you could
use a
Flexgrid ActiveX control (and I'm sure there are other ways).

C) Finally, if the thing is a module in a larger application
_and_ you
have to handle odd-shaped floor plans: the approach at (B) above
is
still possible, though it's more work to store and display an
irregular
floor plan. Or you could use Excel to display the sheets and
provide the
user interface.


On Sat, 25 Mar 2006 13:28:46 +0200, "Alp Bekisoglu"
<alp@access_ng.com>
wrote:


Hi John and sorry for my late response.

I do see your point with excel worksheets. As I tried to explain
briefly
in
my initial post, the main issue is the need to work with a "floor
plan"
which has pre-defined enclosures (so to speak) of 1 sq meters and
indicate
(coloring was the first thing that came to mind) it has been
reserved. It
would be nicer if the routine could also count the cells (again
thinking
in
excel I think) to yield the total sq meters.

What else would you recommend to accomplish this within Access? I
just
couldn't find a solution yet.

Thanks in advance.

Alp

Hi Alp,

What you describe is fraught with danger, because there's no
way to
ensure that the cell references you create in the database will
remain
valid: it's so easy to modify worksheets and workbooks. So if
data
integrity is an issue, you'd probably be better off storing the
data
that is now in Excel workbooks in database tables, and
exporting it to
Excel if or when needed.

But if you do have to use Excel, the first step is to learn
enough about
programming Excel to make it do the Excel part of what you
describe
(running a macro when the user clicks on a cell, changing
colours,
getting the cell reference into a suitable format and appending
it to an
Access table, and so on).

Time enough after that to think about the interaction between
Access and
Excel.

On Wed, 22 Mar 2006 11:45:05 +0200, "Alp Bekisoglu"
<alp@access_ng.com>
wrote:


Hi Experts,

A2000, multiuser, FE/BE database. I would like to design a form
with the
intention:
When a backend record's (a company record) form is opened
- OnClick event of a button to open a specific Excel file
(preferably a
specific tab as well)
- Store in db the mouse selected cell coordinates (together
with the tab
name)
- If possible give an option to change the fill color of the
selected
cells

Moving to another record (company) and hitting the same button,
the same
shall apply but the previously recorded cells should show as
unavailable
(color filled).

In short, I would like to make a sort of "visual" space
reservation sub
application for an event where printing of the spreadsheet will
also be
involved.

Any guidance, suggestion, a starting point, ... is most
welcome.

Thanks in advance.

Alp

 

[Duane Hookom]

Glad to hear you are making progress. This is one of those threads where I
am really glad you can take the ideas and "play" with them rather than come
back for direction at every step.

--
Duane Hookom
MS Access MVP
--

Thanks a lot Duane for your patience and the response. Looks like I'm
getting somewhere. Not solved yet, but getting close. The minute I resolve
why the input works only upto and including J but no further, I think I
can say I'm almost done since the rest will be hiding/formatting.

Sincerely,

Alp

Answers in-line:

--
Duane Hookom
MS Access MVP
--

Hi Duane,

Thanks for the code. Got actually four more questions, if I may ask.
1- As you have already seen the floorplans, any advice on how can I show
the diagonal cut-off ?

Since my solution works with rectangle blocks of seats, I would keep a
table of "unavailable" seats in a table to append when scheduling for an
event. You could then use conditional formating or whatever to hide or
lock them.

2- Is it possible to write a sub or function to set the values of any
clicked "cell" to the field value on an open form? Or do I have to write
the code Me.controlname = Forms!open_form.field_name under each
control's OnClick event?

You could use code like in the UpdateSeating() function to insert a value
from a control elsewhere on the form.

3- Embarrased to ask but will anyway; can you just instruct me on the X
and Y coordinates (rowYval and seatXval) I've got 44 rows and many seats
for each row (up to 91). I know they are twips (I hope) but am a bit
confused. Both height & width for each box on my form is set to 0.432cm
for now but I'm planning to shrink them a little more but on the report
I am not sure what size should I use to fit the entire Hall A Ground
Floor on A4 landscape mode.

The default scale in the report is twips. You may want to copy your
floorplan on graph paper or whatever to "picture" your exact sizes and
positions.

4- Could it be possible to omit boxes (defined by/or content is say
zero) for the aisles in the report and form?

See my answer to question 1. My original solution had some aisles
defined.

Thanks in advance and I hope I'm not running your patience down.

Alp

If you replace the sasComment field with a numeric field, you would
need to remove two pairs of quotes around ctl.Value:

Function UpdateSeating()
'
'bunch of snipped code
'
strSQL = "INSERT INTO tblSeatAssignments (sasEvnID, sasSeaID,
sasComment) " & _
"Values(" & Me.cboEvnID & ", " & lngSeaID & "," & ctl.Value &
")"
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
Set ctl = Nothing
'
'bunch of snipped code
'
End Function

--
Duane Hookom
MS Access MVP
--

Hi Duane,

I have tried your sample, then added my own table and copied the form
to work with the new table but came to a halt. It started giving me
error messages and refused to open.

Anyway, I then tied the table on your form and found out that it only
accepts typed-in input and mainly text (no numbers). I am still trying
to get it to work the way I want: i.e. a form is preopened in the
background providing the company_id and I would like to pass on this
company_id into these locations(seats in your example) preferably by
mouse-click. Thus I tried the OnClick event to pass this number. The
number IS transferred but the UpdateSeating function errs out. If I
don't run the function, then I don't get the company_id recorded. But
if I do the same with say company name, then no errors!

I know it must have something to do with """" s since text requires
them and numbers don't but still fighting it. Or could it be the
Screen.ActiveControl?

Alp

After reviewing your floor plan, I would still attempt to implement
this method. I would create temp tables for each of the different
sections and then use subforms and subreports to display the
sections.

--
Duane Hookom
MS Access MVP
--

Hi Duane,

I've d/l'd the file and will try and see how I can modify it for my
purpose. Thanks for the sample and the guidance.

Alp

I created a demo for another similar question. You can download the
sample file from www.access.hookom.net/samples/seating.zip.

You basically describe the available seating locations in a couple
tables. You can then make reservations for events. I think this
sample could be easily modified to meet your needs.

--
Duane Hookom
MS Access MVP
--

Hi Alp,

Thanks for the information. I feel I'm beginning to understand the
situation - though if there's an easy answer I don't know it. I'll
ask
some fellow MVPs: probably one of them has had a similar problem
before
and will be able to suggest something.

The 255-field limit isn't a barrier: the way to handle this would
be to
have a "tall narrow" table, in which each record represents one
"cell",
with fields identifying the cell, which company it's booked to (a
1:M
relationship with your table of companies) and so on.

On Mon, 27 Mar 2006 16:52:27 +0300, "Alp Bekisoglu"
<alp@access_ng.com>
wrote:


Hi John,

OK some answers to your questions:
1) Actually, part of an event managment database.
2) The actual building footprint is a rectangle BUT with a sort of
cut-off
corner at one side. sort of like:
__________72
| \
| 44
| \
|___________| 21 here
91
72 cells at top and 91 cells at the bottom with a height of 44
cells.
Ofcourse there are quite a few "cells" that correspond to the
aisles/alleys
in between.
The location(s) are actual spreadsheet cells arranged in a way to
look like
squares (rowheight 15, column width 2.14) since you can't actually
tell
excel to have squares properly...

I had "tried to try" (!) over the weekend to do this solely in
Access and
almost gave up completely since 255 field limitation/table is
there.

Sort of "no luck" situation I guess...

Thanks for your help and ideas and am willing to hear any further
suggestions if you're not fed up yet.


Alp

Hi Alp,

I'm not sure how I'd tackle this. Here are a couple of questions
that
would probably affect my decision.

1) Is this going to be an application in its own right, keeping
track of
the areas that have been reserved, or part of a larger database
application (e.g. an event management system)?

2) Are the "floor plans" simple rectangles, or are they actual
floor
plans and hence may have irregular shapes?

A) If it's an application in its own right, I'd think in terms
of
building it as an Excel application (with a custom user
interface,
worksheet protection and so on to prevent users interfering with
the
layout of the worksheet(s)). Data would be stored either in
hidden
worksheets or in a Jet database (.mdb file) managed by the Excel
VBA
code.

B) If it's a module in a database application and the "floor
plans" are
simple rectangles, I'd probably use a table with one record for
each
unit on the plan (each 1m square, in your example) and fields to
show
the coordinates (location) and status of each square.

The user interface could be a continuous form bound to a
temporary table
(one field for each 1m unit on the X axis, one record for each
unit on
the Y axis), with code to run queries to fill the temporary
table from
the main table as the form opens, and to update the main table
as the
user clicks on the controls on the form. Alternatively you could
use a
Flexgrid ActiveX control (and I'm sure there are other ways).

C) Finally, if the thing is a module in a larger application
_and_ you
have to handle odd-shaped floor plans: the approach at (B) above
is
still possible, though it's more work to store and display an
irregular
floor plan. Or you could use Excel to display the sheets and
provide the
user interface.


On Sat, 25 Mar 2006 13:28:46 +0200, "Alp Bekisoglu"
<alp@access_ng.com>
wrote:


Hi John and sorry for my late response.

I do see your point with excel worksheets. As I tried to explain
briefly
in
my initial post, the main issue is the need to work with a
"floor plan"
which has pre-defined enclosures (so to speak) of 1 sq meters
and indicate
(coloring was the first thing that came to mind) it has been
reserved. It
would be nicer if the routine could also count the cells (again
thinking
in
excel I think) to yield the total sq meters.

What else would you recommend to accomplish this within Access?
I just
couldn't find a solution yet.

Thanks in advance.

Alp

Hi Alp,

What you describe is fraught with danger, because there's no
way to
ensure that the cell references you create in the database
will remain
valid: it's so easy to modify worksheets and workbooks. So if
data
integrity is an issue, you'd probably be better off storing
the data
that is now in Excel workbooks in database tables, and
exporting it to
Excel if or when needed.

But if you do have to use Excel, the first step is to learn
enough about
programming Excel to make it do the Excel part of what you
describe
(running a macro when the user clicks on a cell, changing
colours,
getting the cell reference into a suitable format and
appending it to an
Access table, and so on).

Time enough after that to think about the interaction between
Access and
Excel.

On Wed, 22 Mar 2006 11:45:05 +0200, "Alp Bekisoglu"
<alp@access_ng.com>
wrote:


Hi Experts,

A2000, multiuser, FE/BE database. I would like to design a
form with the
intention:
When a backend record's (a company record) form is opened
- OnClick event of a button to open a specific Excel file
(preferably a
specific tab as well)
- Store in db the mouse selected cell coordinates (together
with the tab
name)
- If possible give an option to change the fill color of the
selected
cells

Moving to another record (company) and hitting the same
button, the same
shall apply but the previously recorded cells should show as
unavailable
(color filled).

In short, I would like to make a sort of "visual" space
reservation sub
application for an event where printing of the spreadsheet
will also be
involved.

Any guidance, suggestion, a starting point, ... is most
welcome.

Thanks in advance.

Alp

 

[Alp Bekisoglu]

Hi Duane,

Thanks for the encouragement.
Progress report:
- Still fighting with the entries stuck at seat J (A to J take the entry of
co_id) beyond that I can't seem to transfer input
- Both reports (seatingChart and seatAssignments) print/display as Hall
Name, blank page, first seat, blank page,.... :-(
- Started to think about using a modified code initially by you for the
report as given below:
Public Function BoxItRpt(BoxLeft As Long, BoxTop As Long, _
BoxWidth As Long, BoxHeight As Long, _
BoxNum As Long, whichField As String)
'The function below is called under a report detail section's Format event
as:
'Call BoxIt (2270, 75, 285, 338, 28, FullName, r_BE1_v2)
'Source code by Duane Hookom at microsoft.public.access.reports NG on
22/01/2005
'Modified into a function by Alp Bekisoglu on 22/01/2005
'All units are in twips, except for BoxNum.
'( 1 inch = 1440 twips, 1 cm = 567 twips)
Dim lngLeft As Long ' starting point of boxes (720 = 1/2 inch)
Dim lngTop As Long ' top point of boxes (360 = 1/4 inch)
Dim lngWidth As Long ' width of each box
Dim lngHeight As Long ' height of each box
Dim lngBoxCount As Long ' number of boxes
Dim strField As String ' the field to be parsed

lngLeft = BoxLeft
lngTop = BoxTop
lngWidth = BoxWidth
lngHeight = BoxHeight
lngBoxCount = BoxNum
strField = whichField

Dim lngI As Long 'for looping
Me.FontSize = 10
Me.FontName = "Arial"
For lngI = 1 To lngBoxCount
Me.Line (lngLeft + (lngI - 1) * lngWidth, lngTop)- _
Step(lngWidth, lngHeight), 8421504, B
Me.CurrentX = lngLeft + (lngI - 1) * lngWidth
Me.CurrentY = lngTop + 20
Me.ForeColor = 0
Me.Print " " & Mid(strField & Space(lngBoxCount), lngI, 1)
Next
End Function

with a for/next type routine.

Any further pointers?

Alp

Glad to hear you are making progress. This is one of those threads where I
am really glad you can take the ideas and "play" with them rather than
come back for direction at every step.

--
Duane Hookom
MS Access MVP
--

Thanks a lot Duane for your patience and the response. Looks like I'm
getting somewhere. Not solved yet, but getting close. The minute I
resolve why the input works only upto and including J but no further, I
think I can say I'm almost done since the rest will be hiding/formatting.

Sincerely,

Alp

Answers in-line:

--
Duane Hookom
MS Access MVP
--

Hi Duane,

Thanks for the code. Got actually four more questions, if I may ask.
1- As you have already seen the floorplans, any advice on how can I
show the diagonal cut-off ?
Since my solution works with rectangle blocks of seats, I would keep a
table of "unavailable" seats in a table to append when scheduling for an
event. You could then use conditional formating or whatever to hide or
lock them.

2- Is it possible to write a sub or function to set the values of any
clicked "cell" to the field value on an open form? Or do I have to
write the code Me.controlname = Forms!open_form.field_name under each
control's OnClick event?
You could use code like in the UpdateSeating() function to insert a
value from a control elsewhere on the form.

3- Embarrased to ask but will anyway; can you just instruct me on the X
and Y coordinates (rowYval and seatXval) I've got 44 rows and many
seats for each row (up to 91). I know they are twips (I hope) but am a
bit confused. Both height & width for each box on my form is set to
0.432cm for now but I'm planning to shrink them a little more but on
the report I am not sure what size should I use to fit the entire Hall
A Ground Floor on A4 landscape mode.
The default scale in the report is twips. You may want to copy your
floorplan on graph paper or whatever to "picture" your exact sizes and
positions.

4- Could it be possible to omit boxes (defined by/or content is say
zero) for the aisles in the report and form?
See my answer to question 1. My original solution had some aisles
defined.


Thanks in advance and I hope I'm not running your patience down.

Alp

If you replace the sasComment field with a numeric field, you would
need to remove two pairs of quotes around ctl.Value:

Function UpdateSeating()
'
'bunch of snipped code
'
strSQL = "INSERT INTO tblSeatAssignments (sasEvnID, sasSeaID,
sasComment) " & _
"Values(" & Me.cboEvnID & ", " & lngSeaID & "," & ctl.Value &
")"
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
Set ctl = Nothing
'
'bunch of snipped code
'
End Function

--
Duane Hookom
MS Access MVP
--

Hi Duane,

I have tried your sample, then added my own table and copied the form
to work with the new table but came to a halt. It started giving me
error messages and refused to open.

Anyway, I then tied the table on your form and found out that it only
accepts typed-in input and mainly text (no numbers). I am still
trying to get it to work the way I want: i.e. a form is preopened in
the background providing the company_id and I would like to pass on
this company_id into these locations(seats in your example)
preferably by mouse-click. Thus I tried the OnClick event to pass
this number. The number IS transferred but the UpdateSeating function
errs out. If I don't run the function, then I don't get the
company_id recorded. But if I do the same with say company name, then
no errors!

I know it must have something to do with """" s since text requires
them and numbers don't but still fighting it. Or could it be the
Screen.ActiveControl?

Alp

After reviewing your floor plan, I would still attempt to implement
this method. I would create temp tables for each of the different
sections and then use subforms and subreports to display the
sections.

--
Duane Hookom
MS Access MVP
--

Hi Duane,

I've d/l'd the file and will try and see how I can modify it for my
purpose. Thanks for the sample and the guidance.

Alp

I created a demo for another similar question. You can download the
sample file from www.access.hookom.net/samples/seating.zip.

You basically describe the available seating locations in a couple
tables. You can then make reservations for events. I think this
sample could be easily modified to meet your needs.

--
Duane Hookom
MS Access MVP
--

Hi Alp,

Thanks for the information. I feel I'm beginning to understand
the
situation - though if there's an easy answer I don't know it.
I'll ask
some fellow MVPs: probably one of them has had a similar problem
before
and will be able to suggest something.

The 255-field limit isn't a barrier: the way to handle this would
be to
have a "tall narrow" table, in which each record represents one
"cell",
with fields identifying the cell, which company it's booked to (a
1:M
relationship with your table of companies) and so on.

On Mon, 27 Mar 2006 16:52:27 +0300, "Alp Bekisoglu"
<alp@access_ng.com>
wrote:


Hi John,

OK some answers to your questions:
1) Actually, part of an event managment database.
2) The actual building footprint is a rectangle BUT with a sort
of cut-off
corner at one side. sort of like:
__________72
| \
| 44
| \
|___________| 21 here
91
72 cells at top and 91 cells at the bottom with a height of 44
cells.
Ofcourse there are quite a few "cells" that correspond to the
aisles/alleys
in between.
The location(s) are actual spreadsheet cells arranged in a way to
look like
squares (rowheight 15, column width 2.14) since you can't
actually tell
excel to have squares properly...

I had "tried to try" (!) over the weekend to do this solely in
Access and
almost gave up completely since 255 field limitation/table is
there.

Sort of "no luck" situation I guess...

Thanks for your help and ideas and am willing to hear any further
suggestions if you're not fed up yet.


Alp

Hi Alp,

I'm not sure how I'd tackle this. Here are a couple of
questions that
would probably affect my decision.

1) Is this going to be an application in its own right, keeping
track of
the areas that have been reserved, or part of a larger database
application (e.g. an event management system)?

2) Are the "floor plans" simple rectangles, or are they actual
floor
plans and hence may have irregular shapes?

A) If it's an application in its own right, I'd think in terms
of
building it as an Excel application (with a custom user
interface,
worksheet protection and so on to prevent users interfering
with the
layout of the worksheet(s)). Data would be stored either in
hidden
worksheets or in a Jet database (.mdb file) managed by the
Excel VBA
code.

B) If it's a module in a database application and the "floor
plans" are
simple rectangles, I'd probably use a table with one record for
each
unit on the plan (each 1m square, in your example) and fields
to show
the coordinates (location) and status of each square.

The user interface could be a continuous form bound to a
temporary table
(one field for each 1m unit on the X axis, one record for each
unit on
the Y axis), with code to run queries to fill the temporary
table from
the main table as the form opens, and to update the main table
as the
user clicks on the controls on the form. Alternatively you
could use a
Flexgrid ActiveX control (and I'm sure there are other ways).

C) Finally, if the thing is a module in a larger application
_and_ you
have to handle odd-shaped floor plans: the approach at (B)
above is
still possible, though it's more work to store and display an
irregular
floor plan. Or you could use Excel to display the sheets and
provide the
user interface.


On Sat, 25 Mar 2006 13:28:46 +0200, "Alp Bekisoglu"
<alp@access_ng.com>
wrote:


Hi John and sorry for my late response.

I do see your point with excel worksheets. As I tried to
explain briefly
in
my initial post, the main issue is the need to work with a
"floor plan"
which has pre-defined enclosures (so to speak) of 1 sq meters
and indicate
(coloring was the first thing that came to mind) it has been
reserved. It
would be nicer if the routine could also count the cells (again
thinking
in
excel I think) to yield the total sq meters.

What else would you recommend to accomplish this within Access?
I just
couldn't find a solution yet.

Thanks in advance.

Alp

Hi Alp,

What you describe is fraught with danger, because there's no
way to
ensure that the cell references you create in the database
will remain
valid: it's so easy to modify worksheets and workbooks. So if
data
integrity is an issue, you'd probably be better off storing
the data
that is now in Excel workbooks in database tables, and
exporting it to
Excel if or when needed.

But if you do have to use Excel, the first step is to learn
enough about
programming Excel to make it do the Excel part of what you
describe
(running a macro when the user clicks on a cell, changing
colours,
getting the cell reference into a suitable format and
appending it to an
Access table, and so on).

Time enough after that to think about the interaction between
Access and
Excel.

On Wed, 22 Mar 2006 11:45:05 +0200, "Alp Bekisoglu"
<alp@access_ng.com>
wrote:


Hi Experts,

A2000, multiuser, FE/BE database. I would like to design a
form with the
intention:
When a backend record's (a company record) form is opened
- OnClick event of a button to open a specific Excel file
(preferably a
specific tab as well)
- Store in db the mouse selected cell coordinates (together
with the tab
name)
- If possible give an option to change the fill color of the
selected
cells

Moving to another record (company) and hitting the same
button, the same
shall apply but the previously recorded cells should show as
unavailable
(color filled).

In short, I would like to make a sort of "visual" space
reservation sub
application for an event where printing of the spreadsheet
will also be
involved.

Any guidance, suggestion, a starting point, ... is most
welcome.

Thanks in advance.

Alp

 

[Duane Hookom]

The A-J columns come from the crosstab query where A-J are values in
seaSeatNumber in tblSeating. The append query and the temp seating table
would need to be modified to inlcude additional fields.

Do you have a detail section that is 8" high and the group footer that is
the same height?

--
Duane Hookom
MS Access MVP
--

Hi Duane,

Thanks for the encouragement.
Progress report:
- Still fighting with the entries stuck at seat J (A to J take the entry
of co_id) beyond that I can't seem to transfer input
- Both reports (seatingChart and seatAssignments) print/display as Hall
Name, blank page, first seat, blank page,.... :-(
- Started to think about using a modified code initially by you for the
report as given below:
Public Function BoxItRpt(BoxLeft As Long, BoxTop As Long, _
BoxWidth As Long, BoxHeight As Long, _
BoxNum As Long, whichField As String)
'The function below is called under a report detail section's Format event
as:
'Call BoxIt (2270, 75, 285, 338, 28, FullName, r_BE1_v2)
'Source code by Duane Hookom at microsoft.public.access.reports NG on
22/01/2005
'Modified into a function by Alp Bekisoglu on 22/01/2005
'All units are in twips, except for BoxNum.
'( 1 inch = 1440 twips, 1 cm = 567 twips)
Dim lngLeft As Long ' starting point of boxes (720 = 1/2 inch)
Dim lngTop As Long ' top point of boxes (360 = 1/4 inch)
Dim lngWidth As Long ' width of each box
Dim lngHeight As Long ' height of each box
Dim lngBoxCount As Long ' number of boxes
Dim strField As String ' the field to be parsed

lngLeft = BoxLeft
lngTop = BoxTop
lngWidth = BoxWidth
lngHeight = BoxHeight
lngBoxCount = BoxNum
strField = whichField

Dim lngI As Long 'for looping
Me.FontSize = 10
Me.FontName = "Arial"
For lngI = 1 To lngBoxCount
Me.Line (lngLeft + (lngI - 1) * lngWidth, lngTop)- _
Step(lngWidth, lngHeight), 8421504, B
Me.CurrentX = lngLeft + (lngI - 1) * lngWidth
Me.CurrentY = lngTop + 20
Me.ForeColor = 0
Me.Print " " & Mid(strField & Space(lngBoxCount), lngI, 1)
Next
End Function

with a for/next type routine.

Any further pointers?

Alp

Glad to hear you are making progress. This is one of those threads where
I am really glad you can take the ideas and "play" with them rather than
come back for direction at every step.

--
Duane Hookom
MS Access MVP
--

Thanks a lot Duane for your patience and the response. Looks like I'm
getting somewhere. Not solved yet, but getting close. The minute I
resolve why the input works only upto and including J but no further, I
think I can say I'm almost done since the rest will be
hiding/formatting.

Sincerely,

Alp

Answers in-line:

--
Duane Hookom
MS Access MVP
--

Hi Duane,

Thanks for the code. Got actually four more questions, if I may ask.
1- As you have already seen the floorplans, any advice on how can I
show the diagonal cut-off ?
Since my solution works with rectangle blocks of seats, I would keep a
table of "unavailable" seats in a table to append when scheduling for
an event. You could then use conditional formating or whatever to hide
or lock them.

2- Is it possible to write a sub or function to set the values of any
clicked "cell" to the field value on an open form? Or do I have to
write the code Me.controlname = Forms!open_form.field_name under each
control's OnClick event?
You could use code like in the UpdateSeating() function to insert a
value from a control elsewhere on the form.

3- Embarrased to ask but will anyway; can you just instruct me on the
X and Y coordinates (rowYval and seatXval) I've got 44 rows and many
seats for each row (up to 91). I know they are twips (I hope) but am a
bit confused. Both height & width for each box on my form is set to
0.432cm for now but I'm planning to shrink them a little more but on
the report I am not sure what size should I use to fit the entire Hall
A Ground Floor on A4 landscape mode.
The default scale in the report is twips. You may want to copy your
floorplan on graph paper or whatever to "picture" your exact sizes and
positions.

4- Could it be possible to omit boxes (defined by/or content is say
zero) for the aisles in the report and form?
See my answer to question 1. My original solution had some aisles
defined.


Thanks in advance and I hope I'm not running your patience down.

Alp

If you replace the sasComment field with a numeric field, you would
need to remove two pairs of quotes around ctl.Value:

Function UpdateSeating()
'
'bunch of snipped code
'
strSQL = "INSERT INTO tblSeatAssignments (sasEvnID, sasSeaID,
sasComment) " & _
"Values(" & Me.cboEvnID & ", " & lngSeaID & "," & ctl.Value &
")"
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
Set ctl = Nothing
'
'bunch of snipped code
'
End Function

--
Duane Hookom
MS Access MVP
--

Hi Duane,

I have tried your sample, then added my own table and copied the
form to work with the new table but came to a halt. It started
giving me error messages and refused to open.

Anyway, I then tied the table on your form and found out that it
only accepts typed-in input and mainly text (no numbers). I am still
trying to get it to work the way I want: i.e. a form is preopened in
the background providing the company_id and I would like to pass on
this company_id into these locations(seats in your example)
preferably by mouse-click. Thus I tried the OnClick event to pass
this number. The number IS transferred but the UpdateSeating
function errs out. If I don't run the function, then I don't get the
company_id recorded. But if I do the same with say company name,
then no errors!

I know it must have something to do with """" s since text requires
them and numbers don't but still fighting it. Or could it be the
Screen.ActiveControl?

Alp

After reviewing your floor plan, I would still attempt to implement
this method. I would create temp tables for each of the different
sections and then use subforms and subreports to display the
sections.

--
Duane Hookom
MS Access MVP
--

Hi Duane,

I've d/l'd the file and will try and see how I can modify it for
my purpose. Thanks for the sample and the guidance.

Alp

I created a demo for another similar question. You can download
the sample file from www.access.hookom.net/samples/seating.zip.

You basically describe the available seating locations in a
couple tables. You can then make reservations for events. I think
this sample could be easily modified to meet your needs.

--
Duane Hookom
MS Access MVP
--

Hi Alp,

Thanks for the information. I feel I'm beginning to understand
the
situation - though if there's an easy answer I don't know it.
I'll ask
some fellow MVPs: probably one of them has had a similar problem
before
and will be able to suggest something.

The 255-field limit isn't a barrier: the way to handle this
would be to
have a "tall narrow" table, in which each record represents one
"cell",
with fields identifying the cell, which company it's booked to
(a 1:M
relationship with your table of companies) and so on.

On Mon, 27 Mar 2006 16:52:27 +0300, "Alp Bekisoglu"
<alp@access_ng.com>
wrote:


Hi John,

OK some answers to your questions:
1) Actually, part of an event managment database.
2) The actual building footprint is a rectangle BUT with a sort
of cut-off
corner at one side. sort of like:
__________72
| \
| 44
| \
|___________| 21 here
91
72 cells at top and 91 cells at the bottom with a height of 44
cells.
Ofcourse there are quite a few "cells" that correspond to the
aisles/alleys
in between.
The location(s) are actual spreadsheet cells arranged in a way
to look like
squares (rowheight 15, column width 2.14) since you can't
actually tell
excel to have squares properly...

I had "tried to try" (!) over the weekend to do this solely in
Access and
almost gave up completely since 255 field limitation/table is
there.

Sort of "no luck" situation I guess...

Thanks for your help and ideas and am willing to hear any
further
suggestions if you're not fed up yet.


Alp

Hi Alp,

I'm not sure how I'd tackle this. Here are a couple of
questions that
would probably affect my decision.

1) Is this going to be an application in its own right,
keeping track of
the areas that have been reserved, or part of a larger
database
application (e.g. an event management system)?

2) Are the "floor plans" simple rectangles, or are they actual
floor
plans and hence may have irregular shapes?

A) If it's an application in its own right, I'd think in terms
of
building it as an Excel application (with a custom user
interface,
worksheet protection and so on to prevent users interfering
with the
layout of the worksheet(s)). Data would be stored either in
hidden
worksheets or in a Jet database (.mdb file) managed by the
Excel VBA
code.

B) If it's a module in a database application and the "floor
plans" are
simple rectangles, I'd probably use a table with one record
for each
unit on the plan (each 1m square, in your example) and fields
to show
the coordinates (location) and status of each square.

The user interface could be a continuous form bound to a
temporary table
(one field for each 1m unit on the X axis, one record for each
unit on
the Y axis), with code to run queries to fill the temporary
table from
the main table as the form opens, and to update the main table
as the
user clicks on the controls on the form. Alternatively you
could use a
Flexgrid ActiveX control (and I'm sure there are other ways).

C) Finally, if the thing is a module in a larger application
_and_ you
have to handle odd-shaped floor plans: the approach at (B)
above is
still possible, though it's more work to store and display an
irregular
floor plan. Or you could use Excel to display the sheets and
provide the
user interface.


On Sat, 25 Mar 2006 13:28:46 +0200, "Alp Bekisoglu"
<alp@access_ng.com>
wrote:


Hi John and sorry for my late response.

I do see your point with excel worksheets. As I tried to
explain briefly
in
my initial post, the main issue is the need to work with a
"floor plan"
which has pre-defined enclosures (so to speak) of 1 sq meters
and indicate
(coloring was the first thing that came to mind) it has been
reserved. It
would be nicer if the routine could also count the cells
(again thinking
in
excel I think) to yield the total sq meters.

What else would you recommend to accomplish this within
Access? I just
couldn't find a solution yet.

Thanks in advance.

Alp

message
Hi Alp,

What you describe is fraught with danger, because there's no
way to
ensure that the cell references you create in the database
will remain
valid: it's so easy to modify worksheets and workbooks. So
if data
integrity is an issue, you'd probably be better off storing
the data
that is now in Excel workbooks in database tables, and
exporting it to
Excel if or when needed.

But if you do have to use Excel, the first step is to learn
enough about
programming Excel to make it do the Excel part of what you
describe
(running a macro when the user clicks on a cell, changing
colours,
getting the cell reference into a suitable format and
appending it to an
Access table, and so on).

Time enough after that to think about the interaction
between Access and
Excel.

On Wed, 22 Mar 2006 11:45:05 +0200, "Alp Bekisoglu"
<alp@access_ng.com>
wrote:


Hi Experts,

A2000, multiuser, FE/BE database. I would like to design a
form with the
intention:
When a backend record's (a company record) form is opened
- OnClick event of a button to open a specific Excel file
(preferably a
specific tab as well)
- Store in db the mouse selected cell coordinates (together
with the tab
name)
- If possible give an option to change the fill color of the
selected
cells

Moving to another record (company) and hitting the same
button, the same
shall apply but the previously recorded cells should show as
unavailable
(color filled).

In short, I would like to make a sort of "visual" space
reservation sub
application for an event where printing of the spreadsheet
will also be
involved.

Any guidance, suggestion, a starting point, ... is most
welcome.

Thanks in advance.

Alp

 

[Alp Bekisoglu]

Hi Duane,

It actually IS quite embarrassing but: I found out that the "name"s of the
textboxes on the form were not in accordance with the names I had (or
thought I had) given them unfortunately . Thus correcting that issue
resolved the data entry. How silly can people get sometimes...

Another interesting issue was that eventhough I did not change anything this
time around, while closing the xtab query I was asked to save! I did and
maybe that also helped out.

My detail and facFacility footer sections both are 20.501cm high and I'm
getting a similar situation with the reporting (rptSeatAssignments), now
each box is on a seperate page, no blank pages in between this time. I have
set the height and width of the box to 0.45cm and the twipping info on the
tables start from 0 and increment by 240 reaching to 10560 for columns and
10320 for the rows. Page is set as landscape A4 with page margins set to 0
for all. And this is only the half of Hall A ground floor. What exactly
should I base my detail height and footer heights?

Thanks in advance.

Alp

The A-J columns come from the crosstab query where A-J are values in
seaSeatNumber in tblSeating. The append query and the temp seating table
would need to be modified to inlcude additional fields.

Do you have a detail section that is 8" high and the group footer that is
the same height?

--
Duane Hookom
MS Access MVP
--

Hi Duane,

Thanks for the encouragement.
Progress report:
- Still fighting with the entries stuck at seat J (A to J take the entry
of co_id) beyond that I can't seem to transfer input
- Both reports (seatingChart and seatAssignments) print/display as Hall
Name, blank page, first seat, blank page,.... :-(
- Started to think about using a modified code initially by you for the
report as given below:
Public Function BoxItRpt(BoxLeft As Long, BoxTop As Long, _
BoxWidth As Long, BoxHeight As Long, _
BoxNum As Long, whichField As String)
'The function below is called under a report detail section's Format
event as:
'Call BoxIt (2270, 75, 285, 338, 28, FullName, r_BE1_v2)
'Source code by Duane Hookom at microsoft.public.access.reports NG on
22/01/2005
'Modified into a function by Alp Bekisoglu on 22/01/2005
'All units are in twips, except for BoxNum.
'( 1 inch = 1440 twips, 1 cm = 567 twips)
Dim lngLeft As Long ' starting point of boxes (720 = 1/2 inch)
Dim lngTop As Long ' top point of boxes (360 = 1/4 inch)
Dim lngWidth As Long ' width of each box
Dim lngHeight As Long ' height of each box
Dim lngBoxCount As Long ' number of boxes
Dim strField As String ' the field to be parsed

lngLeft = BoxLeft
lngTop = BoxTop
lngWidth = BoxWidth
lngHeight = BoxHeight
lngBoxCount = BoxNum
strField = whichField

Dim lngI As Long 'for looping
Me.FontSize = 10
Me.FontName = "Arial"
For lngI = 1 To lngBoxCount
Me.Line (lngLeft + (lngI - 1) * lngWidth, lngTop)- _
Step(lngWidth, lngHeight), 8421504, B
Me.CurrentX = lngLeft + (lngI - 1) * lngWidth
Me.CurrentY = lngTop + 20
Me.ForeColor = 0
Me.Print " " & Mid(strField & Space(lngBoxCount), lngI, 1)
Next
End Function

with a for/next type routine.

Any further pointers?

Alp

Glad to hear you are making progress. This is one of those threads where
I am really glad you can take the ideas and "play" with them rather than
come back for direction at every step.

--
Duane Hookom
MS Access MVP
--

Thanks a lot Duane for your patience and the response. Looks like I'm
getting somewhere. Not solved yet, but getting close. The minute I
resolve why the input works only upto and including J but no further, I
think I can say I'm almost done since the rest will be
hiding/formatting.

Sincerely,

Alp

Answers in-line:

--
Duane Hookom
MS Access MVP
--

Hi Duane,

Thanks for the code. Got actually four more questions, if I may ask.
1- As you have already seen the floorplans, any advice on how can I
show the diagonal cut-off ?
Since my solution works with rectangle blocks of seats, I would keep a
table of "unavailable" seats in a table to append when scheduling for
an event. You could then use conditional formating or whatever to hide
or lock them.

2- Is it possible to write a sub or function to set the values of any
clicked "cell" to the field value on an open form? Or do I have to
write the code Me.controlname = Forms!open_form.field_name under each
control's OnClick event?
You could use code like in the UpdateSeating() function to insert a
value from a control elsewhere on the form.

3- Embarrased to ask but will anyway; can you just instruct me on the
X and Y coordinates (rowYval and seatXval) I've got 44 rows and many
seats for each row (up to 91). I know they are twips (I hope) but am
a bit confused. Both height & width for each box on my form is set to
0.432cm for now but I'm planning to shrink them a little more but on
the report I am not sure what size should I use to fit the entire
Hall A Ground Floor on A4 landscape mode.
The default scale in the report is twips. You may want to copy your
floorplan on graph paper or whatever to "picture" your exact sizes and
positions.

4- Could it be possible to omit boxes (defined by/or content is say
zero) for the aisles in the report and form?
See my answer to question 1. My original solution had some aisles
defined.


Thanks in advance and I hope I'm not running your patience down.

Alp

If you replace the sasComment field with a numeric field, you would
need to remove two pairs of quotes around ctl.Value:

Function UpdateSeating()
'
'bunch of snipped code
'
strSQL = "INSERT INTO tblSeatAssignments (sasEvnID, sasSeaID,
sasComment) " & _
"Values(" & Me.cboEvnID & ", " & lngSeaID & "," & ctl.Value &
")"
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
Set ctl = Nothing
'
'bunch of snipped code
'
End Function

--
Duane Hookom
MS Access MVP
--

Hi Duane,

I have tried your sample, then added my own table and copied the
form to work with the new table but came to a halt. It started
giving me error messages and refused to open.

Anyway, I then tied the table on your form and found out that it
only accepts typed-in input and mainly text (no numbers). I am
still trying to get it to work the way I want: i.e. a form is
preopened in the background providing the company_id and I would
like to pass on this company_id into these locations(seats in your
example) preferably by mouse-click. Thus I tried the OnClick event
to pass this number. The number IS transferred but the
UpdateSeating function errs out. If I don't run the function, then
I don't get the company_id recorded. But if I do the same with say
company name, then no errors!

I know it must have something to do with """" s since text requires
them and numbers don't but still fighting it. Or could it be the
Screen.ActiveControl?

Alp

After reviewing your floor plan, I would still attempt to
implement this method. I would create temp tables for each of the
different sections and then use subforms and subreports to display
the sections.

--
Duane Hookom
MS Access MVP
--

Hi Duane,

I've d/l'd the file and will try and see how I can modify it for
my purpose. Thanks for the sample and the guidance.

Alp

I created a demo for another similar question. You can download
the sample file from www.access.hookom.net/samples/seating.zip.

You basically describe the available seating locations in a
couple tables. You can then make reservations for events. I
think this sample could be easily modified to meet your needs.

--
Duane Hookom
MS Access MVP
--

Hi Alp,

Thanks for the information. I feel I'm beginning to understand
the
situation - though if there's an easy answer I don't know it.
I'll ask
some fellow MVPs: probably one of them has had a similar
problem before
and will be able to suggest something.

The 255-field limit isn't a barrier: the way to handle this
would be to
have a "tall narrow" table, in which each record represents one
"cell",
with fields identifying the cell, which company it's booked to
(a 1:M
relationship with your table of companies) and so on.

On Mon, 27 Mar 2006 16:52:27 +0300, "Alp Bekisoglu"
<alp@access_ng.com>
wrote:


Hi John,

OK some answers to your questions:
1) Actually, part of an event managment database.
2) The actual building footprint is a rectangle BUT with a sort
of cut-off
corner at one side. sort of like:
__________72
| \
| 44
| \
|___________| 21 here
91
72 cells at top and 91 cells at the bottom with a height of 44
cells.
Ofcourse there are quite a few "cells" that correspond to the
aisles/alleys
in between.
The location(s) are actual spreadsheet cells arranged in a way
to look like
squares (rowheight 15, column width 2.14) since you can't
actually tell
excel to have squares properly...

I had "tried to try" (!) over the weekend to do this solely in
Access and
almost gave up completely since 255 field limitation/table is
there.

Sort of "no luck" situation I guess...

Thanks for your help and ideas and am willing to hear any
further
suggestions if you're not fed up yet.


Alp

Hi Alp,

I'm not sure how I'd tackle this. Here are a couple of
questions that
would probably affect my decision.

1) Is this going to be an application in its own right,
keeping track of
the areas that have been reserved, or part of a larger
database
application (e.g. an event management system)?

2) Are the "floor plans" simple rectangles, or are they
actual floor
plans and hence may have irregular shapes?

A) If it's an application in its own right, I'd think in
terms of
building it as an Excel application (with a custom user
interface,
worksheet protection and so on to prevent users interfering
with the
layout of the worksheet(s)). Data would be stored either in
hidden
worksheets or in a Jet database (.mdb file) managed by the
Excel VBA
code.

B) If it's a module in a database application and the "floor
plans" are
simple rectangles, I'd probably use a table with one record
for each
unit on the plan (each 1m square, in your example) and fields
to show
the coordinates (location) and status of each square.

The user interface could be a continuous form bound to a
temporary table
(one field for each 1m unit on the X axis, one record for
each unit on
the Y axis), with code to run queries to fill the temporary
table from
the main table as the form opens, and to update the main
table as the
user clicks on the controls on the form. Alternatively you
could use a
Flexgrid ActiveX control (and I'm sure there are other ways).

C) Finally, if the thing is a module in a larger application
_and_ you
have to handle odd-shaped floor plans: the approach at (B)
above is
still possible, though it's more work to store and display an
irregular
floor plan. Or you could use Excel to display the sheets and
provide the
user interface.


On Sat, 25 Mar 2006 13:28:46 +0200, "Alp Bekisoglu"
<alp@access_ng.com>
wrote:


Hi John and sorry for my late response.

I do see your point with excel worksheets. As I tried to
explain briefly
in
my initial post, the main issue is the need to work with a
"floor plan"
which has pre-defined enclosures (so to speak) of 1 sq meters
and indicate
(coloring was the first thing that came to mind) it has been
reserved. It
would be nicer if the routine could also count the cells
(again thinking
in
excel I think) to yield the total sq meters.

What else would you recommend to accomplish this within
Access? I just
couldn't find a solution yet.

Thanks in advance.

Alp

message
Hi Alp,

What you describe is fraught with danger, because there's
no way to
ensure that the cell references you create in the database
will remain
valid: it's so easy to modify worksheets and workbooks. So
if data
integrity is an issue, you'd probably be better off storing
the data
that is now in Excel workbooks in database tables, and
exporting it to
Excel if or when needed.

But if you do have to use Excel, the first step is to learn
enough about
programming Excel to make it do the Excel part of what you
describe
(running a macro when the user clicks on a cell, changing
colours,
getting the cell reference into a suitable format and
appending it to an
Access table, and so on).

Time enough after that to think about the interaction
between Access and
Excel.

On Wed, 22 Mar 2006 11:45:05 +0200, "Alp Bekisoglu"
<alp@access_ng.com>
wrote:


Hi Experts,

A2000, multiuser, FE/BE database. I would like to design a
form with the
intention:
When a backend record's (a company record) form is opened
- OnClick event of a button to open a specific Excel file
(preferably a
specific tab as well)
- Store in db the mouse selected cell coordinates (together
with the tab
name)
- If possible give an option to change the fill color of
the selected
cells

Moving to another record (company) and hitting the same
button, the same
shall apply but the previously recorded cells should show
as unavailable
(color filled).

In short, I would like to make a sort of "visual" space
reservation sub
application for an event where printing of the spreadsheet
will also be
involved.

Any guidance, suggestion, a starting point, ... is most
welcome.

Thanks in advance.

Alp

 

[Duane Hookom]

Do you have the code in the detail section
Me.MoveLayout = False

--
Duane Hookom
MS Access MVP

Hi Duane,

It actually IS quite embarrassing but: I found out that the "name"s of the
textboxes on the form were not in accordance with the names I had (or
thought I had) given them unfortunately . Thus correcting that issue
resolved the data entry. How silly can people get sometimes...

Another interesting issue was that eventhough I did not change anything
this time around, while closing the xtab query I was asked to save! I did
and maybe that also helped out.

My detail and facFacility footer sections both are 20.501cm high and I'm
getting a similar situation with the reporting (rptSeatAssignments), now
each box is on a seperate page, no blank pages in between this time. I
have set the height and width of the box to 0.45cm and the twipping info
on the tables start from 0 and increment by 240 reaching to 10560 for
columns and 10320 for the rows. Page is set as landscape A4 with page
margins set to 0 for all. And this is only the half of Hall A ground
floor. What exactly should I base my detail height and footer heights?

Thanks in advance.

Alp

The A-J columns come from the crosstab query where A-J are values in
seaSeatNumber in tblSeating. The append query and the temp seating table
would need to be modified to inlcude additional fields.

Do you have a detail section that is 8" high and the group footer that is
the same height?

--
Duane Hookom
MS Access MVP
--

Hi Duane,

Thanks for the encouragement.
Progress report:
- Still fighting with the entries stuck at seat J (A to J take the entry
of co_id) beyond that I can't seem to transfer input
- Both reports (seatingChart and seatAssignments) print/display as Hall
Name, blank page, first seat, blank page,.... :-(
- Started to think about using a modified code initially by you for the
report as given below:
Public Function BoxItRpt(BoxLeft As Long, BoxTop As Long, _
BoxWidth As Long, BoxHeight As Long, _
BoxNum As Long, whichField As String)
'The function below is called under a report detail section's Format
event as:
'Call BoxIt (2270, 75, 285, 338, 28, FullName, r_BE1_v2)
'Source code by Duane Hookom at microsoft.public.access.reports NG on
22/01/2005
'Modified into a function by Alp Bekisoglu on 22/01/2005
'All units are in twips, except for BoxNum.
'( 1 inch = 1440 twips, 1 cm = 567 twips)
Dim lngLeft As Long ' starting point of boxes (720 = 1/2 inch)
Dim lngTop As Long ' top point of boxes (360 = 1/4 inch)
Dim lngWidth As Long ' width of each box
Dim lngHeight As Long ' height of each box
Dim lngBoxCount As Long ' number of boxes
Dim strField As String ' the field to be parsed

lngLeft = BoxLeft
lngTop = BoxTop
lngWidth = BoxWidth
lngHeight = BoxHeight
lngBoxCount = BoxNum
strField = whichField

Dim lngI As Long 'for looping
Me.FontSize = 10
Me.FontName = "Arial"
For lngI = 1 To lngBoxCount
Me.Line (lngLeft + (lngI - 1) * lngWidth, lngTop)- _
Step(lngWidth, lngHeight), 8421504, B
Me.CurrentX = lngLeft + (lngI - 1) * lngWidth
Me.CurrentY = lngTop + 20
Me.ForeColor = 0
Me.Print " " & Mid(strField & Space(lngBoxCount), lngI, 1)
Next
End Function

with a for/next type routine.

Any further pointers?

Alp

Glad to hear you are making progress. This is one of those threads
where I am really glad you can take the ideas and "play" with them
rather than come back for direction at every step.

--
Duane Hookom
MS Access MVP
--

Thanks a lot Duane for your patience and the response. Looks like I'm
getting somewhere. Not solved yet, but getting close. The minute I
resolve why the input works only upto and including J but no further,
I think I can say I'm almost done since the rest will be
hiding/formatting.

Sincerely,

Alp

Answers in-line:

--
Duane Hookom
MS Access MVP
--

Hi Duane,

Thanks for the code. Got actually four more questions, if I may ask.
1- As you have already seen the floorplans, any advice on how can I
show the diagonal cut-off ?
Since my solution works with rectangle blocks of seats, I would keep
a table of "unavailable" seats in a table to append when scheduling
for an event. You could then use conditional formating or whatever to
hide or lock them.

2- Is it possible to write a sub or function to set the values of
any clicked "cell" to the field value on an open form? Or do I have
to write the code Me.controlname = Forms!open_form.field_name under
each control's OnClick event?
You could use code like in the UpdateSeating() function to insert a
value from a control elsewhere on the form.

3- Embarrased to ask but will anyway; can you just instruct me on
the X and Y coordinates (rowYval and seatXval) I've got 44 rows and
many seats for each row (up to 91). I know they are twips (I hope)
but am a bit confused. Both height & width for each box on my form
is set to 0.432cm for now but I'm planning to shrink them a little
more but on the report I am not sure what size should I use to fit
the entire Hall A Ground Floor on A4 landscape mode.
The default scale in the report is twips. You may want to copy your
floorplan on graph paper or whatever to "picture" your exact sizes
and positions.

4- Could it be possible to omit boxes (defined by/or content is say
zero) for the aisles in the report and form?
See my answer to question 1. My original solution had some aisles
defined.


Thanks in advance and I hope I'm not running your patience down.

Alp

If you replace the sasComment field with a numeric field, you would
need to remove two pairs of quotes around ctl.Value:

Function UpdateSeating()
'
'bunch of snipped code
'
strSQL = "INSERT INTO tblSeatAssignments (sasEvnID, sasSeaID,
sasComment) " & _
"Values(" & Me.cboEvnID & ", " & lngSeaID & "," & ctl.Value
& ")"
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
Set ctl = Nothing
'
'bunch of snipped code
'
End Function

--
Duane Hookom
MS Access MVP
--

Hi Duane,

I have tried your sample, then added my own table and copied the
form to work with the new table but came to a halt. It started
giving me error messages and refused to open.

Anyway, I then tied the table on your form and found out that it
only accepts typed-in input and mainly text (no numbers). I am
still trying to get it to work the way I want: i.e. a form is
preopened in the background providing the company_id and I would
like to pass on this company_id into these locations(seats in your
example) preferably by mouse-click. Thus I tried the OnClick event
to pass this number. The number IS transferred but the
UpdateSeating function errs out. If I don't run the function, then
I don't get the company_id recorded. But if I do the same with say
company name, then no errors!

I know it must have something to do with """" s since text
requires them and numbers don't but still fighting it. Or could it
be the Screen.ActiveControl?

Alp

After reviewing your floor plan, I would still attempt to
implement this method. I would create temp tables for each of the
different sections and then use subforms and subreports to
display the sections.

--
Duane Hookom
MS Access MVP
--

Hi Duane,

I've d/l'd the file and will try and see how I can modify it for
my purpose. Thanks for the sample and the guidance.

Alp

I created a demo for another similar question. You can download
the sample file from www.access.hookom.net/samples/seating.zip.

You basically describe the available seating locations in a
couple tables. You can then make reservations for events. I
think this sample could be easily modified to meet your needs.

--
Duane Hookom
MS Access MVP
--

Hi Alp,

Thanks for the information. I feel I'm beginning to understand
the
situation - though if there's an easy answer I don't know it.
I'll ask
some fellow MVPs: probably one of them has had a similar
problem before
and will be able to suggest something.

The 255-field limit isn't a barrier: the way to handle this
would be to
have a "tall narrow" table, in which each record represents
one "cell",
with fields identifying the cell, which company it's booked to
(a 1:M
relationship with your table of companies) and so on.

On Mon, 27 Mar 2006 16:52:27 +0300, "Alp Bekisoglu"
<alp@access_ng.com>
wrote:


Hi John,

OK some answers to your questions:
1) Actually, part of an event managment database.
2) The actual building footprint is a rectangle BUT with a
sort of cut-off
corner at one side. sort of like:
__________72
| \
| 44
| \
|___________| 21 here
91
72 cells at top and 91 cells at the bottom with a height of 44
cells.
Ofcourse there are quite a few "cells" that correspond to the
aisles/alleys
in between.
The location(s) are actual spreadsheet cells arranged in a way
to look like
squares (rowheight 15, column width 2.14) since you can't
actually tell
excel to have squares properly...

I had "tried to try" (!) over the weekend to do this solely in
Access and
almost gave up completely since 255 field limitation/table is
there.

Sort of "no luck" situation I guess...

Thanks for your help and ideas and am willing to hear any
further
suggestions if you're not fed up yet.


Alp

message
Hi Alp,

I'm not sure how I'd tackle this. Here are a couple of
questions that
would probably affect my decision.

1) Is this going to be an application in its own right,
keeping track of
the areas that have been reserved, or part of a larger
database
application (e.g. an event management system)?

2) Are the "floor plans" simple rectangles, or are they
actual floor
plans and hence may have irregular shapes?

A) If it's an application in its own right, I'd think in
terms of
building it as an Excel application (with a custom user
interface,
worksheet protection and so on to prevent users interfering
with the
layout of the worksheet(s)). Data would be stored either in
hidden
worksheets or in a Jet database (.mdb file) managed by the
Excel VBA
code.

B) If it's a module in a database application and the "floor
plans" are
simple rectangles, I'd probably use a table with one record
for each
unit on the plan (each 1m square, in your example) and
fields to show
the coordinates (location) and status of each square.

The user interface could be a continuous form bound to a
temporary table
(one field for each 1m unit on the X axis, one record for
each unit on
the Y axis), with code to run queries to fill the temporary
table from
the main table as the form opens, and to update the main
table as the
user clicks on the controls on the form. Alternatively you
could use a
Flexgrid ActiveX control (and I'm sure there are other
ways).

C) Finally, if the thing is a module in a larger application
_and_ you
have to handle odd-shaped floor plans: the approach at (B)
above is
still possible, though it's more work to store and display
an irregular
floor plan. Or you could use Excel to display the sheets and
provide the
user interface.


On Sat, 25 Mar 2006 13:28:46 +0200, "Alp Bekisoglu"
<alp@access_ng.com>
wrote:


Hi John and sorry for my late response.

I do see your point with excel worksheets. As I tried to
explain briefly
in
my initial post, the main issue is the need to work with a
"floor plan"
which has pre-defined enclosures (so to speak) of 1 sq
meters and indicate
(coloring was the first thing that came to mind) it has been
reserved. It
would be nicer if the routine could also count the cells
(again thinking
in
excel I think) to yield the total sq meters.

What else would you recommend to accomplish this within
Access? I just
couldn't find a solution yet.

Thanks in advance.

Alp

message
Hi Alp,

What you describe is fraught with danger, because there's
no way to
ensure that the cell references you create in the database
will remain
valid: it's so easy to modify worksheets and workbooks. So
if data
integrity is an issue, you'd probably be better off
storing the data
that is now in Excel workbooks in database tables, and
exporting it to
Excel if or when needed.

But if you do have to use Excel, the first step is to
learn enough about
programming Excel to make it do the Excel part of what you
describe
(running a macro when the user clicks on a cell, changing
colours,
getting the cell reference into a suitable format and
appending it to an
Access table, and so on).

Time enough after that to think about the interaction
between Access and
Excel.

On Wed, 22 Mar 2006 11:45:05 +0200, "Alp Bekisoglu"
<alp@access_ng.com>
wrote:


Hi Experts,

A2000, multiuser, FE/BE database. I would like to design a
form with the
intention:
When a backend record's (a company record) form is opened
- OnClick event of a button to open a specific Excel file
(preferably a
specific tab as well)
- Store in db the mouse selected cell coordinates
(together with the tab
name)
- If possible give an option to change the fill color of
the selected
cells

Moving to another record (company) and hitting the same
button, the same
shall apply but the previously recorded cells should show
as unavailable
(color filled).

In short, I would like to make a sort of "visual" space
reservation sub
application for an event where printing of the spreadsheet
will also be
involved.

Any guidance, suggestion, a starting point, ... is most
welcome.

Thanks in advance.

Alp

 

[Alp Bekisoglu]

Hi Duane,

Yes, both rptSeatAssignments and rptSeatingChart have the following code:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Me.txtSeat.Left = Me.SeatXVal
Me.txtSeat.Top = Me.rowYVal
Me.MoveLayout = False
End Sub

and they both still display each box on a seperate page. The width of the
paper is definitely sufficient to fit even more boxes side by side but just
couldn't figure out why it is refusing to display all on once page.

Would you rather like to see what I'm doing? If so I can either send the mdb
to you or temporarily put it on the web for you to download.

Alp

Do you have the code in the detail section
Me.MoveLayout = False

--
Duane Hookom
MS Access MVP

Hi Duane,

It actually IS quite embarrassing but: I found out that the "name"s of
the textboxes on the form were not in accordance with the names I had (or
thought I had) given them unfortunately . Thus correcting that issue
resolved the data entry. How silly can people get sometimes...

Another interesting issue was that eventhough I did not change anything
this time around, while closing the xtab query I was asked to save! I did
and maybe that also helped out.

My detail and facFacility footer sections both are 20.501cm high and I'm
getting a similar situation with the reporting (rptSeatAssignments), now
each box is on a seperate page, no blank pages in between this time. I
have set the height and width of the box to 0.45cm and the twipping info
on the tables start from 0 and increment by 240 reaching to 10560 for
columns and 10320 for the rows. Page is set as landscape A4 with page
margins set to 0 for all. And this is only the half of Hall A ground
floor. What exactly should I base my detail height and footer heights?

Thanks in advance.

Alp

The A-J columns come from the crosstab query where A-J are values in
seaSeatNumber in tblSeating. The append query and the temp seating table
would need to be modified to inlcude additional fields.

Do you have a detail section that is 8" high and the group footer that
is the same height?

--
Duane Hookom
MS Access MVP
--

Hi Duane,

Thanks for the encouragement.
Progress report:
- Still fighting with the entries stuck at seat J (A to J take the
entry of co_id) beyond that I can't seem to transfer input
- Both reports (seatingChart and seatAssignments) print/display as Hall
Name, blank page, first seat, blank page,.... :-(
- Started to think about using a modified code initially by you for the
report as given below:
Public Function BoxItRpt(BoxLeft As Long, BoxTop As Long, _
BoxWidth As Long, BoxHeight As Long, _
BoxNum As Long, whichField As String)
'The function below is called under a report detail section's Format
event as:
'Call BoxIt (2270, 75, 285, 338, 28, FullName, r_BE1_v2)
'Source code by Duane Hookom at microsoft.public.access.reports NG on
22/01/2005
'Modified into a function by Alp Bekisoglu on 22/01/2005
'All units are in twips, except for BoxNum.
'( 1 inch = 1440 twips, 1 cm = 567 twips)
Dim lngLeft As Long ' starting point of boxes (720 = 1/2 inch)
Dim lngTop As Long ' top point of boxes (360 = 1/4 inch)
Dim lngWidth As Long ' width of each box
Dim lngHeight As Long ' height of each box
Dim lngBoxCount As Long ' number of boxes
Dim strField As String ' the field to be parsed

lngLeft = BoxLeft
lngTop = BoxTop
lngWidth = BoxWidth
lngHeight = BoxHeight
lngBoxCount = BoxNum
strField = whichField

Dim lngI As Long 'for looping
Me.FontSize = 10
Me.FontName = "Arial"
For lngI = 1 To lngBoxCount
Me.Line (lngLeft + (lngI - 1) * lngWidth, lngTop)- _
Step(lngWidth, lngHeight), 8421504, B
Me.CurrentX = lngLeft + (lngI - 1) * lngWidth
Me.CurrentY = lngTop + 20
Me.ForeColor = 0
Me.Print " " & Mid(strField & Space(lngBoxCount), lngI, 1)
Next
End Function

with a for/next type routine.

Any further pointers?

Alp

Glad to hear you are making progress. This is one of those threads
where I am really glad you can take the ideas and "play" with them
rather than come back for direction at every step.

--
Duane Hookom
MS Access MVP
--

Thanks a lot Duane for your patience and the response. Looks like I'm
getting somewhere. Not solved yet, but getting close. The minute I
resolve why the input works only upto and including J but no further,
I think I can say I'm almost done since the rest will be
hiding/formatting.

Sincerely,

Alp

Answers in-line:

--
Duane Hookom
MS Access MVP
--

Hi Duane,

Thanks for the code. Got actually four more questions, if I may
ask.
1- As you have already seen the floorplans, any advice on how can I
show the diagonal cut-off ?
Since my solution works with rectangle blocks of seats, I would keep
a table of "unavailable" seats in a table to append when scheduling
for an event. You could then use conditional formating or whatever
to hide or lock them.

2- Is it possible to write a sub or function to set the values of
any clicked "cell" to the field value on an open form? Or do I have
to write the code Me.controlname = Forms!open_form.field_name under
each control's OnClick event?
You could use code like in the UpdateSeating() function to insert a
value from a control elsewhere on the form.

3- Embarrased to ask but will anyway; can you just instruct me on
the X and Y coordinates (rowYval and seatXval) I've got 44 rows and
many seats for each row (up to 91). I know they are twips (I hope)
but am a bit confused. Both height & width for each box on my form
is set to 0.432cm for now but I'm planning to shrink them a little
more but on the report I am not sure what size should I use to fit
the entire Hall A Ground Floor on A4 landscape mode.
The default scale in the report is twips. You may want to copy your
floorplan on graph paper or whatever to "picture" your exact sizes
and positions.

4- Could it be possible to omit boxes (defined by/or content is say
zero) for the aisles in the report and form?
See my answer to question 1. My original solution had some aisles
defined.


Thanks in advance and I hope I'm not running your patience down.

Alp

If you replace the sasComment field with a numeric field, you
would need to remove two pairs of quotes around ctl.Value:

Function UpdateSeating()
'
'bunch of snipped code
'
strSQL = "INSERT INTO tblSeatAssignments (sasEvnID, sasSeaID,
sasComment) " & _
"Values(" & Me.cboEvnID & ", " & lngSeaID & "," & ctl.Value
& ")"
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
Set ctl = Nothing
'
'bunch of snipped code
'
End Function

--
Duane Hookom
MS Access MVP
--

Hi Duane,

I have tried your sample, then added my own table and copied the
form to work with the new table but came to a halt. It started
giving me error messages and refused to open.

Anyway, I then tied the table on your form and found out that it
only accepts typed-in input and mainly text (no numbers). I am
still trying to get it to work the way I want: i.e. a form is
preopened in the background providing the company_id and I would
like to pass on this company_id into these locations(seats in
your example) preferably by mouse-click. Thus I tried the OnClick
event to pass this number. The number IS transferred but the
UpdateSeating function errs out. If I don't run the function,
then I don't get the company_id recorded. But if I do the same
with say company name, then no errors!

I know it must have something to do with """" s since text
requires them and numbers don't but still fighting it. Or could
it be the Screen.ActiveControl?

Alp

After reviewing your floor plan, I would still attempt to
implement this method. I would create temp tables for each of
the different sections and then use subforms and subreports to
display the sections.

--
Duane Hookom
MS Access MVP
--

Hi Duane,

I've d/l'd the file and will try and see how I can modify it
for my purpose. Thanks for the sample and the guidance.

Alp

I created a demo for another similar question. You can download
the sample file from www.access.hookom.net/samples/seating.zip.

You basically describe the available seating locations in a
couple tables. You can then make reservations for events. I
think this sample could be easily modified to meet your needs.

--
Duane Hookom
MS Access MVP
--

message Hi Alp,

Thanks for the information. I feel I'm beginning to
understand the
situation - though if there's an easy answer I don't know it.
I'll ask
some fellow MVPs: probably one of them has had a similar
problem before
and will be able to suggest something.

The 255-field limit isn't a barrier: the way to handle this
would be to
have a "tall narrow" table, in which each record represents
one "cell",
with fields identifying the cell, which company it's booked
to (a 1:M
relationship with your table of companies) and so on.

On Mon, 27 Mar 2006 16:52:27 +0300, "Alp Bekisoglu"
<alp@access_ng.com>
wrote:


Hi John,

OK some answers to your questions:
1) Actually, part of an event managment database.
2) The actual building footprint is a rectangle BUT with a
sort of cut-off
corner at one side. sort of like:
__________72
| \
| 44
| \
|___________| 21 here
91
72 cells at top and 91 cells at the bottom with a height of
44 cells.
Ofcourse there are quite a few "cells" that correspond to the
aisles/alleys
in between.
The location(s) are actual spreadsheet cells arranged in a
way to look like
squares (rowheight 15, column width 2.14) since you can't
actually tell
excel to have squares properly...

I had "tried to try" (!) over the weekend to do this solely
in Access and
almost gave up completely since 255 field limitation/table is
there.

Sort of "no luck" situation I guess...

Thanks for your help and ideas and am willing to hear any
further
suggestions if you're not fed up yet.


Alp

message
Hi Alp,

I'm not sure how I'd tackle this. Here are a couple of
questions that
would probably affect my decision.

1) Is this going to be an application in its own right,
keeping track of
the areas that have been reserved, or part of a larger
database
application (e.g. an event management system)?

2) Are the "floor plans" simple rectangles, or are they
actual floor
plans and hence may have irregular shapes?

A) If it's an application in its own right, I'd think in
terms of
building it as an Excel application (with a custom user
interface,
worksheet protection and so on to prevent users interfering
with the
layout of the worksheet(s)). Data would be stored either in
hidden
worksheets or in a Jet database (.mdb file) managed by the
Excel VBA
code.

B) If it's a module in a database application and the
"floor plans" are
simple rectangles, I'd probably use a table with one record
for each
unit on the plan (each 1m square, in your example) and
fields to show
the coordinates (location) and status of each square.

The user interface could be a continuous form bound to a
temporary table
(one field for each 1m unit on the X axis, one record for
each unit on
the Y axis), with code to run queries to fill the temporary
table from
the main table as the form opens, and to update the main
table as the
user clicks on the controls on the form. Alternatively you
could use a
Flexgrid ActiveX control (and I'm sure there are other
ways).

C) Finally, if the thing is a module in a larger
application _and_ you
have to handle odd-shaped floor plans: the approach at (B)
above is
still possible, though it's more work to store and display
an irregular
floor plan. Or you could use Excel to display the sheets
and provide the
user interface.


On Sat, 25 Mar 2006 13:28:46 +0200, "Alp Bekisoglu"
<alp@access_ng.com>
wrote:


Hi John and sorry for my late response.

I do see your point with excel worksheets. As I tried to
explain briefly
in
my initial post, the main issue is the need to work with a
"floor plan"
which has pre-defined enclosures (so to speak) of 1 sq
meters and indicate
(coloring was the first thing that came to mind) it has
been reserved. It
would be nicer if the routine could also count the cells
(again thinking
in
excel I think) to yield the total sq meters.

What else would you recommend to accomplish this within
Access? I just
couldn't find a solution yet.

Thanks in advance.

Alp

message
Hi Alp,

What you describe is fraught with danger, because there's
no way to
ensure that the cell references you create in the
database will remain
valid: it's so easy to modify worksheets and workbooks.
So if data
integrity is an issue, you'd probably be better off
storing the data
that is now in Excel workbooks in database tables, and
exporting it to
Excel if or when needed.

But if you do have to use Excel, the first step is to
learn enough about
programming Excel to make it do the Excel part of what
you describe
(running a macro when the user clicks on a cell, changing
colours,
getting the cell reference into a suitable format and
appending it to an
Access table, and so on).

Time enough after that to think about the interaction
between Access and
Excel.

On Wed, 22 Mar 2006 11:45:05 +0200, "Alp Bekisoglu"
<alp@access_ng.com>
wrote:


Hi Experts,

A2000, multiuser, FE/BE database. I would like to design
a form with the
intention:
When a backend record's (a company record) form is opened
- OnClick event of a button to open a specific Excel file
(preferably a
specific tab as well)
- Store in db the mouse selected cell coordinates
(together with the tab
name)
- If possible give an option to change the fill color of
the selected
cells

Moving to another record (company) and hitting the same
button, the same
shall apply but the previously recorded cells should show
as unavailable
(color filled).

In short, I would like to make a sort of "visual" space
reservation sub
application for an event where printing of the
spreadsheet will also be
involved.

Any guidance, suggestion, a starting point, ... is most
welcome.

Thanks in advance.

Alp