Results 1 to 14 of 14
  1. #1
    Join Date
    Sep 2011
    Posts
    36

    Unanswered: Prompted for parameter of query

    Im trying to create a simple work rota, I have managed to do everything having never used access before all except for the report printing. Basically the problem I am getting is that access is prompting for the filter parameter even though it already knows it. My database primary ID is the date, I have tried filtering just using "Date()" instead of my paramter to test the report and it works for todays report, but when I substitute back my search parameter it comes up with the prompt again.

    Here is the code im using


    DoCmd.OpenReport "HaemDailyReport", acViewPreview, , "Date1 = Date()"

    The one above works and pulls up the record for today, but what I really want it to do is to filter on the date I have just entered into my form. For this im using;

    DoCmd.OpenReport "HaemDailyReport", acViewPreview, , "Date1 = Me.Datebox"

    When I use this it comes up with a prompt asking for me.datebox, however if I go into debug mode and put my cursor over both me.datebox and Date1 in the code it displays the correct date from my form so I know its pulling in the date.

    Anyone any ideas?

    Thanks

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    You have to concatenate the form reference. Here's the syntax:

    Open a second form to the record
    Paul

  3. #3
    Join Date
    Sep 2011
    Posts
    36
    Thanks for that, spent hours yesterday trying to get that to work, got it working withing minute of your post

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Happy to help, and welcome to the site by the way!
    Paul

  5. #5
    Join Date
    Sep 2011
    Posts
    36
    Im getting another prompt for value problem, I have set up a combo box with a list that works from a table "HaemBMS" I have used some code that allows you to add to this list if the name you type dosent already exist using an SQL statement for the raw source "SELECT DISTINCT NameS FROM HaemBms ORDER BY NameS"

    This works fine, however I would now like to be able to delete from the list using the same combobox "BBSAM" With a double click, im using the folowing code and when I run it, it comes up with a prompt that is the name im wanting to delete from the combo box.


    Private Sub BBSAM_DblClick(Cancel As Integer)
    Dim strSQL As String
    Dim BBSAM As String


    SQL = "DELETE * "
    SQL = SQL & "FROM HaemBMS "
    SQL = SQL & "WHERE [NameS] = " & Me.BBSAM

    DoCmd.SetWarnings False
    DoCmd****nSQL SQL
    DoCmd.SetWarnings True
    End Sub

    Any ideas?

  6. #6
    Join Date
    Sep 2011
    Posts
    36
    If i cancel the prompt and go to debug, this is the message thats displayed if I put the curser over the DoCmd****nSQL SQL statment at the bottom of the script.

    SQL = "DELETE* FROM HaemBM WHERE [NameS] = Jak"

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If the value in BBSAM (and its counterpart [NameS] in the table) is not a number, the WHERE clause should be:
    Code:
    "WHERE [NameS] = '" & Me.BBSAM & "'"
    Have a nice day!

  8. #8
    Join Date
    Sep 2011
    Posts
    36
    The forum is starring out the dot run from my code, thats strange

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    No, it's because the '. r u' (without spaces) is treated as spam from a Russian site.
    Have a nice day!

  10. #10
    Join Date
    Sep 2011
    Posts
    36
    Excelent thanks alot

  11. #11
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

  12. #12
    Join Date
    Sep 2011
    Posts
    36
    Last question for tonight, the delete works pefect from the table however the name of the person is still displayed in the combox, I have tried the statement BBSAM.Requery but it doesnt get rid of the name even though its n0 longer in the drop down list. Any ideas how I can blank the combobox?

    Thanks

  13. #13
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    If the combo is bound, the value would likely still be displayed, even though not available in the list (because it's still in the underlying table). Try one of these:

    Me.BBSAM = Null
    Me.BBSAM = ""
    Paul

  14. #14
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The data set in the list part of a combo (or in a list) is not dynamic. You need to "refresh" the contents of the list, like this:
    Code:
    Me.BBSAM.Requery
    Have a nice day!

Posting Permissions

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