Conecting table form and sub form recordfsets

[bceo]

I have had some experience working with Access 2003, but only with simple
standalone databases, which I designed using the Access wizards. I am now
working on a database that uses sub forms. I have the subform which opens
up when I needed it, this is done via a command button which I placed on the
Main Form. I have linked, through the relationships window, the IncidentNo
(auto number) to the same field on the sub form Involved. I do not always
need to use the Involved sub form, when I am entering into the main form. I
need to connect the Involved record to the IncidentNo recordset for this
Icident number. In this way when I print the main form the record of the
Involved shows up for that Incident number only and not the entire involved
sub form records (this is what it is doing now). Also if there is no Involved
sub form used for the Incident Number, no Involved sub report shows when I
print the Main Form for the that Incident.

 

[KARL DEWEY]

I read this several times but do not see a question. What did I miss?

 

[bceo via AccessMonster.com]

I read this several times but do not see a question. What did I miss?

I have had some experience working with Access 2003, but only with simple
standalone databases, which I designed using the Access wizards. I am now

[quoted text clipped - 9 lines]

sub form used for the Incident Number, no Involved sub report shows when I
print the Main Form for the that Incident.

Sorry about that, I am new to using discussion forms. My question is how do
I connect the subform and the mainform so that when I print a single
Incident record, from the main form, using a print command button connected
to a report, it prints the individual Incident record (from the Incident
table) and the subform record (from the subform table) that relates to that
particular Incident, if there is one, if not, it prints only the Incident
record? Right now when I click the print command button, the report prints
all the records in the both tables, which is not want I want. I hope this is
clearer, and thank you for responding I really do appreciate it.

 

[Philip Herlihy]

I read this several times but do not see a question. What did I miss?

I have had some experience working with Access 2003, but only with simple
standalone databases, which I designed using the Access wizards. I am now

[quoted text clipped - 9 lines]

sub form used for the Incident Number, no Involved sub report shows when I
print the Main Form for the that Incident.

Sorry about that, I am new to using discussion forms. My question is how do
I connect the subform and the mainform so that when I print a single
Incident record, from the main form, using a print command button connected
to a report, it prints the individual Incident record (from the Incident
table) and the subform record (from the subform table) that relates to that
particular Incident, if there is one, if not, it prints only the Incident
record? Right now when I click the print command button, the report prints
all the records in the both tables, which is not want I want. I hope this is
clearer, and thank you for responding I really do appreciate it.

That's a lot clearer! A lot of people will only reply if they feel they
have something to contribute, so a clear questions really helps you most.

You haven't given us much detail of how you've set this up, but if
you've used the wizard associated with a command button you've probably
ended up with a small VBA function associated with the OnClick event
(see properties). When I added a command button to a form while
thinking about this, I ended up with this function:

==================================
Private Sub Command499_Click()
On Error GoTo Err_Command499_Click

Dim stDocName As String

stDocName = "rpt_Print_Invoice"
DoCmd.OpenReport stDocName, acNormal

Exit_Command499_Click:
Exit Sub

Err_Command499_Click:
MsgBox Err.Description
Resume Exit_Command499_Click

End Sub
==================================

Note the line starting DoCmd.OpenReport

If you click on "OpenReport" and hit F1, the Help kicks in and shows
that you can add an option condition to this line, either as the name of
a saved query or as a plain WHERE condition (without the WHERE keyword).
One of those should provide the selection you need.

