Form showing incorrect record

[Guest]

I am using Access 2002.
My database (Substance Material Safety Data Sheets) contains 650 records
which were imported from an Excel spreadsheet exported from Access 97.
I use 1 form which is paged all transactions related to the database are via
commnad buttons and all are working fine.
My Problem is: I wish to print another form (Abridged Datasheet) which
should be the current record being viewed or added. When the command button
is used the form is launched but it is not the current record but the first
record in the database.
I never had this problem with Access 97.
Can somebody please help me it's last stages of this particular database.

 

[Guest]

The first problem is you are printing a form. Forms are for viewing and
editing data, reports are for printing. In either case, you need to filter
the report or form based on the current record. Both the OpenForm and
OpenReport methods have a Where argument for exactly this purpose. VBA help
has an example of this in the OpenForm description.

Create a report and print that.

 

[Guest]

Thanks for that - I shall have a go and get back to you

 

[Guest]

Had a look at VBA to solve problem.
I search the database for a record. Accvept the search findings making that
the current record.
Now I wish to make that record the details in the report but I do not see
where to make this so. The DoCmd OpenReport does not give the opportunity
to make the current record being viewed the current record in the report.
I have the Access2 Bible 2nd Edition and that doesn't seem to help me either.
Am I just being plain thick or what ! :-(

 

[J. Goddard]

Hi -

You specify which record(s) to include in the report using the 4th
parameter of the docmd.openreport.

docmd.openreport ReportName,,,"[MsdsNo] = '" & me![sheetnumber] & "'"

where [MsdsNo] is the field in the database containing the material
number, and [sheetnumber] is the control on your form.

If MsdsNo is numeric, you don't need the single quotes.
check the OpenReport entry in VBA Help for more information.

John

 

[Guest]

Hi J.G.
I will follow your instructions and come back to you if I may
--
Beware the unreal !

Hi -

You specify which record(s) to include in the report using the 4th
parameter of the docmd.openreport.

docmd.openreport ReportName,,,"[MsdsNo] = '" & me![sheetnumber] & "'"

where [MsdsNo] is the field in the database containing the material
number, and [sheetnumber] is the control on your form.

If MsdsNo is numeric, you don't need the single quotes.
check the OpenReport entry in VBA Help for more information.

John

Had a look at VBA to solve problem.
I search the database for a record. Accvept the search findings making that
the current record.
Now I wish to make that record the details in the report but I do not see
where to make this so. The DoCmd OpenReport does not give the opportunity
to make the current record being viewed the current record in the report.
I have the Access2 Bible 2nd Edition and that doesn't seem to help me either.
Am I just being plain thick or what ! :-(

 

[Guest]

Hi John
Back again.
Tried your response. Perhaps I need to give a bit more detail.
I use a FORM for input and to show single records. Each record has 2
identifying fields - Grp ID and Prod ID. When searching for a record I use
anothery key field - that of Supplier. Having found the record I cancel the
search and that record remains on screen and is the current record. It is
at this stage that I wish to view and print the report which will be based on
a form and will be the Current Record.
Is this OK or mustI do something different?
The VBA code as as you suggested but it falls over. Each time I try to
correct it I get a different message:

Hope you can and wish to help me further. Thanks

VBA -
Private Sub Command226_Click()
On Error GoTo Err_Command226_Click

Dim stDocName As String

stDocName = "Data Sheet"
DoCmd.OpenReport stDocName,,,[Grp ID] = &me! [Prod ID]

Exit_Command226_Click:
Exit Sub

Err_Command226_Click:
MsgBox Err.Description
Resume Exit_Command226_Click

End Sub

--
Beware the unreal !

Hi -

You specify which record(s) to include in the report using the 4th
parameter of the docmd.openreport.

docmd.openreport ReportName,,,"[MsdsNo] = '" & me![sheetnumber] & "'"

where [MsdsNo] is the field in the database containing the material
number, and [sheetnumber] is the control on your form.

If MsdsNo is numeric, you don't need the single quotes.
check the OpenReport entry in VBA Help for more information.

John

Had a look at VBA to solve problem.
I search the database for a record. Accvept the search findings making that
the current record.
Now I wish to make that record the details in the report but I do not see
where to make this so. The DoCmd OpenReport does not give the opportunity
to make the current record being viewed the current record in the report.
I have the Access2 Bible 2nd Edition and that doesn't seem to help me either.
Am I just being plain thick or what ! :-(

 

[J. Goddard]

Hi -

The 4th parameter of the docmd.openreport is essentially a "where"
clause, but without the WHERE. It must be given as a character string,
much the same as a SQL statement would be, and in this case needs to
have actual values in it, rather than form control names.

I will assume that Grp ID and Prod ID are numeric. Let's take as an
example that Grp ID = 10 and Prod ID = 15. The wherecondition string
would then have to be:

[Grp ID] = 10 and [Prod ID] = 15

To get that, your openreport statement is:

DoCmd.OpenReport stDocName,,,"[Grp ID] = " & me![Grp ID] & " AND [Prod
ID] = " & me![Prod ID]

If Grp ID and Prod ID are not numeric, the syntax is a bit trickier,
with quotation marks to delimit the values.

To get: [Grp ID] = 'A' and [Prod ID] = 'B'
as the wherecondition string, you would need:

DoCmd.OpenReport stDocName,,,"[Grp ID] = '" & me![Grp ID] & "' AND [Prod
ID] = '" & me![Prod ID] & "'"

Hope this gets you going in the right direction

John

 

[Guest]

Thanks John
Before I continue. Your assumption that the values are numeric. As I said
earlier. I searched for a record in the database having found it that
record remains on screen as the CURRENT record. Now, it is THIS CURRENT
record that I wish to view and print using the report based on another form
which shows the record's fields in a typical form layout.
--
D Rodman

Hi -

The 4th parameter of the docmd.openreport is essentially a "where"
clause, but without the WHERE. It must be given as a character string,
much the same as a SQL statement would be, and in this case needs to
have actual values in it, rather than form control names.

I will assume that Grp ID and Prod ID are numeric. Let's take as an
example that Grp ID = 10 and Prod ID = 15. The wherecondition string
would then have to be:

[Grp ID] = 10 and [Prod ID] = 15

To get that, your openreport statement is:

DoCmd.OpenReport stDocName,,,"[Grp ID] = " & me![Grp ID] & " AND [Prod
ID] = " & me![Prod ID]

