HowTo Use a form to query results back to another field in form.

[WildCHR]

This is just a HowTo. I am more than likely putting this out here
because someday I will forget how it is done and want to come back for
a refresher. Or it could be that I want to help others, you be the
judge.

Say I have a list of SKU's that are linked to Vendors...

101537 - Lewis Family Bakers
101248 - Little Debbie
102467 - Mrs. Butter-Worth's
101754 - Peter Pan Peanut Butter

Ok, Besides me being a junk-food junky (FWI I only weigh 170lbs.) say I
wanted to create a form that when I entered that SKU number in a text
box, that it would then fill out the Vendor for me on the form.

My query on the list of SKU/Vendor is a basic query, but to get the
information back to the form, believe it or not, has to come from
another form. So I build a form to read my query. It has two fields,
one for the SKU, the other for the Vendor. I called the from
SKU2Vendor. On the form where I enter the SKU, I build code on the
event LostFocus. It looks like this...

Private Sub SKU_LostFocus()
On Error Resume Next
DoCmd.OpenForm "SKU2Vendor", , , , , A_HIDDEN
[Forms]![MyForm]![Vendor] = [Forms]![SKU2Vendor]![Vendor]
DoCmd.Close A_FORM, "SKU2Vendor"
Me.Refresh
End Sub

This takes the value from the Form SKU2Vendor in the field "Vendor" and
puts that value into the Form MyForm in the field Vendor.

A simular topic as discussed some time ago at
http://groups.google.com/group/micr...&q=access+query+results+to+form&rnum=10&hl=en
but it failed to outline what needed to take place.

Regards,
Me.WildCHR

 

[WildCHR]

Oh, but of course I would have forgot to mention that the Query is a
parameter query, and the criteria for the SKU in the query is the SKU
on the MyForm.

This can be achieved by right clicking in the criteria area and
choosing build. Then follow your way through the All Forms to the form
MyForm.SKU.Value then click OK. This will force the query to only pick
one SKU and one Vendor to match that SKU from MyForm.

Regards,
Me.WildCHR