Access 2007 recordset

[Aleni]

Hi everyone!

I am able to populate the text box with ADO recordset but i am not able to navigate between the records might be because only first record is displayed and not all.
In my navigation button it should come 1 0f 21 but its coming 1 of 1.

I am new to access and this is my first project so please bear with me...
I am using Access 2007
thank you...

 

[Galaxiom]

New one on me. How do you populate a text box with a recordset?

 

[Aleni]

Set cnn = Access.CurrentProject.Connection
Set rs = New ADODB.Recordset

strsql = "SELECT tblAcqDetails.*,tblItems.* FROM tblAcqDetails,tblItems WHERE tblAcqDetails.fkItemID=tblItems.pkItemID AND tblAcqDetails.fldNote = 'New Stock'"

rs.Open strsql, cnn

Me.txtID = rs!pkItemID
Me.txtItem = rs!fldItem
Me.txtQty = rs!fldQty
Me.txtUOM = rs!fldUOM
rs.close

I have this three variables declared in module.

Public strsql As String
Public cnn as new ADOB.connection
Public rs as new ADODB.recordset

With this code I am able to populate text boxes but only the first record not all records.

 

[Galaxiom]

Set the form's Recordset Property to the recordset.

Set Me.Recordset = rs

Then the controls' ControlSource Property to the recordset's fieldname.

 

[Aleni]

Hi Galaxiom!
Thank you for the reply.
I tried your suggestion but it did not work.
There is an error in Set Me.Recordset = rs
The error is "The object you entered is not a valid Recordset property"

Thank you.

 

[Galaxiom]

Post more of the code you are using.

Note:
Dim somevariable as New sometype

This automatically instantiates the variable when it is mentioned in the code so you don't use it with:
Set somevariable = New sometype

It can be handy for testing but most developers avoid the New in the declaration because it masks issue where the variable has gone out of scope. The variable going out of scope often gives messages very like the one you are getting.

Set a break point before you set the form's recordset and check the locals Window to see what is actually in the ADO recordset.

However, why are you using an ADO recordset anyway? Especially from the CurrentProject?

I generally only use fabricated ADO recordsets on forms when I want to display data that does not exist in a table. For example extracting data from text files or displaying transient information. (Though I have used them to display data from SQL Server too.)

I have never tried to use one connected to a table with an updateable form.

If you are just experimenting then try setting the connection as:
CurrentProject.AccessConnection

This is required for updateability in some configurations.

The other issues that confound when using ADO recordsets is the RecordsetType, CursorType and CursorLocation properties.

 

[Aleni]

Actually I am new to access so I do not have much idea.
I will try to work out with what you said.

Thank you.

 

[Galaxiom]

You are jumping in the deep end using ADO recordsets on forms. I am guessing you have a background in other programming.

Easiest way to make a form in Access is to create a query then press Create Form while you are focussed on the query.

Othewise use Form Design and put the query name as the RecordSource property of the form. Then use Add Existing Fields in the Design Ribbon to create the controls on the form.