Help Moving Select Query to a Form

[JGravesNBS]

This query works:
SELECT Max(tblMemPayment.id) AS MaxOfid, tblMemPayment.employee_id
from tblMemPayment
GROUP BY tblMemPayment.employee_id
HAVING (((tblMemPayment.employee_id)=1838));

I'm trying to move the SQL to a form and I'm getting:
Run-time error '13': Type mismatch

How can I adjust the following to work correctly?

Me.MemberIDOld contains 1838

Dim intResult As Integer

intResult = "SELECT Max(tblMemPayment.id) AS MaxOfid, tblMemPayment.employee_id from tblMemPayment GROUP BY tblMemPayment.employee_id HAVING (((tblMemPayment.employee_id) = " & Me.MemberIDOld & "))"

 

[jdraw]

On a form you could have Recordsource---- that is the source of the data to populate the Form.
If you have controls on your form, such as listbox or combobox, you could set the rowsource of the control to some SQL

However, it is unclear exactly what you are trying to do. You can't simply make an integer equal to an SQL string. That just isn't how it works.

You can set your SQL statement as the base for a record set, then you can use fields within the recordset along the lines you have suggested.

see this for more info on recordsets

Good luck. You can also search youtube for MsAccess recordsets introduction

Another approach, but you should learn recordsets anyway, is
Aggregate functions.

eg:

Dim intResult As Integer

IntResult = DMax("tblMemPayment.id","tblMemPayment","tblMemPayment.employee_id =" & Me.MemberIDOld )

Note: This is untested. Research DMax()

 

[MarkK]

You missed a step. The SQL SELECT command needs to be used to open a recordset first. Then you read the data out of the recordset.

const SQL as string = _
   "SELECT Max(id) AS MaxOfid, employee_id " & _
   "FROM tblMemPayment " & _
   "GROUP BY employee_id " & _
   "HAVING employee_id = "

dim rst as dao.recordset
dim result as integer

set rst = currentdb.openrecordset(SQL & Me.MemberIDOld)
result = rst.fields(0)

 

[MarkK]

jdraw, more like quickdraw

 

[jdraw]

Thanks MarkK. I'm usually 10 minutes behind you.

 

[JGravesNBS]

Thanks Mark & JDraw

Both solutions worked, JDraw, after further review I had to add transaction_type_id = 1 to your code

Dim lngMPID As Long

lngMPID = DMax("tblMemPayment.id", "tblMemPayment", _
"tblMemPayment.employee_id =" & Me.EmployeeID & " And " & _
"tblMemPayment.transaction_type_id =" & 1)


Mark, how would I adjust transaction_type_id = 1 to your code below?

Dim rst As dao.Recordset

Const SQL As String = _
"SELECT Max(tblMemPayment.id) AS MaxOfid " & _
"FROM tblMemPayment " & _
"WHERE employee_id = "

Set rst = CurrentDb.OpenRecordset(SQL & Me.EmployeeID)
lngMPID = rst.Fields(0)

 

[MarkK]

Same as what you did in the criteria of your DMax() expression, above. You need to "AND" it together with the other criteria.

"tblMemPayment.employee_id =" & Me.EmployeeID & " And " & _
"tblMemPayment.transaction_type_id =" & 1

It might be easiest if you reverse the order so you can still tag your variable employee_id on the end.

"WHERE TransactionType = 1 AND EmployeeID = "

Or you can replace a dummy value . . .

CONST WHERE_CLAUSE As String = "WHERE EmployeeID = {0} AND TransactionType = 1"

Property Get strWhere as string
   strWhere = VBA.Replace(WHERE_CLAUSE, "{0}", Me.EmployeeID)
End Property

See what happens there? You have tons of ways to do this.

 

[JGravesNBS]

As always, thanks for the clinic