One query to two tables (?)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi!

I have a Form and a Subform, made for data entry.
The Form has common fields to all the records. The Subform has different
fields, consonant diferents subjects.
I would like to know if with one query, running trought a button in form, I
can send the data for two different tables: Table of the commun data fields -
in form - and the data of the fields of subform for another one.

Thanks in advance
an
 
A single form / subform can be used for data entry in multiple tables.
--
***************************
If the message was helpful to you, click Yes next to Was this post helpful
to you?
If the post answers your question, click Yes next to Did this post answer
the question?
 
SP,
Thanks for your reply.

Before my post already I tried with a query but don't work fine.
What condition I need to put in query to add new data in both tables, please.
Thanks.
an
 
Hi!

I have a Form and a Subform, made for data entry.
The Form has common fields to all the records. The Subform has different
fields, consonant diferents subjects.
I would like to know if with one query, running trought a button in form, I
can send the data for two different tables: Table of the commun data fields -
in form - and the data of the fields of subform for another one.

Thanks in advance
an
Click to expand...

If you have a normal bound form, it will update both tables directly,
and it is NOT necessary to have or to run a Query!

Could you explain *why* you need the Query?

To directly answer the question: you may be able to create a Query
including ALL of the fields from both tables - it is especially
important to include the linking field from both tables. Create
another Append query and append *to this join query*.

However, as I suggest, this should not be necessary - there are easier
ways to get the same result.

John W. Vinson[MVP]
 
JV,

Many thanks for your reply and sorry for my delay.
Both Tables are linked trought one field one to many.

I thought about a Append query because the Form and SubForm are unbound
because who to introduce news records doesn't know the data existing.
If it will have solution better, I very am thankful.

an
 
JV,

Many thanks for your reply and sorry for my delay.
Both Tables are linked trought one field one to many.

I thought about a Append query because the Form and SubForm are unbound
because who to introduce news records doesn't know the data existing.
If it will have solution better, I very am thankful.
Click to expand...

Using unbound forms is sometimes necessary, but it is certainly an
advanced technique requiring a lot of extra programming.

I do not understand what you mean by "introduce new (?) records
doesn't know the data existing". Normally one would enter a new record
on the "one" side using the mainform, or find an existing record (for
example using an unbound combo box on the mainform to look up a
record); then enter the related records in the subform. You can also
use the main form's BeforeUpdate event to warn the user if there is
already an existing record with the entered values.

John W. Vinson[MVP]
 
JV,

Excuse me for my I ask not having been explicit.
I intend to insert in new record through one form and sub form - both
unbound - so that user that it does not know of the existing data. From there
that they are unbound.
For other words: that the new inserted data in form - unbound - go for the
table of common data; e that the inserted data in sub form - unbound too - go
for the another table of the respective subject.
Thanks.
an
 
JV,

Excuse me for my I ask not having been explicit.
I intend to insert in new record through one form and sub form - both
unbound - so that user that it does not know of the existing data. From there
that they are unbound.
For other words: that the new inserted data in form - unbound - go for the
table of common data; e that the inserted data in sub form - unbound too - go
for the another table of the respective subject.
Thanks.
Click to expand...

Ok... it's STILL not necessary to use unbound forms for this purpose.
You can instead set the Data Entry property of the form and of the
subform to True; it will display only the blank record and whatever
that user entered during that session, not any previous records.

But if you really want to do it the hard way, you can. Use the Form's
AfterUpdate event with code like:

Private Sub Form_AfterUpdate()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("[NameOfYourTable]", dbOpenDynaset)
rs.AddNew
rs!FieldA = Me!txtFieldA
rs!FieldB = Me!txtFieldB
<and so on>
rs.Update
rs.Close
Set rs = Nothing
End Sub

Put this code into both forms, using the appropriate tablename and
field names and control names.

John W. Vinson[MVP]
 
JV,

Thank you for your help.
an

John Vinson said:
JV,

