Help with Hurricane Database

[Suzann]

I work at the Corporate office of a fast food chain. We have almost 1000
stores in 10 states - mostly in the South. We have many stores in the
hurricane zones. During Rita and Katrina we decided we needed a better way
to track how our stores are affected and what help we could provide. I've
designed a database to do this.

I have three main tables:

tblHurricane Info:

fldHurricaneYear
fldHurricaneName

tblUnitInfo:

fldUnit - the unit number assigned to the store
fldAddress
fldCity
fldState
fldZip
fldPhone
fldDO
fldMgr

tblUnitForm

fldAutoNumber
fldUnit
fldHurricaneName
fldProjectedClose
fldActualClose
fldProjectedOpen
fldActualOpen
fldDamage
fldWorkNeeded

I have a main form with subform set up. The form is based on a query run
with owner's permission. The query pulls together information from three
tables. The main form is autopopulated with the unit information based on
the unit selected by our contact center. The subform has the main data entry
fields noted in the tblUnitForm. What I want to be able to do is ask the
contact center employee for the unit number and the hurricane name. Based on
those responses, the main form will be populated and the subform is ready to
accept data entry. That's where I'm held up. I'm a beginning VBA programmer
- and reading Access 2003 Inside Out; Power Programming with Access 2003 and
Access 2003 VBA Reference. I've started a piece of code, but not sure how to
proceed from here. Here's the snippet:


dim intUnit as Integer
dim strName as String
dim strSQL as String

rst = qryFormDataEntry
intUnit = InputBox("Please enter the Unit Number", "Unit Number")
strName = InputBox("Please enter the Hurricane Name","Hurricane Name")

strSQL = "SELECT qryFormDataEntry.* FROM qryFormDataEntry " _
& "WHERE fldUnit = intUnit and fldHurricaneName = strName);

I may not have copied all the code correctly, but it did compile. I pasted
the code to the On Open event. I'm not sure if that is the place I need to
have it. My question is if I'm heading in the right direction with my very
limited VBA. I want the input from the user to populate the form. Any help
you might be able to offer will be very appreciated.

 

[Douglas J. Steele]

strSQL = "SELECT qryFormDataEntry.* FROM qryFormDataEntry " _
& "WHERE fldUnit = " & intUnit & " and fldHurricaneName = '" & strName & "'"

Exagerated for clarity, the second line is

& "WHERE fldUnit = " & intUnit & " and fldHurricaneName = ' " & strName & "
' "

Note that this assumes that the hurricane name can never include an
apostrophe (O'Reilly). If that were a possibility, you'd need to use

& "WHERE fldUnit = " & intUnit & " and fldHurricaneName = " & _
Chr$(34) & strName & Chr$(34)

or

& "WHERE fldUnit = " & intUnit & " and fldHurricaneName = '" & _
Replace(strName, "'", "''") & "'"

Of course, I have no idea what you're planning on doing with strSQL: all
I've done is correct it so that it will use the values from the variables.

 

[Steve]

First I would suggest some simple changes to your tables:
1. Add a field named HurricaneID (Autonumber) to TblHurricane. Two reasons;
first there is a remote chance of two huuricanes with the same name so
flfHurricanename is not unique and therefore not a good primary key; second,
if the contact center employee mistakenly typed in Catrina, he would get no
records.
2. Consider adding UnitID (Autonumber) to TblUniyInfo for the same reasons
in 1.
3. In TblUnitForm,
a) consider changing fldAutoNumber to UnitFormID (Autonumber) for
clarity as to what table it represents.
b) change fldHurricanename to HurricaneID per 1
c) change FldUnit to UnitID per 2

Your description of the your problem provides that you want to look at a
particular unit and see the effects of multiple hurricanes on the unit. My
recommendation below is based on that description. However, it seems to me
that you would want to pick a particular hurricane and look at the effects
of that hurricane on multiple units in the subform.

In the main form query, set the criteria for UnitID as
Forms!NameOfYourMainForm!SelectUnit

Create a query based on TblHurricaneInfo and TblUnitForm. Set the criteria
for HurricaneID to:
Forms!NameOfYourMainForm!NameOfSubformControl.Form!SelectHurricane

Create a query named QryUnit based on TblUnitInfo and include UnitID,
fldUnit, fldState, fldAddress and FldCity. Sort first by FldStae the
fldCity. Add a combobox named SelectUnit to the top of your main form and
make QryUnit the rowsource. Set the Bound Column property to 1, Column Count
to 5 and column width to 0;1;1;1;1 (Where I show "1" set appropriately for
the field). Put the following code in the AfterUpdate event of the combobox:
Me.Requery.

Create a query named QryHurricanet based on TblHurricaneInfo and include
HurricaneID, fldHurricaneYear and fldHurricaneName. Sort first by
FldHurricaneYear then fldHurricanename. Add a combobox named SelectHurricane
to the top of your subform and make QryHurricanet the rowsource. Set the
Bound Column property to 1, Column Count to 3 and column width to 0;1;1
(Where I show "1" set appropriately for the field). Put the following code
in the AfterUpdate event of the combobox:
Me.Requery.

