Results 1 to 6 of 6
  1. #1
    Madmax is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Jun 2011
    Posts
    80

    Help on where clause of open form

    Good Afternoon,
    New question has been brought about, Can you tell me what is wrong with my SQL Where Expression below. The form itself is unbound, The table lists there name and SSN which the SSN is the primary key of each person.

    I have currently,

    =[Forms]![Master Index Sheet]![Combo23]=[Tables]![Personnel Information]![SSN]

    Tells me the automation object tables doesnt exist.

    I have tried other variations as well to include SSN = SSN and everything. The only way I get it to work is if the form on open is bound to the table but then my other selects for other tables on same form will not work that I will need on this.

    Please let me know what you think,

  2. #2
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272
    You can't refer to tables using Tables!SomeField. It dosn't work. What is it you are really trying to do?

  3. #3
    Madmax is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Jun 2011
    Posts
    80
    I knew that, don't know what I was thinking. What I am trying to do is have a combo box with a list of names populated that when picked will use the SSN and match it to the SSN of the record and pull that record up in the new form. Typically, this is easy on regular forms because the record is selected already on the 1st form so it is just a button. However, this particular Form is not bound to any table. I didnt want to bind it to that table though because I was going to have more combo boxes do the same thing for other tables as well. The whole idea is to make it so a security manager who knows nothing about access can go in from one sheet and pull everything he needs by looking up the name inside the combo box of the item he needs. Clear as mud?

  4. #4
    Madmax is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Jun 2011
    Posts
    80
    Think I know the problem. The first field is where to restrict in the new form. I thought it was the otherway. Will try the below code when I get the time. ="[field name]='"&combo23&"'"
    I think there are supposed to be quotes in it too. Will look. However is that the correct way to name a combobox?

  5. #5
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272
    Still not sure what you are using this for. I think you said it was for a SQL WHERE Clause, but it isn't clear since you haven't given much to go on (like the rest of the SQL). And you have two equal signs which one of them shouldn't be there. And the combo should have a name representative of what it is. Combo23 is a bad name and doesn't help much for self documenting code.

    Now, if the field is numeric it would be (for example in a Where clause):

    Where [FieldNameHere]= [Forms]![FormNameHere]![Combo23]

    And if in code the SQL would have

    " WHERE [FieldNameHere] = " & Forms!FormNameHere.Combo23

    or if the field is text

    " WHERE [FieldNameHere] = " & Chr(34) & Forms!FormNameHere.Combo23 & Chr(34)

  6. #6
    Madmax is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Jun 2011
    Posts
    80
    K Access calls it an SQL Where Condition. Its under Macros I figured out the final part though. in the end the correct statement was in the arguments tab.

    Openform, PRP Tracking Form, Form, , ="[SSN]='" & [Combo39] & "'", , Normal

    The where condition is one of the fields in the bottom section of the macro builder. I dont know why this works but it works and is solved.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Using the TOP clause
    By WSlepecki in forum Queries
    Replies: 1
    Last Post: 04-08-2011, 06:59 AM
  2. Where clause
    By Amerigo in forum Queries
    Replies: 2
    Last Post: 03-30-2011, 07:34 AM
  3. Replies: 2
    Last Post: 02-26-2010, 08:14 AM
  4. IIF clause
    By Peljo in forum Queries
    Replies: 2
    Last Post: 02-05-2008, 11:22 AM
  5. Help on WHERE clause
    By QBCM in forum Programming
    Replies: 1
    Last Post: 12-19-2005, 08:43 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums