Not in List

[Mclaren]

I have a form with a combo box on. The combo box lists all the products available. I have setup a pop up form that lists all possible instance of product codes that are available should a user only enter part of the Product code.

This is all working fine.

What i need to do is should the user choose a product from the list and double clicks on the required product code, i need the code to then be placed into the Combo Box on the first form. I currently save the text to a global variable and then close the popup. How do i then get the full product code to be inserted into the combo box ?



(i should mention, the combo box is a\on a form that is viewed in datasheet mode)

I have tried a few things but just cannot seem to get the selected product code to be inserted into the combo box.

Any help will be greatly appreciated.

[goodguy]

myGlobalVariable = myPopupListBox.Value
myComboBoxName = myGlobalVariable

[Mclaren]

on the ondoubleclick event of the product code field in the popup form, i pass the value to a global variable, my question is, once the popup form closes, how do i get the value to automatically populate the combo box ? I would really like to have this be an automatic process.

I tried the got focus event of the combo box, but to no avail.

[nicknameoscar]

Does the below link help?

http://www.access312.com/access/tip_1.html

Before I read that I was thinking you would have to update the table/query that the form (with the combo box) is based on and then requery the form and combo box. The above article would appear to confirm that if I am reading it correctly.

[Mclaren]

No, the link provides info on adding records to the combo box, which is not what i am trying to do, i have already added this functionality to the popup box if no records exists . See our Product codes can be quite long, and the first 3 to 4 numbers are the same depending on the division the product is associated to. eg : TEL12345 ; TEL56449 OR GMP12345 OR GMP56449.

When the sales rep call through orders, they then give the code eg. 12345 or 56449. when the user then enters the data supplied by the rep eg.12345, a pop up will show giving the different options of codes to choose from (those would be any codes in the DB that have eg. 12345 within the product code) so the user would then get to choose from eg. TEL12345 or GMP12345. the user would then double click on the code he wants and this value must be passed to the combo box on the first form.

The codes TEL12345 and GMP12345 are already existing in the combo box.

The popup form list possible entries should the user only type part of the product code into the combo box.

[goodguy]

I get it: your problem arises from the fact that you want to show the record on a datasheet. I guess you wouldn't be posting here if it were a regular or split form. To get a good look at how datasheets can be manipulated in VBA code, take a look at this.

[Mclaren]

Thanks for the link, Although the link is just for manipulating Data Sheet Properties. I am actually trying to resolve how i can get the selected Product code from the Popup form back onto the Orders form once the pop up form closes.

[Mclaren]

Right now, once the pop up form opens i Copy the text i need, then close the pop up and then paste the text into the combo box on the Orders from. surely this can be automated.

[goodguy]

Please attach your db.

[Mclaren]

will do, but tonite only. thanks GG, ill create a example of what i need as the DB has Propriaty code within.

[Mclaren]

Attached is a small section of the DB, with just the forms i have a problem with.

the main form to open first is the frm_Transaction_Transfer form. Once the form is open, type for example 5850 into the UPN field in the datasheet section of the form. Doing this opens a popup form with 6 entries. I would like to be able to double click on any one of the listed codes and have this (Full UPN Code) Automatically populate the UPN combo box. when the popup form closes the main forms datasheet section is back in focus and even the combo box is in focus, i just need to get the selected upn to automatically enter into the combo box.

[nicknameoscar]

Are you saying the selected upn is already in the recordset for the combo box and you just want to "go to" and "select" that selected upn? You are NOT trying to enter new data into the combo box?

If that is the case then I don't really have any ideas. I spent about a half hour earlier today Googling for answers. I would appear to find something but then it didn't seem to pan out. I found one forum question where the answer appeared to be to put this code in:

Me.ComboBox = rsSel("YourStringHere")

(where "YourStringHere" was the record in the combo box that you want to select)

but then I couldn't find any references to "rsSel". Maybe that is a VB function that is not part of VBA.

It is quite frustrating as I am very sure you know.

This must be a tough nut to crack since none of the crack pros here have weighed in :-) .

[Mclaren]

Are you saying the selected upn is already in the recordset for the combo box and you just want to "go to" and "select" that selected upn? You are NOT trying to enter new data into the combo box?

Exactly. Something so simple but i cannot get it to work.

[RuralGuy]

@McLaren,
The value you want is in the RowSource of the ComboBox but when you start typing the cbo does not scroll down to display that value? It should automatically try and search for that existing value in the RowSource.

[Mclaren]

@ RuralGuy, well the thing is, the user may opt to start typing the product code, omitting the first few digits. Anyways. after trying thinking about it a little harder about my options i have managed to solve it.

Once the user double clicks the option he wishes to use, the popup form calls a public function:

Private Sub UPN_DblClick(Cancel As Integer)

VstrUPN = Me.ProductID

Me.Caption = VstrUPN
DoCmd.Close acForm, "frm_product_Search_Popup", acSaveNo
Call Form_frm_Transaction_Transfers_Detail.InsertText

End Sub

Public Function InsertText()
    Me.cbo_UPNCode = VstrUPN
    VstrUPN = ""
    cbo_UPNCode_AfterUpdate
End Function

The function then inserts the text into the combo box and then sets the QTY field into focus.





I have added my working solution for you guys to see how i got it right.

Thanks to everyone for your assistance.