Re: Help with Hurricane Database

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:1AFCEA14-CF57-4B4D-BBB4-2F2E9DD70B94@xxxxxxxxxxxxxxxx
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@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:460571FA-A440-4C10-98CE-63C00643E6A5@xxxxxxxxxxxxxxxx
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.







.

Relevant Pages

  • Re: Help with Hurricane Database
    ... for the store number and the hurricane name, ... Hurricane Name before the form loads, ... information and the sub form has the data entry portion - I'm ... beginning VBA programmer ...
    (microsoft.public.access.formscoding)
  • Re: Help with Hurricane Database
    ... the main form, open the preliminary form, enter the Hurricane, enter the ... for the store number and the hurricane name, ... Form's query, and you have a more elegant way to get user data. ... beginning VBA programmer ...
    (microsoft.public.access.formscoding)
  • Re: Help with Hurricane Database
    ... Thank you, Bob. ... combo boxes for the Hurricane name and the Unit info that would pull the data ... for the store number and the hurricane name, ... simple parameters in the query design window. ...
    (microsoft.public.access.formscoding)
  • Re: Aggregate calculations on calculated query fields?
    ... "KARL DEWEY" wrote: ... data entry date, display using datasheet form, and then a command button to ... query shown before to do all the calculations. ... UNION SELECT INVENTORY.Date ...
    (microsoft.public.access.queries)
  • Re: Multiple Table Search
    ... The combobox on TrainingRecs Subform2 should be bound to the JobID field and should have ... John Spencer wrote: ... picked up by the select query (the query at the moment is set to return all ... The Data Entry property on the form is set to NO ...
    (microsoft.public.access.queries)