how to open pass through query with recordset

[adnancanada]

I am running pass through query in Access 2013 VBA code back end is SQL Server. I want to pass parameter to query.




Private Sub Command4_Click()
Dim db As DAo.Database


Dim rs As DAo.Recordset

Set db = OpenDatabase("", False, False, "ODBC;")
Set rs = db.OpenRecordset("SELECT PO as PurchDoc, DATE as [Created on], VDRBNBR as Vendor, TM1 as TERM1, TM2 as [TERM2] FROM tblPO where vdrnbr ='Parameter'", dbOpenDynaset, dbSeeChanges)


If Not rs.EOF Then rs.MoveFirst
Do Until rs.EOF

I want to open result in edit mode so that I could I export in excel what is criteria will fit here. Please help.

move next

Loop



End Sub

[pbaldy]

I'm not clear on what you're trying to do, and I don't see a pass through query. If your parameter came from this form:

Set rs = db.OpenRecordset("SELECT PO as PurchDoc, DATE as [Created on], VDRBNBR as Vendor, TM1 as TERM1, TM2 as [TERM2] FROM tblPO where vdrnbr ='" & Me.Textboxname & "'", dbOpenDynaset, dbSeeChanges)

[adnancanada]

I'm not clear on what you're trying to do, and I don't see a pass through query. If your parameter came from this form:

Set rs = db.OpenRecordset("SELECT PO as PurchDoc, DATE as [Created on], VDRBNBR as Vendor, TM1 as TERM1, TM2 as [TERM2] FROM tblPO where vdrnbr ='" & Me.Textboxname & "'", dbOpenDynaset, dbSeeChanges)

Thank you very much for answer it.

My question is that how would I get all the records in edit mode once query is run. I hope you understand it. Yes you are right I have textboxname have Vdrnbr.

[pbaldy]

Well, one record at a time:

rs.Edit
rs!Fieldname = NewValue
rs.Update

[adnancanada]

Well, one record at a time:

rs.Edit
rs!Fieldname = NewValue
rs.Update

I dont want to add new value and update. I want to open all the fields in edit mode.

[pbaldy]

That is editing the record. Are you wanting the user to be able to edit records? If so, you'll need to use a form based on a query or something. The user can't interact directly with a recordset.

[adnancanada]

That is editing the record. Are you wanting the user to be able to edit records? If so, you'll need to use a form based on a query or something. The user can't interact directly with a recordset.

I want to open query just like we open query in access. What is the syntax of record set to run query in edit mode for all fields ?

[pbaldy]

You can just open an Access query based on a linked SQL Server table. It can use a form for criteria. To the best of my knowledge a pass through query is read-only, so can't be used for editing.