Best practice to store/manipulate Excel spreadsheet cells

[Alp Bekisoglu]

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

 

[John Nurick]

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.

 

[Alp Bekisoglu]

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

 

[John Nurick]

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.

 

[Alp Bekisoglu]

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

 

[John Nurick]

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.

 

[John Nurick]

One question/suggestion: do your users have Visio installed? If so, this
is likely to be a better way of getting a "live" floor plan than Excel.

 

[Duane Hookom]

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.

 

[John Nurick]

Another suggestion from a colleague is to use an image control on an
Access form containing a bitmap image of the floor plan, with records in
a table to store the coordinates on the image of each bookable "cell".

You can then get the mouse coordinates when the user clicks, convert
these into image coordinates, use calculation and/or a table lookup to
work out which "cell" the user has clicked on, and then (a) update the
records of which cells are taken and (b) change the colour of the
relevant area in the image.

The sample database at http://www.lebans.com/paintprogram.htm shows the
basics of using VBA to "paint" on an image control.

This sounds like a lot of work, but it would be interesting programming
and would adapt well to an irregular floor plan.

 

[John Nurick]

Thanks, Duane.

Alp, this is the approach I described as (B) a couple of posts ago.

Duane: I think I can see how to cope with the irregular shape (e.g. row
1 ends at seat F, row 2 ends at seat G, row 3 ends at seat H) by using
conditional formatting to hide the controls corresponding to field
values in the temp table for which there's no corresponding record in
the "seating" table.

But I can't quite see a straightforward way of showing "east-west"
aisles as well as "north-south" ones.

 

[Alp Bekisoglu]

Hi John,

Thanks a lot for keeping with me. You can see the actual layout at the
site http://hightech.sada.com.tr and check the floorplan there, sorry I
completey forgot to mention before.

Unfortunately I'm not really clear abut the "tall narrow" table but while
trying stuff, I created a table like:
row_id (runs from 1 to 44) and (just like excel names the colums) fileds
named A,B,...AA,....to CO. Had to manually add the fields to the form since
Access refused the wizard saying too many... But with this, when I click one
"cell" the entire column gets the same i.e. background color on this
continuous form. Sa that failed as well.

Still trying.

Alp

 

[Alp Bekisoglu]

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

 

[Alp Bekisoglu]

Unfortunately "no".

Alp

One question/suggestion: do your users have Visio installed? If so, this
is likely to be a better way of getting a "live" floor plan than Excel.

 

[Alp Bekisoglu]

I have downloaded the db and will see if I can do what you've described. I
do like the challenges but sometimes get frustrated when nothing is being
accomplished... "old age" maybe...

Thanks once again.

Alp

 

[Duane Hookom]

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.

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

 

[John Nurick]

Hi Alp,

Duane's sample database uses the "tall narrow" approach. As you explore
it, you'll see that the "floor plan" - in his case the seating layout -
and information on which seats are booked is stored in tables that have
many records and rather few fields, with a wide temporary table used to
drive the form that displays the seating layout. Note also that this
form is in continuous view, with enough textboxes for just one row of
seats. It doesn't have a grid of 100 textboxes for the 100 seats.

 

[Alp Bekisoglu]

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.

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 John,

I know the form is a continuous one and I really liked it. The problem, as I
see it, is the fact that I do have a lot of those boxes to put in a single
row. I will also try, as Duane pointed out, subforms/reports inorder to cope
with the number of boxes I need. I'm trying to figure out how to divide the
area... I have actually 3 seperate floorplans to work with, then I will need
to divide these also within themselves apparently.

I was actually hoping to hide behind the fact that in a single row all I
need is 91 fields + one for the row number actually and it is way below the
255 limit. Which still makes my table a bit like short-wide...

But refering to tblTempSeating in Duane's example, I am using the same
approach.

Alp

Hi Alp,

Duane's sample database uses the "tall narrow" approach. As you explore
it, you'll see that the "floor plan" - in his case the seating layout -
and information on which seats are booked is stored in tables that have
many records and rather few fields, with a wide temporary table used to
drive the form that displays the seating layout. Note also that this
form is in continuous view, with enough textboxes for just one row of
seats. It doesn't have a grid of 100 textboxes for the 100 seats.

Hi John,

Thanks a lot for keeping with me. You can see the actual layout at the
site http://hightech.sada.com.tr and check the floorplan there, sorry I
completey forgot to mention before.

Unfortunately I'm not really clear abut the "tall narrow" table but while
trying stuff, I created a table like:
row_id (runs from 1 to 44) and (just like excel names the colums) fileds
named A,B,...AA,....to CO. Had to manually add the fields to the form
since
Access refused the wizard saying too many... But with this, when I click
one
"cell" the entire column gets the same i.e. background color on this
continuous form. Sa that failed as well.

Still trying.

Alp

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.

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]

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

 

[John Nurick]

Hi Alp,

A colleague has just showed me a newsgroup thread a few years ago which
also involved a graphical interface for renting exhibition space. I'm
sure you'll find it interesting. You can get there by going to
http://groups.google.com and searching for

"drawing maps and graphs" group:microsoft.public.access.forms