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.

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.

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Suzann" <Suzann@discussions.microsoft.com> wrote in message 
news:460571FA-A440-4C10-98CE-63C00643E6A5@microsoft.com...
>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.
> 

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
resource@pcdatasheet.com





"Suzann" <Suzann@discussions.microsoft.com> wrote in message 
news:460571FA-A440-4C10-98CE-63C00643E6A5@microsoft.com...
>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.
> 

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?

"Douglas J. Steele" wrote:

> 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.
> 
> -- 
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no private e-mails, please)
> 
> 
> "Suzann" <Suzann@discussions.microsoft.com> wrote in message 
> news:460571FA-A440-4C10-98CE-63C00643E6A5@microsoft.com...
> >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.
> > 
> 
> 
> 

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" wrote:

> 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
> resource@pcdatasheet.com
> 
> 
> 
> 
> 
> "Suzann" <Suzann@discussions.microsoft.com> wrote in message 
> news:460571FA-A440-4C10-98CE-63C00643E6A5@microsoft.com...
> >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.
> > 
> 
> 
> 

If you need help in the future expanding what you have to track income, 
costs and claims, contact me at resource@pcdatasheet.com. 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
resource@pcdatasheet.com



"Suzann" <Suzann@discussions.microsoft.com> wrote in message 
news:8BF3D12D-7063-4960-B674-6F81B11306DB@microsoft.com...
> 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" wrote:
>
>> 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
>> resource@pcdatasheet.com
>>
>>
>>
>>
>>
>> "Suzann" <Suzann@discussions.microsoft.com> wrote in message
>> news:460571FA-A440-4C10-98CE-63C00643E6A5@microsoft.com...
>> >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.
>> >
>>
>>
>> 

=?Utf-8?B?U3V6YW5u?= <Suzann@discussions.microsoft.com> wrote in
news:1AFCEA14-CF57-4B4D-BBB4-2F2E9DD70B94@microsoft.com: 

> 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



> "Douglas J. Steele" wrote:
> 
>> 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. 
>> 
>> -- 
>> Doug Steele, Microsoft Access MVP
>> http://I.Am/DougSteele
>> (no private e-mails, please)
>> 
>> 
>> "Suzann" <Suzann@discussions.microsoft.com> wrote in message 
>> news:460571FA-A440-4C10-98CE-63C00643E6A5@microsoft.com...
>> >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.
>> > 
>> 
>> 
1



-- 
Bob Quintal

PA is y I've altered my email address.

-- 
Posted via a free Usenet account from http://www.teranews.com

"Steve" <sorry@private.emailaddress> schreef in bericht =
news:zJidi.1478$W_6.593@newsread1.news.pas.earthlink.net...
> If you need help in the future expanding what you have to track =
income,=20
> costs and claims, contact me at resource@pcdatasheet.com. If nothing =
else, I=20
> can help you with developing the structure of the tables. My fees are =
very=20
> reasonable.
>=20

--=20
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=20

Arno R

"StopThisAdvertising" <StopThisAdvertising@DataShit> wrote in
news:4675bc3b$0$25489$ba620dc5@text.nova.planet.nl: 

> 
> "Steve" <sorry@private.emailaddress> schreef in bericht
> news:zJidi.1478$W_6.593@newsread1.news.pas.earthlink.net... 
>> If you need help in the future expanding what you have to
>> track 
> income, 
>> costs and claims, contact me at resource@pcdatasheet.com. If
>> nothing 
> else, I 
>> can help you with developing the structure of the tables. My
>> fees are 
> very 
>> reasonable.
>> 
> 

>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

PA is y I've altered my email address.

-- 
Posted via a free Usenet account from http://www.teranews.com

"Steve" <sorry@private.emailaddress> wrote in
news:Bahdi.5302$tb6.3432@newsread3.news.pas.earthlink.net: 

> 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.

-- 
Bob Quintal

PA is y I've altered my email address.

-- 
Posted via a free Usenet account from http://www.teranews.com

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.

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Suzann" <Suzann@discussions.microsoft.com> wrote in message 
news:1AFCEA14-CF57-4B4D-BBB4-2F2E9DD70B94@microsoft.com...
> 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?
>
> "Douglas J. Steele" wrote:
>
>> 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.
>>
>> -- 
>> Doug Steele, Microsoft Access MVP
>> http://I.Am/DougSteele
>> (no private e-mails, please)
>>
>>
>> "Suzann" <Suzann@discussions.microsoft.com> wrote in message
>> news:460571FA-A440-4C10-98CE-63C00643E6A5@microsoft.com...
>> >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.
>> >
>>
>>
>> 

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" <sorry@private.emailaddress> wrote in message 
news:zJidi.1478$W_6.593@newsread1.news.pas.earthlink.net...
> If you need help in the future expanding what you have to track income, 
> costs and claims, contact me at "scam site" If nothing else, I can help 
> you with developing the structure of the tables. My fees are very 
> reasonable.
>
> PC Datasheet

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
resource@pcdatasheet.com


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

Quintal is a joke!!!





"Bob Quintal" <rquintal@sPAmpatico.ca> wrote in message 
news:Xns9952CA6B3C0B0BQuintal@66.150.105.47...
> "Steve" <sorry@private.emailaddress> wrote in
> news:Bahdi.5302$tb6.3432@newsread3.news.pas.earthlink.net:
>
>> 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.
>
> -- 
> Bob Quintal
>
> PA is y I've altered my email address.
>
> -- 
> Posted via a free Usenet account from http://www.teranews.com
> 

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.  

"Steve" wrote:

> If you need help in the future expanding what you have to track income, 
> costs and claims, contact me at resource@pcdatasheet.com. 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
> resource@pcdatasheet.com
> 
> 
> 
> "Suzann" <Suzann@discussions.microsoft.com> wrote in message 
> news:8BF3D12D-7063-4960-B674-6F81B11306DB@microsoft.com...
> > 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" wrote:
> >
> >> 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
> >> resource@pcdatasheet.com
> >>
> >>
> >>
> >>
> >>
> >> "Suzann" <Suzann@discussions.microsoft.com> wrote in message
> >> news:460571FA-A440-4C10-98CE-63C00643E6A5@microsoft.com...
> >> >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.
> >> >
> >>
> >>
> >> 
> 
> 
> 

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 :-).  

"Bob Quintal" wrote:

> =?Utf-8?B?U3V6YW5u?= <Suzann@discussions.microsoft.com> wrote in
> news:1AFCEA14-CF57-4B4D-BBB4-2F2E9DD70B94@microsoft.com: 
> 
> > 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
> 
> 
> 
> > "Douglas J. Steele" wrote:
> > 
> >> 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. 
> >> 
> >> -- 
> >> Doug Steele, Microsoft Access MVP
> >> http://I.Am/DougSteele
> >> (no private e-mails, please)
> >> 
> >> 
> >> "Suzann" <Suzann@discussions.microsoft.com> wrote in message 
> >> news:460571FA-A440-4C10-98CE-63C00643E6A5@microsoft.com...
> >> >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.
> >> > 
> >> 
> >> 
> 1
> 
> 
> 
> -- 
> Bob Quintal
> 
> PA is y I've altered my email address.
> 
> -- 
> Posted via a free Usenet account from http://www.teranews.com
> 
> 

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!

"Douglas J. Steele" wrote:

> 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.
> 
> -- 
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no private e-mails, please)
> 
> 
> "Suzann" <Suzann@discussions.microsoft.com> wrote in message 
> news:1AFCEA14-CF57-4B4D-BBB4-2F2E9DD70B94@microsoft.com...
> > 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?
> >
> > "Douglas J. Steele" wrote:
> >
> >> 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.
> >>
> >> -- 
> >> Doug Steele, Microsoft Access MVP
> >> http://I.Am/DougSteele
> >> (no private e-mails, please)
> >>
> >>
> >> "Suzann" <Suzann@discussions.microsoft.com> wrote in message
> >> news:460571FA-A440-4C10-98CE-63C00643E6A5@microsoft.com...
> >> >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.
> >> >
> >>
> >>
> >> 
> 
> 
> 

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

"StopThisAdvertising" wrote:

> 
> "Steve" <sorry@private.emailaddress> schreef in bericht news:zJidi.1478$W_6.593@newsread1.news.pas.earthlink.net...
> > If you need help in the future expanding what you have to track income, 
> > costs and claims, contact me at resource@pcdatasheet.com. If nothing else, I 
> > can help you with developing the structure of the tables. My fees are very 
> > reasonable.
> > 
> 
> -- 
> 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 
> 
> Arno R
> 

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.

"Steve" wrote:

