Need help displaying text data for foriegn Key in form

[Susan Bricker]

Greetings.

I am having trouble populating text data that represents data in my
table. Here's the setup:

There is a People Table (name, address, phone, ...)
peopleID = autonumber key

There is a Judge Table (information about judges)
judgeID = autonumber key
peopleID = key to tblPeople

There is an Events Table (an event represents a weekend competition-
usually 2 days)

There is a Trials Table (every event may have more than one Trial (a
Trial is a Dog Obedience Competition))

There is a TrialsClass Table (every Trial may have up to three different
Classes)

The tblTrialClass has the following fields:
trialclassID = autonumber key
trialID = ID of Trial (refers to record in tblTrials)
judgeID = ID of judge (refers to record in tblJudges, which in turn
has peopleID which refers to record in tblPeople)
class = integer(1=Novice, 2=Open, 3=Utility)

My goal: I would like to have an Events Form to dispaly Event
Information. I have an unbound form that has a combobox that will
enable the user to select an Event to display. Clicking on the right
button will open up the Events Display Window. This means that I need
to display Trial Information and TrialClass Information, also. The plan
is to have a form called frmEvents with a subform called sfrmTrials and
in sfrmTrials have a subform called sfrmTrailClass.

Here's the problem (the first of many, I'm sure):
I'm working on the innermost subform (sfrmTrialClass) and I want to
display two columns:
"CLASS" and "JUDGE NAME (CITY, STATE)"

