[Visual Basic][Access] Referring to an arbitrary field

[John Henry]

A few days ago I asked about some code to loop through controls on a form. Stompy gave me a snippet for that and it's been very useful several times already. However, I'm now trying to use it as a lazy (plus efficient and futureproof) way of uploading data from TextBoxes and ComboBoxes, but I'm having trouble.

I have associated each of the relevant Controls with a field in the database, which is associated with a Data Control on the same form.

The code I'm using to try to add a new record is shown below.


Private Sub cmdAdd_Click()

cmdAdd.Enabled = False

Dim strDbName As String
Dim dbDatabase As Database, rsProperty As Recordset

strDbName = App.Path & "\EstateAgent.mdb"
Set dbDatabase = DBEngine.Workspaces(0).OpenDatabase(strDbName)
Set rsProperty = dbDatabase.OpenRecordset("properties")

rsProperty.AddNew

Dim e As Control

For Each e In Me.Controls
MsgBox e.Name
If (e.DataSource = Data1) Then
rsProperty!e.DataField = e
End If
Next

rsProperty.Close
dbDatabase.Close
Me.Refresh
cmdAdd.Enabled = True

End Sub

It doesn't like the line 'rsProperty!e.DataField = e' and returns an error 'Method or Data Object not found.'

I presume this is because it's trying to find a field called 'e.DataField', rather than the field that it holds. Is there any way I can make it look for whatever field e.DataField holds?

[John Henry]

Just in case anybody with a similar problem comes looking at this thread, I ought to tell you how I got round it.

I'm pretty certain now that it's not possible to do what I was trying to do (but if it is, I'd still be very interested to know how) so I did it a different way.

I'll spare you the source, but the way I did it was to create a new record with all fields blank when you click on add record, then bring up a form to view the record, let the user edit all the textboxes then when they click on add record, it just saves the changes using the update method and the datafield properties.

[magua]

Originally posted by John Henry
rsProperty!e.DataField = e

It doesn't like the line 'rsProperty!e.DataField = e' and returns an error 'Method or Data Object not found.'

I presume this is because it's trying to find a field called 'e.DataField', rather than the field that it holds. Is there any way I can make it look for whatever field e.DataField holds?

First of all, rs!... is an abominable kuldge and should be avoided. Second of all, unless you specify you want to set the field's Value, it may assume you want to set the field object equal to the control, which won't work.

But the following should:

rsProperty(e.DataField).Value = e