Conditionally use two different Queries for RowSource?

[sturose2000]

I've found snippets of help in other posts and in Help files, but still
can't get this to work nicely:

I have a form frmContacts, that contains an unbound combo box cboUniv and a
bound combo box cboOrganization. The cboUniv is just to make the choices
"pretty," viz., each record in tblContacts has an Organization; the
Organization may be a University or may have an Affiliation with a
University. Thus, I have three queries: qryUniv selects only Universities
from tblOrganizations; qrySelectAffiliated selects the University and any
Organization that is affiliated with that University (chosen by cboUniv) or
if cboUniv is Null then it returns all Organizations; qryReverseAffiliated
is identical to qryUniv if cboOrganization is Null and selects only the
University that is affiliated with cboOrganization if it has a value. All
these queries work when run on their own.

What I want is the following:
1. For records on frmContacts where both cboUniv and cboOrganization are
Null (usually these are new records) then the RowSource for cboUniv should
be qryUniv.
2. For records on frmContacts where cboUniv is Null and cboOrganization is
not Null (happens for old records that I made before adding cboUniv and also
for Organizations that are not affiliated with a University, e.g. companies)
then value of cboUniv should be Null or the University from
qryReverseAffiliated.
3. When I select a University on cboUniv, then the list in cboOrganization
will update by qrySelectAffiliated.

Like I said, I've snipped pieces of advice and have come up with the
following, but since I've never worked with VBA before I'm a bit lost as to
why this isn't working:

Private Sub cboUniv_AfterUpdate()
Me.cboOrganization.Requery
End Sub
' This takes care of case 3 above; it seems to work alright.

Private Sub Form_Current()
If IsNull(Me.cboOrganization) Then
Me.cboUniv.RowSource = "qryUniv"
Me.cboUniv.DefaultValue = "Null"
Me.cboUniv.Value = "Null"
Me.cboUniv.Requery
Else
Me.cboUniv.RowSource = "qryReverseAffiliated"
Me.cboUniv.Requery
End If
End Sub
' This produces an error I think...

Private Sub cboUniv_OnGotFocus()
Me.cboUniv.RowSource = "qryUniv"
Me.cboUniv.DefaultValue = Me.cboUniv.Value
Me.cboUniv.Requery
End Sub
' This is so that I can change the value in cboUniv even after cboUniv and
cboOrganization have been set.

Private Sub Organization_NotInList()
Me.cboUniv.RowSource = "qryReverseAffiliated"
Me.cboUniv.Requery
Me.cboOrganization.Requery
End Sub
' This was meant to take care when I choose the wrong cboUniv after
cboOrganization already has a value.

Any help would be *greatly* appreciated. I can provide more info if
necessary, but I'm trying to be as clear as possible here.

TIA,
Stu

 

[david epsom dot com dot au]

What exactly is not working? What do you see? What error did you get?

You may find that checking the ListCount works better than doing a
requery. In some situations Requery seems to be ignored.

(david)

 

[sturose2000]

(Sorry, I clicked Reply, not Reply Group...)

----- Original Message -----

What exactly is not working? What do you see? What error did you get?

You may find that checking the ListCount works better than doing a
requery. In some situations Requery seems to be ignored.

(david)

When I first open the form or move to a new record I get the following
message:
"The expression On Current you entered as the event property setting
produced the following error: Procedure declaration does not match
description of event or procedure having the same name.
*The expression may not result in the name of a macro, the name of a
user-defined function, or [Event Procedure].
*There may have been an error evaluating the function, event, or macro."

I get the same message with On Got Focus and with After Update, and the
cboUniv does not properly requery.
The reason I'm doing all this, is more of a workaround than anything else.
What happened before I started messing in VBA (i.e. the only event procedure
I had was the cboUniv_AfterUpdate, which I found in a similar post in this
ng from rpw) was that when I updated cboUniv, it would properly requery the
source for cboOrganization.

