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
– June7, Commented Feb 6, 2020 at 23:26
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
– Alex, Commented Feb 8, 2020 at 15:49

June7 · Accepted Answer · 2020-02-08 18:21:10Z

1

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.

answered Feb 8, 2020 at 18:21

June7

21.7k8 gold badges28 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related