Switch between sub forms

[Furquhart via AccessMonster.com]

Access 2007

I want to use VBA to switch between two subforms on the same main form. Both
subforms are open.

Frm_Master
Frm_Heat_Sub (open active)
Frm_PrizeAttach_sub (open but not active)

My code:

Dim stHeat As String
Dim stDocName As String
Dim stHeatID As String

stDocName = "Qry_AddNewPrizeMatchRecord"
stHeat = Forms!Frm_Master!Frm_Heat_Sub!HeatName

*** Problem area

Forms!Frm_Master!Frm_PrizeAttach_Sub!position.SetFocus
DoCmd.SelectObject acForm, "Forms!Frm_Master!Frm_PrizeAttach_Sub", False

Error: I get an error that the form I want to switch to is not open, but it
is.

*** End problem area

DoCmd.SetWarnings False
DoCmd.OpenQuery stDocName (append qry)
DoCmd.SetWarnings True

Any help?

Thanks

Fred

 

[Marshall Barton]

Access 2007

I want to use VBA to switch between two subforms on the same main form. Both
subforms are open.

Frm_Master
Frm_Heat_Sub (open active)
Frm_PrizeAttach_sub (open but not active)

My code:

Dim stHeat As String
Dim stDocName As String
Dim stHeatID As String

stDocName = "Qry_AddNewPrizeMatchRecord"
stHeat = Forms!Frm_Master!Frm_Heat_Sub!HeatName

*** Problem area

Forms!Frm_Master!Frm_PrizeAttach_Sub!position.SetFocus
DoCmd.SelectObject acForm, "Forms!Frm_Master!Frm_PrizeAttach_Sub", False

Error: I get an error that the form I want to switch to is not open, but it
is.

*** End problem area

Subforms are not opened, they are just displayed by the
containing subform control.

Making a form active is just a matter of moving the focus to
the subform control and then to a control in the subform.
From code in the main form:

Me.subformcontrol2.SetFocus
Me.subformcontrol2.Form.somecontrol.SetFocus

Note that a subform control and the form object it displays
may or may not have the same name.

 

[DStegon via AccessMonster.com]

What do you mean ...
"Switch between" ????
Are they both visible?
Do you want to switch the focus and if so why?

Need more to go on about "what" you are really trying to do.

 

[Furquhart via AccessMonster.com]

This is a fund raising, duck race, database for a not-for-profit. Hundreds of
people attend and tens of thousands of $ are raised for Hospice of
Northeastern Illinois.

The purpose of this portion of the database is to associate the donated
prizes with specific heats of the race. The Heat table and form is pre-
populated with the number of heats, Heat1, Heat2, Heat3 and so on. The prizes,
several hundred, are donated by various businesses. The prizeID is a PK and
the description is the nature of the prize such as "1 large pizza from
Pizzahut in Elgin".

You select the Heat you want to build and you click the button and the heatID
is copied over to the PrizeAttach table and form. Then you click on a
particular prize and the description is copied over to the PrizeAttach table
in the same record as the Heat you just entered.

My skill level with data architecture is high. The database is fully
normalized. My skill level with VB, however, is quite limited. There are
probably much more sophisticated ways to accomplish this and I am most open
to suggestions but please keep in mind my experience level with VB. I am also
new to these forums.

Three sub forms on the master form. All the forms are visible all the time.

PrizeAttach Heat Prizes

PrizeAttach PK HeatIDPK PrizeIDPK
HeatFK PrizeDescription
Prize Description

<---------- <---------
Build Heat Move Heat Move Description


What I want to do.

Step 1:

I want to click on the Heat form, selecting the heat I want to build. Then I
click on a command button and move over to the PrizeAttach form, create a new
PrizeAttach record, and paste the HeatID into the HeatFK field.

Step 2:

Then I want to click on the Prize form selecting the prize that I want to
associate with the heat I just entered in step 1. Click a command button and
it will copy the prize description, select the PrizeAttach form, and paste
the prize into the PrizeAttach record I just created and then refresh the
screen so the data shows.

I can accomplish this with an append query that runs from VB but there is
probably an easier way.

In the end, all the prizes witll be attached to the appropriate heats prior
to the race. Then at the race the prizes will be matched with the (duckID PK
to the whole database). (This portion is not a part of my question).

I hope this clarifies sufficiently.

Thanks

Fred

What do you mean ...
"Switch between" ????
Are they both visible?
Do you want to switch the focus and if so why?

Need more to go on about "what" you are really trying to do.

Access 2007

[quoted text clipped - 33 lines]

Fred

 

[DStegon via AccessMonster.com]

Why not have one form? Put a list box of ComboBox on the form that has the
Heat. Put code in the Not In List Event that adds the record to the Heat
Table and requeries the combobox.

Have a second list box that lists the Prizes (especially if more than one
prize can be for one heat (one to many). You could then select the prize(s)
and have a command button to create the Prize Attach entry.

One thing... Why would you use the Prize Desc in the PrizeAttach table and
not the primary key of the Prize?? Never want to save text twice. Storing
the number (PK) of the prize is far faster and takes up far less space. You
can report on the descript by query innner joining the PrizeAttch table to
the Prizes table. One of the reason you definately want to do this is if you
EDIT the Prize Desc in the Prizes table the edit would follow right through
to the PrizeAttach table and any reporting done on that data. Your way would
have any EDIT to the Prizes table also having someone needing to EDIT the
Attach table to make them duplicate... that is why you want it normalized.

You could also put a third list box on the form that would show the current
prizes for any heat when you select the heat from the combo box/listbox. Upon
selection, have the event populate the PrizeAttach listbox to show all
Current prizes for the heat. This gives you the ability to add and also
delete (you could put a command button that code could delete an entry from
the PrizeAttach table if it was incorrectly input.

Three sub forms on the master form. All the forms are visible all the time.

PrizeAttach Heat Prizes

PrizeAttach PK HeatIDPK PrizeIDPK
HeatFK PrizeDescription
Prize Description

<---------- <---------
Build Heat Move Heat Move Description

What I want to do.

Step 1:

I want to click on the Heat form, selecting the heat I want to build. Then I
click on a command button and move over to the PrizeAttach form, create a new
PrizeAttach record, and paste the HeatID into the HeatFK field.

Step 2:

Then I want to click on the Prize form selecting the prize that I want to
associate with the heat I just entered in step 1. Click a command button and
it will copy the prize description, select the PrizeAttach form, and paste
the prize into the PrizeAttach record I just created and then refresh the
screen so the data shows.

I can accomplish this with an append query that runs from VB but there is
probably an easier way.

In the end, all the prizes witll be attached to the appropriate heats prior
to the race. Then at the race the prizes will be matched with the (duckID PK
to the whole database). (This portion is not a part of my question).

I hope this clarifies sufficiently.

Thanks

Fred

What do you mean ...
"Switch between" ????

[quoted text clipped - 8 lines]