setting form field to the results of a vba query 1

[jwrocko]

I want to be able to populate a form field using a query.
The fields on the form are: Part Number, Quantity, and Vendor ID. My form, on click, will check to see if I have multiple vendors available for the specified part number. If there is only one, I want the field to be populated with the specific vendor id, otherwise if (vendors > 1) then I have a modal form that comes up with a list of the vendors for the specified part (this is already working). I am able to specify a vendor for each part, but I only want to have to do that if more than one vendor exists. So, what I guess I am asking for is a way to dynamically set the default value of my vendorID field to a valid number.

 

[GingerR]

So in your form's OnOPen event, or Current event, or maybe when you hit a button that says "Get Vendors"....you can put code that says this (you'll have to tweak for your control and object names):

dim intVendorCount as Integer

'Determine number of Vendors for this PartNumber
intVendorCount = dcount("VendorID","PartVendorTable","PartNumber = " & PartNumber)

'If intVendorCount = 1 then put the VendorName in the text box on the form.
'If intVendorCount > 1 then open the modal form
Select Case intVendorCount
 Case 0
     Exit Sub
 Case 1
     Me.VendorIDTextBox = dlookup("VendorID","PartVendorTable","PartNumber = " & PartNumber)
  Case > 1
    Docmd.OpenForm "FormWithMultipleVendorsListed"

End Select

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[jwrocko]

Thanks GingerR - it works exactly like I want.