making my form look like a datasheet

[Guest]

I work in tourism. We have a form that we collect every month from
guesthouses that has a list of all nationalities and then columns for the
lenght of stay. It looks like this:

Country 1Day 2Day 3Day 4ormore Total
America 5 6 1 0 12
Armenia 3 0 0 2 5
Brazil 0 0 1 1 2

So, there were a total of 12 americans who stayed at the guesthouse, 5 who
stayed 1 day, 6 who stayed 3 days and no one who stayed 4 or more days.

I have one table that has a list of all of the countries with an ID for
each. And I have a table that has 5 fields: 1 for the country ID, 1 for
'1Day', another for '2day', another for '3day' and one more for '4ormore'.

It's easy to make a data entry form for this. The problem is that with this
structure, I have to type in, or select from a combo box the country. I want
to make a form that shows ALL of the countries with four columns for lenght
of stay (1day, 2day etc) so that I can simply enter numbers in the columns.

I tried a different database structure where I made a separate field for
each country/length of entry, but that amounted to over 255 fields! Not a
good structure.

Any idea how I could keep the structure but make a form that makes it look
more like a datasheet with ALL countries showing and four columns for the
lenght of stay?

Paul

 

[Guest]

Paul,

One way to achieve what you wish is to base a continuous subform on a dummy
table that will hold one period's worth of data, and place it on an unbound
main form that has a single control that identifies the month and year, or
the date of the survey.

After you've entered the data into the form, a command button can insert
this set of data into your normalized table, increment the date control, and
run an Update query on the dummy table, setting all fields except the country
field to Null, thus reinitializing the form for the next period.

OnKeyDown event procedures assigned to each subform control makes the form
easier to navigate, calling the following custom procedure.

Private Sub HandleKeyDown(KeyCode As Integer)
On Error Resume Next
Select Case KeyCode
Case vbKeyDown
DoCmd.GoToRecord acActiveDataObject, , acNext
Case vbKeyUp
DoCmd.GoToRecord acActiveDataObject, , acPrevious
End Select
End Sub

Hope that helps.
Sprinks

 

[Guest]

Yes, this helps, but I then I would not be able to scroll through the records
from the dummy form, would I?

I have another idea: If I had a form that had a list box for the 'country
field', and then separate controls for my lenght of stay fields, could I get
the list box to change for each new record, moving alphabetically through my
list of countries? It would incrementally change like this:

Record 1: the list box starts at 'Algeria'
Record 2: the list box skips 'Algeria' and starts at 'America' (the next
record in my countries' table which provides the data for the list box)
Record 3: the list box starts at 'Angola'.
....Record 82: the list box ends at Zimbabwe
Record 83: the list box stops/does not allow any entry.

For each new record entered, the list box would start at the next
alphabetical country.

I could make the list box show only country at a time, so that my staff
would automatcally be choosing the correct country name, thus quickly being
able to fill in data for each successive country by using the Tab key/moving
on to the next country.

I'm thinking the perhaps the list box would be based on a query of my
countries table. Perhaps after each time I hit the Tab key at last lenght of
stay field that it would requery the countries query and bring up the next
country in the list box. I don't know how to make such a query, however.

I'm not a pro at this stuff, so I don't know much about using codes and
such. Sorry to bother you about this and appreciate anyhelp that you can give.

Thanks.