Move record selector

C

Can

I have a continuous subform (modal pop-up) in a database. I want to copy
some of the fields into a temporary table. As the user does data entry they
are in the last record in the subform. I want to move the record selector to
the top but the code I have has been tempermental. Any ideas?

Here's the two versions of code I've tried:

VERSION 1

Dim dbs As Database
Set dbs = CurrentDb

Dim rstSubform As DAO.Recordset
Dim rstCurrent As Recordset

Set rstCurrent = dbs.OpenRecordset("_Current_Location", dbOpenDynaset)

tmpValue = "No"
rstCurrent.MoveFirst

Dim lng As Integer
lng = 0
Set rstSubform = Me!zzzAdd_Location_Tanks_Split.Form.RecordsetClone

If rstSubform.RecordCount > 0 Then
rstSubform.MoveFirst
Do While lng < Me!zzzAdd_Location_Tanks_Split.Form.SelTop And
Not rstSubform.EOF
rstCurrent.AddNew
rstCurrent![_Current_Location_Location_ID] = Location_ID.Value
rstCurrent![_Current_Location_To_Tank_ID] =
Forms![Add_Location_Split]![zzzAdd_Location_Tanks_Split]![Location_Tanks_To_Tank_ID]
rstCurrent![_Current_Location_From_Tank_ID] =
Forms![Add_Location_Split]![zzzAdd_Location_Tanks_Split]![Location_Tanks_From_Tank_ID]
rstCurrent![_Current_Location_TotalMales] =
Forms![Add_Location_Split]![zzzAdd_Location_Tanks_Split]![Location_Tanks_TotalMales]
rstCurrent![_Current_Location_TotalFemales] =
Forms![Add_Location_Split]![zzzAdd_Location_Tanks_Split]![Location_Tanks_TotalFemales]
rstCurrent![_Current_Location_TotalUnknown] =
Forms![Add_Location_Split]![zzzAdd_Location_Tanks_Split]![Location_Tanks_TotalUnknown]
rstCurrent.Update
rstSubform.MoveNext
lng = lng + 1
Loop
End If

Set rstSubform = Nothing
rstCurrent.Close

VERSION 2

Dim dbs As Database
Set dbs = CurrentDb

Dim rstSubform As DAO.Recordset
Dim rstCurrent As Recordset

Set rstCurrent = dbs.OpenRecordset("_Current_Location", dbOpenDynaset)

tmpValue = "No"
rstCurrent.MoveFirst

Set rstSubform = Me!zzzAdd_Location_Tanks_Split.Form.RecordsetClone

rstSubform.MoveFirst

Do Until rstSubform.EOF
rstCurrent.AddNew
rstCurrent![_Current_Location_Location_ID] = Location_ID.Value
rstCurrent![_Current_Location_To_Tank_ID] =
Forms![Add_Location_Split]![zzzAdd_Location_Tanks_Split]![Location_Tanks_To_Tank_ID]
rstCurrent![_Current_Location_From_Tank_ID] =
Forms![Add_Location_Split]![zzzAdd_Location_Tanks_Split]![Location_Tanks_From_Tank_ID]
rstCurrent![_Current_Location_TotalMales] =
Forms![Add_Location_Split]![zzzAdd_Location_Tanks_Split]![Location_Tanks_TotalMales]
rstCurrent![_Current_Location_TotalFemales] =
Forms![Add_Location_Split]![zzzAdd_Location_Tanks_Split]![Location_Tanks_TotalFemales]
rstCurrent![_Current_Location_TotalUnknown] =
Forms![Add_Location_Split]![zzzAdd_Location_Tanks_Split]![Location_Tanks_TotalUnknown]
rstCurrent.Update
If (rstSubform.RecordCount = rstSubform.AbsolutePosition + 1) Then
Exit Do
Else
rstSubform.AbsolutePosition = rstSubform.AbsolutePosition + 1
End If
Loop

Set rstSubform = Nothing
rstCurrent.Close
 
D

Damon Heron

Not sure, but you may be able to use Stephen Lebans code to move the
scrollbar back to the top (the first record) without an API call.
Here is a sample db for you to look at.
http://www.lebans.com/setgetsb.htm

HTH
Damon

