Have never seen nor would use Me("something"). If not Me.Something then Me.Controls("Something")
Perhaps the form collection is the collection being referred to. Could try the bracketed syntax above or replace with a valid value as a test.
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.
That was the first thing I tried. I.e., !["Mov" & I] = "" Also, I first coded the source of the assignment Me.Controls("tbMov" & I) like I would normally do but dropped the ".Control" only after trying a few other things out of desperation.Could try the bracketed syntax above or replace with a valid value as a test.
Try
Code:.Fields("Mov" & I) = Me.Controls("tbMov" & I)
Then as always, must verify that values and concatenations are what you expect. I get the impression that this happens even on the first pass which is why I didn't mention that the collection is zero based if it is for a recordset fields or form controls. So it's more than likely that at least Mov1 would work. I guess I'd be debug.printing either
rs!fields("Mov" & I).Name (or for the form controls collection).
Not sure which because your post suggests that there is a With block above and I'm assuming it is for the recordset. Do you mind if I ask why you post the absolute minimum amount of code when you need help? It begs too many questions and assumptions - at least on my part.
Last edited by Micron; 08-11-2021 at 01:50 PM. Reason: spelin and gramur
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.
My access at least wont digest !["Mov" & I]
It doesn't complain about .Fields("Mov" & I) though.
Yes, perfect. Access simply needed to recognize my referenced to members of the Fields collection.
Bill
It will work IF the With isIt doesn't complain about .Fields("Mov" & I) though.
With rs.Fields but not if it is With rs
as in
which works. Too bad what the With was about wasn't evident.Code:With rs.Fields Debug.Print !sysnum End With
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.
Code:Set rsWorks = DBEngine(0)(0).OpenRecordset("QWorks") With rsWorks .AddNew !Title = WorksTitle !CatSystem = CatSys !CatSystemVal = CatSysVal !CompID = CompID !OrchID = OrchID !CondID = CondID !GenreID = GenreID !SoloID = SoloID !VolID = lngVolID For I = 1 To 16 .Fields("Mov" & CStr(I)) = Me.Controls("tbMov" & CStr(I)) .Fields("Mov" & CStr(I) & "t") = Me.Controls("t" & CStr(I)) Next I .Update End With rsWorks.Close Set rsWorks = Nothing
In my testing I found it was the !["..."] notation it barked at. To be specific it's the string within the square brackets. I've used ![ControlName] without issue
Code:Private Sub Command6_Click() Dim db As DAO.Database Dim rs As DAO.Recordset Dim I As Integer Set db = CurrentDb Set rs = db.OpenRecordset("Table1") With rs .AddNew 'For I = 1 To 3 '.Fields("Mov" & I) = Me.Controls("tbMov" & I) 'Next I ![Mov1] = Me.Controls("tbMov1") '<---- This works !["Mov1"] = Me.Controls("tbMov1") '<---- This doesn't work .Update .Close End With Set rs = Nothing Set db = Nothing End Sub
!["Mov1"] = Me.Controls("tbMov1") '<---- This doesn't work
because of the quotes inside the brackets? [ ] defines the field name so [field1], not ["field1"].
Either [Mov1] or ("Mov1") but not ["Mov1"].
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.
Yep, that's what I've found.