Running a Select statement in VBA

[geraldk]

I have this select statement that I want to run in a form then pass the results when I open a report:

"SELECT Max(tblPosition.POSID) AS MaxOfPOSID" & _
"FROM tblPosition" & _
"GROUP BY tblPosition.EMPID" & _
"HAVING (((tblPosition.EMPID)=[Forms]![frmEmployee]![txtEMPID]));"

Right now I have the report opening to match the employee ID that I see on the form.

stDocName = "rptEmployee"
stLinkCriteria = "[EmpID]=" & Me![txtEMPID] <- would like to add another criteria to it for the POSID from the select statement above.
DoCmd.OpenReport stDocName, , acViewPreview, stLinkCriteria

Thank you,

[pbaldy]

You can open a recordset on that SQL, or switch to a DMax().

[geraldk]

You mean open a recordset when I open the report?

[Paul H]

Couldn't you store the results of your select statement in a text box on your form then refer to it in strCriteria?

stLinkCriteria = "[EmpID]=" & Me![txtEMPID] & " and [POSID] = " & me.txtMaxPosID

[pbaldy]

You mean open a recordset when I open the report?

No, I mean open a recordset on that SQL to get the number, and then use the number in your code to open the report. You might find the DMax simpler.

[pbaldy]

Couldn't you store the results of your select statement in a text box on your form then refer to it in strCriteria?

stLinkCriteria = "[EmpID]=" & Me![txtEMPID] & " and [POSID] = " & me.txtMaxPosID

Sure, but how are you going to get the results to the textbox?

[geraldk]

I made a list box on the form, then I need to highlight the first value in the list box. Made a separate query to get values that I am looking for. Now is list box I have the values

9
5
4
3

but I need to select 9 to pass on to the report.

Found some code here: http://www.access-programmers.co.uk/...d.php?t=138069

Need to see if I can get it work for me.

[pbaldy]

Try

stLinkCriteria = "[EmpID]=" & Me![txtEMPID] & " And POSID = " & DMax("POSID", "tblPosition", "EMPID = " & [Forms]![frmEmployee]![txtEMPID])

[Paul H]

I was going to use the DMax as the Control Source for the text box. It amounts to the same thing I think.

[pbaldy]

That would work. When you said "store the results of your select statement in a text box" I thought you would have tried to make the SQL statement the Control Source, which wouldn't work.

[geraldk]

Here is the code I am working with:

j = 0
i = 0
RecordCount01 = Me.lstMaxPosID.ListCount
Do Until i = Me.lstMaxPosID.ListCount
i = i + 1
If Me.lstMaxPosID.Selected(i) Then j = i
Loop
If j = 0 Then
Me.lstMaxPosID.Selected(i) = True
End If
strLinkPos = Me.lstMaxPosID.value


So if I these values in list box (come from query and sort from the top value that I need)

4
5
9
6

I need it highligt 4 and give me the value back as 4

[pbaldy]

Did you try what I posted above? That's a convoluted way of getting the max (or min, if your desires have changed).

[geraldk]

Found the code to make this work as I needed:

Me.lstMaxPosID = Me.lstMaxPosID.ItemData(0)

Just like to thank everyone who helped out me here.