Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2007
    Posts
    25

    Unanswered: Comparing form listbox ID to Table ID for each Record matching...

    This is the rowsource of the list box.

    SELECT [AddressBook].[ID], [AddressBook].[Category], [AddressBook].[CompanyName] AS [Company Name], [AddressBook].[LastName] AS [Last Name], [AddressBook].[FirstName] AS [First Name], [AddressBook].[JobTitle] AS Title, [AddressBook].[Department], [AddressBook].[ContactMethod] AS [Contact Method] FROM AddressBook WHERE ((([AddressBook].[CompanyName]) Like [Forms]![frmChooseContact]![SortBy] & '*')) ORDER BY [AddressBook].[CompanyName];

    I need via VBA to check to see if the highlighted listbox item (Me.Contacts) is already in tblProjectContact Under ContactID field, but only those entries whose ProjectNo field is the same as Me.ProjectNumber

    So should be something like

    For Each Projectnumber in tblProjectContact that = Me.ProjectNumber
    If Me.Contacts = tblProjectContact.ContactID
    MsgBox "User already added to project"
    Else
    Dim rs As Recordset
    Set rs = CurrentDb.OpenRecordset("tblProjectContacts")
    rs.AddNew
    rs!ProjectNo = Me.ProjectNumber
    rs!ContactID = Me.Contacts
    rs.Update
    Set rs = Nothing
    End IF


    I've got the concept down, just not the correct syntax.

    Thanks,

    RJ

  2. #2
    Join Date
    Dec 2007
    Posts
    25
    I think I am on the right track with this, but can't quite get the right syntax..

    I currently have....

    Code:
    For Each [ID] In [tblProjectContacts]
      If [tblProjectContacts].[ProjectNo] = Me.ProjectNumber Then
        If [tblProjectContact].[ContactID] = Me.Contacts Then
          MsgBox "Contact is already added to project."
        Else
          Dim rs As Recordset
          Set rs = CurrentDb.OpenRecordset("tblProjectContacts")
          rs.AddNew
          rs!ProjectNo = Me.ProjectNumber
          rs!ContactID = Me.Contacts
          rs.Update
          Set rs = Nothing
        End If
      End If
    Exit For
    However, I'm getting a Compile Error, Variable Required.



    I am thinking there may be a better than checking every ID of tblProjectContacts, but I'd have to isolate to check only those where Me.ProjectNumber is equal to tblProjectContacts.ProjectNo

    Hmmm. Give it some more thought....

  3. #3
    Join Date
    Dec 2007
    Posts
    25
    Quote Originally Posted by rjwebgraphix
    Hmmm. Give it some more thought....
    This might work better, however the syntax is still wrong....

    Code:
    For Each [ID] In [tblProjectContacts] WHERE [tblProjectContacts].[ProjectNo] = Me.ProjectNumber
        If [tblProjectContact].[ContactID] = Me.Contacts Then
    	MsgBox "Contact is already added to project."
        Else
    	Dim rs As Recordset
    	Set rs = CurrentDb.OpenRecordset("tblProjectContacts")
    	rs.AddNew
    	rs!ProjectNo = Me.ProjectNumber
    	rs!ContactID = Me.Contacts
    	rs.Update
    	Set rs = Nothing
        End If
    Exit For
    It's stopping on the ID in the For statement.

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    are you sure a recordset is ideal?

    consider this:

    dim strSQL as string
    if isnull(DLOOKUP("ContactID", "tblProjectContact", "ContactID = " & Me.Contacts & " And ProjectNo = " & Me.ProjectNumber)) then
    strSQL = "INSERT INTO tblProjectContracts(ProjectNo, ContactID) VALUES(" & Me.ProjectNumber & ", " & Me.Contacts & ")"
    currentdb.execute strSQL
    endif

    izy

    LATER: that might be total crap: it will add ONE record into tblProjectContract for ONE contractID and ONE projectNo if that record does not already exist. if your game-plan is different: please explain again.
    Last edited by izyrider; 12-16-07 at 13:25.
    currently using SS 2008R2

  5. #5
    Join Date
    Dec 2007
    Posts
    25
    Quote Originally Posted by izyrider
    are you sure a recordset is ideal?


    LATER: that might be total crap: it will add ONE record into tblProjectContract for ONE contractID and ONE projectNo if that record does not already exist. if your game-plan is different: please explain again.

    No, I'm not sure a recordset is ideal, but you got me on the right track and its working as desired now. I'm still using the recordset.

    In the table I needed Multiple contacts for any given project Number.

    What I ended up going with is....

    Code:
    If IsNull(DLookup("ContactID", "tblProjectContacts", "ContactID = " & Me.Contacts & " And ProjectNo = " & Me.ProjectNumber)) Then
        Dim rs As Recordset
        Set rs = CurrentDb.OpenRecordset("tblProjectContacts")
        rs.AddNew
        rs!ProjectNo = Me.ProjectNumber
        rs!ContactID = Me.Contacts
        rs.Update
        Set rs = Nothing
        [Forms]![frmMainProjects]![SubFrmOpenProject].Requery
    Else
    MsgBox "Contact has already been added to project number" & Me.ProjectNumber
    End If
    NOTE: The requery is for the form that called the form this code is in.

    Its actually working as desired this way.

    Thanks for the assistance.

    RJ

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    great that dlookup() helped.

    needed Multiple contacts yes, but you only have ONE contact selected so you can only insert (or .AddNew) one contact (my uncertainty was if you had many projectno to which you wanted to add this contact in the same lump of code).

    IMHO the SQL execute should be faster than your recordset approach.

    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Dec 2007
    Posts
    25
    Quote Originally Posted by izyrider
    great that dlookup() helped.

    needed Multiple contacts yes, but you only have ONE contact selected so you can only insert (or .AddNew) one contact (my uncertainty was if you had many projectno to which you wanted to add this contact in the same lump of code).

    IMHO the SQL execute should be faster than your recordset approach.

    izy
    Not in this case. Contacts are added to a single project at a time from the main Project form. It is possible to add multiple contacts to a single project in one pass, but I elected not to do it that way since the multi-select listbox is still beyond my understanding. Although, I have a need to figure it out, just not required at this stage of development.

    As far as the SQL vs. RS. I'm not sure, at this time, how a few miliseconds are going to matter. Maybe in the future it will as in the end there could be a years worth of projects open at a single time. If its a closed project, it wouldn't matter because you wouldn't be adding contacts to a closed project, so, at this point, I can't see it really mattering one way or the other, but I could be wrong.

    I still have a long way to go with this database, so I may revisit anything that appears slow. Right now with only having 4 dummy Projects and 5 dummy contacts, it appears to be fast enough.

    Thanks for the input though. It helps with areas I still don't understand. I'll do some research on that method for informational purposes.

Posting Permissions

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