Tip: if you want to learn how to refer to various bits and pieces in
your database try fooling around with the Expression Builder, or see
this site:
http://www.mvps.org/access/forms/frm0031.htm
You'll need to figure out how to refer to the selected record - I can't
tell you how to do this without knowing more about your form structure
(although more expert commentators may well know a standard way of doing
this - I'm only an interested amateur!). Options include having the
query (on which your report is based) refer to the value of a control on
your form, or applying filters to the report.

HTH

Phil, London

 

[tina]

presumably your Incident table has a primary key field, and that field is
included in the form's RecordSource. for example purposes, let's say the
field's name is IncidentID. you need to limit the report's output to the
record with the IncidentID value of the currently selected record in your
form.

you don't say whether the Print command button is running VBA code or a
macro. if it's a macro, then open the macro in Design view, click on the
OpenReport action to select that line, and look at the Where Condition space
in the Action Arguments section in the bottom half of the macro Design
window. you'll need to enter a Where condition, for example:

[IncidentID] = [Forms]![MyFormName]![IncidentID]

replace MyFormName with the correct name of the form. and press F1 in that
field, which will open Access Help at the OpenReport Action topic; read up
on the action, so you'll understand how the arguments work.

if the command button is running VBA code instead, then you'll need to add
the WHERE condition to the code, as

DoCmd.OpenReport "MyReportName", , , "IncidentID = " & Me!IncidentID

again, replace MyReportName with the correct name of the report. the above
code assumes that the IncidentID field has a Number data type. if the
field's data type is Text, then use the syntax below instead for the WHERE
condition, as

"IncidentID = '" & Me!IncidentID & "'"

and again, you can click on the word OpenReport, then press F1, to go to the
appropriate Help topic.

and, of course, in both the macro and VBA examples above, you'd need to
replace IncidentID with the correct name of the primary key field in your
Incidents table.

hth

I read this several times but do not see a question. What did I miss?

I have had some experience working with Access 2003, but only with simple
standalone databases, which I designed using the Access wizards. I am

now
[quoted text clipped - 9 lines]

sub form used for the Incident Number, no Involved sub report shows when I
print the Main Form for the that Incident.

Sorry about that, I am new to using discussion forms. My question is how do
I connect the subform and the mainform so that when I print a single
Incident record, from the main form, using a print command button connected
to a report, it prints the individual Incident record (from the Incident
table) and the subform record (from the subform table) that relates to that
particular Incident, if there is one, if not, it prints only the Incident
record? Right now when I click the print command button, the report prints
all the records in the both tables, which is not want I want. I hope this is
clearer, and thank you for responding I really do appreciate it.

 

[bceo via AccessMonster.com]

presumably your Incident table has a primary key field, and that field is
included in the form's RecordSource. for example purposes, let's say the
field's name is IncidentID. you need to limit the report's output to the
record with the IncidentID value of the currently selected record in your
form.

you don't say whether the Print command button is running VBA code or a
macro. if it's a macro, then open the macro in Design view, click on the
OpenReport action to select that line, and look at the Where Condition space
in the Action Arguments section in the bottom half of the macro Design
window. you'll need to enter a Where condition, for example:

[IncidentID] = [Forms]![MyFormName]![IncidentID]

replace MyFormName with the correct name of the form. and press F1 in that
field, which will open Access Help at the OpenReport Action topic; read up
on the action, so you'll understand how the arguments work.

if the command button is running VBA code instead, then you'll need to add
the WHERE condition to the code, as

DoCmd.OpenReport "MyReportName", , , "IncidentID = " & Me!IncidentID

again, replace MyReportName with the correct name of the report. the above
code assumes that the IncidentID field has a Number data type. if the
field's data type is Text, then use the syntax below instead for the WHERE
condition, as

"IncidentID = '" & Me!IncidentID & "'"

and again, you can click on the word OpenReport, then press F1, to go to the
appropriate Help topic.

and, of course, in both the macro and VBA examples above, you'd need to
replace IncidentID with the correct name of the primary key field in your
Incidents table.

hth
[quoted text clipped - 12 lines]

all the records in the both tables, which is not want I want. I hope this is
clearer, and thank you for responding I really do appreciate it.

Thank you very much for the reply, I will try these suggestions and I will
reply back if I was successful or not. At that time I will let you know how
the Database was set up. At this point and time I only use wizards to setup
my forms and reports, but I want to learn more.

 

[bceo via AccessMonster.com]

presumably your Incident table has a primary key field, and that field is
included in the form's RecordSource. for example purposes, let's say the
field's name is IncidentID. you need to limit the report's output to the
record with the IncidentID value of the currently selected record in your
form.

you don't say whether the Print command button is running VBA code or a
macro. if it's a macro, then open the macro in Design view, click on the
OpenReport action to select that line, and look at the Where Condition space
in the Action Arguments section in the bottom half of the macro Design
window. you'll need to enter a Where condition, for example:

[IncidentID] = [Forms]![MyFormName]![IncidentID]

replace MyFormName with the correct name of the form. and press F1 in that
field, which will open Access Help at the OpenReport Action topic; read up
on the action, so you'll understand how the arguments work.

if the command button is running VBA code instead, then you'll need to add
the WHERE condition to the code, as

DoCmd.OpenReport "MyReportName", , , "IncidentID = " & Me!IncidentID

again, replace MyReportName with the correct name of the report. the above
code assumes that the IncidentID field has a Number data type. if the
field's data type is Text, then use the syntax below instead for the WHERE
condition, as

"IncidentID = '" & Me!IncidentID & "'"

and again, you can click on the word OpenReport, then press F1, to go to the
appropriate Help topic.

and, of course, in both the macro and VBA examples above, you'd need to
replace IncidentID with the correct name of the primary key field in your
Incidents table.

hth
[quoted text clipped - 12 lines]

all the records in the both tables, which is not want I want. I hope this is
clearer, and thank you for responding I really do appreciate it.

Thanks for your help, I will try your suggestions as well and get back to you.
I have downloaded the instructions from the web link you so kindly provided.
As I stated in my other post of today, I use only the database wizards to set
up the forms and report for this database.

 

[bceo via AccessMonster.com]

presumably your Incident table has a primary key field, and that field is
included in the form's RecordSource. for example purposes, let's say the

[quoted text clipped - 42 lines]
Thank you very much for the reply, I will try these suggestions and I will
reply back if I was successful or not. At that time I will let you know how
the Database was set up. At this point and time I only use wizards to setup
my forms and reports, but I want to learn more.

2nd Reply

First let me say tanks you both Phillip and you Tins for your suggestions,
unfortunately I still can get the results I wish for. So I will take a hint
from Phillip and lay out exactly what I am doing. This will take awhile as I
want to be exact in the info I give to you.
I have a single database with two tables 1) IncidentNo, set as Auto Number
(Primary Key) and 2) Involved (no primary key, but with a IncidentNo field
that is set as a number field.
In the relationship window I have connected the two IncidentNo fields (I did
this so that I would not have to enter the Incident No in the Involved sub
form).

I have set up two forms using the form wizard and pointing the wizard to the
appropriate table 1) Main Incident Form and 2) Involved Sub Form.

