How to make a string call out a table (1 Viewer)

[JuniorWoodchuck24]

I've developed a database that starts with a user login and then allows the user to select a project number. The project number is then used as an OpenArgs to concatenate with a string to form a table's name. I'm having a syntax error with the following code:


Tables:
OverallProjectReport-101
OverallProjectReport-102

Forms:
ProjectList (user selects proj number 101/102/etc and makes OpenArgs = to that number)
StartPage (Has all the reports/forms for user)


Problem area:
Defined on Start Page VBA is StrProj as following:
StrProj = "OverallProjectReport-" & Me.OpenArgs
DoCmd.OpenReport "OverallReportView1", acViewPreview, StrProj

My problem is that StrProj on the filter (Do.Cmd 3rd part) is coming up as table doesn't exist. The error shows 'OverallProjectReport-101' does not exist. I'm thinking my error is in my syntax and that it believes that StrProj isn't a table name.

 

[ghudson]

The OpenArgs value should be for a "record" to filter on in the record source of the form you are opening.

 

[JuniorWoodchuck24]

I'm trying to find a way to call different project tables to easily feed into reports for the end user. Forms/Reports in front end db and Tables in back end db. All the tables will be called by same name, but have the proj number attached to them. Even if I attach the number without using the Me.OpenArgs to it, it gives me an error. Is there a way to concatenate two items and set the value to a variable and have the table called up from the variable?

 

[vbaInet]

Have you checked the value of strProj to ensure it's a valid string?

StrProj = "OverallProjectReport-" & Me.OpenArgs
[COLOR=Red][B]msgbox StrProj[/B][/COLOR]
DoCmd.OpenReport "OverallReportView1", acViewPreview, StrProj

 

[JuniorWoodchuck24]

StrProj = "OverallProjectReport-" & Me.OpenArgs

When I use the msgbox it pops up with:

OverallProjectReport-101

The error is on the DoCmd part:

Run-time error '3011':

The Microsoft Office Access database engine could not find the object 'OverallProjectReport-101'. Make sure the object exists and that you spell its name and the path name correctly.

It's spelled exactly like the table is so I'm assuming their is some syntax issue with StrProj being a str and the filter that is needed being a table

 

[vbaInet]

You've put strProj in the Filter argument of the OpenReport method. OpenArgs is that last argument:

docmd.OpenReport "ReportName", acViewPreview,,,,[COLOR=Red][B]OpenArgs here[/B][/COLOR]

Count the commas. When you type you should be able to see it too.

 

[JuniorWoodchuck24]

Huh?

A little bit confused by your comment. I don't have StrProj in the OpenArgs section because I have something else in there.

Over View of DB:
User opens DB
User enters username/pass on login form
This opens up ProjectList (form), (OpenArgs set to project number)
This opens up start page with project number as open args on this form
-On this form I'm trying to set StrProj to equal the projectnumber + OverallProjectReport (concatenate the two) and use that as the filter the report pulls. The Report runs a query that can do either English/Metric Units for simple conversion and it's one report. So the OpenArgs on the StartPage going to the report is based on Units the user wants. The error is happening with the StrProj part. So either you can't include "-" or "101" (numbers) in the table title or it doesn't like to pull on those.

 

[vbaInet]

You can use the WHERE argument instead of the filter, it's the next argument after the Filter.

Either of those arguments require you to specify a field name for which it should filter upon.

Docmd.Openreport "NameOfReport",,"[[COLOR=Red][B]FieldName[/B][/COLOR]] = '" & strProj & "'"

 

[boblarson]

JuniorWoodchuck24 you are totally misunderstanding the user of filters, the where clause, and open args. If you are trying to filter the report based on a FIELD then you would use the WHERE CLAUSE (not the Filter area). The FILTER is really not used all that much in this context but you have to have a saved, valid filter to call for in that location if you were going to use it.

If you are trying to set the report's record source then the OPEN ARGS would be the place to do it and then you have to handle that also in the Report's OPEN event.