Not in list error, will not LinkCriteria????

[d9pierce1]

Views: 3 Size: 170.8 KB">Parkway Projects New.zip

Hi Everyone,
On my form (CompanyMembersFrm) I have a combo (CboContactID) and am trying to figure out if NotInList Event fires, I need to take the CompanyID from the CboCompanyID and the NewData from the CboContactID
to my form (ContactFrm) which will open if x=Yes... I have tried mutil ways to do this and currently using code below:

Private Sub CboContactID_NotInList(NewData As String, Response As Integer)
Dim strsql As String, x As Integer
    Dim LinkCriteria As String
x = MsgBox("Contact not in Current List, Would you Like to Add?", vbYesNo)
If x = vbYes Then
strsql = "INSERT INTO ContactTbl (CompanyID, ContactFirst) " & _
        "VALUES (" & CboCompanyID & ", '" & NewData & "')"
    'MsgBox strsql
    CurrentDb.Execute strsql, dbFailOnError
    LinkCriteria = "[CompanyID],[ContactFirst] = " & Me!CboCompanyID & " & Me!CboContactID & """
    DoCmd.OpenForm "ContactFrm", , , LinkCriteria
    
    
    Response = acDataErrAdded
Else
    Response = acDataErrContinue
End If


End Sub

This gets an error that i need to have titleID or something to that affect?
I basically just want the form ContactFrm to open if its not on list and carry over the CompanyID and Contact First
so i can continue filling out the rest of the data on the form.

This has to be something simple doesnt it?
Thanks

[ranman256]

you must specify each field:
(this assumes both fields are numeric)


LinkCriteria = "[CompanyID]=" & Me!CboCompanyID & " and [ContactFirst]=" & Me!CboContactID


but the ContactID should be unique, so you should only need to open on the ContactID. But if that works then good.

[d9pierce1]

OK,
So I changed it to this:

Private Sub CboContactID_NotInList(NewData As String, Response As Integer)
Dim strsql As String, x As Integer
    Dim LinkCriteria As String
x = MsgBox("Contact not in Current List, Would you Like to Add?", vbYesNo)
If x = vbYes Then
strsql = "INSERT INTO ContactTbl (CompanyID, ContactFirst) " & _
        "VALUES (" & CboCompanyID & ", '" & NewData & "')"
    'MsgBox strsql
    
    CurrentDb.Execute strsql, dbFailOnError
    LinkCriteria = "[CompanyID]=" & Me!CboCompanyID & " and [ContactFirst]=" & Me!CboContactID
    DoCmd.OpenForm "ContactFrm", , , LinkCriteria
    
    
    Response = acDataErrAdded
Else
    Response = acDataErrContinue
End If
End Sub

And when it runs, i get this error:

You cannot add or change a record because a related record is required in table "TitleTbl"

I do have a title field in the table but it is not set to required.

[d9pierce1]

So, I took out the default value of 0 in the title field in table. That ended that error but gave me a new one.

It does put into table but wont goes to debug. I beleive it has something to do with the ContactFirst field being text.
any ideas
Thanks

[d9pierce1]

I just dont know,
Have tried to debug this all day long and cannot get past this error.
Its not getting the value of CboContactID... Its always=0"

I have also tried the following:
LinkCriteria = "[CompanyID]=" & Me!CboCompanyID & " and [ContactFirst]=" & Me!CboContactID.Value
LinkCriteria = "[CompanyID]=" & Me!CboCompanyID & " and [ContactFirst]=" & Me!CboContactID.Column(2)
LinkCriteria = "[CompanyID]=" & Me!CboCompanyID & " and [ContactFirst]=" & Me!CboContactID.Text


Have put different "" "'" and such in different places.


I just dont get it. This cannot be this complicated to solve.

[Micron]

"VALUES (" & CboCompanyID...
CboCompanyID is invisible thus it is null. Why would you put a selectable control on a form and make it invisible? Even if you set a value to it in code, it makes no sense to me to make it a combo box.
If this is your only issue, then you could have found it by examining your sql string - very basic trouble shooting. I don't know if that's all that's wrong with this process but given the foregoing, I elected to stop there for now.

Surely you have been told to have Option Explicit at the top of EVERY code module?

[ssanfu]

There were several issues with your form, so I modified it (see attached).
I did not do anything with the "NotInList" code. I was just trying to get the form working.
I deleted the hidden combo box, changed the record source for the form and changed the row source for combo box "cboContactID". The combo box row source should not have had a "WHERE" clause.
I added two queries - prefixed with "qry".
I added "Option Explicit" to all of the Code Modules.


Curious table name - "CompanyMemberTbl". There are no "Members" or Member table. The junction table is linked to "ProjectTbl" and "ContactTbl", not to "CompanyTbl". I would have named it "ContactProjectTbl", but then again, no data from "ProjectTbl" is on the form....???

[d9pierce1]

Parkway Projects New.zip


