Too many fields - How to have it shown on form for data entry (MVPsHelp)

[fiazbfs]

I have a table and I think it is normalized based on other postings I
have read.

There are about 500 object<->value pairs, that needs to be tracked for
500 different objects on every single day of the year. And, all 500
objects should have a value for that day. Object 1-30 is about one
topic, Object 31-60 is about another topic, etc.

tblObjects, I have 500 records like this
(pk) fldObjectID - autonum
fldObjectName - Text 50

tblObjectValues,

(pk) fldDate - Date/Time
(pk) fldObjectID - (fk) to tblObjects
fldObjectValue - integer

Now, I want to show the 500 records of tblObjects in a form (under
different tabs / sections), so that user can go and type in the values
for each of those objects. Just like users will see field labels and
they have to fill in the corresponding value for that field in the
text box.

This is how the form will look like.

/++++++++++++++++++++++++\
lblfldDate: [ 08/09/2007 txtfldDate ]
-----------------------------

[Tab 1] [Tab 2] [Tab 3]

lblObjectName1 [txtObjectValue1]
lblObjectName2 [txtObjectValue2]
....
....

lblCalculatedText [ txtObjectValue2 - txtObjectValue1]
....

\++++++++++++++++++++++++/

How can I achieve this. I know that I can use a datasheet and ask
users to choose ObjectIDs and type in the value in the ObjectValue
column. But, I do not want users to select Object IDs manually. I want
it to show (just like a field name would) and users should just go in
and fill in the blanks for the corresponding date.

Also, if users select manually, they may miss out on some of the
ObjectIDs for that day.

Can someone point me in the right direction to see a sample of this
implemented somewhere or guide me here.

Thanks.

 

[Tony Toews [MVP]]

How can I achieve this. I know that I can use a datasheet and ask
users to choose ObjectIDs and type in the value in the ObjectValue
column. But, I do not want users to select Object IDs manually. I want
it to show (just like a field name would) and users should just go in
and fill in the blanks for the corresponding date.

Once the user choose the date then run an append query based on that
date and the ObjectID master table. This will then create the 500
records for that day in the table. Now use the datasheet view of the
form to view the 500 records. I prefer using continuous form view
although in such a simple data schema this won't make much difference.

I'd also suggest checking to see if records already exist for that
ObjectID and date in the table and not insert any dupes. This will
handle the case where you realize you are missing some ObjectIDs, you
add them, and then come back into the form. Now those missing
ObjectIDs will be automatically added.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[ifiaz]

Thanks for your reply.

Since I have never had any experience with running append queries or
using forms to do data entry this way, Could you guide me with a
sample database (using your suggested functionality) somewhere on the
net where I may use it as an example to learn and implement my own
database.

 

[Pat Hartman]

I would modify the design slightly to accommodate your desire to use
multiple tabs. You need to add a column to tblObjects that identifies the
group to which it belongs. You appear to be depending on the range in the
ID field for this but that will fail should you need to add an item to any
set. You can also add a sequence field so that you can control the order in
which the items appear for data entry. Without this, you are limited to ID
order or alpha order, either of which may have flaws.

Here's an example from one of my applications of how you might add
additional rows. In my case, I use the AfterUpdate event of a form, you may
want to use the Click event of a button to start your process. The code
defines the database and a querydef object. It sets the database to the
currentdb(). It sets the querydef to the name of the query I want to run.
In my case, my queries take parameters. I don't think you will need them
unless you can't calculate the date. If you can't, you can have the users
enter it on a form and pass it in to the query as I do with "Me.RiskID".
Since my code is running in a separate form from where the request
originated, there are two lines after the append query - one to requery the
main form so that it shows the records I just added - In your case, you will
probably need several requeries. One for each subform. (each tab page
should have a separate subform for simplicity). And the final statement
closes the popup form without prompting for a save.

Private Sub Form_AfterUpdate()
Dim db As DAO.Database
Dim qd As DAO.QueryDef

On Error GoTo Err_cmdSave_Click
'add exposures and coverage types
Set db = CurrentDb()
Set qd = db.QueryDefs("qAppendRiskExposures")
qd.Parameters("EnterRiskID") = Me.RiskID
qd.Parameters("EnterChangedBy") = Environ("UserName")
qd.Execute
Set qd = db.QueryDefs("qAppendCoverageTypes")
qd.Parameters("EnterRiskID") = Me.RiskID
qd.Parameters("EnterChangedBy") = Environ("UserName")
qd.Execute

Forms!frmCompanyOverview.lstRiskID.Requery
DoCmd.Close acForm, "frmAddRisk", acSaveNo

Exit_cmdSave_Click:
Exit Sub

Err_cmdSave_Click:
Select Case Err.Number
Case Else
MsgBox Err.Number & "-" & Err.Description
Resume Exit_cmdSave_Click
End Select
End Sub

 

[Tony Toews [MVP]]

I would modify the design slightly to accommodate your desire to use
multiple tabs.

Ah, I had missed that request. Thanks for noticing and for the code.

Tony

--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/