I have set up two reports using the report wizard and pointing the wizard to
the appropriate form. (1 Incident Printed Report and 2) Involved Sub Report.
I then went back to the Main Incident Form and place a Command Button to
print the Contents of the record I am looking at.

The Command Button properties on Onclick, event Procedures gives you the
following:

Private Sub Print_Report_Click()
On Error GoTo Err_Print_Report_Click

Dim stDocName As String

stDocName = "Incident Printed Report"
DoCmd.OpenReport stDocName, acPreview

Exit_Print_Report_Click:
Exit Sub

Err_Print_Report_Click:
MsgBox Err.Description
Resume Exit_Print_Report_Click

End Sub
I then entered two records using the main form and when I clicked the Command
Button (which I have set to Print Preview) I get two pages on one I get the
1st incident with the involved from both records and on the 2nd page I get
the 2nd record and again with both involved printed.

I attempted to redo the VB on the Properties with what you gave me Tina it
looks like this:

Private Sub RECORD_Click()
On Error GoTo Err_RECORD_Click

Dim stDocName As String

stDocName = "Incident Printed Report"
DoCmd.OpenReport "Incident Printed Report", acPreview, "[IncidentNo]=" &
Me!IncidentNo

Exit_RECORD_Click:
Exit Sub

Err_RECORD_Click:
MsgBox Err.Description
Resume Exit_RECORD_Click

End Sub

When I clicked the Command button this time I got the same results.

