Requery not working

011billyw · #1 (**permalink**) 02-10-12, 19:57

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!

Sinndho · #2 (**permalink**) 02-11-12, 12:29

Quote:

Originally Posted by 011billyw

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).

011billyw · #3 (**permalink**) 02-11-12, 15:23

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 !

Sinndho · #4 (**permalink**) 02-11-12, 18:22

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.

011billyw · #5 (**permalink**) 02-11-12, 19:05

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

Sinndho · #6 (**permalink**) 02-12-12, 02:53

Quote:

Originally Posted by 011billyw

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

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

thank and sorry to waste your time bro

No time wasted, you're welcome!

011billyw · #7 (**permalink**) 02-12-12, 03:28

thanks for replying
so if i don't use the look-up field

Quote:

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!

Sinndho · #8 (**permalink**) 02-12-12, 04:02

Quote:

Originally Posted by 011billyw

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

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

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

(" 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

Lots of Thanks!

As usual: You're welcome!

011billyw · #9 (**permalink**) 02-12-12, 14:23

i fell like i nub, welll i guess i am ^^

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

Sinndho · #10 (**permalink**) 02-12-12, 15:23

Simply open the form in design view and press the Alt + F11 keys.

011billyw · #11 (**permalink**) 02-12-12, 17:21

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

011billyw · #12 (**permalink**) 02-13-12, 14:26

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?

Sinndho · #13 (**permalink**) 02-14-12, 14:20

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:

Quote:

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?

011billyw · #14 (**permalink**) 02-15-12, 18:23

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?

Internet Services

Web Development

Digital Marketing

Consumer Tech