Alli (igz00) wrote in visualbasic,

VB 6.0 Data Environments/Reports

I'm using VB 6 with an Access Database (I know... I know...) and I'm trying to get it to generate a report based on user selected criteria.

Basically my database is full of contacts that can be involved in my company's programs. The user should be able to select a program from a dropdown and then print a report of all the contacts participating in that program.

I've been able to make it print every contact in every program, but not for a specific program only

  • Post a new comment


    default userpic

    Your IP address will be recorded 

wouldn't you just modify the query?
in theory... have you ever used VB's data environment?
I did VB6 for years, then switched over to VB.Net and now do C#.Net.

I ALWAYS got fed up with the objects, and would switch over to using ADO. I just liked the level of control you get when you use the raw objects.
Well, are you using a query or some other method for populating the drop-down?

The way I see it you could have two drop-downs; one for contacts, and one for programs.

You could use conditional logic in VB to only use certain SQL based on if the user has selected one or both drop-downs, because you would need the criteria of one to depend on the other in that case.

Your drop-downs would need SQL with VB variables inserted to make this work.
well I need ALL contacts for a specific program, so I only need one drop down box.

The issue is not the selection of the specific group of contacts. I'm not sure how to make that set of data the recordset for a report. Make sense?
So you need whatever is selected in your form control to pass to a report object as the recordset?

Well, you could set the recordset (in code) to be the standard SELECT statement, and then concatenate whatever is selected in that form control on the end as a WHERE predicate.

Or have I missed the bus again?

On the right track, but how do I set the recordset of a report?
In Design mode, it's in the properties window, in the usual place.

At runtime, you'll either need to do it in code (recommended), or you can do it in the various options within Access, if you want.


Private Sub Report_Open(Cancel As Integer)
Dim WhereClause As String

If Form1!ComboBox = "" Then
WhereClause = ";"
WhereClause = "Where YourFieldName = " + Form1!ComboBox + ";"

Me!Recordset = "SELECT * FROM TABLE1" + WhereClause
End Sub

So, something along those lines. Basically you want the code to work whether the combobox is populated or not, hence all the stuff with the "WhereClause" variable. Note that sourcing directly from the form control in this manner will require the form to be open during the report opening. Otherwise you'd need to declare the WhereClause variable as Global, or save it to a file or table, say.

Hope this helps.
basically this is my code:

Private Sub DataReport_Activate()

Me.DataSource = "SELECT * FROM Product WHERE productID = " & _

End Sub

and its telling me that its an invalid datasource
Is "CInt(frmReportTool.txtProductID.Text)" valid syntax?

Semi-colon missing.

access doesn't use semi-colons
Weird. When I last used Access, if you use the query wizard, bash in any old query in the "builder" and then left-click and pick "SQL View", you'll see there is a semi-colon on the end of the statement. AND I used to put them in VBA.
well I guess I should have said, Access doesn't care if they are there or not. I did, however, try it with and without the semi-colon and it didn't make a difference.
yOur conversation is sOO cOOL!!!
prOgramming Languages.., specifically VB is so nice right?
i'm also dOing a 2 thesis right now.. and i'm using the VB... it's so cOOl!!!
... data rEport??? akUkU!!! i am using that also!?! ~_^!!!