Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Sep 2007
    Posts
    148

    Unanswered: Type converstion function

    I have the amost same routine in two separte forms. One work and one doesn't. I really need help on this one.

    The purpose of this routine is to set a flag to true in a user table so that the list box will list the correct users which have been selected.


    *************************************************
    This ONE WORK -- It sets the PrintFlagapplications field to True only for the records in the list box (lstCurrentapplicationsRes)


    Dim i As Byte

    For i = 0 To lstCurrentApplicationsRes.ListCount
    CurrentDb.Execute ("UPDATE tblCobbCountyContact SET PrintFlagApplications = True WHERE CobbCountyContactID='" & lstCurrentApplicationsRes.ItemData(i) & "';")
    Next
    ************************************************** **

    This ONE doesn't work -- It set the PrintFlagOther field to True for all the records in tblOtherContact table. It seems that the CurrentDb.Execute ignore the condition. The list box lstCurrentOtherRes does display the correct chosen record.


    Dim i As Byte

    For i = 0 To lstCurrentOtherRes.ListCount
    CurrentDb.Execute ("UPDATE tblOtherContact SET PrintFlagOther = true WHERE 'OtherContactID = lstCurrentOtherRes.ItemData(i) ' ;")
    Next

    **********************************************

    The only different in these two VBA code is that CobbCountyContactID is a text field and OtherContactID is a "autonumber" field. They both are the primary key in their respective table.

    Please help me.
    Last edited by sweetmail; 01-12-08 at 17:26.

  2. #2
    Join Date
    Sep 2007
    Posts
    148
    I did this testing. It works. Can someone help me how to make lstCurrentOtherRes.Itemdata(i) be a interger? thanks a lot.

    *********************************

    Dim i As Byte
    For i = 0 To lstCurrentOtherRes.ListCount
    'CurrentDb.Execute ("UPDATE tblOtherContact SET PrintFlagOther = true WHERE 'OtherContactID = & lstCurrentOtherRes.ItemData(i) &' ;")
    CurrentDb.Execute ("UPDATE tblOtherContact SET PrintFlagOther = true where OtherContactID = 4 ;")

    Next

  3. #3
    Join Date
    Mar 2007
    Posts
    277
    Try:
    CurrentDb.Execute ("UPDATE tblOtherContact SET PrintFlagOther = True WHERE OtherContactID = " & lstCurrentOtherRes.ItemData(i))
    RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP, ac07, ac10 - WinXP Pro, Win7 Pro
    Please reply to this forum so all may learn.

  4. #4
    Join Date
    Sep 2007
    Posts
    148
    Thank you RuralGuy.

    It is still not working, I have this error message

    Run-time error '3075';

    Syntax error (missing operator) in query expression 'OtherContactID='.

    Please help.

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    RuralGuy's suggestion should work unless .ItemData(i) is a string value instead of an integer value. If it's a string value...

    CurrentDb.Execute ("UPDATE tblOtherContact SET PrintFlagOther = True WHERE OtherContactID = ' " & lstCurrentOtherRes.ItemData(i) & " ' ") 'Note: I put spaces between ' and " to show syntax.

    But you may want to check to make sure lstCurrentOtherRes.ItemData(i) is returning the correct value (try doing a msgbox lstCurrentOtherRes.ItemData(i) in the line before the CurrentDb.Execute to see what value it is.)

    You can also try
    Dim ID as integer
    ID = lstCurrentOtherRes.ItemData(i)
    msgbox ID
    CurrentDb.Execute ("UPDATE tblOtherContact SET PrintFlagOther = True WHERE OtherContactID = " & ID & "")
    or
    CurrentDb.Execute ("UPDATE tblOtherContact SET PrintFlagOther = -1 WHERE OtherContactID = " & ID & "")

    Make sure OtherContactID is spelled correctly also.

    I also think this...Dim i As Byte should be Dim i as integer
    Last edited by pkstormy; 01-13-08 at 01:37.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Sep 2007
    Posts
    148
    Paul Kohn

    Thank you so much for writing but it still didn't work. I change the OtherContactID to be a "text" field instead of "autonumber". I have to go and change all the tables and fix all the relationship. All in all, it works now. Not really what I want but after spending a whole day on this problem. I kind of given up.

    Dim i As Byte
    For i = 0 To lstCurrentOtherRes.ListCount

    CurrentDb.Execute ("UPDATE tblOtherContact SET PrintFlagOther = true WHERE OtherContactID = '" & lstCurrentOtherRes.ItemData(i) & "' ;")

    Next

    thank you all for helping.

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Not sure I would have changed the autonumber field to a text field. I might reconsider doing this and go back to the autonumber field. If you have a backup of the mdb, I would try changing the Dim i as Byte to Dim i as integer and possibly try that (I'd be curious if that would work.) There are also other options verses the CurrentDb.Execute command which does the Update on the table (ex: opening a recordset and looping through it updating the values.) I usually open a recordset verses the CurrentDb.Execute as I've never had problems opening a recordset and updating the values but the CurrentDb.Execute should work with the correct syntax (although I've had similar problems as yours with CurrentDb.Execute and the syntax or mismatch of values.) Guess that's why I've gotten in the habit of opening a recordset and looping through it verses the CurrentDb.Execute.

    What value was returned when you put in a msgbox lstCurrentOtherRes.ItemData(i) before the CurrentDb.Execute? Or what value was returned if you
    Dim ID as integer
    ID = lstCurrentOtherRes.ItemData(i)
    msgbox ID

    Keep in mind that a msgbox XX is your friend and can be very helpful in deducing problems. I will often do this in places where I think the value returned is questionable.
    Last edited by pkstormy; 01-13-08 at 03:38.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    For i = 0 To lstCurrentOtherRes.ListCount - 1



    meanwhile, you can probably let the SQL engine take the strain and run a single UPDATE rather than your loop-of-UPDATEs scheme.

    UPDATE blah blah blah WHERE OtherContactID IN (.....

    the IN expression will depend on how you are generating your list .rowsource

    izy
    currently using SS 2008R2

  9. #9
    Join Date
    Sep 2007
    Posts
    148
    This give me error when there is no record (Access complain about Null) When I do have record, it does give me the right number

    Dim ID as integer
    ID = lstCurrentOtherRes.ItemData(i)
    msgbox ID

    I have also try this msgbox before the updata statement

    Msgbox "This is" &lstCurrentOtherRes.ItemData(i)

    It did give me the correct information.

    I do have a backup mdb and will try the record set suggestion this afternoon.

    *******************************

    izyride, would you give me an example. My lstCurrentOtherRes box get the names from a query off a table tblOtherRes.

    UPDATE blah blah blah WHERE OtherContactID IN (.....

    the IN expression will depend on how you are generating your list .rowsource

    thank you both of you.

    I totally agreed that I should keep my OtherContactID be an autonumber.
    Thank you much.

  10. #10
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    please post the SQL of the query providing .recordsource for lstCurrentOtherRes

    izy

    LATER: whilst we are waitig for your .rowsource SQL, here is an example:

    if your.rowsource is

    SELECT someID, this, that FROM tblOtherRes WHERE someCondition
    ...and it works for your list
    ...and your want to run the UPDATE for all IDs in your list (which is implicit in your loop attempt)

    then have a go with:

    strSQL "UPDATE tblOtherContact SET PrintFlagOther = True WHERE OtherContactID IN (SELECT someID FROM tblOtherRes WHERE someCondition)"
    currentdb.execute strSQL

    note that you only want the ID field from your .rowsource query - other fields are dropped.

    this might not be the most efficient route in the universe, but it has got to be better than looping through a list running an UPDATE for each list item.
    Last edited by izyrider; 01-13-08 at 11:14.
    currently using SS 2008R2

  11. #11
    Join Date
    Sep 2007
    Posts
    148
    izy, thank you. I am not home yet and as soon as I do, will let you know how it goes. thank you you'all so much for helping.

  12. #12
    Join Date
    Sep 2007
    Posts
    148
    This is the SQL code for the lstCurrentOtherRes, an unbound list box in the frmOtherResAB form.

    SELECT tblOtherRes.OtherContactID, tblOtherRes.OtherResID, tblOtherRes.ChangeControlFormDetailsID
    FROM tblOtherContact INNER JOIN tblOtherRes ON tblOtherContact.OtherContactID = tblOtherRes.OtherContactID
    WHERE (((tblOtherRes.ChangeControlFormDetailsID)=[Forms]![frmChangeOfControlForm]![ChangeControlFormDetailsID]));

    ************************************************

    This is the code I run in the Form (frmOtherResAB) to check this unbound list box so that the names with PrintFlagOther set to true will be display on the selected list box.

    Dim i As Byte
    For i = 0 To lstCurrentOtherRes.ListCount
    CurrentDb.Execute ("UPDATE tblOtherContact SET PrintFlagOther = True WHERE 'OtherContactID = " & lstCurrentOtherRes.ItemData(i) & " ' ")
    Next


    It set the PrintFlagOther to True for all the records in the tblOtherContact.

    Izy, I am testing you suggestion.

    thanks
    Last edited by sweetmail; 01-13-08 at 16:26.

  13. #13
    Join Date
    Sep 2007
    Posts
    148
    Hello Izy, how do I define this strsql. The following produce a complie error: sub or function not defined.


    strsql "UPDATE tblOtherContact SET PrintFlagOther = True WHERE OtherContactID IN (SELECT tblOtherRes.OtherContactID FROM tblOtherRes WHERE ((tblOtherRes.ChangeControlFormDetailsID)=[Forms]![frmChangeOfControlForm]![ChangeControlFormDetailsID]))"

    CurrentDb.Execute strsql

    thanks

  14. #14
    Join Date
    Sep 2007
    Posts
    148
    Dear all,

    I am happy to report that with all of your help I have found the code that I need.


    Dim i As Byte

    If lstCurrentOtherRes.ListCount >= 1 Then
    For i = 0 To lstCurrentOtherRes.ListCount - 1

    CurrentDb.Execute ("UPDATE tblOtherContact SET PrintFlagOther = true WHERE OtherContactID=" & lstCurrentOtherRes.ItemData(i))
    Next
    End If



    thanks for all your help.
    Last edited by sweetmail; 01-14-08 at 00:22.

  15. #15
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    Code:
    strSQL "UPDATE tblOtherContact SET PrintFlagOther = True WHERE OtherContactID IN (SELECT OtherContactID FROM tblOtherRes WHERE ChangeControlFormDetailsID = " & Forms!frmChangeOfControlForm!ChangeControlFormDetailsID & ")"
    izy
    currently using SS 2008R2

Posting Permissions

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