pop up form by selection

[Guest]

I am attempting to create form that has all the same base information in all
data bases but upon a selection from a combo box a different subform pops up
to complete the specific request they are inputing.

 

[Jeff Boyce]

The terminology you are using is a bit confusing, too.

What do you mean by "all the same base information in all databases"? Are
you talking about tables (the object that holds data in an Access database
file), or are you talking about multiple Access .mdb files (i.e.,
"databases")?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Jeff,

What I mean is my main form uses a two databases called issues and contacts.
This form also has a combo box which offers a selection. I want a different
subform to pop-up for specific selections in the combo box (i.e. if the
selection is Facilities one form would open which belongs to the facilities
database, if the selection is Ordering it would open the Ordering database
subform and so on). Does this clear up my first message?

Bob

 

[Jeff Boyce]

Bob

In Access, the things that hold buckets o' data are called 'tables', not
'databases'. The entire .mdb file is called a 'database' in Access. I
offer these definitions so you can be assured that what you are asking for
and what folks are answering match up better.

One approach to doing what you've described would be to change the source of
the subform (by the way, another definition for Access -- a subform is a
form that is embedded within a "main form").

Thus, subforms do not "pop up". If you want a form to pop up, it's called
either a "popup form" or a "dialog box").

To get a form to pop up, use code in the combo box's AfterUpdate event that
opens the new form.

To get a different subform to "load" on your main form, again add code to
the combo box's AfterUpdate event, but this time, in the code, alter the
'source' for the subform.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Jeff,

I apologize if I may have perturbed you with my lack of proper terminology.
I actually accomplished the function I was attempting to create. I am now
having an issue getting the ID fields to link after the selection is made in
the main form and the popup form opens. I thank you for any and all
assistance.

Bob

 

[Jeff Boyce]

I'm not bothered in the slightest. I only offered the clarification because
not sharing the same definitions means the answers you get may not be to the
questions you are asking.

I'm not sure what you mean by "getting the ID fields to link". Can you
clarify this?

Is the combo box's AfterUpdate event opening your "popup form"? If so, are
you using the OpenArgs syntax to pass in the ID, or are you using the popup
form's OnOpen event to "look" back at the form from which it is popping up
to gather the value of the ID?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

My main form (frmIssues) when it opens it assigns and Autonumber into the ID
field. The mainform also has a combo box which has different categories of
issues to put in a request for. The combo box when a selection is made it
will open other forms depending on the selection in the combo box. I used a
macro in the After update property of the combo box to open the specific form
for each selection. Each category has a different table and each table has
an ID field. I have created a relationship between the Issues table and all
other tables based on the ID field. My current problem is that when a
selection is made in the combo box the form pops up but I can not get the ID
to link in the ID field of the form so the record will save. Thanks for all
your time and patience,

Bob

 

[Guest]

Jeff,

As far as using OpenArgs syntax I have not used this syntax previsouly. I
would not know the approiate way to write out the syntax.

 

[Jeff Boyce]

Bob

To clarify, it sounds like you've joined your main table and your other
tables on their ID fields. Are all the tables using Autonumber data types
for ID fields? If so, that is certainly a problem!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Jeff,

No, I do not have all tables with a autonumber, only tblIssues and the
remaining tables have the ID field as a number field only. I do have all the
tables joined as a one to many relationship. The one being the main table
and the many side are the remaining tables.

Regards,

Bob

 

[Jeff Boyce]

OK, thanks for clarifying.

So, to recap, you have a main form, based on a main table, which has an
AutoNumber ID field as a primary key. You have multiple subforms, each
based on a different table, those tables being related to the main table as
"child" tables (i.e., main-to-other is 1:m).

You are trying to get a different subform to display, depending on some
choice in a combo box on the main form (but still related to the record
being displayed on the main form).

Here's a potential alternate approach to having those various subforms swap
in and out (or "popup", as you've described them)...

On your main form, add a tab control. Use a combo box in the form header to
allow the user to select a record from the main table. In the AfterUpdate
event of that (unbound) combo box, requery the form. Base the form on a
query that looks to the form's combobox to select the record to return.

Now, in design view, add controls on the first/front tab that correspond to
your main table fields. You'll know you can "drop" them onto the first tab
when the background of the tab turns dark. Next, click on the next tab.
Drag one of your (previously created) subforms onto this tab. Click the
next tab, drag/drop the next subform. For each of these subforms, link
Parent/Child on that ID field you mentioned

Repeat until you've place one subform on each tab.

Now, when you first open the form, the query looks at the combo box, sees
nothing, so loads nothing. Since there's no ID field, the subforms load
nothing.

Select a record in the header's combobox. Access requeries the source of
the form (using the choice in the combobox), loads the (single) record, then
loads the subforms belonging to that single record. If you click on various
tabs, you see the subform data associated with that tab and with the main
form record.

Does this give you a way to get done what you are trying to do?

Regards

Jeff Boyce
Microsoft Office/Access MVP


N

 

[Guest]

Jeff,

This will work out. I was attempting to create something a little more
complex for me own benefit. I have done the tab control before and it will
accomplish the task at hand. Thank you for your time and effort to assit me.
In the future I will make sure my questions are more technically sound so
there will not be so much clarification needed.

Thanks again for your help,

Bob

 

[Jeff Boyce]

Bob

I'm not sure about needing any more 'technical soundness'. But not being
there and not being able to see what you see means we'll take a few more
stabs in the dark first!<g>

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Jeff,

I have solved my problem to some degree. I am now able to use my combo box
to make a selection and pop up the form assinged to each selection. I am
also able to bring the ID from the main form over to the pop up form using a
query that queries last record. In my macro I save the record on my main
form then the query allows me to get the ID from a combo box on the pop up
form. Once I select the ID the record locks and will not allow a new record
to be input. If I change the criteria of the query, the query will bring up
every record that is in the table and assign the new ID to every record.
Although it will allow me to input a new record. Can you help me out with
this? Any and all assistance is greatly appreciated.

Regards,

Bob

 

[Jeff Boyce]

I'll suggest that you "start over"... post your (new) question as a new
post. More folks will read a new post than will follow this one all the way
down-thread to this point.

More folks = more eyes & brains considering your question.

Regards

Jeff Boyce
Microsoft Office/Access MVP