Excuse me for my I ask not having been explicit.
I intend to insert in new record through one form and sub form - both
unbound - so that user that it does not know of the existing data. From there
that they are unbound.
For other words: that the new inserted data in form - unbound - go for the
table of common data; e that the inserted data in sub form - unbound too - go
for the another table of the respective subject.
Thanks.
Click to expand...

Ok... it's STILL not necessary to use unbound forms for this purpose.
You can instead set the Data Entry property of the form and of the
subform to True; it will display only the blank record and whatever
that user entered during that session, not any previous records.

But if you really want to do it the hard way, you can. Use the Form's
AfterUpdate event with code like:

Private Sub Form_AfterUpdate()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("[NameOfYourTable]", dbOpenDynaset)
rs.AddNew
rs!FieldA = Me!txtFieldA
rs!FieldB = Me!txtFieldB
<and so on>
rs.Update
rs.Close
Set rs = Nothing
End Sub

Put this code into both forms, using the appropriate tablename and
field names and control names.

John W. Vinson[MVP]
Click to expand...
 
JV I am having somewhat of the same issue with my forms.
Specifically, the data I enter on top (main form) of my form and data on my
subform (bottom) gets treated as two recordsource. From a recordsource point
of view, it seems like two different records. Any idea how i can fix this?

Thanks,
--
Gecatoni


John Vinson said:
JV,

Excuse me for my I ask not having been explicit.
I intend to insert in new record through one form and sub form - both
unbound - so that user that it does not know of the existing data. From there
that they are unbound.
For other words: that the new inserted data in form - unbound - go for the
table of common data; e that the inserted data in sub form - unbound too - go
for the another table of the respective subject.
Thanks.
Click to expand...

Ok... it's STILL not necessary to use unbound forms for this purpose.
You can instead set the Data Entry property of the form and of the
subform to True; it will display only the blank record and whatever
that user entered during that session, not any previous records.

But if you really want to do it the hard way, you can. Use the Form's
AfterUpdate event with code like:

Private Sub Form_AfterUpdate()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("[NameOfYourTable]", dbOpenDynaset)
rs.AddNew
rs!FieldA = Me!txtFieldA
rs!FieldB = Me!txtFieldB
<and so on>
rs.Update
rs.Close
Set rs = Nothing
End Sub

Put this code into both forms, using the appropriate tablename and
field names and control names.

John W. Vinson[MVP]
Click to expand...
 
Sorry.
You wrote for an.
It suggested that write for JV, with click in the line of the reply of it.
Good luck.
an

gecatoni said:
JV I am having somewhat of the same issue with my forms.
Specifically, the data I enter on top (main form) of my form and data on my
subform (bottom) gets treated as two recordsource. From a recordsource point
of view, it seems like two different records. Any idea how i can fix this?

Thanks,
--
Gecatoni


John Vinson said:
JV,

Excuse me for my I ask not having been explicit.
I intend to insert in new record through one form and sub form - both
unbound - so that user that it does not know of the existing data. From there
that they are unbound.
For other words: that the new inserted data in form - unbound - go for the
table of common data; e that the inserted data in sub form - unbound too - go
for the another table of the respective subject.
Thanks.
Click to expand...

Ok... it's STILL not necessary to use unbound forms for this purpose.
You can instead set the Data Entry property of the form and of the
subform to True; it will display only the blank record and whatever
that user entered during that session, not any previous records.

But if you really want to do it the hard way, you can. Use the Form's
AfterUpdate event with code like:

Private Sub Form_AfterUpdate()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("[NameOfYourTable]", dbOpenDynaset)
rs.AddNew
rs!FieldA = Me!txtFieldA
rs!FieldB = Me!txtFieldB
<and so on>
rs.Update
rs.Close
Set rs = Nothing
End Sub

Put this code into both forms, using the appropriate tablename and
field names and control names.

John W. Vinson[MVP]
Click to expand...
Click to expand...
 
Back
Top