More Help Needed - Report Objects

[S Jackson]

Sorry for the double post - see Build Query to Collect Names of Report
Objects - but I need this done by Friday and am in a bit of a mess.

I have three tables: tblReports, tblGroups, tblGroupMembers

I want to build a form so that a user who has created a new report can add
the report to the reports table. Then a second form to add the report to
tblGroupMembers.

The first form to allow the user to add the new report to the reports table
is giving me trouble. The form's source is tblReports. The first unbound
control is suppose to be a drop-down list of all report objects contained in
db:

SELECT [Name]
FROM MSysObjects
WHERE [Type] = -32764;

The user will select the new report from the list - now how do I add this
selection to the tblReports? The selection needs to be entered into the
field [rptFileName] in tblReports.

Then I want to user to fillin the remaining fields in tblReports - [RptName]
(this is not the file name, but rather a descriptive name) and
[RptDescription]. I also want these fields to be automatically populated if
the user selects a report that already belongs to tblReports.

I'm lost and in a fog here. Any help is appreciated.
S. Jackson

 

[Pavel Romashkin]

Base the form on the table user is adding reports to. Create the combo
box for report name and text boxes (or whatever other controls) for
other fields describing the report.
1. Make the combo box (drop list) unbound and make the combo box row
source equal to the SELECT statement you were given. Selecting the name
from the list will set it in the other table.
2. In AfterUpdate event for the combo box:
a) Use FindFirst to see if the report is already in the table
b) if it is, use Bookmark to go to that record - this will display the
pertinent information
c) if not, set the ReportName field using the selection in the combo box.

This way is needed because you want to use the same combo box for both
lookup of existing names and entering new names using a defined report
list. The former prevents you from using a bound combo box.

Pavel

 

[S Jackson]

Thanks Pavel. Your answer makes sense, its just that my ignorance (I'm a
green beginner at VBA Access) precludes me being able to implement it.

So far I've got this under the AfterUpdate Event:

Dim strSQL As String

strSQL = "Select tblReports.RptName, " _
& "FROM tblReports " _
& "WHERE tblReports.RptName = " & Me.RptFilecmbo & ""

Me.RptName = strSQL

I keep getting this error:

Run-Time error '-2147352567 (80020009)

The field is too small to accept the amount of data you attempted to add.
Try inserting or pasting less data.

What am I doing wrong here? I figure if I can get this part to work, then I
can figure out the FindFirst and Bookmark stuff after.

S. Jackson

Base the form on the table user is adding reports to. Create the combo
box for report name and text boxes (or whatever other controls) for
other fields describing the report.
1. Make the combo box (drop list) unbound and make the combo box row
source equal to the SELECT statement you were given. Selecting the name
from the list will set it in the other table.
2. In AfterUpdate event for the combo box:
a) Use FindFirst to see if the report is already in the table
b) if it is, use Bookmark to go to that record - this will display the
pertinent information
c) if not, set the ReportName field using the selection in the combo box.

This way is needed because you want to use the same combo box for both
lookup of existing names and entering new names using a defined report
list. The former prevents you from using a bound combo box.

Pavel

Sorry for the double post - see Build Query to Collect Names of Report
Objects - but I need this done by Friday and am in a bit of a mess.

I have three tables: tblReports, tblGroups, tblGroupMembers

I want to build a form so that a user who has created a new report can add
the report to the reports table. Then a second form to add the report to
tblGroupMembers.

The first form to allow the user to add the new report to the reports table
is giving me trouble. The form's source is tblReports. The first unbound
control is suppose to be a drop-down list of all report objects contained in
db:

SELECT [Name]
FROM MSysObjects
WHERE [Type] = -32764;

The user will select the new report from the list - now how do I add this
selection to the tblReports? The selection needs to be entered into the
field [rptFileName] in tblReports.

Then I want to user to fillin the remaining fields in tblReports - [RptName]
(this is not the file name, but rather a descriptive name) and
[RptDescription]. I also want these fields to be automatically populated if
the user selects a report that already belongs to tblReports.

I'm lost and in a fog here. Any help is appreciated.
S. Jackson

 

[Pavel Romashkin]

Sorry, I should have been more specific.
Make
SELECT [Name] FROM MSysObjects WHERE [Type] = -32764
the RowSource for the combo box, which is unbound.
In this combo box AfterUpdate event:

Me.MoveFirst
Me.Form.Recordset.FindFirst "ReportName = '" & Me.MyComboBox & "'"
IF Me.Form.Recordset.EOF then
Me!ReportName = Me.MyComboBox
END IF

This assumes that: ReportName is the name of the field in the table you
are adding report names to, MyComboBox is the name of the combo box control.
This should do it. It seems you need not use Bookmarks because you can
use Recordset to FindFirst (typically, to avoid the form switching
records, you'd use RecordsetClone, then its Bookmark property will
retain the location of the found record).

Good luck,
Pavel

Thanks Pavel. Your answer makes sense, its just that my ignorance (I'm a
green beginner at VBA Access) precludes me being able to implement it.

So far I've got this under the AfterUpdate Event:

Dim strSQL As String

strSQL = "Select tblReports.RptName, " _
& "FROM tblReports " _
& "WHERE tblReports.RptName = " & Me.RptFilecmbo & ""

Me.RptName = strSQL

I keep getting this error:

Run-Time error '-2147352567 (80020009)

The field is too small to accept the amount of data you attempted to add.
Try inserting or pasting less data.

What am I doing wrong here? I figure if I can get this part to work, then I
can figure out the FindFirst and Bookmark stuff after.

S. Jackson

Base the form on the table user is adding reports to. Create the combo
box for report name and text boxes (or whatever other controls) for
other fields describing the report.
1. Make the combo box (drop list) unbound and make the combo box row
source equal to the SELECT statement you were given. Selecting the name
from the list will set it in the other table.
2. In AfterUpdate event for the combo box:
a) Use FindFirst to see if the report is already in the table
b) if it is, use Bookmark to go to that record - this will display the
pertinent information
c) if not, set the ReportName field using the selection in the combo box.

This way is needed because you want to use the same combo box for both
lookup of existing names and entering new names using a defined report
list. The former prevents you from using a bound combo box.

Pavel

Sorry for the double post - see Build Query to Collect Names of Report
Objects - but I need this done by Friday and am in a bit of a mess.

I have three tables: tblReports, tblGroups, tblGroupMembers

I want to build a form so that a user who has created a new report can add
the report to the reports table. Then a second form to add the report to
tblGroupMembers.

The first form to allow the user to add the new report to the reports table
is giving me trouble. The form's source is tblReports. The first unbound
control is suppose to be a drop-down list of all report objects contained in
db:

SELECT [Name]
FROM MSysObjects
WHERE [Type] = -32764;

The user will select the new report from the list - now how do I add this
selection to the tblReports? The selection needs to be entered into the
field [rptFileName] in tblReports.

Then I want to user to fillin the remaining fields in tblReports - [RptName]
(this is not the file name, but rather a descriptive name) and
[RptDescription]. I also want these fields to be automatically populated if
the user selects a report that already belongs to tblReports.

I'm lost and in a fog here. Any help is appreciated.
S. Jackson