Search code examples
vbams-accessms-access-forms

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

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..

Solution

  • 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.