But when I went to the next record, say for example record #2 I chose
cboUniv=Washington, then chose cboOrganization for something affiliated with
Washington, and for record #3 cboUniv=Iowa. Now I go back to #2 and cboUniv
is still Iowa (as set in #3) and cboOrganization shows blank (but I think
its value is still stored, it's just NotInList). So I had to go back to
reset cboUniv to Washington and then cboOrganization would be correct.

I don't know, would it be easier just to store cboUniv in the parent table
tblContacts and make it a bound combo? Then at least I wouldn't need to
worry about the reverse requery???

BTW I looked up the ListCount--haven't tried it yet but the thing is not all
Universities have the same number of Organizations.

Thanks,
Stu

 

[sturose2000]

Ok, I have something that works now (at least it works well enough...)

I added a column to tblContacts and then bound frmContacts.cboUniv to
tblContacts.University
This takes care of the wrong university showing up when going back to old
records.

To take care of the cboOrganizations problem, I used the following:

Private Sub Form_Current()
Me.cboOrganization.Requery
End Sub

Private Sub cboUniv_AfterUpdate()
Me.cboOrganization.Requery
End Sub

Private Sub cboOrganization_NotInList(NewData As String, Response As
Integer)
Me.cboOrganization.Requery
End Sub

Like I said, it works well enough, even though it maybe wasn't *exactly*
what I wanted (would have preferred not to add University to tblContacts,
but it's not too big deal yet, still only working with a few records until
next week...) Looks like I can get rid of qryReverseAffiliated.

Thanks,
Stu

----- Original Message -----

What exactly is not working? What do you see? What error did you get?

You may find that checking the ListCount works better than doing a
requery. In some situations Requery seems to be ignored.

(david)

When I first open the form or move to a new record I get the following
message:
"The expression On Current you entered as the event property setting
produced the following error: Procedure declaration does not match
description of event or procedure having the same name.
*The expression may not result in the name of a macro, the name of a
user-defined function, or [Event Procedure].
*There may have been an error evaluating the function, event, or macro."

I get the same message with On Got Focus and with After Update, and the
cboUniv does not properly requery.
The reason I'm doing all this, is more of a workaround than anything else.
What happened before I started messing in VBA (i.e. the only event procedure
I had was the cboUniv_AfterUpdate, which I found in a similar post in this
ng from rpw) was that when I updated cboUniv, it would properly requery the
source for cboOrganization.

But when I went to the next record, say for example record #2 I chose
cboUniv=Washington, then chose cboOrganization for something affiliated with
Washington, and for record #3 cboUniv=Iowa. Now I go back to #2 and cboUniv
is still Iowa (as set in #3) and cboOrganization shows blank (but I think
its value is still stored, it's just NotInList). So I had to go back to
reset cboUniv to Washington and then cboOrganization would be correct.

I don't know, would it be easier just to store cboUniv in the parent table
tblContacts and make it a bound combo? Then at least I wouldn't need to
worry about the reverse requery???

BTW I looked up the ListCount--haven't tried it yet but the thing is not all
Universities have the same number of Organizations.

Thanks,
Stu

 

[Guest]

Hi,

It looks like you're doing pretty well with picking up snippits of help here and there and getting it to work for you.

As I was reading your posts, I began to wonder about the table structure and relationships. It seems like you might be creating more problems by fixing the ones you have with these combos.

After reading your last post, tblContacts now has one field for Organization and one field for University. That means that any contact can only have one Organization and only one University and I doubt that your real world will stay that way.

Also, it seems like Universities are a field in tblOrganizations - is this correct? If so, then you should consider having separate tables for each.

You may want to post your table structure for others to analyze.

 

[Guest]

Hi again,

I have built some tables and a form that replicates your scenario as I understand it. Here are the tables:

tblContact
ContactID
ContName
OrgID

tblOrganization
OrgID
OrgName
UnivID

tblUniversity
UnivID
UnivName

The form has two combo boxes on it. One for the Organization list, and one for the University list. Here's what happens with this form. When switching records, the organization assigned to the contact is displayed and the university assigned to the organization is displayed. When you click into the university combo, the full list of universities is available. When you click into the organization combo after that, only organizations associated with the selected university is available.

Here is the entire code to make this happen. Please excuse the sloppy naming conventions.

Option Compare Database
Option Explicit

Private Sub Combo3_AfterUpdate()
Me.OrgID.RowSource = "SELECT tblOrganizations.OrgID, " & _
"tblOrganizations.OrgName, tblOrganizations.UnivID" & _
" FROM tblOrganizations " & _
"WHERE (((tblOrganizations.UnivID)=[Forms]![frmContacts]![Combo3]));"
Me.OrgID.Requery

End Sub

Private Sub Combo3_GotFocus()
Combo3.RowSource = "SELECT tblUniversity.UnivID, tblUniversity.UnivName" & _
" FROM tblUniversity;"
Combo3.ColumnCount = 2
Combo3.ColumnWidths = "0 cm;2.5 cm"
Me.Combo3.Requery
End Sub

Private Sub Form_Current()
Combo3.Value = Null
Combo3.RowSource = "SELECT tblOrganizations.OrgID, tblOrganizations.OrgName," & _
"tblOrganizations.UnivID, tblUniversity.UnivName" & _
" FROM tblOrganizations INNER JOIN tblUniversity" & _
" ON tblOrganizations.UnivID = tblUniversity.UnivID;"
Combo3.ColumnCount = 4
Combo3.ColumnWidths = "0 cm;0 cm;0 cm;2.5 cm"
Combo3.Requery
Combo3.Value = OrgID.Value
Me.OrgID.Value = Combo3.Value
End Sub

 

[sturose2000]

Thanks for the interest. Here's my (abridged) table structure:

tblContacts
ContactAutoID (PK, autonumber)
Last Name, First, etc
University [may be Null for non-university Contacts]
Organization [may be Null or identical to University for some Contacts]
SurveyVerstion
(other detail fields...)
tblEntityTypes
EntityTypeID (number, PK but not autonumber)
EntityTypeLabel
[University = 1, College = 2, Office = 11, Corporation = 21, etc...]
tblOrganizations
OrgID (PK, autonumber)
OrganizationName
OrganizationType (Lookup from tblEntityTypes)
WebURL
Affiliations [allowed to be Null, otherwise lookup from a query that
selects all OrganizationType<=2)

There are other tables, one for each SurveyVersion (yet to be written, but
we're surveying engineering faculty with Version 1, Tech Transfer staff with
Version 2, students with Version 3, administrators with Version 4, etc...)
and some tables with intellectual property policy info that I've researched
ahead of time.

I will take a look at the code from your other post sometime this weekend,
mostly for my own future reference and not for the job now: my boss is very
entrepreneurial and likes the solutions that work *now* not that work
*best*! =^) But I guess the project details (many of which are very
specific and probably not worth posting) and the time frame (we're doing
these interviews within a month, so when a Contact changes Organizations
that won't concern us) have influenced my design.

On a side note, I'm very happy I've had to do this for work, since I've
tried to sit down with MS Access a number of times for personal projects and
just couldn't scrape together the motivation to learn it right. My job
gives me an excuse to delve into VBA and SQL, something I wouldn't have time
for during the school year!

Thanks again to all who read and posted,
Stu

Hi,

It looks like you're doing pretty well with picking up snippits of help

here and there and getting it to work for you.

As I was reading your posts, I began to wonder about the table structure

and relationships. It seems like you might be creating more problems by
fixing the ones you have with these combos.

After reading your last post, tblContacts now has one field for

Organization and one field for University. That means that any contact can
only have one Organization and only one University and I doubt that your
real world will stay that way.

Also, it seems like Universities are a field in tblOrganizations - is this

correct? If so, then you should consider having separate tables for each.

 

[Guest]

I have a couple of questions and a couple of comments for you, if you don't mind.

If an organization that is a university (say, USC) is entered into the db, should it be possible for USC to be affiliated with UCLA or any other university or college? The way tblOrganizations is setup, it is possible - I just don't understand why it should be though.

Are you trying to allow frmContacts to create the affiliation to a university or organization? If so, it won't work. Let's say that you have USC as an organization with no affiliation. You select USC in cboOrganization, then select University in cboUniv. The field Affiliations in tblOrganization is not updated, so the next time you select USC in cboOrganization, you will have to also select University in cboUniv. If you assign Affiliations in a frmOrganization then the table is updated and every time you select USC in cboOrganization, cboUniv can display USC. Another problem with having University bound to tblContacts is that if you ever do associate USC the organization with USC the affiliation in tblOrganization, none of the University fields will be updated.

I would imagine that you would want all Organizations that have an EntityType of University or College to also have an affiliation to the University or College of the same name (and therefore type). An update query should be able to do this for you quickly.

Generally, the FK field name is the same as the PK field name of the foriegn table. For example, in your tblContacts the Organization field is FK to tblOrganizations-OrgID. Typically, it's tblContacts-OrgID and tblOrganization-OrgID. This usually makes it easier for others to follow relationships by looking at the table structure.

In the code that I posted earlier, I modified Row Source properties by including the query SQL as part of the code. I noticed that your row source modifications used a query object. You may or may not know this already, but you don't have to use a query object. You can 'build' the query in the properties of the control. That keeps the db window cleaner. If you don't know how and would like to, post back.

Personally, I think I might design things a little different. Unbound combo that selects entity types, unbound combo that selects affiliations, then cboOrganization filtered on the selections in the other two combos.

Hope this helps
--
rpw

Thanks for the interest. Here's my (abridged) table structure:

tblContacts
ContactAutoID (PK, autonumber)
Last Name, First, etc
University [may be Null for non-university Contacts]
Organization [may be Null or identical to University for some Contacts]
SurveyVerstion
(other detail fields...)
tblEntityTypes
EntityTypeID (number, PK but not autonumber)
EntityTypeLabel
[University = 1, College = 2, Office = 11, Corporation = 21, etc...]
tblOrganizations
OrgID (PK, autonumber)
OrganizationName
OrganizationType (Lookup from tblEntityTypes)
WebURL
Affiliations [allowed to be Null, otherwise lookup from a query that
selects all OrganizationType<=2)

There are other tables, one for each SurveyVersion (yet to be written, but
we're surveying engineering faculty with Version 1, Tech Transfer staff with
Version 2, students with Version 3, administrators with Version 4, etc...)
and some tables with intellectual property policy info that I've researched
ahead of time.

I will take a look at the code from your other post sometime this weekend,
mostly for my own future reference and not for the job now: my boss is very
entrepreneurial and likes the solutions that work *now* not that work
*best*! =^) But I guess the project details (many of which are very
specific and probably not worth posting) and the time frame (we're doing
these interviews within a month, so when a Contact changes Organizations
that won't concern us) have influenced my design.

On a side note, I'm very happy I've had to do this for work, since I've
tried to sit down with MS Access a number of times for personal projects and
just couldn't scrape together the motivation to learn it right. My job
gives me an excuse to delve into VBA and SQL, something I wouldn't have time
for during the school year!

Thanks again to all who read and posted,
Stu

Hi,

It looks like you're doing pretty well with picking up snippits of help

here and there and getting it to work for you.

As I was reading your posts, I began to wonder about the table structure

and relationships. It seems like you might be creating more problems by
fixing the ones you have with these combos.

After reading your last post, tblContacts now has one field for

Organization and one field for University. That means that any contact can
only have one Organization and only one University and I doubt that your
real world will stay that way.

Also, it seems like Universities are a field in tblOrganizations - is this

correct? If so, then you should consider having separate tables for each.

 

[sturose2000]

Thx for the feedback. My answers follow each question below:

I have a couple of questions and a couple of comments for you, if you don't mind.

If an organization that is a university (say, USC) is entered into the db,

should it be possible for USC to be affiliated with UCLA or any other
university or college? The way tblOrganizations is setup, it is possible -
I just don't understand why it should be though.
Well you're right, the way I've set it up it is possible though probably not
necessary or desired. Though in one interesting case, for example, there is
a collaboration between MIT and Stanford in some sort of engineering
design--though that particular program won't be a part of our data set.

Are you trying to allow frmContacts to create the affiliation to a

university or organization? If so, it won't work. Let's say that you have
USC as an organization with no affiliation. You select USC in
cboOrganization, then select University in cboUniv. The field Affiliations
in tblOrganization is not updated, so the next time you select USC in
cboOrganization, you will have to also select University in cboUniv. If you
assign Affiliations in a frmOrganization then the table is updated and every
time you select USC in cboOrganization, cboUniv can display USC. Another
problem with having University bound to tblContacts is that if you ever do
associate USC the organization with USC the affiliation in tblOrganization,
none of the University fields will be updated.
At one point I wanted frmContacts to be able to create an affiliation, but
after half a day of working on it with no luck, I gave up. Good to know at
least it can't be done, not just that I couldn't do it.

I would imagine that you would want all Organizations that have an

EntityType of University or College to also have an affiliation to the
University or College of the same name (and therefore type). An update
query should be able to do this for you quickly.
Ok, I "cheated" here. When EntityType is University or College, I generally
just leave affiliation blank. In all my queries where I want, say, all the
Organizations that are affiliated with USC and also USC itself, I've added
an SQL statement allowing selection of records with Affiliations Is Null and
University = whichever one I'm looking up.

Generally, the FK field name is the same as the PK field name of the

foriegn table. For example, in your tblContacts the Organization field is
FK to tblOrganizations-OrgID. Typically, it's tblContacts-OrgID and
tblOrganization-OrgID. This usually makes it easier for others to follow
relationships by looking at the table structure.
Yes, after looking at some other folks' sample code I realized that, but
when I first built the tables and then went back to update them I changed
one of the names and never re-aligned them. If I have time before my
deadline I'll fix that.

In the code that I posted earlier, I modified Row Source properties by

including the query SQL as part of the code. I noticed that your row source
modifications used a query object. You may or may not know this already,
but you don't have to use a query object. You can 'build' the query in the
properties of the control. That keeps the db window cleaner. If you don't
know how and would like to, post back.
Ok, now I'm looking again at what you posted. That makes sense. Like I
said, I haven't used VBA ever, and very limited Access prior to this
project, and in order to test that my queries were indeed returning what I
expected I wrote them separately.

Personally, I think I might design things a little different. Unbound

combo that selects entity types, unbound combo that selects affiliations,
then cboOrganization filtered on the selections in the other two combos.
Point taken. Again, I'm working against deadlines and always have to
balance doing it "right" with doing it so it works well enough. If I get
skeleton built in time, I will try to tinker and get the details working
better.

Hope this helps

It really has!
If you're curious, I've been trying to keep the current version (at the end
of each working day) posted so my boss can see it. Find it at
http://students.olin.wustl.edu/~rosenbergs/entre/survey_v5.mdb (but please
don't "steal" any names from my tblContacts!!)

-Stu