> 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
> resource@pcdatasheet.com
> 
> 
> PS  A polar bear and Quintal walk into the woods ...........
> 
> Quintal is a joke!!!
> 
> 
> 
> 
> 
> "Bob Quintal" <rquintal@sPAmpatico.ca> wrote in message 
> news:Xns9952CA6B3C0B0BQuintal@66.150.105.47...
> > "Steve" <sorry@private.emailaddress> wrote in
> > news:Bahdi.5302$tb6.3432@newsread3.news.pas.earthlink.net:
> >
> >> 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.
> >
> > -- 
> > Bob Quintal
> >
> > PA is y I've altered my email address.
> >
> > -- 
> > Posted via a free Usenet account from http://www.teranews.com
> > 
> 
> 
> 

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

"Suzann" <Suzann@discussions.microsoft.com> wrote in message 
news:9C41C88F-6F77-432D-B67F-EFE889FC6A7A@microsoft.com...
> 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.
>
> "Steve" wrote:
>
>> 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
>> resource@pcdatasheet.com
>>
>>
>> PS  A polar bear and Quintal walk into the woods ...........
>>
>> Quintal is a joke!!!
>>
>>
>>
>>
>>
>> "Bob Quintal" <rquintal@sPAmpatico.ca> wrote in message
>> news:Xns9952CA6B3C0B0BQuintal@66.150.105.47...
>> > "Steve" <sorry@private.emailaddress> wrote in
>> > news:Bahdi.5302$tb6.3432@newsread3.news.pas.earthlink.net:
>> >
>> >> 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.
>> >
>> > -- 
>> > Bob Quintal
>> >
>> > PA is y I've altered my email address.
>> >
>> > -- 
>> > Posted via a free Usenet account from http://www.teranews.com
>> >
>>
>>
>> 

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 Marshall, MVP" wrote:

> 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
> 
> "Suzann" <Suzann@discussions.microsoft.com> wrote in message 
> news:9C41C88F-6F77-432D-B67F-EFE889FC6A7A@microsoft.com...
> > 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.
> >
> > "Steve" wrote:
> >
> >> 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
> >> resource@pcdatasheet.com
> >>
> >>
> >> PS  A polar bear and Quintal walk into the woods ...........
> >>
> >> Quintal is a joke!!!
> >>
> >>
> >>
> >>
> >>
> >> "Bob Quintal" <rquintal@sPAmpatico.ca> wrote in message
> >> news:Xns9952CA6B3C0B0BQuintal@66.150.105.47...
> >> > "Steve" <sorry@private.emailaddress> wrote in
> >> > news:Bahdi.5302$tb6.3432@newsread3.news.pas.earthlink.net:
> >> >
> >> >> 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.
> >> >
> >> > -- 
> >> > Bob Quintal
> >> >
> >> > PA is y I've altered my email address.
> >> >
> >> > -- 
> >> > Posted via a free Usenet account from http://www.teranews.com
> >> >
> >>
> >>
> >> 
> 
> 
> 

On Sun, 17 Jun 2007 18:15:01 -0700, Suzann <Suzann@discussions.microsoft.com>
wrote:

>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]

"Steve" <sorry@private.emailaddress> wrote in message 
news:zJidi.1478$W_6.593@newsread1.news.pas.earthlink.net...
> My fees are very reasonable.
>

X-Complaints-To: abuse@earthlink.net

From: "Steve" <sorry@private.emailaddress>
Newsgroups: microsoft.public.access.formscoding
References:  <460571FA-A440-4C10-98CE-63C00643E6A5@microsoft.com> 
<Bahdi.5302$tb6.3432@newsread3.news.pas.earthlink.net> 
<8BF3D12D-7063-4960-B674-6F81B11306DB@microsoft.com>
Subject: Re: Help with Hurricane Database
Lines: 210
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3138
X-RFC2646: Format=Flowed; Original
Message-ID: <zJidi.1478$W_6.593@newsread1.news.pas.earthlink.net>
Date: Sun, 17 Jun 2007 22:38:23 GMT
NNTP-Posting-Host: 65.78.214.12
X-Complaints-To: abuse@earthlink.net
X-Trace: newsread1.news.pas.earthlink.net 1182119903 65.78.214.12 (Sun, 17 
Jun 2007 15:38:23 PDT)
NNTP-Posting-Date: Sun, 17 Jun 2007 15:38:23 PDT
Organization: EarthLink Inc. -- http://www.EarthLink.net
Path: 
glkas0286.greenlnk.net!glkas0286!news.gradwell.net!news-peer-lilac.gradwell.net!news.glorb.com!newshub.sdsu.edu!elnk-nf2-pas!newsfeed.earthlink.net!stamper.news.pas.earthlink.net!newsread1.news.pas.earthlink.net.POSTED!902d780b!not-for-mail
Xref: glkas0286.greenlnk.net microsoft.public.access.formscoding:245141