If Grp ID and Prod ID are not numeric, the syntax is a bit trickier,
with quotation marks to delimit the values.

To get: [Grp ID] = 'A' and [Prod ID] = 'B'
as the wherecondition string, you would need:

DoCmd.OpenReport stDocName,,,"[Grp ID] = '" & me![Grp ID] & "' AND [Prod
ID] = '" & me![Prod ID] & "'"

Hope this gets you going in the right direction

John

Hi John
Back again.
Tried your response. Perhaps I need to give a bit more detail.
I use a FORM for input and to show single records. Each record has 2
identifying fields - Grp ID and Prod ID. When searching for a record I use
anothery key field - that of Supplier. Having found the record I cancel the
search and that record remains on screen and is the current record. It is
at this stage that I wish to view and print the report which will be based on
a form and will be the Current Record.
Is this OK or mustI do something different?
The VBA code as as you suggested but it falls over. Each time I try to
correct it I get a different message:

Hope you can and wish to help me further. Thanks

VBA -
Private Sub Command226_Click()
On Error GoTo Err_Command226_Click

Dim stDocName As String

stDocName = "Data Sheet"
DoCmd.OpenReport stDocName,,,[Grp ID] = &me! [Prod ID]

Exit_Command226_Click:
Exit Sub

Err_Command226_Click:
MsgBox Err.Description
Resume Exit_Command226_Click

End Sub

 

[J. Goddard]

Hi -

Now you have me confused. Once you have the record you want on the
screen as the current record, what exactly do you want to do with it -
open another form, or print a report for that record?

... Now, it is THIS CURRENT
record that I wish to view and print using the report based on

