auto filled fields displays only partial data

[Guest]

Hello---HelpPlease!
I created a simple Access database to store records of problems (named
Requests) and each problems answers (named Solutions) .

I have designed this database so each that Request and Solution is
associated with an appropriate Topic when recorded which I then use to
conduct searches by Topic to quickly access relevent records

The database comprises of;

tblRequests
RequestID (PK AutoNumber)
TopicID (FK) One in one to many relationship
Topic (Text lookup to tblTopics)
Request (Text)
Solution (Text)

tblTopics
TopicID (PK AutoNumber) Many part of one to many relationship
Topic (Text)

frmRequests
cboTopics
txtRequests
txtSolutions

The cboTopics is working to display all records in txtRequests----
HOWEVER..............
It only fills in part of the records in txtSolutions field

Eg: There are multiple records under the Topic Printer and when I choose
Printer from the cboTopics on my frmRequests; multiple records are displayed
in txtRequests but only one record is displayed in txtSolution

My code

Option Compare Database
Option Explicit

Private Sub cboRequest_Change()
With Me.lstSolution
.Requery
If .ListCount > 0 Then
.SetFocus
.Selected(0) = True
End If
End With
End Sub

Private Sub cboTopic_Change()
With Me.lstRequest
.Requery
If .ListCount > 0 Then
.SetFocus
.Selected(0) = True
End If
End With
With Me.lstSolution
.Requery
If .ListCount > 0 Then
.SetFocus
.Selected(0) = True
End If
End With
End Sub

Private Sub cmd_Close_Click()
On Error GoTo Err_cmd_Close_Click


DoCmd.Close

Exit_cmd_Close_Click:
Exit Sub

Err_cmd_Close_Click:
MsgBox Err.Description
Resume Exit_cmd_Close_Click

End Sub

Any suggestion welcome
Cheers Ohbe

PS: How do I create Folder type tabs to forms to improve a dayabases
navigation

 

[tina]

from your tables design, looks like your relationships are backward.
tblTopics looks like a simple list of all possible topics, which you use to
categorize each request/solution record. correct?

if each topic may have many requests, BUT each request may only be
categorized to one topic, then you have a one-to-many relationship with
tblTopics on the "one" side and tblRequests on the "many" side. in that
case, your tables design is *almost* correct. just remove the Topic text
field from tblRequests (duplicating the data, which is already stored in
tblTopics, is unnecessary, problematic, and a violation of data
normalization rules. and using a Lookup field in a table is a big design
no-no. for details, see http://www.mvps.org/access/lookupfields.htm) also,
make sure your relationship setup in the Relationships window correctly
defines tblTopics as the "left-side" or "parent" table, and tblRequests as
the "right-side" or "child" table.

you can use a single, simple form for data entry, bound to tblRequests, with
a combo box bound to the foreign key field TopicID, and the combo box's
RowSource set to tblTopics.
*************************

on the other hand, if each topic many have many requests, AND each request
may be categorized to multiple topics, then you have a many-to-many
relationship between the two tables. to model such a relationship, you need
three tables, as

tblRequests
RequestID (PK AutoNumber)
Request (Text)
Solution (Text)

tblTopics
TopicID (PK AutoNumber)
Topic (Text)

tblRequestTopics (linking table that resolves many-to-many relationship)
RequestID (fk from tblRequests)
TopicID (fk from tblTopics)
if one request is categorized to three different topics, then this table's
data would look like

RequestID TopicID
1 1
1 2
1 3

note: you can use the two foreign key fields as a combination primary key,
or add an Autonumber field as the table's primary key.

use a standard mainform/subform setup to enter request records. bind the
main form to tblRequests. bind the subform to tblRequestTopics, with the
subform control's MasterLinkField and ChildLinkField set to RequestID. in
the subform, bind the foreign key field TopicID to a combo box, and use
tblTopics for the combo box's RowSource.
**********************

in either solution, note: to add new topics to tblTopics (so that they show
in the combo box droplist), enter the records in a separate form bound
directly to tblTopics.

hth

 

[Guest]

from your tables design, looks like your relationships are backward.
tblTopics looks like a simple list of all possible topics, which you use to
categorize each request/solution record. correct?

if each topic may have many requests, BUT each request may only be
categorized to one topic, then you have a one-to-many relationship with
tblTopics on the "one" side and tblRequests on the "many" side. in that
case, your tables design is *almost* correct. just remove the Topic text
field from tblRequests (duplicating the data, which is already stored in
tblTopics, is unnecessary, problematic, and a violation of data
normalization rules. and using a Lookup field in a table is a big design
no-no. for details, see http://www.mvps.org/access/lookupfields.htm) also,
make sure your relationship setup in the Relationships window correctly
defines tblTopics as the "left-side" or "parent" table, and tblRequests as
the "right-side" or "child" table.

you can use a single, simple form for data entry, bound to tblRequests, with
a combo box bound to the foreign key field TopicID, and the combo box's
RowSource set to tblTopics.
*************************

on the other hand, if each topic many have many requests, AND each request
may be categorized to multiple topics, then you have a many-to-many
relationship between the two tables. to model such a relationship, you need
three tables, as

tblRequests
RequestID (PK AutoNumber)
Request (Text)
Solution (Text)

tblTopics
TopicID (PK AutoNumber)
Topic (Text)

tblRequestTopics (linking table that resolves many-to-many relationship)
RequestID (fk from tblRequests)
TopicID (fk from tblTopics)
if one request is categorized to three different topics, then this table's
data would look like

RequestID TopicID
1 1
1 2
1 3

note: you can use the two foreign key fields as a combination primary key,
or add an Autonumber field as the table's primary key.

use a standard mainform/subform setup to enter request records. bind the
main form to tblRequests. bind the subform to tblRequestTopics, with the
subform control's MasterLinkField and ChildLinkField set to RequestID. in
the subform, bind the foreign key field TopicID to a combo box, and use
tblTopics for the combo box's RowSource.
**********************

in either solution, note: to add new topics to tblTopics (so that they show
in the combo box droplist), enter the records in a separate form bound
directly to tblTopics.

hth





Tina

Thank you and sorry for the delay in responding!

Your solution did help but it also served to help me discover a new approach
so I learnt more than I anticipated. lol.

I have to laugh at something, so seeminly obvious to experience users,
but.... for a novice, I thought it humourous that Access help yeilds no
assistance on the

I quote

""left-side" or "parent" table, and tblRequests as

the "right-side" or "child" table.

Being a parent I assume that the parent would be the table with the many
parts to contribute and the child to be the one part to give. (sorry to all
the younger readers--but what can I say-- I am an oldie)

In this field I also find it a stong possibility that the reverse is true
because kids have a language all of there own and that can also be said of
Microsoft Access and VBA

Who knows?-- Not me and I guess that is part of the problem really

Thanks Tina

 

[tina]

you're welcome, Sherry, glad you were able to figure it out despite the
confusion over terminology. relationships can be tricky in Access until you
learn the terminology and get some experience at building them, and
sometimes even then they can be tough - in that way, they *are* similar to
real life relationships. <g>

tables/relationships are the foundation of every relational database, and
getting them "right" is the crucial first step in building your database. to
learn more about defining tables/relationships, one good text is Database
Design for Mere Mortals by Michael Hernandez. and there are many valuable
links at the following webpage:
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html
beginning with the Starting Out and Database Design 101 links.

hth