I am trying to build a form in access which in the top portion would
contain master records and a subform would contain detail records.

My current issue lies in that I have a "Next Record" button on the
form to move from one master record to the next in the recordset which
is created as the form is opened.

When I click the next record button the first time it does move to the
next record, but then subsequent clicks do nothing. What am I doing
wrong. Also is there a better programming style/way to do one
recordset when the form opens and use that recordset across multiple
subroutines? I am going to want to do a routine for next record and
previous record as an example.

Private Sub Next_record_Click()
Dim address_issues As DAO.Database
Dim accts As DAO.Recordset
Dim rssql As String
Set address_issues = CurrentDb

rssql = "select instill_code, organization_name, acct_num, ACCT_NAME,
BUYER_ADDRESS1, BUYER_ADDRESS2, BUYER_CITY, BUYER_STATE,
BUYER_POSTAL_CODE, DPSU_SAP, DPSU_ORG_NAME, UPDATED_BY FROM
TBL_DPSU_TO_MAP WHERE DPSU_SAP = '';"

Set accts = address_issues.OpenRecordset(rssql, dbOpenDynaset)

With accts

Do Until accts.EOF



Me![Seller Name] = accts.Fields("instill_code").Value
Me![Acct Num] = accts.Fields("acct_num").Value
Me![Acct Name] = accts.Fields("acct_name").Value
Me![Address1] = accts.Fields("BUYER_ADDRESS1").Value
Me![Address2] = accts.Fields("BUYER_ADDRESS2").Value
Me![City] = accts.Fields("BUYER_CITY").Value
Me![State] = accts.Fields("BUYER_STATE").Value
Me![Postal Code] = accts.Fields("BUYER_POSTAL_CODE").Value
Me![DPSU_SAP] = accts.Fields("dpsu_sap").Value
Me![DPSU_ORG_NAME] = accts.Fields("dpsu_org_name").Value
Me![UPDATED_BY] = accts.Fields("updated_by").Value
..MoveNext

Loop
End With

accts.Close
address_issues.Close

End Sub

You "next record" code re-assigns and re-loads the reocrdset which means it
going to start at the 1st record every time, and then only move to the next
2nd record after that. So, each time you are re-loading the reocrdset...it
starts at the beginnning..

However, why all such code? Just use a bound form.

I you must assign the sql, then go:

rssql = "select instill_code, organization_name, acct_num, ACCT_NAME,
BUYER_ADDRESS1, BUYER_ADDRESS2, BUYER_CITY, BUYER_STATE,
BUYER_POSTAL_CODE, DPSU_SAP, DPSU_ORG_NAME, UPDATED_BY FROM
TBL_DPSU_TO_MAP WHERE DPSU_SAP = '';"

me.RecordSource = rssql

Then, use the built in navigation buttons, or place the following code
behind a button:

On Error Resume Next
DoCmd.GoToRecord acActiveDataObject, , acNext

In fact, why is this sql/recordset even being created here. Why not use a
bound form? If your form is bound, then we don't need that sql to be
assigned to the forms reocrdset, but just the navigation code as above.


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(E-Mail Removed)