By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,576 Members | 1,462 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,576 IT Pros & Developers. It's quick & easy.

Pause for user input

P: 98
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
Sep 28 '06 #1
Share this Question
Share on Google+
6 Replies


100+
P: 143
Is the symbol already in the table, or are you trying to add a new one?
Sep 29 '06 #2

P: 98
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.
Sep 29 '06 #3

100+
P: 143
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?
Expand|Select|Wrap|Line Numbers
  1. Private Sub ComboSelectSymbol_AfterUpdate()
  2.  
  3. Dim MsgAnswer As String
  4. Dim strSQL As String
  5. 'MsgAnswer = 6
  6. DoCmd.SetWarnings (False)
  7. DoCmd.RunSQL "DELETE * FROM TempSymbol"
  8. 'Do While MsgAnswer = 6
  9.  
  10. strSQL = "INSERT INTO TempSymbol ( TempSymbol ) "
  11. strSQL = strSQL & "SELECT DailyPrice.Symbol "
  12. strSQL = strSQL & "From DailyPrice "
  13. strSQL = strSQL & "WHERE (((DailyPrice.Symbol)=[forms]![DailyPrice]![comboselectsymbol]));"
  14.  
  15. DoCmd.RunSQL strSQL
  16.  
  17. MsgAnswer = MsgBox("Do you want select another symbol? ", vbYesNo)
  18. If MsgAnswer = 6 Then
  19. Me!ComboSelectSymbol.SetFocus
  20. Me!ComboSelectSymbol.DropDown
  21. End If
  22.  
  23. 'Loop
  24.  
  25. End Sub
  26.  
Sep 29 '06 #4

P: 98
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?
Expand|Select|Wrap|Line Numbers
  1. Private Sub ComboSelectSymbol_AfterUpdate()
  2.  
  3. Dim MsgAnswer As String
  4. Dim strSQL As String
  5. 'MsgAnswer = 6
  6. DoCmd.SetWarnings (False)
  7. DoCmd.RunSQL "DELETE * FROM TempSymbol"
  8. 'Do While MsgAnswer = 6
  9.  
  10. strSQL = "INSERT INTO TempSymbol ( TempSymbol ) "
  11. strSQL = strSQL & "SELECT DailyPrice.Symbol "
  12. strSQL = strSQL & "From DailyPrice "
  13. strSQL = strSQL & "WHERE (((DailyPrice.Symbol)=[forms]![DailyPrice]![comboselectsymbol]));"
  14.  
  15. DoCmd.RunSQL strSQL
  16.  
  17. MsgAnswer = MsgBox("Do you want select another symbol? ", vbYesNo)
  18. If MsgAnswer = 6 Then
  19. Me!ComboSelectSymbol.SetFocus
  20. Me!ComboSelectSymbol.DropDown
  21. End If
  22.  
  23. 'Loop
  24.  
  25. End Sub
  26.  
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!!!
Sep 29 '06 #5

PEB
Expert 100+
P: 1,418
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

:)
Sep 30 '06 #6

PEB
Expert 100+
P: 1,418
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


:)
Sep 30 '06 #7

Post your reply

Sign in to post your reply or Sign up for a free account.