Results 1 to 14 of 14
  1. #1
    Join Date
    Feb 2012
    Posts
    8

    Red face Unanswered: Requery not working

    hi i have this problem requiring information of in a form

    i dont have any relationships build for this file because i am using look up field

    The reason for this is because i am using ONE table of 4k contacts with different people information to fill 4 different fields.(i did this because i could not make 1 to many relationships from this table with one to the different field of the Project List)

    i attached a sample of what i did. i made it in 5 min. but if has my problem

    PROBLEM: DOES NOT REQUERY WHEN I ADD NEW INFORMATION.
    NOTE: please dont forget to select category when u test to add new contact.

    i have a table of 4k contacts, this table has 2 form(just like outlook)

    the project uses thoses 4k contact and i use the criteria to limit the amountof contact on each field of "owner, client....." and not to have 4k people in my combo box

    for requery i used---> =[owner].[requery] in got on focus

    i tried to use bba command(cmd.requery (name)) / in click, before update

    thanks in advanced!

    i just have 4 weeks in access thank you so much!
    Attached Files Attached Files
    Last edited by 011billyw; 02-10-12 at 21:02. Reason: missing info

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by 011billyw View Post
    PROBLEM: DOES NOT REQUERY WHEN I ADD NEW INFORMATION.
    NOTE: please dont forget to select category when u test to add new contact.
    As far as I can see, everything works the way it's supposed to.
    1. I added a new project using the form "Project List" and kept that form open.
    2. I opened the form "VCA Full Contact List" and added a new contact, adding it to some categories.
    3. I switched back to the form Project List: the new contact I added was visible in the combos for the categories I linked it to.

    Notes: You would do yourself a favour and spare many headaches if you could refrain from using spaces and other non-alphanumeric characters (except the underscore) in the names of the objects (tables, columns, forms, etc.).
    You should also stay away from Lookup field in a table, that's one of the worst idea the Access team ever had (see: The Access Web - The Evils of Lookup Fields in Tables).
    Have a nice day!

  3. #3
    Join Date
    Feb 2012
    Posts
    8
    yes i see that i work, but since it is user friendly i don't want them to go open any forms. Which is why i created a not in the list event(this is where after adding the contact and closing that Contact List Form doesn't work)

    so if you are in the project form, and type a new name and press enter it will pop up a box, then it will look for the name in the ContactListForm and if you don't find it will take you to add a new contact. This is where the problem begins since when you type select the Category, save it and close it(while the Project Form is open). Then it doesn't re query and i don't see the new contact, lets say in the owner field.

    Please would you revise this. THANKS !

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Several problems come from the features I warned you about in my previous post. Moreover some procedures are imcomplete and there are flaws in the logic behind them. As it is you cannot even compile the project without errors.

    For instance:

    a) in the following procedure, the final End If instruction is missing:
    Code:
    Private Sub client_NotInList(NewData As String, Response As Integer)
    Dim MsgBoxAnswer As Integer
        Response = acDataErrContinue
    MsgBoxAnswer = MsgBox("Do you want to add " & NewData & " to the VCA List?", vbYesNo, "Add New Owner?")
    
    If MsgBoxAnswer = vbYes Then
        TextMiddenField = "AddOK"
        DoCmd.OpenForm ("VCA Full Contact List")
        DoCmd.FindRecord (NewData), , , , , acCurrent
        
        FoundRecord = MsgBox("Did you find " & NewData & " in the List?", vbYesNo, "Found Contact")
            If FoundRecord = vbYes Then
            Response = acDataErrContinue
                
                Else
                DoCmd.GoToRecord , , acNewRec
            End If
    Else
        Me.client = Null
        DoCmd.GoToControl ("Client")
    End If    ' MISSING End If INSTRUCTION.
       
    End Sub
    b) in the next procedure, there is a problem because of the space in the name of a control ("Construction Management") and the final End If instruction is missing too.
    Code:
    Private Sub Construction_Management_NotInList(NewData As String, Response As Integer)
    Dim MsgBoxAnswer As Integer
        Response = acDataErrContinue
    MsgBoxAnswer = MsgBox("Do you want to add " & NewData & " to the VCA List?", vbYesNo, "Add New Owner?")
    
    If MsgBoxAnswer = vbYes Then
        TextMiddenField = "AddOK"
        DoCmd.OpenForm ("VCA Full Contact List")
        DoCmd.FindRecord (NewData), , , , , acCurrent
        
        FoundRecord = MsgBox("Did you find " & NewData & " in the List?", vbYesNo, "Found Contact")
            If FoundRecord = vbYes Then
            Response = acDataErrContinue
                
                Else
                DoCmd.GoToRecord , , acNewRec
            End If
    Else
        Me.Construction Management = Null    ' NO SPACE IN CONTROL NAMES OR USE THE PROPER SYNTAX
                                             ' Me[Construction Management]
        DoCmd.GoToControl ("Construction Management")
    End If    ' MISSING End If INSTRUCTION.
    
    End Sub
    c) The problem due to a space character in the name of an object also exists for "General Contractor" in the procedure "General_Contractor_NotInList".

    d) You use variables that are not declared and are useless, such as in:
    Code:
    TextMiddenField = "AddOK"
    You should add a Option Explicit statement in the declaration section of every module and change the "Require variable declaration" setting of Access.

    e) The way you handle the NotInList events is flawed because:
    1. If the user answers "Yes" to the question "Do you want to add ...", you open the form "VCA Full Contact List":
    Code:
        DoCmd.OpenForm ("VCA Full Contact List")
    2. Then you try to find a record that you know does not exist (this is the reason why the NotInList event was triggered):
    Code:
        DoCmd.FindRecord (NewData), , , , , acCurrent
    3. Then you ask whether the record was found or not (???):
    Code:
        FoundRecord = MsgBox("Did you find " & NewData & " in the List?", vbYesNo, "Found Contact")
    4. If not, the form moves to a new (empty) record:
    Code:
    DoCmd.GoToRecord , , acNewRec
    (so that the user is able to add a new contact, I suppose) and the execution of the procedure ends because you did not open the other form ("VCA Full Contact List") in dialog mode, so there is no way inside the form "Project List" to know whether a new contact was actually added and when, which would be necessary to requery the combobox after the addition of this new contact.

    Adding:
    Code:
    =[Construction Management].[Requery]
    in the OnGotFocus property of the combo is not a solution and is useless to handle such cases as the combo already has the focum when the NotInList event happens.
    Last edited by Sinndho; 02-11-12 at 19:27.
    Have a nice day!

  5. #5
    Join Date
    Feb 2012
    Posts
    8
    Hello again, sorry about this.. like i said i started 5 week ago from scratch and i started learning about coding 1 week ago. i attack a copy or ORIGINAL FILE, and you will understand why there appears to have unnecessary coding.


    i deleted a lot of record and filed of information are blank for protection

    1. the Company has 6k contact. this is why i have a find button in case any contact becomes either a CLIENT, OWNER, OR ANY CATEGORY.

    this is why the owner list in the project form only has about 300 each.

    now when u type 1 that is not in the list, the code searches for that record, and if it exits allows the used to locate it and edit it it.( TRY IT PLS) if it in not i will take you to add a new record.
    then, they user has the option of double click the display and get to another form that has more field(like OUTLOOK)


    -------------------------------------
    sorry about this but i dont know what else to do or how to solve this. As you can see i cant use 1 to many relationships because i am using 1 table to fill multiple columns.


    i saw your post so i need to add that coding in the NOTINTHELISTEVENT?

    or u are saying that i am in an end corner? ((i

    i added an attachment of the file, it is the copy of the original with less info


    thank and sorry to waste your time bro
    Last edited by 011billyw; 02-11-12 at 20:08. Reason: added more information

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by 011billyw View Post
    1. the Company has 6k contact. this is why i have a find button in case any contact becomes either a CLIENT, OWNER, OR ANY CATEGORY.

    this is why the owner list in the project form only has about 300 each.

    now when u type 1 that is not in the list, the code searches for that record, and if it exits allows the used to locate it and edit it it.( TRY IT PLS) if it in not i will take you to add a new record.
    then, they user has the option of double click the display and get to another form that has more field(like OUTLOOK)
    Now I understand better, although this seems to be a rather unusual way of handling such cases (at least according to me), but why not?

    Personally, I would perform the search directly into the table (through a recordset) and if the name is found, I would inform the user that the contact name was found in such or such category and propose to add it to the category presently being handled:
    Code:
    Private Function ContactFound(ByVal ContactName As String) As Boolean
    
        Const c_Criteria  As String = "[Display Name] = '@N'"
        Dim rst As DAO.Recordset
        
        Set rst = CurrentDb.OpenRecordset("Contacts", dbOpenSnapshot)
        rst.FindFirst Replace(c_Criteria, "@N", ContactName)
        If rst.NoMatch = False Then ContactFound = True
        rst.Close
        Set rst = Nothing
        
    End Function
    The process of adding a new contact to a category would then be very straightforward in a normalized database, but this can be difficult when using the lookup and multi item features found in the new versions of Access, this is why I warned you agains them.

    Quote Originally Posted by 011billyw View Post
    i saw your post so i need to add that coding in the NOTINTHELISTEVENT?
    or u are saying that i am in an end corner?
    Nor one, nor the other: what I'm saying is that as the normal execution of the form "Project List" should be halted until the "NotInList" problem is fixed, which can only be done in the form "VCA Full Contact List", I would open open this later form in Dialog mode:
    Code:
    DoCmd.OpenForm ("VCA Full Contact List"), , , , , acDialog
    so that the execution of the code in the NotInList event handler halts while "VCA Full Contact List" remains open and only resume after it is closed. The combo could then be requeried

    You should try to fix the coding errors first (missing statements, etc.) and have a project that can be compiled first, though.

    Quote Originally Posted by 011billyw View Post
    thank and sorry to waste your time bro
    No time wasted, you're welcome!
    Have a nice day!

  7. #7
    Join Date
    Feb 2012
    Posts
    8
    thanks for replying
    so if i don't use the look-up field
    Now I understand better, although this seems to be a rather unusual way of handling such cases (at least according to me), but why not?
    how can i do the same thing with relationships?

    the reason i used it is because i am new to access and i thought that was the only way to to what i did.
    And sorry but what do you mean by"i should perform a search directly into the table (through a record-set)"?(sorry about my lack of knowledge about this Access). And where do i put the FIRST code you posted?

    i think that the original has the missing statement such as END IF. but i dont know how difference the statement since the coding i didn is throuh a combination research, a couple of videos from YouTube and links (i took 8 hours to even make sense of it ^^)i dont know if you can be me a hand in this.

    you only would need to do one since the rest of the field in the form have the same coding but with different fields.(or a link that can help me)

    (" I though my coding was goof for a beginner, i thought i was going to finish this Friday but idk how long this problem is gonna take me")

    Lots of Thanks!

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by 011billyw View Post
    And sorry but what do you mean by"i should perform a search directly into the table (through a record-set)"?(sorry about my lack of knowledge about this Access). And where do i put the FIRST code you posted?
    The code I posted precisely does perform that search. It can be pasted in the module of the form "Project List" and used in a construction such as:
    Code:
    If ContactFound(NewData) = True Then ...
    Quote Originally Posted by 011billyw View Post
    i think that the original has the missing statement such as END IF. but i dont know how difference the statement since the coding i didn is throuh a combination research, a couple of videos from YouTube and links (i took 8 hours to even make sense of it ^^)i dont know if you can be me a hand in this.
    VBA is a programming language with a precise syntax with which you need to comply. The fastest way to figure out what's wrong consists in trying to compile the project. Every syntax error will then be highlighted and an error message explaining the problem will be displayed.

    There are many sites dedicated to programming in VBA. See for instance:
    Microsoft Access Database Development
    MS Access Topics
    http://fisher.osu.edu/~muhanna_1/837...ls/vb_intr.pdf

    A very valuable source of information can be found at:
    The Access Web - Welcome

    Quote Originally Posted by 011billyw View Post
    you only would need to do one since the rest of the field in the form have the same coding but with different fields.(or a link that can help me)
    In every "NotInList" event handlers, you could have a single line invoking the same general procedure. This would make the maintenance of the code easier: if you need to modify something, you only need to change the code in one place.

    Quote Originally Posted by 011billyw View Post
    (" I though my coding was goof for a beginner, i thought i was going to finish this Friday but idk how long this problem is gonna take me")
    Don't be bitter and keep going: Rome was not built in a single day.


    Quote Originally Posted by 011billyw View Post
    Lots of Thanks!
    As usual: You're welcome!
    Have a nice day!

  9. #9
    Join Date
    Feb 2012
    Posts
    8
    i fell like i nub, welll i guess i am ^^

    ey where do i find the module of the FORM Project List?

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Simply open the form in design view and press the Alt + F11 keys.
    Have a nice day!

  11. #11
    Join Date
    Feb 2012
    Posts
    8
    can u please post some pictures?
    i pasted the code u gave me and putted the open form in dialog mode. and it still does the same thing but now takes a lot longer

    i fixed the errors u mentioned but i don't know what to do next

  12. #12
    Join Date
    Feb 2012
    Posts
    8
    bro with the coding i have i notices that in order for the changes of my editing to take place in the Project Form I ACTUALLY HAVE TO DELETE THE CURRENT DATA CLICK SOMEWHERE ELSE AND THEN IT WILL SHOW THE RESULTS

    Sorry is this caused because of the problems u mentioned before?
    Attached Files Attached Files

  13. #13
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    In Access, several forms can be open at the same time, each form running asynchronously in its own thread. Even if they share a set or a subset of common data, it does not mean that they are able to communicate.

    In your application, an exception (item not in list) must be cleared in a way or another (cancel entry or add to list). However, you try to solve the problem by opening another form independently. There is no way for the form "Projects Info" to "know" that the problem (item not in list) was solved somewhere else: both forms do not communicate (see above).

    What you describe:
    I ACTUALLY HAVE TO DELETE THE CURRENT DATA CLICK SOMEWHERE ELSE AND THEN IT WILL SHOW THE RESULTS
    happens because after the second form ("VCA Full Contact List") was open, you actually added a contact to the desired category. By deleting the contents of the textbox in the combo, then retyping something into it, the data set is refreshed (because of the Requery in the GotFocus event). If you had not modified the contact data in the second form, nothing would have changed and the "not in list" problem would remain the same (try it and you'll see what I mean). You should not expect the users of your application to always do what you suppose they should, believe me: they will not.

    This is a problem of logic in the workflow of your application, not simply a problem of syntax in the VBA code, even if there are still problems there too:
    there are several references to objects that do not exist (General_Contactor_Contact, General_Contactor, Combo69) and the project still cannot be compiled.

    Come to think of it, I wonder why you need to classify those contacts in several categories while any contact can pertain to any (or even all) of these and that the user can add any contact to any category at any time. Would it not be simpler to base the data set of the query on the whole contact list?
    Have a nice day!

  14. #14
    Join Date
    Feb 2012
    Posts
    8
    this is solved thanks guys!

    here is the link
    Microsoft Access tips: Adding items to a combo/list box

    how do i marke it solved?

Tags for this Thread

Posting Permissions

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