Hi ssanfu
Thanks for taking a look at the forms. The reason that companyID was on the form is how i filtered the contacts. Once data all loaded i have like 25000 contacts, 10000 companies. I wanted my CbocontactID to only show the contacts for the company i was working with, not all compaies like is now.

My intentions of this is to be able to store my projects, companies, and contacts and use those tables to creat project team members (companies), there role, and what their scope(s) are per project. I may have a company listed for 5 things such as Burke Masonary. There role is a subcontractor yet they may have 5 different scopes of work within the project. Such as concrete, masonry, waterproofing.... and they i may have 200 contacts with them overall, yet i want to
be able to add contacts from table to a project, and the not in list i am having issues with would allow me to add from that page. The CboContactID should only show the contacts for that company, not all so thats why the where clause came into play.

In any event, I made some changes to my origional but still having an issue with this not in list. Everything else seems to work real smooth with this as i spent a lot of time inputting data today. The only issue i ran into was this not in list.
I have attached the DB again after i added data and made changes to the origional.

Thanks

[d9pierce1]

Parkway Projects Now.zip

Hi Micron

I will try to explain and yes i do know the option explicit but there is just a few lines of code in db and i was going to do that after i made sure all worked well. It just wasnt a major priority the last couple of days making this. As far as the hidden combos, i have them hidden so it cant be changed. These forms with the hidden combos on them are opened with a button which makes them not null. They are never null if opened by a button or form.

So, I have 5 forms on this totoal:
CompanyFrm
CompanyMemberFrm
ContactFrm
ProjectFrm
ProjectMemberSubFrm

ComapnyTbl stores company info, thats it. Form is used for data input. Has a button to view/add contacts for the company that is open.
ContactTbl stores the contact info for each company. Can be opened from either CompanyFrm by Contact Button or CompanyMemberFrm by means of a NotInList event fires by CboContactID. That
are the only two places that should open this ContactFrm.
ProjectTbl stores the Project info and has a sub form on form. Sub form is ProjectMembersFrm and this is designed to add Project Team Members (Companies) that are with this project. I add a company, then select a company role, and then select the scope of work on this subform. One company may be listed more than once as they have more then one Scope. My Mason may do Concrete, Waterproofing, and Masonary so they may be listed three times with different scopes. The button "Contacts" next to each line will open form "CompanyMemberFrm" to this specific company and allow me to select from a list the contacts that are involved with this specific project and only show the contacts for this specific company. If a new contact is entered and NotInList, then the event should fire and open "ContactFrm" to a new record but it needs to carry over the CompanyID at the least as you cant choose company on that form "ContactFrm"... I would also like it to add the first name "ContactFirst" but not fully necessary as long as it opens to new record and put the CompanyID in the Cbo (Hidden).... and on close, it should requery the CboContactID in the CompanyMemberFrm.

Keeping in mind that a company can have 100's of contacts and not all of them are on a single project, and some projects have the same companies on them with the same or different contacts.
I will do some reports once working so i can send out Sub List, Vendor List, and such....but thats next week....

I added some data today, all worked well once i did some tweeking with one exception of the form "CompanyMembersFrm" CboContactID NotInList Event. It puts it in the table yet still has an error. If i look into it, the LinkCriteria shows company ID is correct, but the CboContactID is Null or =0" yet it still puts the name in the table. Not sure whats going on, have tried multi methods and nothing.

In any event, thats you and hope i can get some assistance to figure this out. Its one issue and done!

I did just find one more issue, When a select a different project, and go to add a contact to the subform, it opens up but has all of that companies previously selected contacts in it. It doesnt separate them by ProjectID? Any more suggestions would just love....
Thanks

[Micron]

I went by post 1, meaning I opened from the nav pane. In that case, the combo has no value as I wrote. So what is the procedure?
It's 1:00 AM now and I have just spent 3 hours trying to fix Prime on my android tv (figured it out, but boy was it a run around). I'll have to review your prior post(s) much later on in this day.

[d9pierce1]

Hi Micron.
Thanks and glad you figured out your tv...watching tv use to be so simple
If you have time please take a look at it. I basically am having issues with the NotInList on CboContactID on form "CompanyMemberFrm" and I am also having issues with that same form with not being
distinct between projectId... If i change project, my current contacts are still in their from the last project. I just found that out last night but the post #9 has the db attached and what the intentions are.
I will create a switchboard and get rid of all the navigation pane and such once i finish up and get this to function as it should. Basically all runs as desired except those two issues.
Thank you
Dave

[Micron]

This is a struggle to follow. Here's what I see so far.
I can get the text of the company name into the contacts form when the button is clicked on project form with the following code. That is of little use in creating a new record.

DoCmd.OpenForm "contactFrm", acNormal, , "[CompanyID]=" & Me.CboCompanyID
Forms!contactfrm.TxtCompanyName = Me.CboCompanyID.Column(1)

Sorry, I don't want to fool around with embedded macros, so that was my approach.

