Dim max As Long, db As Database, qdf As QueryDef, strSQL As String
max = DMax("Order_Number", "order_det_update")
strSQL = "INSERT INTO order_det_update ( ORDER_NUMBER, ITEMNO, FirstOfITEM_DESC, 0, 2, 3, 4, 5, 6, 7, 8, 9 )"
strSQL = strSQL & "SELECT [ord det conv].ORDER_NUMBER,.......(detail omitted)
strSQL = strSQL & "FROM [ord det conv]"
strSQL = strSQL & "WHERE ([ord det conv].ORDER_NUMBER > 'max')"
Set db = CurrentDb
Set qdf = db.CreateQueryDef("")
qdf.SQL = strSQL
When I run this I get a data type mismatch error. I think the problem is that the "WHERE" statement is not able to read the "max" variable that I'm trying to insert after the > . The max definition (Dmax) at the top is working properly b/c it returns the correct number. If I remove the apostrophe's it gives me another error. Am I missing something here?
Thanks for correction on the variable, it worked great!
On the subform, there were good suggestions but they didn't quite fit my situation. Problem is each record in my subform has two rows so I don't think datasheets will work. I'm thinking about a flexgrid type activeX solution, but that looks time consuming. Think that it's going to stay the way it is for a while.