Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hi! (Access97)

    <P ID="edit" class=small>(Edited by Francois on 19-Jan-05 19:04. add listbox comment)</P>What if you use :
    sSQL = "Insert Into [tbl_Date] Select [tbl_500 - Data].[Pay Date] FROM [tbl_500 - Data]"

    If lst_Cutoff_Dates is a listbox you can't set his rowsource to a Insert SQL statement. You have to use a Select statement
    Francois

  2. #2
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hi! (Access97)

    Oh, allright, I've tried
    sSQL = "Select [tbl_500 - Data].[Pay Date] FROM [tbl_500 - Data]" with the same "success" LOL
    Thanks

  3. #3
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hi! (Access97)

    Ok, when I am running it as a Module - table gets populated.
    When I run it on Form cmd Click event I am still getting that error.

    Public Function cmd_Run_Click()
    Dim sSQL As String
    Dim dbR As DAO.Database

    Set dbR = CurrentDb()
    DoCmd.SetWarnings True

    sSQL = "Insert into Data_table SELECT [tbl_500 - Data].[Pay Date] FROM [tbl_500 - Data];"
    dbR.Execute sSQL
    lst_Cutoff_Dates.RowSource = sSQL

    End Function

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hi! (Access97)

    You are still trying to use a Insert into as rowsource for your listbox.
    What do you exactly want ?
    Populate the table Data_table with your pay dates in the tbl_500-Data ?
    Then you haven't to insert them into a table.
    Just set the rowsource of the listbox to :
    SELECT [tbl_500 - Data].[Pay Date] FROM [tbl_500 - Data]
    Francois

  5. #5
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hi! (Access97)

    I am getting error that I can only run Action query which is Select Query not.
    This is why I am changing it to Append Query.

    I want this code to run on Form event cmd_Click and it does not !
    But it does run when written as a Module.
    That's it...

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hi! (Access97)

    OK, let me ask it in another way :
    What is the purpose of this line:
    lst_Cutoff_Dates.RowSource = sSQL
    What are you trying to achieve with this line ?
    Francois

  7. #7
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hi! (Access97)

    There is a Form with a ListBox
    ListBox contains Dates to Select
    When Date Selected in ListBox - Report will be run for this particular Date

    Thanks

  8. #8
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL as row source for list box (Access97)

    Subject edited by HansV to be more informative than 'Hi!'

    Hi!
    Trying to execute code

    Private Sub cmd_Run_Click()
    Dim sSQL As String
    Dim dbR As DAO.Database

    Set dbR = CurrentDb()
    DoCmd.SetWarnings False

    sSQL = "Insert Into [tbl_Date] Select Distinct [Pay Date] FROM [tbl_500 - Data]"
    DoCmd.RunSQL sSQL
    lst_Cutoff_Dates.RowSource = sSQL

    End Sub

    Getting error
    The expression on click you entered ... produces ... error
    The expression may not result in the name of macro
    There may have been an error evaluating the function...

    Do you see anything wrong with my code? Thanks

  9. #9
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hi! (Access97)

    If with the line
    lst_Cutoff_Dates.RowSource = sSQL
    you want to populate the listbox try this code :

    Public Function cmd_Run_Click()

    lst_Cutoff_Dates.RowSource = "SELECT [tbl_500 - Data].[Pay Date] FROM [tbl_500 - Data];"
    lst_Cutoff_Dates.Requery

    End Function
    Francois

  10. #10
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hi! (Access97)

    I was just trying to run simple Update

    Private Sub txt_From_AfterUpdate(Cancel As Integer)
    ReportEndDate = CDate(Me!txt_From)
    MsgBox "edUpdated"

    End Sub

    and i've got the same error message. Could that be my references are off or something?
    Once HansV told me to use ADO.database instead of Db.Database and it fixed the issue. I suspect the same thing is going on now. Thanks

  11. #11
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Hi! (Access97)

    Hans would never had told you to use ADO.database, since there is no such thing, although he might very well have told you to use ADODB.Recordset for something. Furthermore, the AfterUpdate event has no cancel argument. Invalid code is always going to give you error messages, and they won't be fixed by references.

    You cannot use an action query as a rowsource for anything, if for no other reason than the fact that action queries do not return rows. Are you trying to run an update query or are you trying to do something else. Earlier in this thread you indicated
    <hr>When Date Selected in ListBox - Report will be run for this particular Date<hr>
    . Instead of asking why the code doesn't work, try explaining what you want it to do, in detail. Then someone can help you.
    Charlotte

  12. #12
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hi! (Access97)

    Now you've lost me !
    You say that you want to run an Update, using an Insert statement where you should use a select statement.

    For the references, in the VBE editor, select Tools, References and see if you have some that start with MISSING...
    If so uncheck them and try to run the code .
    Francois

  13. #13
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hi! (Access97)

    No need to shout, Charlotte, people do make mistakes and HansV told me to use DAO.Database ofcourse!

  14. #14
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hi! (Access97)

    Francois,
    I used Update example just to see if any simple code will work within my Form and it does not work.
    As I said code do execute when run as a Module and do give an error when Form used.
    Thanks

  15. #15
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hi! (Access97)

    If you want you can attach a stripped version of your db and I'll have a look at it tomorrow. See <!profile=HansV>HansV<!/profile>'s <post#=401925>post 401925</post#> to get it under the 100K limit.
    Francois

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •