Assigning a textbox value to a multiple table/query field values

Question

I have a Main form in formview and a related subform in datasheet view asking results to the same query.

In the Main form i have a "free" textbox that is not linked to any query field. This textbox is an "input" value that i want to use for a module/function that will assign the txtbox.value to ALL the values under a query field. Example:

Main Form:

Private Sub Command1_Click()
Dim txtbox As String
Dim qryField as String
Dim FieldName as String
Dim subfrm as Object

txtbox = textbox.Value
FieldName = "Data"
qryField = Me.RecordSource & "." & FieldName
subfrm = subfrmOfMain

Call AssignValueToAllFieldValues (subfrm, txtbox, qryField)
End Sub

In the module/function:

Public Sub AssignValueToAllFieldValues (Byval subfrm as Object, Byval txtbox as String, Byval qryField as String)

For each qryField.value in subfrm.qryField
  "assign txtbox.value to qryField.value"
Next
End Sub

This doesn't work of course..

Code like CurrentDb.Execute "UPDATE tablename SET fieldname=" & Me.textboxname & "'". If field is number type remove apostrophe delimiters, if it is date/time type use # delimiter. Use a WHERE clause if need to restrict records. — June7
I was trying with your solution many times till at i understood that i have to use delimiters even in the WHERE clause... Now all works well! Plz make your comments as a solution to my question so i can flag as a good solution! — Alex

June7 June7 · Accepted Answer · 2020-02-08T18:21:10

Update field in underlying table with an UPDATE action SQL. Code like:

CurrentDb.Execute "UPDATE tablename SET fieldname='" & Me.textboxname & "'".

Remove apostrophe delimiters for number type field; for date/time type use # delimiter. Use a WHERE clause if need to restrict records. Apply delimiters as needed for parameters in WHERE clause as well.

Assigning a textbox value to a multiple table/query field values

1 Answers