Help with Sub-form

  • Thread starter Thread starter Jattdogg
  • Start date Start date
J

Jattdogg

HELPPP ME!! haha

I need someone's help. I've been browsing through help
topics/sites/groups on Sub-form issues but haven't been able to
implement any of the solutions into my database.

Here is my deal:

I have a form called "searchform"
-on this form, i have two combo boxes both of which are drop down lists
that are populated via queries. The names of the boxes are "Combo19"
and "Combo21"

-i created a sub-form on "searchform" by clicking on the
"subform/subreport" icon on the toolbar.

-when i did that, it asked me "what data would you like to use for your
subform..."

-i selected the option to "use an existing table/query" and chose the
query that i created called "Subform_Query". I then selected all fields
and called this new subform "SearchForm_subform"

So far so good right...wrong...

When i open the "searchform" form, and select an option in each of the
two drop downs, its then supposed to feed those two values to the query
called "Subform_Query" and then populate the "SearchForm_subform" form
below.

It doesnt do this. I'm pretty sure that there is some sort of refresh
or requery issue but nothing i try seems to work.

If i open up the "Subform_Query" manually and enter in the two values
manually, the query returns what i expected it to. So i nkow my query
is working.

Can someone please help me figure out how to get it to refresh when i
select options from the two drop down lists?????

I;ve seen people mention something about me.formname.requery or
something related to that. Ive tried a few tings but nothing works.
When i type in "me.whatever..." and then try to run the form, it gives
me some error about a macro or something... im sure i have confused you
allbut pleaseeee help me!
 
I'm willing to email my database to someone who can perhaps take a look
at my database and perhaps tweak that issue!
 
I have a form called "searchform"
-on this form, i have two combo boxes both of which are drop down lists
that are populated via queries. The names of the boxes are "Combo19"
and "Combo21"
Click to expand...

Do yourself a favor. Rename them to something meaningful.
-i created a sub-form on "searchform" by clicking on the
"subform/subreport" icon on the toolbar.
Click to expand...
-when i did that, it asked me "what data would you like to use for your
subform..."

-i selected the option to "use an existing table/query" and chose the
query that i created called "Subform_Query". I then selected all fields
and called this new subform "SearchForm_subform"
Click to expand...

Well, that doesn't help us much at all, since we have no idea what
table Subform_Query is based on, what data it contains, or much of
anything else about it...
So far so good right...wrong...

When i open the "searchform" form, and select an option in each of the
two drop downs, its then supposed to feed those two values to the query
called "Subform_Query" and then populate the "SearchForm_subform" form
below.
Click to expand...

How does it "feed" them? Are the Combo Boxes criteria on the query?
It doesnt do this. I'm pretty sure that there is some sort of refresh
or requery issue but nothing i try seems to work.
Click to expand...

Two suggestions:

1. In the AfterUpdate event of each combo box, click the ... icon,
invoke the Code Builder, and edit the sub to

Private Sub comboboxname_AfterUpdate()
Me!SearchForm_subform.Requery
End Sub

OR...

remove the criteria from Subform_Query and instead use the Subform's
Master/Child Link Field property. Set the Master Link Field to

Combo19;Combo21

(or your new names of course) and the matching fieldnames (separated
by a semicolon) in the Child Link Field. This will automate the link
without requiring any code at all.
I;ve seen people mention something about me.formname.requery or
something related to that. Ive tried a few tings but nothing works.
When i type in "me.whatever..." and then try to run the form, it gives
me some error about a macro or something... im sure i have confused you
allbut pleaseeee help me!
Click to expand...

Don't whine <g>...

I think what's happening is that you're trying to type the code
directly into the AfterEvent property. That's not how you do it -
click the ... icon, choose Code Builder, and Access will open the VBA
editor window.

Elsethread you ask about sending the database. That's not typically
appropriate - the folks who answer questions here are all volunteers,
donating time from their real-life jobs; private email support is
appropriate for paying customers, but not here (unless someone offers
free service).

John W. Vinson[MVP]
 
Well, i appreciate your response and will take a look at some of the
things you mentioned.

As for the naming of those fields, i just made it quickly on the fly as
i was playing around with the combobox, normally common sense tells you
to give it a meaningful name.

I dont appreciate your "don't whine" comment and your comment about :