I am sorry this post is so long but I wanted to make what I am truing to
doing cleared then I have been before.
Once again what I wish to happen when I click the command button is for only
one record with it's involved information, if there is any, to be printed and
that is all, not all the records in both tables.

Once again thank you very much for your information and your patience I do
appreciate it. Again I am sorry about the length, but Phillip was correct in
that I may have not supplied you with all the information you needed to
enable you to help me.

 

[tina]

okay, posting your code was good. you're close, but you didn't modify the
line of code i posted, you changed it. take a look at your VBA code:

DoCmd.OpenReport "Incident Printed Report", acPreview, "[IncidentNo]=" &
Me!IncidentNo

and ignore the linewrap; in your form's module, the code is all on one line,
as it should be. now take a look at the code i posted:

DoCmd.OpenReport "MyReportName", , , "IncidentID = " & Me!IncidentID

ignore the difference in fieldnames; just look at the syntax. you'll see
that my code has three commas in it, while yours has only two. that makes a
big difference, because a different argument follows each comma. so you're
putting the Where argument in the wrong place. try your code again, as

DoCmd.OpenReport "Incident Printed Report", acPreview, , "IncidentNo = "
& Me!IncidentNo

again, ignore the linewrap in this post; in the form's module, the code
should be all on one line. and again, you need to read up on the OpenReport
topic in Help, so you'll understand how the arguments work.

also, since you want to learn more, suggest you start "at the beginning".
for more information, see http://home.att.net/~california.db/tips.html.
start with tip #1; then, when you're ready, move on to tips #2 - #9.

hth

presumably your Incident table has a primary key field, and that field is
included in the form's RecordSource. for example purposes, let's say the

[quoted text clipped - 42 lines]

http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/200903/ 1

Thank you very much for the reply, I will try these suggestions and I will
reply back if I was successful or not. At that time I will let you know how
the Database was set up. At this point and time I only use wizards to setup
my forms and reports, but I want to learn more.

2nd Reply

First let me say tanks you both Phillip and you Tins for your suggestions,
unfortunately I still can get the results I wish for. So I will take a hint
from Phillip and lay out exactly what I am doing. This will take awhile as I
want to be exact in the info I give to you.
I have a single database with two tables 1) IncidentNo, set as Auto Number
(Primary Key) and 2) Involved (no primary key, but with a IncidentNo field
that is set as a number field.
In the relationship window I have connected the two IncidentNo fields (I did
this so that I would not have to enter the Incident No in the Involved sub
form).

I have set up two forms using the form wizard and pointing the wizard to the
appropriate table 1) Main Incident Form and 2) Involved Sub Form.

I have set up two reports using the report wizard and pointing the wizard to
the appropriate form. (1 Incident Printed Report and 2) Involved Sub Report.
I then went back to the Main Incident Form and place a Command Button to
print the Contents of the record I am looking at.

The Command Button properties on Onclick, event Procedures gives you the
following:

Private Sub Print_Report_Click()
On Error GoTo Err_Print_Report_Click

Dim stDocName As String

stDocName = "Incident Printed Report"
DoCmd.OpenReport stDocName, acPreview

Exit_Print_Report_Click:
Exit Sub

Err_Print_Report_Click:
MsgBox Err.Description
Resume Exit_Print_Report_Click

End Sub
I then entered two records using the main form and when I clicked the Command
Button (which I have set to Print Preview) I get two pages on one I get the
1st incident with the involved from both records and on the 2nd page I get
the 2nd record and again with both involved printed.

I attempted to redo the VB on the Properties with what you gave me Tina it
looks like this:

Private Sub RECORD_Click()
On Error GoTo Err_RECORD_Click

Dim stDocName As String

stDocName = "Incident Printed Report"
DoCmd.OpenReport "Incident Printed Report", acPreview, "[IncidentNo]=" &
Me!IncidentNo

Exit_RECORD_Click:
Exit Sub

Err_RECORD_Click:
MsgBox Err.Description
Resume Exit_RECORD_Click

End Sub

When I clicked the Command button this time I got the same results.