another > form which shows the record's fields in a typical form layout.

Reports are not based on forms - they are based on tables or queries.
If what you want to do is open another form that *Looks* like a report,
and then print that form, that is not a good approach. Forms are for
screen viewing and data entry. Design a proper report, using the
required table or query as its record source, then open the report from
the search form, using the docmd.openreport as I showed you. If you put
the code in the On Click event of a command button on the search form,
it will print the current record.

John

 

[Guest]

Sorry for confusing you. I'll explain step by step

1. open the database main form. The header has a number of command
buttons 1 of which is search for a record.
2. this is activated and a record is sought. When found the search is
cancelled and the found record is the current record (I assume).
3. there is another command button, that is for the report. When clicked
the report opens (it is based on a form and not from selection available
through the wizard). The record that appears is not the record that was
searched for but the 1st record in the database.

Question Can I make the record that was found during the search and is
currently on screen (in a form) the subject of the report (that is based on a
form)?

I hope this makes it clearer. In the '97 version of the databse the form
used in the report was in fact part of the main screen form but I can't do
that with the 2002 version. Tried to use tabs but this played havoc with
the mainscreen from.

I relly appreciate you continued assistance.

 

[J. Goddard]

Hi -

I follow what you are trying to accomplish. Now, what is the code that
runs when you click the command button to open the report? I think that
is where the problem is - the report is opening with all the records
instead if just the one you want.

Question
Can I make the record that was found during the search and is
currently on screen (in a form) the subject of the report (that is
based on a form)?

Yes - that is done all the time. But what do you mean when you say "the
report is based on a form"? Reports are based on data - tables or
queries - not forms. (I use A2000 - is this a new feature to A2002?)

Remember, forms and reports are independent entities - they act
differently and are used for different purposes - you cannot make a
report part of a form, or vice versa.

If the report you want to open uses the same data (i.e. table) as its
record source as you are using in the form, then you should have no problem.

As I said, post the code behind the command button to run the report,
and we can go from there.

John

 

[Guest]

--
D Rodman

Hi -

I follow what you are trying to accomplish. Now, what is the code that
runs when you click the command button to open the report? I think that
is where the problem is - the report is opening with all the records
instead if just the one you want.

THE CODE FROM PROPERTIES ->

Private Sub Command228_Click()
On Error GoTo Err_Command228_Click

Dim stDocName As String

stDocName = "Data Sheet"
DoCmd.OpenReport stDocName, acPreview

Exit_Command228_Click:
Exit Sub

Err_Command228_Click:
MsgBox Err.Description
Resume Exit_Command228_Click

End Sub

Yes - that is done all the time. But what do you mean when you say "the
report is based on a form"?

'Data Sheet' was created using the fields from the table 'Main'. When
saving - the option was given to save it as a report. This I did. This is
what I mean when I say the report is based on a form. Basically I am using
my own design "Data Sheet" as the report's formatting.

Reports are based on data - tables or

queries - not forms. (I use A2000 - is this a new feature to A2002?)

A2000 SHOULD READ ACCESS 2002

 

[J. Goddard]

Hi -

Your problem is as I thought. In your code to run the report, the line

DoCmd.OpenReport stDocName, acPreview

opens the report "Data Sheet" with ALL the records in its underlying
table or query.

You only want to view one record from that table or query; to do that
you use the 4th parameter of the Openreport line as a WHERE clause, but
without the WHERE. The record on the screen when you click the button
to print is automatically the "current record", and you refer to its
control using me![controlname].

Now, the two fields that identify the record to be printed are [grp Id]
and [prod id]. Let's assume, for example, that the control on your
search form are called [PrintGroup] and [PrintProduct], and that the
corresponding fieds in the table/query for the report are [grp ID] and
[Prod ID]. The Openreport line now becomes:


DoCmd.OpenReport stDocName, acPreview,,"[Grp ID] = " &
me![printgroup] & " AND [Prod ID] = " & me![PrintProduct]

which will print (only) the record you want.

Hope this makes things clearer.

John