"Elsethread you ask about sending the database. That's not typically
appropriate - the folks who answer questions here are all volunteers,
donating time from their real-life jobs; private email support is
appropriate for paying customers, but not here (unless someone offers
free service). "

First of all, no need to turn this into a personal insult here. I didnt
insult you in anyway so theres no need for it.

If you don't want to see my database thats fine. I never asked you
personally or said you had to. I asked simple becuase like you
mentioned there are volunteers (who i respect and appreciate) that are
willing to help and sometimes, willing to help someone out a little
more. not everything has a price tag attached to it. I help people for
free all the time and im sure you don't speak for everyone on here. If
no one wants to help me, thats fine.

Again, i appreciate your efforts and time to help me but please refrain
from criticizing me.
 
Again, i appreciate your efforts and time to help me but please refrain
from criticizing me.
Click to expand...

I apologize, Jattdogg. I was in a bad humor and you were unfortunate
in catching the brunt of it.

I hope my suggestions (particularly the master/child link) helped
solve your problem; if not, please post back and I'll help if I can,
or another volunteer may do so.

John W. Vinson[MVP]
 
No worries man :)

Anyhow ask for what you told me to do.....
-i created a sub-form on "searchform" by clicking on the
"subform/subreport" icon on the toolbar.
-when i did that, it asked me "what data would you like to use for your
subform..."
Click to expand...
-i selected the option to "use an existing table/query" and chose the
query that i created called "Subform_Query". I then selected all fields
and called this new subform "SearchForm_subform"
Click to expand...


Well, that doesn't help us much at all, since we have no idea what
table Subform_Query is based on, what data it contains, or much of
anything else about it...

Subform_Query basically...
SELECT MasterData_Test.MMID, IDTABLE_with_autonum_Test.Title,
MasterData_Test.PaymentAmt, MasterData_Test.PayeeName,
MasterData_Test.PaymentType, MasterData_Test.PaymentDate,
MasterData_Test.Country
FROM MasterData_Test INNER JOIN IDTABLE_with_autonum_Test ON
MasterData_Test.MMID = IDTABLE_with_autonum_Test.MMID
WHERE
(((IDTABLE_with_autonum_Test.Title)=[Forms]![SearchForm]![Combo19]) AND
((MasterData_Test.Country)=[Forms]![SearchForm]![Combo21]));


So far so good right...wrong...
Click to expand...
When i open the "searchform" form, and select an option in each of the
two drop downs, its then supposed to feed those two values to the query
called "Subform_Query" and then populate the "SearchForm_subform" form
below.
Click to expand...


How does it "feed" them? Are the Combo Boxes criteria on the query?

Yes, as you can see from my above query, the query depends on the
values selected from the two drop down combo boxes on my form
"SearchForm".

It doesnt do this. I'm pretty sure that there is some sort of refresh
or requery issue but nothing i try seems to work.
Click to expand...


Two suggestions:

1. In the AfterUpdate event of each combo box, click the ... icon,
invoke the Code Builder, and edit the sub to

Private Sub comboboxname_AfterUpdate()
Me!SearchForm_subform.Requery
End Sub

Tried this, didnt have an effect.


OR...


remove the criteria from Subform_Query and instead use the Subform's
Master/Child Link Field property. Set the Master Link Field to


Combo19;Combo21


(or your new names of course) and the matching fieldnames (separated
by a semicolon) in the Child Link Field. This will automate the link
without requiring any code at all.


Matching fields names? From the Subform_Query query?
I played around with waht you said but nothing worked.



I think what's happening is that you're trying to type the code
directly into the AfterEvent property. That's not how you do it -
click the ... icon, choose Code Builder, and Access will open the VBA
editor window.

No, i did as you said from before but again, this didnt have an effect.


Any other help, tips, suggestions?
 
Nevermind!

I figured out the problem. I'll admit it although quite embarrased
haha.

I had referenced the wrong variable in the query.


Anyways, thanks for your help John.
 
I figured out the problem.
Click to expand...

Good! I was getting really puzzled at what might be the problem...
glad you got it solved and again, sorry I blew up at you.

John W. Vinson[MVP]
 
John said:
Good! I was getting really puzzled at what might be the problem...
glad you got it solved and again, sorry I blew up at you.

John W. Vinson[MVP]
Click to expand...

:) Again, no worries mate!
 
Back
Top