Form based on two queries

[Robin Chapple]

I have a membership form based on two queries.

The membership query provides the fixed information about the member
and the history query provides the history of joining and terminating
at various clubs in the league.

The two queries are used in a third query to provide data for a form
with active members by requiring the terminated field to be "null".

When I attempt to add a new member the fields in the history table are
not available. If I enter a "LastName", save the record and return the
fields are available.

I have tried a requery but that does not return me to the new record.
I must be close ???

Thanks,

Robin Chapple

 

[John Vinson]

I have a membership form based on two queries.

The membership query provides the fixed information about the member
and the history query provides the history of joining and terminating
at various clubs in the league.

The two queries are used in a third query to provide data for a form
with active members by requiring the terminated field to be "null".

When I attempt to add a new member the fields in the history table are
not available. If I enter a "LastName", save the record and return the
fields are available.

I have tried a requery but that does not return me to the new record.
I must be close ???

I strongly suspect you'll do better to use a Form for the membership
data, with a Subform for the history data (presumably in a one to many
relationship). Trying to create One Great Master Query is rarely the
best approach!

John W. Vinson[MVP]

 

[Robin Chapple]

Thanks John,

I started with that technique and it failed.

Back to the drawing board.

Robin

 

[John Vinson]

I started with that technique and it failed.

Well... it's the correct technique, and it works fine in thousands of
databases.

If you could post some indication of what you tried (the Recordsources
of the form and subform, and the Master/Child Link Field properties),
and some indication of the nature of the failure, I'm sure we can get
it working for you!

John W. Vinson[MVP]

 

[Robin Chapple]

If you could post some indication of what you tried (the Recordsources
of the form and subform, and the Master/Child Link Field properties),
and some indication of the nature of the failure, I'm sure we can get
it working for you!

I have returned to "Plan A"

I have found one error and corrected it.

I now have a single field sub form based on the history query. It is
a combo box to look up the club name.

It is tab index number four. The next field in the main form is tab
index number five. The cursor tabs into the history field and the next
tab removes the display in the field but stays there. The record is
not destroyed because it returns if I close and return.

I need to tab from the sub form to the next field on the main form.

Thanks,

Robin Chapple

..

 

[Joan Wild]

I need to tab from the sub form to the next field on the main form.

Ctrl + Tab

 

[Robin Chapple]

Thanks Joan,

I did not know that but this will eventually be used by others and I
need to tab down the 20 fields.

I was expecting to learn the code to perhaps put in the "Lost Focus"
property of the 4th tab.

Rather like this advice for tabbing into the sub form:

Me!frmMemberClubHistoryTerminated.SetFocus
Me!frmMemberClubHistoryTerminated!Reason.SetFocus

In this case the main form is "Members" and the sub form is
"MemberClubHistory" without the terminated bit.

 

[John Vinson]

I have found one error and corrected it.

I now have a single field sub form based on the history query. It is
a combo box to look up the club name.

It is tab index number four. The next field in the main form is tab
index number five. The cursor tabs into the history field and the next
tab removes the display in the field but stays there. The record is
not destroyed because it returns if I close and return.

I need to tab from the sub form to the next field on the main form.

A subform *is a form* - the usual assumption is that a Form will
contain the "one" side of a one to many relationship, and that you'll
typically want to add more than one record on the subform (rather than
just a single field in a single record).

You can tab out of a Subform to the next control in the mainform's tab
order with Ctrl-Tab (as Joan says); if you want to automate this, put
an additional unbound textbox on the form, last in the tab order. Its
Visible property must be true for it to get the focus, but you can
make it Transparent and one twip on a side so the user can't
accidentally select it. In its GotFocus event put code

Private Sub txtRelay_GotFocus()
Parent.SetFocus
Parent!ControlOfYourChoice.SetFocus
End Sub

John W. Vinson[MVP]

 

[Robin Chapple]

Thanks John,

The table on which the subform(1) is based has several fields.

The field that is involved in this question needs to be in tab order
number four for cosmetic reasons, that is to emulate the current paper
document that is prepared.

Another field, [DateJoinedThisClub] needs to be in tab order
position number 20 and will be subform(2).

Does that change the advice?

The other field are:
ClubName
DateJoinedThisClub
DateTerminated

On reflection, if it is significant, I could have [ClubName] and
[DateJoinedThisClub] on the same subform.

Robin Chapple

 

[John Vinson]

Thanks John,

The table on which the subform(1) is based has several fields.

The field that is involved in this question needs to be in tab order
number four for cosmetic reasons, that is to emulate the current paper
document that is prepared.

