returning to a new record in main form from subform

[Bill]

Sorry but I program in Access so infrequently that I must refresh my memory
on basic fucntion/proceedures/design.

First, what is the maximum number of nested subforms?

Secondly and most importantly, I'd like to move to the next record on the
main form after adding/updating a record on a sub/sub form. How to?

I've fooled with gotocontrol and setfocus but I don't know if I'm getting
errors because my syntax is incorrect or these methods I'm using are not
appropriate.

Furthermore, I question the basic design of my db. I have hundreds of
records of "leads" in tblmain, which I have either collecte myself or my
telemarketer has provided . I have contacted only a portion of them for which
I intend to input the contat data collected into the table tblRan. Of those
only a handful are sales, for which I store the data in the table tblSales.
The relationship of these tables are nested One-to Many although there is
only one record for each sale in tblSale and one record of the contact
information in tblRan. It appears to me the only logical relationship of
these tables would be One-to-many but the reality is there are never many
related records but only one.

Am I thinking correctly on this issue by using one-to-many relationships
although I know there are only one relate record in each of the nested tables?

ie, one sales record for each ran record and only one ran record for each
record in the main table. There are hundreds of records in the main table
without any associated records in the related tables.

Bill

 

[tina]

comments inline.

Sorry but I program in Access so infrequently that I must refresh my memory
on basic fucntion/proceedures/design.

First, what is the maximum number of nested subforms?

i don't recall, but you might find the info by looking up "specifications"
in Help.

Secondly and most importantly, I'd like to move to the next record on the
main form after adding/updating a record on a sub/sub form. How to?

I've fooled with gotocontrol and setfocus but I don't know if I'm getting
errors because my syntax is incorrect or these methods I'm using are not
appropriate.

post your code, including what form or subform you're running the code from
and what event of what object.

Furthermore, I question the basic design of my db. I have hundreds of
records of "leads" in tblmain, which I have either collecte myself or my
telemarketer has provided . I have contacted only a portion of them for which
I intend to input the contat data collected into the table tblRan. Of those
only a handful are sales, for which I store the data in the table tblSales.
The relationship of these tables are nested One-to Many although there is
only one record for each sale in tblSale and one record of the contact
information in tblRan. It appears to me the only logical relationship of
these tables would be One-to-many but the reality is there are never many
related records but only one.

Am I thinking correctly on this issue by using one-to-many relationships
although I know there are only one relate record in each of the nested tables?

ie, one sales record for each ran record and only one ran record for each
record in the main table. There are hundreds of records in the main table
without any associated records in the related tables.

well, if you're *absolutely sure* that one lead will only generate one conta
ct, and one contact will only generate one sale, then each child record is
actually a subset of the parent record. you can use a one-to-one
relationship between the tables, rather than one-to-many. that means that
the primary key field of tblLeads will be directly linked to the primary key
field of tblRan, which will be directly linked to the primary key field of
tblSales - rather than each child table having a separate primary key field
and a foreign key field to link to the parent table. that's a legitimate
tables design in circumstances like these.

in practical terms, i don't see it being that big a difference, as opposed
to using a one-to-many setup. and creating a one-to-one link between tables
will severely limit the flexibility of your design: if you encounter
circumstances where a single lead is "run" more than once, or a single run
results in more than one sale, at some point in the future, then you're
stuck - your database won't support the data.

hth

 

[Klatuu]

The structure for your database would be better if:
You have one table that contains Contact Information. Then in that table,
have a status field that would be:
Lead
Ran
Sold

Then you can use that field as a flter when you want to look up for each
status.

If it is possible ( I doubt it, because I have designed databases for
telemarketing before) that a Contact could have more than one run, then you
would need 2 tables.

ContactInfo
ContactLead

ContactInfo would contain all the demographic info, and Lead would contain
all the Lead Info (called, ran, sold, cold, etc)

To get really sophisticated, you could have a table of Telemarketers, a
table of Salesmen, a table of Installers.

Then, ContactLead could be related to the additional tables so you would
know who called, when they called, the result (No Answer, Left Voice Mail,
Left Message, Appointment set, etc), Who ran the lead and the result of the
appointment (Not Home, Canceled, Presented, Sold, etc) And, if Sold, who did
the install, etc.

 

[Klatuu]

BTW, If you are Dr Modo, post back, I know you.

 

[David C. Holley]

You'll want to play with GoToRecord

 

[Bill]

Thanks,

That makes me feel better about my design. However, I still don't know how
to move to a new main record after I add a record in the 2 deep nested sub
form.

One would think I need to setfocus on the main form then do a goto record
but my syntax must be incorrect cause it's not working.

The forms are frmMain (mainform), frmBC (subform), frmRan (subsubform). The
last control I update is RanNotes in the newly added record of frmRan.

 

[Bill]

Sorry Tina, I forgot to respond to your question concerning events. The event
I'd like to use is the onclick of a button (Go To New Main Record) at the
bottom of the subsubform.

 

[David C. Holley]

I believe that the GoToRecord has a parameter specifiying the form which
should recieve the action.

 

[tina]

i built a main/sub/subsub forms setup to see if i could figure something
out, and came up with this: on the subsubform command button's Click event,
i added the following, as

Me.Parent.Parent!SomeControlName.SetFocus
With Me.Parent.Parent
DoCmd.RunCommand acCmdRecordsGoToNew
End With

it worked without error in my A2000 db running in A2003. when the new record
became current, the focus was on the main form, and on the first control in
the tab order - not on the control i initially set focus to. just as an
extra test, i added a second SetFocus action inside the With statement,
after the DoCmd action, and was able to move the focus to another control
without error.

hth