CODEWITHSUNDEEP
ms-accessvba
J.Scott
J.Scott

Reputation: 1

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

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.

Upvotes: 0

Views: 618

Answers (1)

Lenny Sockman
Lenny Sockman

Reputation: 101

If you're simply trying to assign the result of a query to the value of a field in a form, I'd recommend a DLookup() function. You can use it like this (assuming the form's control is a textbox):

Me.TextBox.Value = DLookup("FieldName", "QueryName")

More elaborate solutions would involve recordsets, etc. but if you're looking for a quick, simple solution this should do just fine. Hope that helps!

Upvotes: 0

Related Questions