ADO Form bind

  • Thread starter Thread starter Gsurfdude
  • Start date Start date
G

Gsurfdude

Hello,

I have a form that is set to Continuous and in the Form_Open event I have
the code which "binds it"
Set cn = CurrentProject.AccessConnection
Set rs = New ADODB.Recordset
With rs
Set .ActiveConnection = cn
.Source = "SELECT * " & _
"FROM FIELD_COLLECT_ALL WHERE
FIELD_COLLECT_ALL.Status_user_id='Test';"

.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.Open
End With
Set Me.Recordset = rs
me.text0 = rs!County
Set rs = Nothing
Set cn = Nothing

I have text boxes that are not bound to a field. The query above returns 100
rows but in the text box it's only the first record 100 times. How do I get
each individual record to display in the text box in the continuous form?

I tried this

Do While Not RS.EOF
me.Text0 = RS!County
rs.move next
loop

It did not work correctly.
 
Delete the line me.text0 = rs!County from your code. Open your form in design
view and set the control source of text0 to County. Then when you set the
recordset in code, it should display as you expect. FWIW, it is helpful to
give a meaningful name to your control, e.g. txtCountyCode instead of text0.

HTH,
Chris
 
Thanks. Yeah, I know to name the control to something meaningful. Now,
setting the control source to County makes the text not updateable that I
need. When you try to enter something, the message at the bottom of Access
displays "This record set is not updateable"
 
Gsurfdude said:
Thanks. Yeah, I know to name the control to something meaningful. Now,
setting the control source to County makes the text not updateable that I
need. When you try to enter something, the message at the bottom of Access
displays "This record set is not updateable"
Click to expand...


If you're using Access 2000, I don't believe you can bind an updatable form
to an ADO recordset using the Jet provider. If you're using Access 2002 or
later, you should be able to, according to this KB article:


http://support.microsoft.com/kb/281998/
How to bind Microsoft Access forms to ADO recordsets

However, that article states that you must also specify a server-side
cursor:

.CursorLocation = adUseServer

And "the recordset must contain one or more fields that are uniquely
indexed, such as a table's primary key."

I'm curious as to why you are going to all this trouble, though. If you
want to bind a form dynamically, why not just set the form's RecordSource
property to the desired query? For example, all of your code above could be
replaced by:

Me.RecordSource = _
"SELECT * FROM FIELD_COLLECT_ALL " & _
"WHERE Status_user_id='Test';"
 
Mr. Goldgar,

I finally set the record source to Me.RecordSource = _
"SELECT * FROM FIELD_COLLECT_ALL " & _
"WHERE Status_user_id='Test';" .

Now I have another issue. In the forms property, I set the Record Type
property to Dynaset (Inconsistent Updates). This form contains about 50
controls (combination of Text boxes and Combo boxes).

I needed this Inconsistant updates so the user can edit the data - however,
I do not want the data that was updated in a particular control to update the
underlying table (Oracle linked tables). When ALL edits are done in a record
then they need to click a button that saves and performs other things. I
tried using the forms BeforeUpdate event:

Cancel = true

But it freezes and will not let me move to another record. I simply need to
be able to edit any of the 50+ data items with out saving each one as it is
edited. Rather, edit what ever and then save the whole record.

I used this and it does work in the forms BeforeUpdate event:
If MsgBox("Changes have been made to this record." _
& vbCrLf & vbCrLf & "Do you want to save these changes?" _
, vbYesNo, "Changes Made...") = vbYes Then
DoCmd.Save
Else
DoCmd.RunCommand acCmdUndo
End If

but I do not want to have message boxes poping up on every item edited -
that'd be anoying to the user. Make sense?

Thank you.
 
Back
Top