selecting records for Access 97 report from VB 6 program

[tyedyejenn]

I am writing a program in VB6 and want to run a report from Access 97 to select certain records.&nbsp;&nbsp;I have the following line of code to run the report<br><br>Accapp.DoCmd.OpenReport &quot;LogIn Report&quot;, acViewNormal<br><br>I just don't know how to add a sql type statement to select the user name of person logging in(this infor is stored in strName)<br><br>If anyone can help me with this I would greatly appreciate it.&nbsp;&nbsp;Thanks in advance for the help.<br>Jenn

 

[kathryn]

I'm not sure what you are aiming for.&nbsp;&nbsp;Do you want the user name of the person logged in to be passed as a parameter to the report when it opens?<br><br>If so, there are a number of ways to do it.&nbsp;&nbsp;You could create a tiny table in your access database and update the one record, which would only have one field, with the user name, and then run your report.&nbsp;&nbsp;You would have to build a query to base the report on which referred to the new tiny table.<br><br>Or, you could open the report in design view and get the report source property and edit it, then save the report and reopen it in normal view.&nbsp;&nbsp;I have a few reports in which I have to do this and it works pretty well.<br><br> <p>Kathryn<br><a href=mailto: > </a><br><a href= > </a><br>

 

[tyedyejenn]

Kathryn,<br><br>I created a small report just to figure out how to run an Access Report from VB.&nbsp;&nbsp;I figured that out, I can print the report but it shows me all records in the table.&nbsp;&nbsp;So now, I am trying to figure out how to run the report but only with records(in this case 1 record) that match the current user(strName).&nbsp;&nbsp;I hope this clarifies what I am trying to accomplish.&nbsp;&nbsp;Any suggestions are greatly appreciated.&nbsp;&nbsp;Thanks Again,<br>Jenn

 

[kathryn]

Sorry for the delay in replying; I was out with a sick child yesterday.

Your report is based on a set of records, either a table or a query. To limit the number of records to be the records for the current user, you must have a field in those records which has the users name.

If you can access the report from VB, can you access it's properties? Here is the code I have used in similar situations

***Begin code snippet

'Set report's recordsource
Dim repname As String
repname = &quot;rptAP131_A&quot;

'Open report in design view
DoCmd.OpenReport repname, acViewDesign

'Change a property of the report
Reports(repname).RecordSource = strTblName

'Close and save the report
DoCmd.Close acReport, repname, acSaveYes

***End code snippet

You could use similar code to turn on the filter property and set the filter to equal strName

Me.Filter = &quot;YourFieldName= '&quot; & strName & &quot;'&quot;
Me.FilterOn = True

HTH