G
GeorgeAtkins
I have been having trouble programmatically coming to terms with subforms,
esp. subforms that are datasheets, and how to reference them.
I have an application that pulls data from a SQL database to insert into an
Access database. Getting the data out of SQL Server is not the problem. This
seems to be related to the use of bookmarks, but I'm stuck on things such as:
do I reference the subform as a control on the master form or as a subform?
And how does the syntax work?
So I have a main form with command buttons and an embedded subform,
displayed as a datasheet, linked by query to an Access table. I want to
insert matching SQL data into the table.
I usually get some kind of error, such as:
"Run time 3021
Either BOF or EOF is True, or the current record has been deleted. Requested
operation requires a current record."
Here is a snippet of my code. I've omitted the standard stuff that works
just fine (connecting to SQL and the SQL query, itself). Code wraps in this
post.
Dim cnn As ADODB.Connection ' sql server
Dim rst As ADODB.Recordset ' sql server
Dim rstSubform As DAO.Recordset ' Access recordset
Set cnn = New ADODB.Connection ' sql server connection
Set rst = New ADODB.Recordset ' sql server recordset
' Here, recordset based on subform seen as control of main form
Set rstSubform =
Forms![frmviewdata][frmStuOptions_Sheet_SUB].Form.RecordsetClone
' An attempt to store the key for the current record in the subform
RecId = Me.frmStuOptions_Sheet_SUB.Controls("Primary")
' Supposedly, synch the curent record with the bookmark version.
With rstSubform
.FindFirst "[primary] =" & RecId
Forms![frmviewdata]![frmStuOptions_Sheet_SUB].Form.Bookmark =
..Bookmark
End With
' open SQL connecton
' ... omitted code
' get current first and last names for student from Access table.
StuLName = rstSubform.Fields("LName").Value
StuFName = rstSubform.Fields("FName").Value
strSearchName = rstSubform.Fields("Primary").Value
' SQL query omitted here...
' get sql data
Set rst = cnn.Execute(qrySQL)
' And now, trying to insert the data!
If rst.BOF Or rst.EOF Then
MsgBox "Found no student number! "
Else ' found match, so insert the data into Access
With rst
YesNo = MsgBox("Found data: " & .GetString & vbCrLf & _
"Insert this student's ID?", vbYesNo,"Look for Student ID")
' THIS DOES NOT SEEM TO WORK. I'm sinking...!
If YesNo = 6 Then ' 6 = Yes
Forms![frmviewdata]![frmStuOptions_Sheet_SUB].Form.Bookmark
=.Bookmark
' The return rs has 5 fields, I pull data from the 3rd one.
rstSubform.FindFirst "Primary = " & strSearchName
rstSubform.Edit
rstSubform.Fields("Student ID").Value = .Fields(3).Value
rstSubform.Update
End If
'Me.Refresh
End With
End If
....
Egad! What a hopeless mess! Can anybody pull me out?
George
esp. subforms that are datasheets, and how to reference them.
I have an application that pulls data from a SQL database to insert into an
Access database. Getting the data out of SQL Server is not the problem. This
seems to be related to the use of bookmarks, but I'm stuck on things such as:
do I reference the subform as a control on the master form or as a subform?
And how does the syntax work?
So I have a main form with command buttons and an embedded subform,
displayed as a datasheet, linked by query to an Access table. I want to
insert matching SQL data into the table.
I usually get some kind of error, such as:
"Run time 3021
Either BOF or EOF is True, or the current record has been deleted. Requested
operation requires a current record."
Here is a snippet of my code. I've omitted the standard stuff that works
just fine (connecting to SQL and the SQL query, itself). Code wraps in this
post.
Dim cnn As ADODB.Connection ' sql server
Dim rst As ADODB.Recordset ' sql server
Dim rstSubform As DAO.Recordset ' Access recordset
Set cnn = New ADODB.Connection ' sql server connection
Set rst = New ADODB.Recordset ' sql server recordset
' Here, recordset based on subform seen as control of main form
Set rstSubform =
Forms![frmviewdata][frmStuOptions_Sheet_SUB].Form.RecordsetClone
' An attempt to store the key for the current record in the subform
RecId = Me.frmStuOptions_Sheet_SUB.Controls("Primary")
' Supposedly, synch the curent record with the bookmark version.
With rstSubform
.FindFirst "[primary] =" & RecId
Forms![frmviewdata]![frmStuOptions_Sheet_SUB].Form.Bookmark =
..Bookmark
End With
' open SQL connecton
' ... omitted code
' get current first and last names for student from Access table.
StuLName = rstSubform.Fields("LName").Value
StuFName = rstSubform.Fields("FName").Value
strSearchName = rstSubform.Fields("Primary").Value
' SQL query omitted here...
' get sql data
Set rst = cnn.Execute(qrySQL)
' And now, trying to insert the data!
If rst.BOF Or rst.EOF Then
MsgBox "Found no student number! "
Else ' found match, so insert the data into Access
With rst
YesNo = MsgBox("Found data: " & .GetString & vbCrLf & _
"Insert this student's ID?", vbYesNo,"Look for Student ID")
' THIS DOES NOT SEEM TO WORK. I'm sinking...!
If YesNo = 6 Then ' 6 = Yes
Forms![frmviewdata]![frmStuOptions_Sheet_SUB].Form.Bookmark
=.Bookmark
' The return rs has 5 fields, I pull data from the 3rd one.
rstSubform.FindFirst "Primary = " & strSearchName
rstSubform.Edit
rstSubform.Fields("Student ID").Value = .Fields(3).Value
rstSubform.Update
End If
'Me.Refresh
End With
End If
....
Egad! What a hopeless mess! Can anybody pull me out?
George