Simple button on form (brain freeze!)

[jhko]

Hi Gang:



I've got a simple database. One table is employee names and IDs and a few other fields. Another related table lists review dates (there are many review dates per employee). I've set up a one to many relationship between the tables. I've also made a simple report that lists review dates.

I've made a form to display the employee names and ID data. I have a button on that form. When I click the button, I want to go to the report that lists all the review dates, but display only review dates for the employee currently shown on the form.

This is stupidly simple right? Can't I do it with a simple macro? For some reason I can't figure this out or get it to work- brain freeze!!

I must be overlooking something really simple. I'd be very appreciative of any help.

Many thanks in advance.

[pbaldy]

This would be one way:

http://www.baldyweb.com/wherecondition.htm

Macros do have the wherecondition argument too, though I think the syntax is different.

[jhko]

Thanks very much Paul. I got this to work using your recommended line of code (instead of a macro, shown below), but now my report prints out by default upon clicking the button instead of just appearing on the screen. Is there a property or Access feature that controls this? I'd like to just show the report on the screen, not instantly print it.

Private Sub Command36_Click()
DoCmd.OpenReport "ReviewHistoryReport", , , "PayrollServiceID = " & Me.PayrollServiceID
End Sub

[pbaldy]

Look in VBA help at OpenReport; you've left out the argument that controls print/preview.

[jhko]

Thanks so much Paul, you got me looking in the right direction. Needs the acViewPreview (below). Now everything is working fine! BTW, I'm curious, why do you have to write "PayrollServiceID = " & Me.PayrollServiceID. Why not just "PayrollServiceID = Me.PayrollServiceID"? Why the & statement?


Private Sub Command36_Click()
DoCmd.OpenReport "ReviewHistoryReport", acViewPreview, , "PayrollServiceID = " & Me.PayrollServiceID
End Sub

[pbaldy]

Happy to help. If you don't concatenate the value, you are passing the literal string

"PayrollServiceID = Me.PayrollServiceID"

instead of

"PayrollServiceID = 123"

to the report. The report can't evaluate the form reference, so it will fail. Concatenating values into strings is very common when working with SQL in VBA, and the wherecondition is basically SQL (it's defined as an SQL "WHERE" clause without the word WHERE).

[jhko]

Thanks very much Paul. I'm slowly starting to get it. I'm beginning to know just slightly more about Access than a mature adult banana slug ;-).

You've been great! I really appreciate the forum and your responses. As I get better, hopefully I'll be able to help others.

Best Regards,

Joe O.

[pbaldy]

Happy to help Joe, though I resent the comparison, being a mature adult banana slug myself. Okay, maybe I'm not that mature.