Return the result of a search

[Loc]

I think it would be best if I explained what I am doing.

I am making an inventory program and I am currently working on taking the items out of inventory or similar to selling an item, with multiple parts on one form. So to make things easy to understand I will just reference this as a sales form. What I would like to do is be able to do a search for an item because the employees don't always wright the correct part number. But the catch is when the parts manager clicks on that part in the search I want to take that item and put it into the sales form that he is currently working in. I have the code for the search I just don't understand how to make the code that will return the item I select to the sales form. Just keep in mind this will not be like just open a new form, the form is a multiple item form and already open.

[Dal Jeanis]

when the parts manager clicks on that part in the search

what is the exact name and type of control that the part number is in - and is it a listbox or combo box?

return the item I select to the sales form

what is the exact name and type of control that you want part number to be in - and is it a text box, a listbox or combo box?

Assuming, for example, that the manager was selecting the correct part number from a combobox called cbxPartNumber, and that the boundcolumn on cbxPartNumber was the Part Number you wanted, and that the selected part number was supposed to go into a text box called txtPartNumber on the same form, then the code would go in the AfterUpdate event of cbxPartNumber, and would look something like this -

Private Sub cbxPartNumber_AfterUpdate()
On Error GoTo Err_cbxPartNumber_AfterUpdate

'   Set the new value in the other control
    Me.txtPartNumber = Me.cbxPartNumber.Value

'   Now requery the form, uncomment if necessary
'   Me.Requery  
     
Exit_cbxPartNumber_AfterUpdate:
    Exit Sub
Err_cbxPartNumber_AfterUpdate:
    MsgBox Err.Description
    Resume Exit_cbxPartNumber_AfterUpdate
    
End Sub

If the control you want to change is not on the same main form as the control where the value has just been selected, then see this handy reference by Dev Ashish & Arvin Meyer for finding the right way to reference controls on any of the other possible combinations of main forms, subforms and sister subforms. http://access.mvps.org/access/forms/frm0031.htm

[Loc]

What I was going to do was use a button to open a search form that will have a txtbox and a button that says search & add to sales form. I was looking for then have him to click a button to add the part he has highlited to the sales form. But on the sales form it would return the information to a combo box. The combo box it will return no the sales form is called "cboParts". The combo box only shows 2 fields in this the Part Number and the Description and they are not the key field. The table I get the item from is called "Parts" and the fields I will use in my search are "PartNo", "Description", "ExtDescription" & "Machine". It will do a search using a txtbox and check all of these fields for a match.

Also that link you posted was very nice and was wondering if you know were I get information on the Me statement that is used. I have used it but only when I have found code that fits my needs I don't fully understand it.

[Dal Jeanis]

There's a more technical explanation available, but "Me" refers to the object (usually a form) that is currently active when the code is running.

For instance, if the code is within the event of a control on a subform, then "Me" is the subform, and Me.Parent would be the form. There are rules you should eventually memorize about when to use a "." and when to use a "!" to get the objects that are on the form, but for now you can refer to that page in post 2 to look it up until you get comfortable with the subject.

[Dal Jeanis]

Your search SQL will look something like this example, assuming the textbox is called txtSearch:

SELECT *
FROM Parts
WHERE ( (PartNo like "'*" & Me.txtSearch & "*'") 
OR (PartNo like "'*" & Me.txtSearch & "*'") 
OR (Description like "'*" & Me.txtSearch & "*'") 
OR (ExtDescription  like "'*" & Me.txtSearch & "*'") 
OR (Machine  like "'*" & Me.txtSearch & "*'"));

That code will not run as is - you'll have to adapt it to how you are doing the search, for example if it's in a control filter, you'll have to drop the "WHERE" and change around all the quotes as needed, or if it's in a stored query you'd probably want to use a temp variable to store the search string value of "'*" & Me.txtSearch & "*'".

[Loc]

