Pause for user input

[ineedahelp]

Can anyone help me...my msgbox seems to be disallowing my users from selecting a new symbol from my combo box called comboSelectSymbol. I ultimately want my users to be able to select any number of stock symbols from my combobox comboSelectSymbol and have these symbols added to a table called TempSymbol. There are over 4000 symbols to select from. Thank you for any help!

Private Sub comboSelectSymbol_AfterUpdate()
Dim MsgAnswer As String
Dim strSQL As String
MsgAnswer = 6
DoCmd.SetWarnings (False)
DoCmd.RunSQL "DELETE * FROM TempSymbol"
Do While MsgAnswer = 6
strSQL = "INSERT INTO TempSymbol (Symbol)"
strSQL = strSQL & " VALUES ('" & comboSelectSymbol & "');"
DoCmd.RunSQL strSQL
MsgAnswer = MsgBox("Do you want select another symbol? ", vbYesNo)
If MsgAnswer = 6 Then
Me!comboSelectSymbol.SetFocus
Me!comboSelectSymbol.Dropdown
End If

Loop
DoCmd.SetWarnings (True)
End Sub

[Tanis]

Is the symbol already in the table, or are you trying to add a new one?

[ineedahelp]

Is the symbol already in the table, or are you trying to add a new one?

All symbols are being pulled from a table called DailyPrice. The objective here is to allow the user to select any number of symbols from combo box...output to a temp table called TempSymbol...and then the program will use this list to perform other tasks.

[Tanis]

I have slightly modified your code. I have changed it so that the criteria refers to the combobox on the form. It looks like the loop is causing the problem. Take it out of the loop and it functions as it should. One question though,why are duplicating data?

Private Sub ComboSelectSymbol_AfterUpdate()
 
Dim MsgAnswer As String
Dim strSQL As String
'MsgAnswer = 6
DoCmd.SetWarnings (False)
DoCmd.RunSQL "DELETE * FROM TempSymbol"
'Do While MsgAnswer = 6
 
strSQL = "INSERT INTO TempSymbol ( TempSymbol ) "
strSQL = strSQL & "SELECT DailyPrice.Symbol "
strSQL = strSQL & "From DailyPrice "
strSQL = strSQL & "WHERE (((DailyPrice.Symbol)=[forms]![DailyPrice]![comboselectsymbol]));"
 
DoCmd.RunSQL strSQL
 
MsgAnswer = MsgBox("Do you want select another symbol? ", vbYesNo)
If MsgAnswer = 6 Then
Me!ComboSelectSymbol.SetFocus
Me!ComboSelectSymbol.DropDown
End If
 
'Loop
 
End Sub
 

[ineedahelp]

I have slightly modified your code. I have changed it so that the criteria refers to the combobox on the form. It looks like the loop is causing the problem. Take it out of the loop and it functions as it should. One question though,why are duplicating data?

Private Sub ComboSelectSymbol_AfterUpdate()
 
Dim MsgAnswer As String
Dim strSQL As String
'MsgAnswer = 6
DoCmd.SetWarnings (False)
DoCmd.RunSQL "DELETE * FROM TempSymbol"
'Do While MsgAnswer = 6
 
strSQL = "INSERT INTO TempSymbol ( TempSymbol ) "
strSQL = strSQL & "SELECT DailyPrice.Symbol "
strSQL = strSQL & "From DailyPrice "
strSQL = strSQL & "WHERE (((DailyPrice.Symbol)=[forms]![DailyPrice]![comboselectsymbol]));"
 
DoCmd.RunSQL strSQL
 
MsgAnswer = MsgBox("Do you want select another symbol? ", vbYesNo)
If MsgAnswer = 6 Then
Me!ComboSelectSymbol.SetFocus
Me!ComboSelectSymbol.DropDown
End If
 
'Loop
 
End Sub
 

I don't want to duplicate data. I want the use to be able to choose any number of symbols and store these selections into a temporary table. Should I be using multi-select instead? If so, do you know the code for this? thank you so much for help!!!

[PEB]

Hi,

I agree with Tanis that the loop is doing the problem...

Instaed using loop why don't you put an After update event on your combo box and so when the value is updated this After Update will run an other time the function... And so your cycle will function...?


Best regards

:)

[PEB]

Opa,

I've seen that your code is in after update event

So what is the need of your cycle?

U can make appear your msgbox without using a cycle, can't U?

With the same result I think?

if msgbox() =vbyes then
yourcontrol.setfocus
yourcontrol.dropdown
end if


:)