I am sorry this post is so long but I wanted to make what I am truing to
doing cleared then I have been before.
Once again what I wish to happen when I click the command button is for only
one record with it's involved information, if there is any, to be printed and
that is all, not all the records in both tables.

Once again thank you very much for your information and your patience I do
appreciate it. Again I am sorry about the length, but Phillip was correct in
that I may have not supplied you with all the information you needed to
enable you to help me.

 

[bceo via AccessMonster.com]

[quoted text clipped - 6 lines]

the Database was set up. At this point and time I only use wizards to setup
my forms and reports, but I want to learn more.

2nd Reply

First let me say tanks you both Phillip and you Tins for your suggestions,
unfortunately I still can get the results I wish for. So I will take a hint
from Phillip and lay out exactly what I am doing. This will take awhile as I
want to be exact in the info I give to you.
I have a single database with two tables 1) IncidentNo, set as Auto Number
(Primary Key) and 2) Involved (no primary key, but with a IncidentNo field
that is set as a number field.
In the relationship window I have connected the two IncidentNo fields (I did
this so that I would not have to enter the Incident No in the Involved sub
form).

I have set up two forms using the form wizard and pointing the wizard to the
appropriate table 1) Main Incident Form and 2) Involved Sub Form.

I have set up two reports using the report wizard and pointing the wizard to
the appropriate form. (1 Incident Printed Report and 2) Involved Sub Report.
I then went back to the Main Incident Form and place a Command Button to
print the Contents of the record I am looking at.

The Command Button properties on Onclick, event Procedures gives you the
following:

Private Sub Print_Report_Click()
On Error GoTo Err_Print_Report_Click

Dim stDocName As String

stDocName = "Incident Printed Report"
DoCmd.OpenReport stDocName, acPreview

Exit_Print_Report_Click:
Exit Sub

Err_Print_Report_Click:
MsgBox Err.Description
Resume Exit_Print_Report_Click

End Sub
I then entered two records using the main form and when I clicked the Command
Button (which I have set to Print Preview) I get two pages on one I get the
1st incident with the involved from both records and on the 2nd page I get
the 2nd record and again with both involved printed.

I attempted to redo the VB on the Properties with what you gave me Tina it
looks like this:

Private Sub RECORD_Click()
On Error GoTo Err_RECORD_Click

Dim stDocName As String

stDocName = "Incident Printed Report"
DoCmd.OpenReport "Incident Printed Report", acPreview, "[IncidentNo]=" &
Me!IncidentNo

Exit_RECORD_Click:
Exit Sub

Err_RECORD_Click:
MsgBox Err.Description
Resume Exit_RECORD_Click

End Sub

When I clicked the Command button this time I got the same results.

I am sorry this post is so long but I wanted to make what I am truing to
doing cleared then I have been before.
Once again what I wish to happen when I click the command button is for only
one record with it's involved information, if there is any, to be printed and
that is all, not all the records in both tables.

Once again thank you very much for your information and your patience I do
appreciate it. Again I am sorry about the length, but Phillip was correct in
that I may have not supplied you with all the information you needed to
enable you to help me.

Thank you Tina for your post and correction, I did see the three comas, but
did not know they were that important, sorry! I replace the line with your
new line ensuring that I put the the expression entirely one one line and
again when I clicked the command button I still got multiply involved forms.
so I deleted both of the records and Compacted and repaired the database and
put in 2 more records both using the Involved sub form. Once again both sub
forms appear on the preview. From your post it seems I have a lot to learn
and I will take your suggestion and start the leaning curve. I also plan to
rebuild the database from scratch using the lessons I hope to learn from your
links. Once again thank you for all your help and advise. Once again here
is VB code from the ONClick propeties for you information.

Private Sub Print_Report_Click()
On Error GoTo Err_Print_Report_Click

Dim stDocName As String

stDocName = "Incident Printed Report"
DoCmd.OpenReport "Incident Printed Report", acPreview, , "IncidentNo = "
& Me!IncidentNo

Exit_Print_Report_Click:
Exit Sub

Err_Print_Report_Click:
MsgBox Err.Description
Resume Exit_Print_Report_Click

End Sub

 

[tina]