I did the search in VB already and that works fine. I am a little confused on this code and it returns this information back to my sales order. I am opening the search in a separate form as I mentioned and looking to return the selected information when user clicks the add button.

[Loc]

So what I want to do is return the result from my sub form to the combo box.

[Dal Jeanis]

Okay, so I need the specifics. You have a subform that is showing information from which the user can select one of the returned items.

What kind of control is the information in on the subform? For instance, it could be displaying in a list box, a combo box, or a continuous subform that displays each row of data.

Suppose the "Add" button is on each row, in a command button control called (for example) cmdAdd. Then, behind that button, you will put VBA code in the Click event. That code will tell the combo box on the main form, the parent, to change to its new value, and then tell the parent to requery, if necessary.

Private Sub cmdAdd_Click()
On Error GoTo Err_cmdAdd_Click

' Set the new value in the other control 
Me.Parent!cbxPartNumber = Me.PartNumber

' Now requery the form, uncomment if necessary
' Me.Parent.Requery 

Exit_cmdAdd_Click: 
   Exit Sub

Err_cmdAdd_Click: 
   MsgBox Err.Description 
   Resume Exit_cmdAdd_Click 

End Sub

Use that same page http://access.mvps.org/access/forms/frm0031.htm to make sure that you are using the correct syntax to name the control on the parent form, to the left of the "=" sign. Use the correct name of the control on the subform that contains the value you want it set, to the right of the "=" sign.

Adjust the syntax until the results are doing what you want.

[Loc]

What I have is a Multiple Items form called UsedPartsForm built using a table called sellTableEnt & has 4 fields in it, SellID(Key Field), Date, PartID(Link to Parts Table) & Quanity(Yes it's misspelled in the table as well..lol). In the from I have 4 fields Date, Part Number(cboParts, A Combo Box Linked to table called Parts this is linked to this form by the field called PartsID), Description (Column 2 of the cboParts) and Quantity. sellTableEnt is a temp table for processing this information. The Process button is in the footer and does the whole form when the user is done.

as you know what happens sometimes is the part number the employees write is not correct and we need to do a search using the description they write down. I have a button on the bottom of this form that says search and a form pops up called UsedPartsSearch & searches the Parts table. In this search I have a Subform called UsedPartsSubform and this is were my search results is returned to(search works just fine). My field names are PartNo, Description, ExtDescription, & Machine. What I would like to do is be able to double click on the part I need in the result subform and have it return to UsedPartsForm and populate cboParts.

So I think I only need the PartID returned I think I am not sure. Thank you for the help if I did not make any sense in what I typed let me know. I have been up for the last 27 hours I work nights.

[Loc]

I hope this help. I took the tables and the forms I was using.

Thanks again..

Sample.zip

[Dal Jeanis]

Start from the bottom. You have a UsedPartsSubform with fields named PartNo, Description, ExtDescription, & Machine. You want to double click on the subform, and have it do something on the UsedPartsForm.

So, in the double-click for the subform itself, you'll be putting code like I gave you. Then, you have to find the control on UsedPartsForm that has the value you want to change.

If there is only one control on the main form UsedPartsForm, then this can be done. Look at the Not in these forms section of that page I sent you to at http://access.mvps.org/access/forms/frm0031.htm. That's how you refer to a form that is not the Parent or Child or Sibling of the current form you are on.

If there is more than one of the same control on the main form - for instance, if the UsedPartsForm is allowing multiple items to be entered at the same time, and the search could have been invoked from any of them, then this is a little more complicated, because the main form isn't the parent for the UsedPartsSubform, so you'll have to be passing some information around, probably using temp variables or linkage. I probably wouldn't be able to debug this kind of setup via posts like this.

Get some sleep, and then let me know if you have any other questions.

[Loc]

I got it! Thanks for all your help one more quick question how can I force this to return the result in a new or one with no PartID(control Name). I know it would be an if statement but I am not sure how to make look at this field and put it in the empty record.