Form for display multiple records from a database (VBA)???

[jmasm]

I am trying to create a VBA code that allows me to display data from a SQL database.
I'm using this code:

Private Sub Form_Open(Cancel As Integer)
Dim i As Integer
Dim rsPDM As ADODB.Recordset
Dim strPDM As StringSet
rsPDM = New ADODB.Recordset
strPDM = "select * From PDM Where Ugf = 'Centro Litural';"
rsPDM.Open strPDM, CurrentProject.Connection,adOpenDynamic, adLockOptimistici = 1
Do While Not rsPDM.EOF
If i = DLookup("ID", "PDM", "ID =" & i) Then
Me.ID = rsPDM!ID
rsPDM.MoveNext
End If
i = i + 1
Loop
rsPDM.CloseSet
rsPDM = Nothing
End Sub

The code works for a single recordeset.
For many records presents only the latest.

 

[dhookom]

Why are you using any code? You can link to a SQL Server table or create a pass-through query. Then use your table or query as the Record Source of your form?

Duane
Hook'D on Access
MS Access MVP

 

[jmasm]

yes I could use a pass-through query but I'm not reading all the records from a table.
As you can see in the code that I use I have a where clause.
This clause is coming from a text box.
This I can do to a single record with the code I have.
When I try to view multiple records, the form only shows the last record of the table.
As I try to accomplish this with a query, I can not use the text box.
If you know how I can accomplish this with a query, I would appreciate if you help me with the steps.

 

[dhookom]

You can use code to modify the SQL property of a saved pass-through query to set any type of criteria.

Duane
Hook'D on Access
MS Access MVP

 

[jmasm]

I do not understand!
Can you explain with an example or step by step.
Thanks!!!

 

[dhookom]

I don't see anything suggesting "This clause is coming from a text box."

Private Sub Form_Open(Cancel As Integer)
  Dim db as DAO.Database
  Dim qd as DAO.QueryDef 
  Dim strSQL as String
  Set db = Currentcb
  Set qd = db.QueryDefs("qsptYourQueryNameHere")
  'modify this sql to meet your needs
  strSQL = "select * From PDM Where Ugf = 'Centro Litural';"
  qd.SQL = strSQL
  Set qd = Nothing
  Set db = Nothing
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[jmasm]

The DAO works only for ACCESS 2007 or earlier.
I'm using ACCESS 2010, where the code only works with ADODB.
Can you explain with an example or step by step for ADODB.
Thanks!!!

 

[dhookom]

I rarely use ADO. You might want to check this link.

Duane
Hook'D on Access
MS Access MVP

 

[jmasm]

The DAO is not a solution!!!
I Tried to do everything but nothing worked.
Can you explain how you can create a new record (row) in a form to display data from the database using the ADO?
When looking for a way to solve my problem, I found a way to show the data from the database through a subform. But the examples I found are not explicit. You have some examples of how to send data to a subform.
Thanks!!!

 

[dhookom]

Search Bing on "ADOX querydef". You should be able to modify the SQL property of a saved pass-through query to display whatever you want.

Duane
Hook'D on Access
MS Access MVP