If you need help in the future expanding what you have to track income,
costs and claims, contact me at resource@pcdatasheet.com. 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
resource@pcdatasheet.com



"Suzann" <Suzann@discussions.microsoft.com> wrote in message
news:8BF3D12D-7063-4960-B674-6F81B11306DB@microsoft.com...
> 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" wrote:
>
>> 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
>> resource@pcdatasheet.com
>>
>>
>>
>>
>>
>> "Suzann" <Suzann@discussions.microsoft.com> wrote in message
>> news:460571FA-A440-4C10-98CE-63C00643E6A5@microsoft.com...
>> >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.
>> >
>>
>>
>>

"Suzann" <Suzann@discussions.microsoft.com> wrote in message 
news:A20BD197-57F4-4087-AAFB-6DB06AE2F6F3@microsoft.com...
>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.

Please don't let "Steve" put you off.  The vast majority of people who lurk 
here will be more than happy to help FOC if they can.

Keith.
www.keithwilby.com 

Sounds as though what you might be looking for is a Filter for the form.

dim intUnit as Integer
dim strName as String
dim strFilter as String

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

  strFilter = "fldUnit = " & intUnit & " and fldHurricaneName = '" & strName 
& "'"
  Me.Filter = strFilter
  Me.FilterOn = True

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Suzann" <Suzann@discussions.microsoft.com> wrote in message 
news:3F86CBA6-B46F-40DA-92C5-706EF2A3B42E@microsoft.com...
> 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!
>
> "Douglas J. Steele" wrote:
>
>> 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.
>>
>> -- 
>> Doug Steele, Microsoft Access MVP
>> http://I.Am/DougSteele
>> (no private e-mails, please)
>>
>>
>> "Suzann" <Suzann@discussions.microsoft.com> wrote in message
>> news:1AFCEA14-CF57-4B4D-BBB4-2F2E9DD70B94@microsoft.com...
>> > 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?
>> >
>> > "Douglas J. Steele" wrote:
>> >
>> >> 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.
>> >>
>> >> -- 
>> >> Doug Steele, Microsoft Access MVP
>> >> http://I.Am/DougSteele
>> >> (no private e-mails, please)
>> >>
>> >>
>> >> "Suzann" <Suzann@discussions.microsoft.com> wrote in message
>> >> news:460571FA-A440-4C10-98CE-63C00643E6A5@microsoft.com...
>> >> >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.
>> >> >
>> >>
>> >>
>> >>
>>
>>
>> 

Just be aware that with this ELEGANT way to get user data, when the user 
wants to look at the record of a different unit, the user will have to close 
the main form, open the preliminary form, enter the Hurricane, enter the 
unit and click a button. The user will love you for that! With a combobox in 
the main form and a combobox in the subform you can change records 
immediately from the main form.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
resource@pcdatasheet.com







"Bob Quintal" <rquintal@sPAmpatico.ca> wrote in message 
news:Xns9952C82209C50BQuintal@66.150.105.47...
> =?Utf-8?B?U3V6YW5u?= <Suzann@discussions.microsoft.com> wrote in
> news:1AFCEA14-CF57-4B4D-BBB4-2F2E9DD70B94@microsoft.com:
>
>> 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
>
>
>
>> "Douglas J. Steele" wrote:
>>
>>> 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.
>>>
>>> -- 
>>> Doug Steele, Microsoft Access MVP
>>> http://I.Am/DougSteele
>>> (no private e-mails, please)
>>>
>>>
>>> "Suzann" <Suzann@discussions.microsoft.com> wrote in message
>>> news:460571FA-A440-4C10-98CE-63C00643E6A5@microsoft.com...
>>> >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.
>>> >
>>>
>>>
> 1
>
>
>
> -- 
> Bob Quintal
>
> PA is y I've altered my email address.
>
> -- 
> Posted via a free Usenet account from http://www.teranews.com
>