Can said:
I have a continuous subform (modal pop-up) in a database. I want to copy
some of the fields into a temporary table. As the user does data entry
they
are in the last record in the subform. I want to move the record selector
to
the top but the code I have has been tempermental. Any ideas?

Here's the two versions of code I've tried:

VERSION 1

Dim dbs As Database
Set dbs = CurrentDb

Dim rstSubform As DAO.Recordset
Dim rstCurrent As Recordset

Set rstCurrent = dbs.OpenRecordset("_Current_Location",
dbOpenDynaset)

tmpValue = "No"
rstCurrent.MoveFirst

Dim lng As Integer
lng = 0
Set rstSubform = Me!zzzAdd_Location_Tanks_Split.Form.RecordsetClone

If rstSubform.RecordCount > 0 Then
rstSubform.MoveFirst
Do While lng < Me!zzzAdd_Location_Tanks_Split.Form.SelTop And
Not rstSubform.EOF
rstCurrent.AddNew
rstCurrent![_Current_Location_Location_ID] =
Location_ID.Value
rstCurrent![_Current_Location_To_Tank_ID] =
Forms![Add_Location_Split]![zzzAdd_Location_Tanks_Split]![Location_Tanks_To_Tank_ID]
rstCurrent![_Current_Location_From_Tank_ID] =
Forms![Add_Location_Split]![zzzAdd_Location_Tanks_Split]![Location_Tanks_From_Tank_ID]
rstCurrent![_Current_Location_TotalMales] =
Forms![Add_Location_Split]![zzzAdd_Location_Tanks_Split]![Location_Tanks_TotalMales]
rstCurrent![_Current_Location_TotalFemales] =
Forms![Add_Location_Split]![zzzAdd_Location_Tanks_Split]![Location_Tanks_TotalFemales]
rstCurrent![_Current_Location_TotalUnknown] =
Forms![Add_Location_Split]![zzzAdd_Location_Tanks_Split]![Location_Tanks_TotalUnknown]
rstCurrent.Update
rstSubform.MoveNext
lng = lng + 1
Loop
End If

Set rstSubform = Nothing
rstCurrent.Close

VERSION 2

Dim dbs As Database
Set dbs = CurrentDb

Dim rstSubform As DAO.Recordset
Dim rstCurrent As Recordset

Set rstCurrent = dbs.OpenRecordset("_Current_Location",
dbOpenDynaset)

tmpValue = "No"
rstCurrent.MoveFirst

Set rstSubform = Me!zzzAdd_Location_Tanks_Split.Form.RecordsetClone

rstSubform.MoveFirst

Do Until rstSubform.EOF
rstCurrent.AddNew
rstCurrent![_Current_Location_Location_ID] = Location_ID.Value
rstCurrent![_Current_Location_To_Tank_ID] =
Forms![Add_Location_Split]![zzzAdd_Location_Tanks_Split]![Location_Tanks_To_Tank_ID]
rstCurrent![_Current_Location_From_Tank_ID] =
Forms![Add_Location_Split]![zzzAdd_Location_Tanks_Split]![Location_Tanks_From_Tank_ID]
rstCurrent![_Current_Location_TotalMales] =
Forms![Add_Location_Split]![zzzAdd_Location_Tanks_Split]![Location_Tanks_TotalMales]
rstCurrent![_Current_Location_TotalFemales] =
Forms![Add_Location_Split]![zzzAdd_Location_Tanks_Split]![Location_Tanks_TotalFemales]
rstCurrent![_Current_Location_TotalUnknown] =
Forms![Add_Location_Split]![zzzAdd_Location_Tanks_Split]![Location_Tanks_TotalUnknown]
rstCurrent.Update
If (rstSubform.RecordCount = rstSubform.AbsolutePosition + 1)
Then
Exit Do
Else
rstSubform.AbsolutePosition = rstSubform.AbsolutePosition +
1
End If
Loop

Set rstSubform = Nothing
rstCurrent.Close
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

scanning to access table 1
recordset errors 0
Accessing a new record 1
openrecordset 2
Temporary recordsets 3
Not recognizing specification 4
Save selected items NOT selected in an Extended List Box based onother condition 6
Code to list Objects in DB lists deleted SQL 4

Top