Here's what I did (that did not work):
1. Created sfrmTrialClass (view is Continuous Forms)
2. Made bound controls (me![judgeID] and me![class) have "visible=no"
3. Created two unbound controls to display the text that I want:
txtClass (text box for Class) and cboJudge (combobox for Judge Name,
city, and state)
4. I set the recordsource for cboJudge to display the text that I want
(with SQL)...this sort of works ... I'll explain later.
5. To test the subform as a standalone form, I created some test data.
There are 12 records in tblTrialClass (and the necessary associated
records).
6. In the Form_Load( ) routine of the subform (sfrmTrialClass), I have
the following:

Private Sub Form_Load()
On Error GoTo Err_Form_Load
strProcName = "Form_Load" 'Global field for err msg
'Populate txtClass
Select Case Me!class
Case 1
Me!txtClass = "Novice"
Case 2
Me!txtClass = "Open"
Case 3
Me!txtClass = "Utility"
End Select
'Populate cboJudge
Me!cboJudge = Me!judgeID
Exit_Form_Load:
Exit Sub
Err_Form_Load:
MsgBox "ERROR in " & strModName & "-" & strProcName & "( ): " & _
Err.Number & " " & Err.Description
Resume Exit_Form_Load
End Sub

*++++++++++++++++++++++++++++++++*

When I run the form (sfrmTrialClass), all the records display but have
the same txtClass ("Novice") and all have the same cboJudge data. The
data is from the 1st record. I then changed the visible property for
me![class] and me![judgeID] to verify that all records were being
displayed and all the records are being read and displayed because the
correct integer data was displayed, but the displayed text (populated by
the LOAD routine) is not correct.

Additional Notes:
The form is going to be used to display the data and to edit/add data.
I'm sure that when I get to the edit/add actions I will have to
synchronize the text fields (that the user will type in and the
underlying field that will be saved in the records. Probably will have
to do something in the AFTERUPDATE routines.

Can you help me display the appropriate text?

Thanks.
SueB

*** Sent via Developersdex http://www.developersdex.com ***

[Susan Bricker]

I think that I should have had the subject of this Post read:

"Newbie needs help displaying text in Unbound Controls"

*** Sent via Developersdex http://www.developersdex.com ***

[rkc]

<snip a-lot-o-stuff>>

Here's what I did (that did not work):
1. Created sfrmTrialClass (view is Continuous Forms)
2. Made bound controls (me![judgeID] and me![class) have "visible=no"
3. Created two unbound controls to display the text that I want:
txtClass (text box for Class) and cboJudge (combobox for Judge Name,
city, and state)
4. I set the recordsource for cboJudge to display the text that I want
(with SQL)...this sort of works ... I'll explain later.
5. To test the subform as a standalone form, I created some test data.
There are 12 records in tblTrialClass (and the necessary associated
records).
6. In the Form_Load( ) routine of the subform (sfrmTrialClass), I have
the following:

Private Sub Form_Load()
On Error GoTo Err_Form_Load
strProcName = "Form_Load" 'Global field for err msg
'Populate txtClass
Select Case Me!class
Case 1
Me!txtClass = "Novice"
Case 2
Me!txtClass = "Open"
Case 3
Me!txtClass = "Utility"
End Select
'Populate cboJudge
Me!cboJudge = Me!judgeID
Exit_Form_Load:
Exit Sub
Err_Form_Load:
MsgBox "ERROR in " & strModName & "-" & strProcName & "( ): " & _
Err.Number & " " & Err.Description
Resume Exit_Form_Load
End Sub

*++++++++++++++++++++++++++++++++*

When I run the form (sfrmTrialClass), all the records display but have
the same txtClass ("Novice") and all have the same cboJudge data. The
data is from the 1st record. I then changed the visible property for
me![class] and me![judgeID] to verify that all records were being
displayed and all the records are being read and displayed because the
correct integer data was displayed, but the displayed text (populated by
the LOAD routine) is not correct.

Additional Notes:
The form is going to be used to display the data and to edit/add data.
I'm sure that when I get to the edit/add actions I will have to
synchronize the text fields (that the user will type in and the
underlying field that will be saved in the records. Probably will have
to do something in the AFTERUPDATE routines.

Can you help me display the appropriate text?

The bottom line is that even though you see multiple records in
continuous view there is still only one current record. Your
textbox is showing data based on the first record because it is
the current record. If you were to put your Load code in the
Form's Current event you would see that clicking the navigation
buttons would change your unbound textbox(s) to reflect data in
the new current record.

You need to grab your novice, utility or open text values in
the query the form is based on.

[Susan Bricker]

Thank you, rkc, for responding. I corrected the problem by associating
the sfrmTrialClass and sfrmTrialInfo (subforms) with queries not the
associated tables. This way I was able to query the tables with the key
and the table with the actual text data and put that in the result of
the query. Then the form could reference the text data.

As for the Class text data instead of integer value, I created another
table (tblClasses) that has two columns; classID (long integer key) and
class (text). This way other tables can reference the classID and with
a combo box or query I can get the text.

I now have the main form (frmEvents) with the subform (sfrmTrialInfo)
and it's subform (sfrmTrialClass) displaying data (in "browse" mode).

Now my problem is what do I do if there are no records in the
subordinate tables (tblTrials and tblTrialClass). Now I get an error in
the Form_Loan( ) routine when I attempt to set the AllowAdditions,
AllowEdits, and AllowDeletions to FALSE (for browse mode) when there are
not records to populate the form.

Do you have any ideas about this latest problem?

Thanks,
SueB
*** Sent via Developersdex http://www.developersdex.com ***

[rkc]

Susan Bricker wrote:

Now my problem is what do I do if there are no records in the
subordinate tables (tblTrials and tblTrialClass). Now I get an error in
the Form_Loan( ) routine when I attempt to set the AllowAdditions,
AllowEdits, and AllowDeletions to FALSE (for browse mode) when there are
not records to populate the form.

Do you have any ideas about this latest problem?

Nope. Post the error message.
What else is going on in the Form_Load code?

[Susan Bricker]

rkc,

I don't have the error number here (I'm at work and this "project" is a
side/fun task for a friend). However, I got past the error attempting
to change the AllowAdditions/AllowEdits/AllowDeletions properties of the
subforms by doing the following:

In the parent form's Form_Load:
1. used DCOUNT to determine if there are any Trial Records for the
current Event. If NO then set a label that says "No Trials Defined for
this Event" to VISIBLE and set the subform control to NOT VISIBLE.

That works fine.

Now I am working on what if there are Trial Records but for some of the
Trial Records there are no TrialClass Records (the nested subform). I
need to get some logic into the Form_Current routine of the
sfrmTrialInfo (outter subform in frmEvents) to detect if there are any
TrialClass records and then do the same sort of thing in that subform
(make a label visible and the subform (sfrmTrialClass) control not
visible.

I'm working on that and I'm sure that eventually I'll get that piece to
work. NOT THERE YET.

Thanks for answering my Post. I'm sure I'll have more questions when I
try to handle the Add a new Record / Edit existing Record situations,
because the forms are based on Queries not Tables and I think that I'll
have to "manually" (in VBA) save the updated/new records using DAO db
and rs commands. But I'm not there, yet.

Regards,
SueB

*** Sent via Developersdex http://www.developersdex.com ***

[rkc]

Susan Bricker wrote:

Thanks for answering my Post. I'm sure I'll have more questions when I
try to handle the Add a new Record / Edit existing Record situations,
because the forms are based on Queries not Tables and I think that I'll
have to "manually" (in VBA) save the updated/new records using DAO db
and rs commands. But I'm not there, yet.

I have no idea of the details of what you're up to, but in most cases
a form's recordsource should be a query and in most cases it is possible
to make the query updatable. Since you have chosen Access as your
application platform it just makes sense to let Access do it's thing
instead of figuring out ways to fight it.

[Susan Bricker]

Thank you for the input. I'll definitely see where I can simplify and
use Access more.

SueB
*** Sent via Developersdex http://www.developersdex.com ***

[Susan Bricker]

rkc,

Thank you for your input. I changed the forms to get their data from
the associated tables and added comboboxes to the forms (and subforms)
to display the text I wanted. I set the recordsource of the comboboxes.
The FK fields (from the underlying tables will have their controls NOT
VISIBLE and I've added code to synchronize the comboboxes with their
associcated FK fields (IDs).

It's working just the way I want it to. Life is good - at the moment.

Thanks, again.
SueB

*** Sent via Developersdex http://www.developersdex.com ***

[rkc]

Susan Bricker wrote:

It's working just the way I want it to. Life is good - at the moment.

Boo-yeah!

Stick to it and you can do it. It ain't brain surgery.