Results 1 to 11 of 11

access vba misscoding that disables the REQUERY

  1. #1
    011billyw is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Feb 2012
    Posts
    31

    access vba misscoding that disables the REQUERY

    hello all sorry for interrupting, i think my access file has some miss coding since i started from scratch in this project for my boss.

    This Access has 2 Purposes,
    1.- to input new Projects in the Form ( Client,Owner,General Contractor,Construction Management)
    2. To check for new contacts.(this looks a little like outlook, it has a double click event)

    Someone told me the problem is in the NotInTheList Event(he told me that it didnt allow my current form to requery) I am using LOOKUP FIELD because i dont know how to make a realtionship of one table with mutiple field in another table.

    PROBLEM= When i type an owner or any type of contact in the form "Projects Info", it ask if i want to add, then the code searches for a record in the list of 7k contacts.(i purposely deleted, and altered the information for privacy reasons). and asked if the contact was found(if chek yes, then i will take you to the contact, if chek no it will take u to add newrecord.)



    then i close it and the new contact is not requeried in the Field of the form Project Info.

    Please help me to work with the code, i only have 2 weeks learning about coding without any help

    Thank You in advanced.

    I ADDED AN ATTACHEMENT

    i could add my attachement so i will paste the code for NOt in the list Event
    Code:
    Private Sub Owner_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.Owner = Null
    	DoCmd.GoToControl ("Owner")
    
    			
    End If
    End Sub
    i added the attachment it works now


    This is the coding that some guys gave me and told me to paste in the the Project List form Module
    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
    Last edited by 011billyw; 02-13-2012 at 12:45 PM. Reason: more info

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,257
    Your issue is solved?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    011billyw is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Feb 2012
    Posts
    31
    no it is not...

    But i just tried adding new contacts and updating, the only way it works is when after closing the Contact List, i have to clear the field and click somewhere else for it to requery it

    i dont know if this is because of the coding problem

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,257
    The posted project does not include the ContactFound function nor is there a form called Project List.

    Review this article about using ListItemsEditForm property and the NotInList event http://www.everythingaccess.com/tuto...-lookup-tables
    Last edited by June7; 02-15-2012 at 12:15 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    011billyw is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Feb 2012
    Posts
    31
    thanks men i will try it and if i can solve it i will mark the post as solved
    --------
    quick question, i have a list of 7 k people and each of them is divided in 7 categories.

    Lets say that before adding a new Record in the Owner Category, i want to search if there is a record with that name first, but i am not sure is the code provided in that page helps
    Code:
    Using another event to open a form
    To avoid these limitations, you could choose another event to pop up the form to edit the list. Perhaps the combo's DblClick event, a custom shortcut menu, or the click of a command button beside the combo. This approach does require some programming. There are several issues to solve here, since the edit form may already be open. 
    
    Add code like this to the combo's event:
    
    Private Sub CustomerID_DblClick(Cancel As Integer)
        Dim rs As DAO.Recordset
        Dim strWhere As String
        Const strcTargetForm = "Customers"
        
        'Set up to search for the current customer.
        If Not IsNull(Me.CustomerID) Then
            strWhere = "CustomerID = """ & Me.CustomerID & """"
        End If
        
        'Open the editing form.
        If Not CurrentProject.AllForms(strcTargetForm).IsLoaded Then
            DoCmd.OpenForm strcTargetForm
        End If
        With Forms(strcTargetForm)
        
            'Save any edits in progress, and make it the active form.
            If .Dirty Then .Dirty = False
            .SetFocus
            If strWhere <> vbNullString Then
                'Find the record matching the combo.
                Set rs = .RecordsetClone
                rs.FindFirst strWhere
                If Not rs.NoMatch Then
                    .Bookmark = rs.Bookmark
                End If
            Else
                'Combo was blank, so go to new record.
                RunCommand acCmdRecordsGoToNew
            End If
        End With
        Set rs = Nothing
    End Sub
    Then, in the pop up form's module, requery the combo:
    
    Private Sub Form_AfterUpdate()
    On Error GoTo Err_Handler
        'Purpose:   Requery the combo that may have called this in its DblClick
        Dim cbo As ComboBox
        Dim iErrCount As Integer
        Const strcCallingForm = "Orders"
        
        If CurrentProject.AllForms(strcCallingForm).IsLoaded Then
            Set cbo = Forms(strcCallingForm)!CustomerID
            cbo.Requery
        End If
        
    Exit_Handler:
        Exit Sub
        
    Err_Handler:
        'Undo the combo if it has a partially entered value.
        If (Err.Number = 2118) And (iErrCount < 3) And Not (cbo Is Nothing) Then
            cbo.Undo
            Resume
        End If
        MsgBox "Error " & Err.Number & ": " & Err.Description
        Resume Exit_Handler
    End Sub
    
    Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As Integer)
        If Response = acDeleteOK Then
            Call Form_AfterUpdate
        End If
    End Sub

  6. #6
    011billyw is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Feb 2012
    Posts
    31
    I played with the Code and it Works perfectly, except that i dont know how to change the code to look for existing Records with the Same Display_Name

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,257
    I would have to analyse project.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    011billyw is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Feb 2012
    Posts
    31
    Thanks man that would be appreciated...

    Sorry about this but i think that is not the coding to see if the contact already exist, since that is noly for the double Click Event.

    Thanks

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,257
    I mean provide your current version of project and I will look at.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    011billyw is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Feb 2012
    Posts
    31
    bro i need your msn because the file is 20,000 kb of zise, and the forum only has a 20k kb limit

    ty

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,257
    2mb zip is allowed in the forum.

    Could try extracting just the relevent objects and code.

    Or upload to fileshare site such as box.com and post link to the file. Be sure to run Compact & Repair first and zip a very large file for faster download.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Access Requery of underlying Form
    By tcheck in forum Access
    Replies: 1
    Last Post: 11-17-2011, 10:58 AM
  2. Requery
    By Grizz2 in forum Queries
    Replies: 2
    Last Post: 05-31-2011, 10:23 AM
  3. Access novice help with refresh/requery
    By cvacgreg in forum Access
    Replies: 3
    Last Post: 02-04-2011, 08:51 PM
  4. Replies: 1
    Last Post: 08-19-2010, 01:08 PM
  5. Replies: 5
    Last Post: 11-10-2009, 01:16 PM

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
  •  
Tech Forums: Microsoft Office Forums