Access 2013:Trying to write macro to update form fields from a query result

Question

I am trying to create a macro to update the value of a field in a form based off the results of a query.

I am entirely new to working with vba in Access so I apologize if I am asking a basic question.

I should mention, the "test" query returns exactly one result. It is essentially used similarly to a VLookup.

Currently My code is thus:

Private Sub UpdateBasic_Click()
  Dim bucket As String
  DoCmd.OpenQuery "test", acViewNormal, acReadOnly
  'this line is meant to record the result of the query into a variable. It is not working but I haven’t found the right command to get it to pick up the data yet.
  bucket = A1
  DoCmd.Close acQuery, "test", acSaveNo
  DoCmd.OpenForm "BasicData", acNormal, , , acFormEdit, acWindowNormal, "Global_ID = 'sdkfa'"
  'this line is meant to update the value of the field on the form.
  DoCmd.SetProperty testfield, acPropertyValue, bucket
End Sub

I am having no luck getting the SetProperty command to work at all. It is consistently telling me I have a data type mismatch regardless of whether I try to give it a variable like bucket or a value like 10. Error message is as follows:

Run-time error ‘2948’: An Expression you entered is the wrong data type for one of the arguments.

Any and all help would be appreciated.

score 0 · Answer 1 · answered Mar 23 '15 at 19:41

DoCmd.SetProperty testfield, acPropertyValue, bucket

Using the above code, you are attempting to set the property for the control, not change the value. A property would be background color, height, width, etc.

If want to set the value, you need to simply use the me function to refer to control on a form.

me.testfield = bucket

This tells access to populate the form control named testfield with the value being held by the variable bucket.

Note: You may need to add a me.refresh after that command to get the form to update the fields.

Access 2013:Trying to write macro to update form fields from a query result

1 Answers1