I'm struggling with the following
a) I don't see why you'd have 2 representations of the same data on the contacts form. The listbox is a summary of each individual record, which you can navigate through. The listbox seems unnecessary - or the rest of the form is.
b) since the contacts form allows for new records, why are you worried about a not in list event for a contact?
c) don't understand "If a new contact is entered and NotInList, then the event should fire and open "ContactFrm"?? I thought the idea was to open contacts form via button? It doesn't open to a new record as you say it does. Should it? Again, the user can simply go to a new record?
d) I cannot create a new contact because your combo becomes Null as I mentioned before. It should be a textbox as I mentioned before. Perhaps if the form actually opened to a new record one could make the text and ID values be whatever they were on the projects subform record. How intuitive is your button caption though? I'd be clicking a button to see contacts (because that's what it says) only to find an empty form? What is the point of adding them from the project subform anyway? They don't appear there, so it seems disconnected to me. Plus, you already have a process to open the form for adding contacts (which I haven't vetted yet).

I can usually restrain myself from criticizing someone's efforts but I think in this case I have to say without malice but with respect that your design approach as a whole is a bit clunky, illogical and hard to follow. That should improve with experience. Regardless I think you will persevere with what you have. If that is the case, consider some of the points I made above and address what to do about them in order to slog on.

[d9pierce1]

Hi Micron
OK,
With your comment a) The list box is a summary of the contacts with that company. Just easy to pick from then using buttons to get that contact info to my txt boxes. One could use the arrow keys but when there are 300 contacts in one company that list box just makes it easier to scroll to find the contact you want, dbl click on it, and its full info is there on screen. Seems simple to me?

With comment b) |When in projects form, and i need to add a contact, i hit contacts button, if the contact i am adding is not in the list, then i want it to open the ContactsFrm to a new record and carry over the CompanyID to the ContactFrm, as i would want it to be that company i am adding the contact to, then close and return to pick from my list to add it to a sublist (CompanyMemberFrm)

With Comment c) Yes on first part. The company is related to contact(s) and a button is used from the company form to add new contacts or view them. The company is also related to the projects but not all companies, just ones i pick for that specific project. The contacts are also related to the specific Project and Company but not all the contacts. So, if i add a new company from the CompanyFrm i will also want to add contacts. Hit the button for contacts on that form and a pop up form opens to ContactFrm Where i can add contacts. When i am in the ProjectFrm, i select an existing company from dropdown and then i can select specific contacts once i hit the contact button next to it on the subform. that will open my CompanyMembersFrm where i can select from down down the contacts i want to add to this project. If not in list then i need to open up the contacts form, enter new data, and have that available in my dropdown CboContactsID. Its just a way to open the form if i need to add a new contact i dont have under that company. WE get new people all the time.

Look at it as one is a company and contacts, and the projects are a sub list of companys and contacts that belong to that project. Two separate parts... Hope that makes sense. I will have multi contacts from a specific company on many projects and could have different contacts on different projects from the same company but dont want all contacts to be in each project.

As Comment d) I guess i just dont understand how that becomes null? I think its looking for an ID when i jsut want it to put txt in the ContactFirst, not the ID... My naming of them may be some of the issue.

Summary,
If you get a new company you may want to open CompanyFrm and add the new data, then add contacts for that company at that time. Close and done.... If you are working with ProjectsFrm and you add a new Company to the subform, you want to add company contacts to that so you know who to talk to so you hit the contacts button, select one or more contacts for this project that relate to that specific company, then if not in the dropdown list, you need to add it. and thats where the notinlist comes into play. you open up your ContactsFrm to add a new contact, close and then select from the drop down and you have there info. There will be a few reports to follow but want to get this functioning correctly first before i create the reports. I need to store all companies and contacts yet only store specific companies and contacts to projects. Hope this clears that up

[d9pierce1]

Micron,
Maybe look at it like this?
I work in the field, dont always have internet available or its so slow i dont use it. I am a superintendent for commercial construction. I basically babysit and insure your favorite stores get open. With that said, i deal with subcontractors, clients, my office, vendors, suppliers and so forth. You have a company that has lets say 50 employees and I hire you as a subcontrator to do specific tasks for me. Your emplyees are assigned to my project(s) and I need to keep track of them to each specific project. Now you may send over billbob and stan to one of my projects, and Billybob and Glen to a different project. I need all of you in my companies and contacts yet i only need you, BillyBob, and Stan in this project and you billybob and glen in the other project so i know who is at what project. Hope this helps makes sense of this. I need to have phone numbers, emails addresses and names available no matter what and easy to access. If someone comes into my site office, he doesnt have 20 minutes to let me sign on to something, take his info, and such. This makes it easy and quick to get their info and have it available.

[Micron]

It seems that when I removed your macro and coded the opening of the form I elected to open the wrong form. I guess you didn't follow my last post otherwise you might have caught that I was referencing the wrong one. So now I have to digest your last 3 posts and go at it again - but later.
Sorry for the big mistake.