Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2014
    Posts
    22
    Provided Answers: 1

    Answered: Same code-different result: syntax error

    I have a drop down box with search options. For instance, drop down offers search parameters and then a button to open another form i.e. "Details" showing the results. I have created another drop down with different search options. This button would open the "Details" form but show the records sorted by the search parameters.

    The OnClick code I have for the first drop down box button works perfect. The second button does not. Below is the code for both.
    The one that works: DoCmd.OpenForm "Issue Details", , , "Status = '" & Me.cmb_ChooseStatus & "'"

    The one that doesn't: DoCmd.OpenForm "Issue Details", , , "Reported By = '" & Me. cmb_ChooseRepBy & "'"

    It looks like the same code to me, but when I click the button to run the second code, I get a syntax error:
    Run-time error '3075':
    Syntax error (missing operator) in query expression 'Reported By = 'Catherine Simmons".

    Catherine Simmons would be one of the search parameters.

    Please advise. Thanks,

  2. Best Answer
    Posted by Missinglinq

    "I believe that's because you have a Space in your Field name Reported By. Only alpha/numeric characters and the Underscore character should be used for Field/Object names. For this kind of thing, because of the Space, you need to enclose it in Square Brackets:

    DoCmd.OpenForm "Issue Details", , , "[Reported By] = '" & Me. cmb_ChooseRepBy & "'"

    Linq ;0)>"


  3. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    I believe that's because you have a Space in your Field name Reported By. Only alpha/numeric characters and the Underscore character should be used for Field/Object names. For this kind of thing, because of the Space, you need to enclose it in Square Brackets:

    DoCmd.OpenForm "Issue Details", , , "[Reported By] = '" & Me. cmb_ChooseRepBy & "'"

    Linq ;0)>
    Last edited by Missinglinq; 12-09-14 at 12:54.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #3
    Join Date
    Nov 2014
    Posts
    22
    Provided Answers: 1
    That partially worked. I took out the spaces on everything. New code below.
    DoCmd.OpenForm "Issue Details", , , "ReportedBy = '" & Me.cmb_ChooseOperationsStaff & "'"
    I get no results. Please help. FYI I went through choosing all of the drop-down options. However, when I choose one specific option, it returns the correct result, but only one of the records. That option is referenced in more that one record.

    Please advise

  5. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    First of does "I took out the spaces on everything" mean that you replaced the Field names in the Tables, as well as in code, removing the Spaces?

    Next, your original posted code was

    DoCmd.OpenForm "Issue Details", , , "Reported By = '" & Me.cmb_ChooseRepBy & "'"

    and your last posted code is

    DoCmd.OpenForm "Issue Details", , , "ReportedBy = '" & Me.cmb_ChooseOperationsStaff & "'"

    You've changed the Combobox you're comparing ReportedBy to.

    Quote Originally Posted by bscottj48 View Post

    ...when I choose one specific option, it returns the correct result, but only one of the records. That option is referenced in more that one record...
    Using the Where parameter with DoCmd.OpenForm is only intended to return a single Record, with the 'Where' usually being a value that is unique to a given Record. To return all Records with a matching 'option' would require Filtering the Form that is being opened to Records that match. You could pass the option value, using OpenArgs, then retrieve the OpenArgs when the Form opens and Filter the Form based on the OpenArgs.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

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