You should now be able to select the unit in the combobox at the top of the
main form and the main form will jump to that unit's record. Then you should
be able to select the hurricane in the combobox at the top of the subform
and the subform will jum to that hurricane record for the selected unit.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[Suzann]

Thank you for your help. I've entered the hurricane names for the next 5
years in the tblHurricaneInfo and there are no names with an apostrophe.
Since I have a feeling we're going to have a repeat of the 2005 season, I've
even included the Greek names. Hopefully, I'm wrong.

As for the strSQL, that's what I should use to get user input for the store
number and the hurricane name, isn't it? Maybe there's a better way that I
don't know about. In past situations on databases I've used for my own use,
I would use simple parameters in the query design window. However, since I
want the user to be prompted for the Unit Number and Hurricane Name before
the form loads, and then have that info populate the form - again, the main
form has the store information and the sub form has the data entry portion -
I'm not sure if I'm going about it in the right way. Is there a better way?

 

[Suzann]

Thank you so much - I'll incorporate your suggestions into the design.
You're right on what data we're trying to capture. During 2005, our Florida
stores were hit by several storms and we would have possibly 3 claims for
different hurricanes on 1 store. After Katrina, the panic set in and when
Rita came along, Corpus Christi was under a mandatory evacuation - we headed
north - right into the path where Rita eventually landed. I'll be developing
the back end to track lost store income, costs from prelandfall and costs
associated with damage, etc after landfall. This will also be used to help
with the claims we file.

But right now, I'm focusing on the main data entry form. I knew the
direction I needed to go, but wasn't sure how to get there. I want the user
to be prompted for the Unit number: that will populate the main form with
the store information. The hurricane name will be autopopulated from the
user's response and will lead the subform for data entry. I have the tab set
to go to the subform first. Again, I know what I want to happen, but not
sure how to get there.

I'll incorporate both your and Douglas' suggestions - thank you so much.

 

[Steve]

If you need help in the future expanding what you have to track income,
costs and claims, contact me at (e-mail address removed). If nothing else, I
can help you with developing the structure of the tables. My fees are very
reasonable.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[Bob Quintal]

Thank you for your help. I've entered the hurricane names for
the next 5 years in the tblHurricaneInfo and there are no
names with an apostrophe. Since I have a feeling we're going
to have a repeat of the 2005 season, I've even included the
Greek names. Hopefully, I'm wrong.

As for the strSQL, that's what I should use to get user input
for the store number and the hurricane name, isn't it? Maybe
there's a better way that I don't know about. In past
situations on databases I've used for my own use, I would use
simple parameters in the query design window. However, since
I want the user to be prompted for the Unit Number and
Hurricane Name before the form loads, and then have that info
populate the form - again, the main form has the store
information and the sub form has the data entry portion - I'm
not sure if I'm going about it in the right way. Is there a
better way?

I think so. I'd have a preliminary form that allows picking your
criteria from comboboxes for the hurricane and the unit. There
would also be a command button to open your main form, and one
to close the database.

The VBA command to open your main form allows Ppassing of
criteria as part of the Where Clause.

it would be something along the lines of
stWhereClause = "fldHurricaneName = """ & cboHurricane _
& "Unit = " & cboUnit
DoCmd.OpenForm "formname" ,,stWhereClause

With this method, you don't need to put any criteria in the Main
Form's query, and you have a more elegant way to get user data.

HTH
Q


1

 

[Bob Quintal]

To the original poster:
Beware of his guy !!
Most people here have a common belief that the newsgroups are
for *free exchange of information*.
But Steve is a notorious job hunter in these groups, always
trying to sell his services.

Before you intend to do business with him look at:
http://home.tiscali.nl/arracom/whoissteve.html

And just as important, his advice is ofter (usually??) incorrect
or incomplete. See my comments in my response to his first post.

 

[Bob Quintal]

First I would suggest some simple changes to your tables:
1. Add a field named HurricaneID (Autonumber) to
TblHurricane. Two reasons; first there is a remote chance of
two huuricanes with the same name so flfHurricanename is not
unique and therefore not a good primary key; second, if the
contact center employee mistakenly typed in Catrina, he would
get no records.

Don't bother. Year and name together make a perfectly legitimate
primary key for the hurricane table.

2. Consider adding UnitID (Autonumber) to TblUniyInfo for the
same reasons in 1.

Don't bother. Unit ID is a perfect candidate for Primary Key in the
Unit table.

 

[Douglas J. Steele]

strSQL is a valid SQL statement for selecting specific data from
qryFormDataEntry. However, now that you've created it in your code, what are
you intending to do with it? Are you intending to reset the form's
RecordSource to it? Are you intending to open a recordset in code, retrieve
data from that recordset and do something with that data? By itself, having
a SQL statement stored in a string does nothing for you.

 

[John Marshall, MVP]

The only one here who needs help is you stevo. These newsgroups are for FREE
support, not a location for you to prey on innocent victims.

As to reasonable fees, that is subject to the quality of the workmanship,
something you have yet to demonstrate.

John... Visio MVP

 

[Steve]

Once again num nutts shows his total ignorance and lack of manners!!
1. There is no Year field in TblUnitForm so you can not create a
relationship between the Hurricane table and the TblUnitForm. Even if you
add a Year field, you are still vulnerable to typing errors. Quintal has
polar bear dung for a brain!!
2. That's exactly what I said; add UnitID as the primary key - it's the
perfect primary key for the unit table. fldUnit makes you vulnerable to
typing errors. Do polar bears poop in the woods? Quintal does!!!

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)


PS A polar bear and Quintal walk into the woods ...........

Quintal is a joke!!!

 

[Suzann]

Thank you for your help. The backend is something I'm very familiar with - I
usually have ODBC links to the tables in our Accounting software and prepare
reports and queries for the senior management. I also usually transfer the
data to an outside database so I can do pivot tables in Excel from the Access
table since the data is too large for Excel spreadsheets.

 

[Suzann]

Thank you, Bob. I'll certainly try this . . In the original query, I had
combo boxes for the Hurricane name and the Unit info that would pull the data
from the two tables and pre-populate the main form. I'm just kind of lost on
how to get the choices from the user .. and then have that data show up in
the main part of the form so the contact center agent would have that data to
refer to. All the suggestions I've received are truly appreciated .

 

[Suzann]

That's where I'm stuck, Douglas (and by the way, your website has helped me a
lot in the past). What I want is for the user to have combo boxes to choose
the unit number (pulled from tblUnitInfo) and the hurricane name (pulled from
tblHurricaneInfo). This info would then show up in the form that they would
enter data into. I can do this with a normal parameter query and have used
that in the past. But the contact center wants to have a preliminary dialog
box requesting the unit and hurricane data. That data would then be passed
to the query for the data entry form. I've got the form and subform built -
along with a form that is a modified switchboard. There will be one button
on the switchboard that will open the data entry form. A dialog box would
pop up before the data entry form opens that would request unit and
hurricane. Then the data entry form opens with the main part of the form
populated with the unit info. The subform is where they would enter if it's
a government mandated evacuation, closing info, etc.

I guess I don't understand completely what I read in the text about passing
data from the dialog box to the query behind the form. I'll try your
suggestions and Bob's and refer back to my books. Thanks so much to all of
y'all. I also hope that y'all have had a great Father's Day!

 

[Suzann]

I'm sorry I seem to have opened up a can of worms. Thank you for your help.

 

[Suzann]

Please stop this profanity. I do appreciate your original help and will
consider it. I did not mean to cause a problem - nor will I be a part of it.
Let it end here. Thank you.

Once again num nutts shows his total ignorance and lack of manners!!
1. There is no Year field in TblUnitForm so you can not create a
relationship between the Hurricane table and the TblUnitForm. Even if you
add a Year field, you are still vulnerable to typing errors. Quintal has
polar bear dung for a brain!!
2. That's exactly what I said; add UnitID as the primary key - it's the
perfect primary key for the unit table. fldUnit makes you vulnerable to
typing errors. Do polar bears poop in the woods? Quintal does!!!

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)


PS A polar bear and Quintal walk into the woods ...........

Quintal is a joke!!!

 

[John Marshall, MVP]

You did not cause a problem. Steve is the problem and you were a potential
victim. His only real purpose for being here is to seperate unsuspecting
victims from their cash.

These newsgroups were provided by Microsoft for FREE Peer to Peer support.
There are many people who enjoy helping others in these newgroups. Their
rewards are the occassional thank you, the challenge of the question and the
satisfaction knowing that they helped someone. Unfortunately, steve can not
grasp this concept and considers the newsgroups a source of income.

John... Visio MVP

Please stop this profanity. I do appreciate your original help and will
consider it. I did not mean to cause a problem - nor will I be a part of
it.
Let it end here. Thank you.

 

[Suzann]

I understand and I appreciate the fact that there are people on the net and
possibly in this newsgroup who do prey on people like me.

I have the utmost respect for this newsgroup and for people like you, Bob
and Doug who constantly answer questions for end users like me who are
wanting to learn more but are stuck on one particular point. I will continue
to solicit advice and help. From Doug, I know that I need to study the
particular passage in the book that offered the string query information
because I didn't grasp the content. From Bob, I now know how to design the
dialog form that will pass the information to the data entry form. Now, Bob
... my next question - lol. What does the HTH stand for? I may post tomorrow
when I try to pass the selections to the data entry form.

John, thank you . I admire people who stand up for quality and fairness.

 

[John W. Vinson]

Please stop this profanity. I do appreciate your original help and will
consider it. I did not mean to cause a problem - nor will I be a part of it.
Let it end here. Thank you.

Suzann, thank you for your poise and professionalism. Please accept my
apologies on the part of the regulars in the group. Steve is the source of the
problem, and certainly NOT you; he's been making inappropriate posts for some
time, though nothing like this. Please don't take this unpleasantness as any
sort of reflection on you or your question!

John W. Vinson [MVP]