Another field, [DateJoinedThisClub] needs to be in tab order
position number 20 and will be subform(2).

Does that change the advice?

The other field are:
ClubName
DateJoinedThisClub
DateTerminated

On reflection, if it is significant, I could have [ClubName] and
[DateJoinedThisClub] on the same subform.

I'm REALLY completely confused then.

You say "Another field, [DateJoinedThisClub] needs to be in tab order
position number 20 and will be subform(2).". It sounds like you're
using a Subform to enter a single field, and that you want this field
entered in the midst of the fields on another subform.

I think this may be a very good example of why using a paper form as a
template for an Access database user interface is fraught with
problems! I even fear that you've used the paper form to design your
tables, though it's very hard to tell.

In short - no. I don't understand your table structure, and I don't
understand your form structure. My advice was posited on the
assumption that you're using a form and a subform in a
database-logical manner, i.e. that you have a Form based on the "one"
side table, and a Subform based on a different table in a one to many
relationship with this first table; and that you want to *ordinarily*
enter only one (of many possible) records on the subform. It appears
that this is not the case... but I just don't understand what your
case is.

What are your Tables?
How are they related?
What are the recordsources of your Form, and your subforms?

John W. Vinson[MVP]

 

[Robin Chapple]

John,

The main form is [frmMembers] based on [tblmembers]. It keeps all
information that is dedicated to the member. Name, address, phone
numbers and so on.

The [tblHistory] has a members history as he joins each club, if he is
a founder member and when he terminates.

[MemberID] is the joining field.

I don't need the [DateTerminated] field on the form when he is a new
member. It is not a hardship to have [ClubName] and
[DateJoinedThisClub] on one sub form.

Emulating an existing form is an advantage when dealing with members
who are looking for excuses to stay with pencil and paper. If it is
thwart with problems and hardship I will have to follow that path.

Thanks John,

The table on which the subform(1) is based has several fields.

The field that is involved in this question needs to be in tab order
number four for cosmetic reasons, that is to emulate the current paper
document that is prepared.

Another field, [DateJoinedThisClub] needs to be in tab order
position number 20 and will be subform(2).

Does that change the advice?

The other field are:
ClubName
DateJoinedThisClub
DateTerminated

On reflection, if it is significant, I could have [ClubName] and
[DateJoinedThisClub] on the same subform.

I'm REALLY completely confused then.

You say "Another field, [DateJoinedThisClub] needs to be in tab order
position number 20 and will be subform(2).". It sounds like you're
using a Subform to enter a single field, and that you want this field
entered in the midst of the fields on another subform.

I think this may be a very good example of why using a paper form as a
template for an Access database user interface is fraught with
problems! I even fear that you've used the paper form to design your
tables, though it's very hard to tell.

In short - no. I don't understand your table structure, and I don't
understand your form structure. My advice was posited on the
assumption that you're using a form and a subform in a
database-logical manner, i.e. that you have a Form based on the "one"
side table, and a Subform based on a different table in a one to many
relationship with this first table; and that you want to *ordinarily*
enter only one (of many possible) records on the subform. It appears
that this is not the case... but I just don't understand what your
case is.

What are your Tables?
How are they related?
What are the recordsources of your Form, and your subforms?

John W. Vinson[MVP]

 

[John Vinson]

John,

The main form is [frmMembers] based on [tblmembers]. It keeps all
information that is dedicated to the member. Name, address, phone
numbers and so on.

The [tblHistory] has a members history as he joins each club, if he is
a founder member and when he terminates.

[MemberID] is the joining field.

I don't need the [DateTerminated] field on the form when he is a new
member. It is not a hardship to have [ClubName] and
[DateJoinedThisClub] on one sub form.

I guess I'm still perplexed. You can have ClubName as a combo box
(storing the unique club ID but displaying the club name), and it
appears that it would make sense to have this as a control on the
History subform; and the same subform would logically have the
DateJoined and DateTerminated fields (the latter being left blank if
it's not a current member).

Emulating an existing form is an advantage when dealing with members
who are looking for excuses to stay with pencil and paper. If it is
thwart with problems and hardship I will have to follow that path.

Very familiar with the problem... "new is evil". :-{(

IME the best solution is to find and nurture an advocate - someone who
can be convinced that the new, automated method is BETTER and easier
than paper. Let *them* convince the other members (as the developer,
you unfortunately have less credibility than another volunteer would).

If it's really vital to jump into and out of a subform, you can - just
sprinkle unbound transparent 1-pixel square textboxes through the tab
order, and have code in their GotFocus events to set the focus to some
other control. Good luck!

John W. Vinson[MVP]