Populate unbound form with recordset

[pacg]

Hi
- one unbound continuous form (form1)
- one recordset (via DAO)
- 2 fields: NAP and DateNAP ()

Onload i only get one record (the last one) from the recordset (=10).
How can i pull all the records into the form.

code--
Private Sub Form_Load()
Dim rst As DAO.Recordset
Dim strSQL As String
Dim dbs As Database

Set dbs = CurrentDb
strSQL = "Select N_APagamento, Data_APagamento, ID_Programa, ID_Medida FROM AUT_PAGAMENTO WHERE ID_Programa = '26'"

Set rst = dbs.OpenRecordset(strSQL)

Do While Not rst.EOF
NAP = rst!N_APagamento
Data_NAP = rst!Data_APagamento
rst.MoveNext
Loop

rst.Close
Set rst = Nothing
dbs.Close

End Sub
---
Thanks

 

[ChrisLomax]

Private Sub Form_Load()
Dim rst As DAO.Recordset
Dim strSQL As String
Dim dbs As Database

Set dbs = CurrentDb
strSQL = "Select N_APagamento, Data_APagamento, ID_Programa, ID_Medida FROM AUT_PAGAMENTO WHERE ID_Programa = '26'"

Set rst = dbs.OpenRecordset(strSQL)
rst.MoveFirst
Do While Not rst.EOF
NAP = rst!N_APagamento
Data_NAP = rst!Data_APagamento
rst.MoveNext
Loop

rst.Close
Set rst = Nothing
dbs.Close

End Sub

I think that should do it?

 

[pacg]

Chris
I want the 10 records in the form. I only have 1, and its the last one.
Nevertheless good observation because DAO needs the position.

 

[ChrisLomax]

strSQL = "Select N_APagamento, Data_APagamento, ID_Programa, ID_Medida FROM AUT_PAGAMENTO WHERE ID_Programa = '26'"

That is your problem right there

You are selecting only one record

Change the query to

strSQL = "Select N_APagamento, Data_APagamento, ID_Programa, ID_Medida FROM AUT_PAGAMENTO"

 

[pacg]

My recordset have 14 records. The criteria WHERE is just to filter few records.
I want to populate my unbound form with the 14 records.

 

[ChrisLomax]

Sorry, just thinking, do you only have two fields on a form?

You want to replicate what access does when you put a recordset into the access properties?

Im just trying to figure out how you have this laid out, i dont know if you can operate a form the same way access can manipulate a form.

I would suggest to use the listbox Active X control and populate that?

Here is an example of using a listbox

Set cnxn2 = New ADODB.Connection
cnxn2.Provider = "Microsoft.Jet.OLEDB.4.0"
cnxn2.Open "s:\Starscanner\StarScanner.MDB", _
"Admin"

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseServer

rs.Open "Select * From Patient", cnxn2
rs.MoveFirst

While Not rs.EOF
Set itmadd = ListView1.ListItems.Add
itmadd.Text = rs!PatID
itmadd.SubItems(1) = rs!BabyName
rs.MoveNext
Wend

rs.Close
cnxn2.Close

That is the only thing i can suggest

You can use the RecordCount fucntion of the rst to find out how many records are actually being returned?

 

[ChrisLomax]

Just a thought, have you got "Continuous Form" property set on the form you are trying to put the information into?

 

[pacg]

1st Q: I only have two fields now. When this works i'll put more fields. Recordcount = 14.
2nd question: Form is set to continuous.

Thanks anyway.

 

[ChrisLomax]

No, what i mean is will this method of populating an unbound form work, from what i can gather you want it to create more fields to accomodate the amount of rows in the table?

Just like when you make a bound form and it creates more field depending on the amount of rows in the select query or in the table you are selecting from

What is the reason for the unbound form?

Why cant you use the RowSource property of the form?

 

[pacg]

The reason for the unbound form its to not use a table/query and to not ocupy the table.
RowSource? hum... Probably i can use that. I'll give it a try.

Thanks Chris

 

[ChrisLomax]

If you think that using this method will cut out the code and time it takes to occupy then you are wrong, it would be quicker to run a query on the form.

Try using this

Create your SQL query like you did before

FrmName.RecordSource = SQL Query

Easy

You can assign the SQL dynamically if thats what you want so you define the SQL query when you open the form.

You seem to be doing a very complicated thing to a very simple operation?

 

[pacg]

I'll explain.
Not very with english so ...
I need to "snapshot" the data from the table into the form. Why? Snapping only establish the connection in that second. Why? I'll beleive this app will be used by several (>=15) users. So, many will be doing updates, inserts, querying...
I dont want block records.
Am i thinking correctly?

 

[WayneRyan]

pacg,

Unless you want to make this form your entire career, I would strongly
suggest that you go back to a bound form. You will expend much less
time and energy AND you'll introduce fewer errors. There is no reason
to try to undertake an effort that Access can do far better; which is
maintain your data & control your forms.

A query will give you a viable snapshot of your data. In fact, it will
do it a WHOLE lot quicker that a recordset. In the worst case scenario,
a user is looking at a print preview that might be 5 seconds old. If
anyone had entered a record in that time frame, your DAO method wouldn't
see it either.

In any multi-user application, when a user views a report, the data might
have been subsequently altered since the reports generation. But what
can you do? That's the way it is.

Instead of unbound forms, spend some time on forecasting software so that
the users will know what their report data might look like in the next
5 minutes, 5 hours ...

Just some thoughts,
Wayne

 

[pacg]

Chris: Thanks for the time spent!
Wayne: Good thoughts !! Not my career but the experience tell me that (when happens) this is the best solution. You must think the three actions separadely: insert, editions and removes.

Thank you both
pacg

 

[Bat17]

pacg

If you create a query, you can use its property sheet to set it to a 'snapshot' type query. This will load the data quickly and will not lock other records. It is not editable and new records cannot be added through it.

HTH

Peter