473,554 Members | 2,826 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Need help displaying text data for foriegn Key in form

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=Novic e, 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 ***
Nov 13 '05 #1
9 2326

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 ***
Nov 13 '05 #2
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.

Nov 13 '05 #3

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 ***
Nov 13 '05 #4
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?
Nov 13 '05 #5

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 ***
Nov 13 '05 #6
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.
Nov 13 '05 #7

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 ***
Nov 13 '05 #8
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 ***
Nov 13 '05 #9
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.

Nov 13 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

11
8731
by: Jim | last post by:
Hi, I keep getting form results emailed to me that would indicate a form from my web site is getting submitted with all fields blank or empty, but my code should preventing users from proceeding if they left any field blank. My guess is that someone is trying to hack the site using the form to gain entry or run commands -- I don't really...
4
7311
by: Les Juby | last post by:
Can someone please help with a suggestion as to how I can keep the formatting (carriage returns) that the user enters into a memo field and then display that later. I figured I might be able to use: 'replace carriage returns with BRs comment=Replace(comment, chr(13), "<br>") but obviously net.! The <pre> tag doesn't sem to help either...
2
2501
by: Susan Bricker | last post by:
Greetings! Still the same application (as previous posts). I worked on the app while at work (don't tell my boss ... cause this is just for fun and not work related) and the form was working, but now when I try to repeat the code at home (for real) it won't work. I suspect that I haven't replicated the logic and forms at home from what I...
4
1690
by: irfi | last post by:
Hi, I am a form which has a sub form, The sub form is linked with primary key of main form as a foriegn key in sub form, very basic, In the main form, I have another field which is investor, and in the sub form, I have created investory foreign key, this is the second link between main form and subm form.
0
1214
by: darrel | last post by:
Hi there, good day! i need some help in displaying my records,,, my assignment is i have to display a database records in a labels. Its like this in my database i have a table called "SUBJECTS" that is consists of 4 fields, SUBJECT CODE, SUBJECT, TYPE, & UNITS, i have input their all the data for my fields that have unique identifier. My...
2
3140
by: sorobor | last post by:
dear sir .. i am using cakephp freamwork ..By the way i m begener in php and javascript .. My probs r bellow I made a javascript calender ..there is a close button ..when i press close button then the calender gone actually i want if i click outside off the calender then it should me removed ..How kan i do this ... Pls inform me as early...
7
3301
by: Dave | last post by:
Hello All, These one may be a bit tricky, and what I'd like to do may not even be possible. I would love to hear any ideas you guys have for solving this. Here is the situation: I have a form that displays the high-level data for a set of records in continuous forms mode. I have a simple command button that will open another form with...
1
2922
by: vikjohn | last post by:
I have a new perl script sent to me which is a revision of the one I am currently running. The permissions are the same on each, the paths are correct but I am getting the infamous : The specified CGI application misbehaved by not returning a complete set of HTTP headers. The scripts are very long but here are the opening statements: The One...
11
2406
by: dba | last post by:
Have been displaying data from database using html for some time but just recently trying to display data back to "form". Can't find answer. <form method="post" action="<?php echo $PHP_SELF;?>"> First Name:<input type="text" size="12" maxlength="12" name="Fname"><br > Last Name:<input type="text" size="12" maxlength="36" name="Lname"><br />...
0
7579
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7780
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8013
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
7862
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6120
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5414
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3539
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3530
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1994
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.