new records in form and sub form

[Guest]

I have created a form with 8 subforms , when i click new record , i want the
data from form 1 and sub form 1 to appear in other sub forms . at the moment
only the customer id appears i also want date and order number

 

[Guest]

Use the After Update event of the main form. Test to see if it is a new
record and if it is, load the data from the controls on the main form:

If Me.NewRecord Then
Me.subForm1.OrderNum = Me.OrderNum
Me.subForm2.OrderNum = Me.OrderNum
Me.subForm1.OrderDate = Me.OrderDate
Me.subForm2.OrderDate = Me.OrderDate
End If

 

[John Vinson]

I have created a form with 8 subforms , when i click new record , i want the
data from form 1 and sub form 1 to appear in other sub forms . at the moment
only the customer id appears i also want date and order number

Klatuu's answer is correct - but I do have a concern. Creating empty
"placeholder" records, with only the ID and date, is almost NEVER a
good idea; such empty records have a way of never getting filled in.

It sounds like you're trying to store data in advance of having real
data (a bad idea) or to store data redundantly in eight tables (an
even worse idea). Are you sure you really need to do this? What tables
are these subforms bound to?

John W. Vinson[MVP]

 

[Guest]

I totally agree with you, John. My first thought when I read this post was
"Bad Database Design!", but today I am feeling frustrated and did not feel
like reading a return post on why this is a special case and I really have to
do it this way.

Now, in defense of the poster, I can understand why sometimes you have to
grin and bear it and do things you know are wrong. In my case, I am
supporting the world's worst database design (I will take any challanges on
that, no related tables, some date carried in as many as 10 tables). My user
nods and agrees when I beg to be allowed to fix it.
"Yes, we will do that some day, but first I need 12 more reports."
"Okay, but every thing I do will be throw away because all the data
structures will change and I will have to redo it."
Sweet smile, "I understand, but my customers really want these reports."

Yes, Virginia, there is an Access Hell.

 

[Guest]

i am still learning a selftaught learning as i go along but the client i
am doing database for wants 8 reports with so many fields and drop down
combos a query cant cope in am like a dog with a bone digging at it to find
the way. as i said once ive entered the customer address etc i just need the
subforms to relate to date employee surveyordernumber and customer id which
at the moment the subforms show customer id . but ask when trying to enter
other info for employee id and they dont show surveyorderid. unless i go to
previous record then every thing works fine. sorry if i seem a novice .
vanessa

 

[John Vinson]

Sweet smile, "I understand, but my customers really want these reports."

Yes, Virginia, there is an Access Hell.

<wry grin> Yep... bt,dt,gtts. And using the t-shirt for a car washing
rag.

John W. Vinson[MVP]

 

[Guest]

Nothing wrong with being a novice, Vanessa. I apologize if I offended you.
You probably inherited a poorly designed database.
As to a query not being able to cope with all the fields, this probably is
not true.

First, here is a link that will show you how to Add (All) to your combo
boxes so that if nothing is chosen, not selection will be made on the
corresponding field:

http://www.mvps.org/access/forms/frm0043.htm

Now, for each field in your query that has a combo box, you need to put
something like this in the criteria row to do your filtering:

Like Iif(Me.cboFirstCombo = "(ALL)", "*", Me.cboFirstCombo)

With this technique, you wont have to have redundant data floating around
bloating your database.

Post back if you have more questions.

 

[John Vinson]

i am still learning a selftaught learning as i go along but the client i
am doing database for wants 8 reports with so many fields and drop down
combos a query cant cope in am like a dog with a bone digging at it to find
the way. as i said once ive entered the customer address etc i just need the
subforms to relate to date employee surveyordernumber and customer id which
at the moment the subforms show customer id . but ask when trying to enter
other info for employee id and they dont show surveyorderid. unless i go to
previous record then every thing works fine. sorry if i seem a novice .
vanessa

You haven't described your table structures, so I'm groping in the
dark somewhat. By "So many fields" do you mean that you're trying to
get all of the information for a Report into a single record of one
table? If so you're on the wrong track; that's neither necessary nor
(sometimes) even possible. The Report should be based on a multitable
query, or you may want a Report with Subreports. Again... not knowing
what's in your table I can't give specific advice!

John W. Vinson[MVP]

 

[Guest]

I have tried this not any joy so far
The main form is related to a query add new customer , the subform are
related to survey, location query and table survey . dont know if this
changes things.

 

[Guest]

i have built a survey database which has one main table Survey, customers
and employees, these tables are related. then because there are so many
fields and drop down boxes i had to create 8 reports related to survey table
.. The forms are 8 because of so much detail i have created main form add
customer then the sub forms are all tab subforms . but form i customer is
customer details , then subform 2 has the date and surveyorder number and
employee details, other subforms just have alot of info but have to relate
and have same date employee customer and surveyorder number. I think that
covers it.
thanks

 

[John Vinson]

i have built a survey database which has one main table Survey, customers
and employees, these tables are related. then because there are so many
fields and drop down boxes i had to create 8 reports related to survey table
. The forms are 8 because of so much detail i have created main form add
customer then the sub forms are all tab subforms . but form i customer is
customer details , then subform 2 has the date and surveyorder number and
employee details, other subforms just have alot of info but have to relate
and have same date employee customer and surveyorder number. I think that
covers it.

Ok... you've made THE most common mistake for survey type data:
adding one field per question. This design is simply WRONG and will
cause you endless trouble. It sounds like (as is also very common) you
designed your table structures to fit the desired report. That's
backwards! You should design your table structures relationally (as
tall-thin tables), and THEN design the Report to print out that data.

Your Survey may benefit from considering Duane Hookum's excellent "At
Your Survey" sample database:

http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='At Your Survey 2000'

This uses one *record* per question, rather than one *field* per
question.

If you're stuck with (or committed to) the incorrectly normalized
table structure, please do as I have requested SEVERAL times: this
time I'll try to be as specific as possible.

Open your Form. View its Properties. On the Data tab the first line is
"Recordsource". Post the value of the Recordsource property.

View the properties of the first Subform. If its Recordsource is a
Query, please open that Query in SQL view. Copy and paste the SQL text
to a message here. Do the same for the third (first of the Survey
data??) Subform. (No need to do all 8 subforms unless they are
dramatically different in structure; two is enough).

Also please post the values of the Master Link Field and Child Link
Field properties of the two Subform controls.

Remember - YOU can see your database. You know its structure and how
its forms are set up. We can't.

John W. Vinson[MVP]