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 comboSelectSymb ol. I ultimately want my users to be able to select any number of stock symbols from my combobox comboSelectSymb ol 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 comboSelectSymb ol_AfterUpdate( )
Dim MsgAnswer As String
Dim strSQL As String
MsgAnswer = 6
DoCmd.SetWarnin gs (False)
DoCmd.RunSQL "DELETE * FROM TempSymbol"
Do While MsgAnswer = 6
strSQL = "INSERT INTO TempSymbol (Symbol)"
strSQL = strSQL & " VALUES ('" & comboSelectSymb ol & "');"
DoCmd.RunSQL strSQL
MsgAnswer = MsgBox("Do you want select another symbol? ", vbYesNo)
If MsgAnswer = 6 Then
Me!comboSelectS ymbol.SetFocus
Me!comboSelectS ymbol.Dropdown
End If

Loop
DoCmd.SetWarnin gs (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...an d 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.set focus
yourcontrol.dro pdown
end if


:)