you're welcome. i won't address the specific result you posted; since you're
going to start fresh, working up the learning curve, i expect your current
problem will be solved as you implement proper table design, relationships,
form and report design, etc. good luck with it, and come back to the ngs
anytime with specific questions, as you learn.

presumably your Incident table has a primary key field, and that field is
included in the form's RecordSource. for example purposes, let's say

the
[quoted text clipped - 6 lines]

the Database was set up. At this point and time I only use wizards to setup
my forms and reports, but I want to learn more.

2nd Reply

First let me say tanks you both Phillip and you Tins for your suggestions,
unfortunately I still can get the results I wish for. So I will take a hint
from Phillip and lay out exactly what I am doing. This will take awhile as I
want to be exact in the info I give to you.
I have a single database with two tables 1) IncidentNo, set as Auto Number
(Primary Key) and 2) Involved (no primary key, but with a IncidentNo field
that is set as a number field.
In the relationship window I have connected the two IncidentNo fields (I did
this so that I would not have to enter the Incident No in the Involved sub
form).

I have set up two forms using the form wizard and pointing the wizard to the
appropriate table 1) Main Incident Form and 2) Involved Sub Form.

I have set up two reports using the report wizard and pointing the wizard to
the appropriate form. (1 Incident Printed Report and 2) Involved Sub Report.
I then went back to the Main Incident Form and place a Command Button to
print the Contents of the record I am looking at.

The Command Button properties on Onclick, event Procedures gives you the
following:

Private Sub Print_Report_Click()
On Error GoTo Err_Print_Report_Click

Dim stDocName As String

stDocName = "Incident Printed Report"
DoCmd.OpenReport stDocName, acPreview

Exit_Print_Report_Click:
Exit Sub

Err_Print_Report_Click:
MsgBox Err.Description
Resume Exit_Print_Report_Click

End Sub
I then entered two records using the main form and when I clicked the Command
Button (which I have set to Print Preview) I get two pages on one I get the
1st incident with the involved from both records and on the 2nd page I get
the 2nd record and again with both involved printed.

I attempted to redo the VB on the Properties with what you gave me Tina it
looks like this:

Private Sub RECORD_Click()
On Error GoTo Err_RECORD_Click

Dim stDocName As String

stDocName = "Incident Printed Report"
DoCmd.OpenReport "Incident Printed Report", acPreview, "[IncidentNo]=" &
Me!IncidentNo

Exit_RECORD_Click:
Exit Sub

Err_RECORD_Click:
MsgBox Err.Description
Resume Exit_RECORD_Click

End Sub

When I clicked the Command button this time I got the same results.

I am sorry this post is so long but I wanted to make what I am truing to
doing cleared then I have been before.
Once again what I wish to happen when I click the command button is for only
one record with it's involved information, if there is any, to be printed and
that is all, not all the records in both tables.

Once again thank you very much for your information and your patience I do
appreciate it. Again I am sorry about the length, but Phillip was correct in
that I may have not supplied you with all the information you needed to
enable you to help me.

Thank you Tina for your post and correction, I did see the three comas, but
did not know they were that important, sorry! I replace the line with your
new line ensuring that I put the the expression entirely one one line and
again when I clicked the command button I still got multiply involved forms.
so I deleted both of the records and Compacted and repaired the database and
put in 2 more records both using the Involved sub form. Once again both sub
forms appear on the preview. From your post it seems I have a lot to learn
and I will take your suggestion and start the leaning curve. I also plan to
rebuild the database from scratch using the lessons I hope to learn from your
links. Once again thank you for all your help and advise. Once again here
is VB code from the ONClick propeties for you information.

Private Sub Print_Report_Click()
On Error GoTo Err_Print_Report_Click

Dim stDocName As String

stDocName = "Incident Printed Report"
DoCmd.OpenReport "Incident Printed Report", acPreview, , "IncidentNo = "
& Me!IncidentNo

Exit_Print_Report_Click:
Exit Sub

Err_Print_Report_Click:
MsgBox Err.Description
Resume Exit_Print_Report_Click

End Sub