Results 1 to 12 of 12
  1. #1
    Join Date
    Jul 2003
    Posts
    292

    Unanswered: Updating with Checkboxes....

    I'm pretty new to access and hardly have worked with checkboxes before soo .. here my problem..

    I have a form that the user fills out.. theres a combo field on the form with yes or no to a project. When the form is filled out.. to figure out if the project is complete or not.. the project is set to yes or no.

    all projects that are marked yes is fine.. but all projects that are marked no .. the user has to go back and fix.. once the project is fixed the user has to go back and update the record marking the project to YES then update. This part is fine..

    I wanted to add a checkbox next to the field "project" yes/no ( that is a combo field) ..

    -I went into my table added a field called "fixed" with datatype "yes/no"
    -On my form I added a checkbox field and labeled that "fixed"

    In my code I thought I could update as I did the others but i get an error..

    Compile Error:
    Method or data member not found

    This maybe an easy question but I stated before I new to access to any help would be appreciated..

    Thanks !!

    Heres the code if you wanted to look.. again thanks

    Code:
    Private Sub Command62_Click()
    Dim f1 As String
    Dim f14 As String
    '***changed to variant data type
    Dim f13 As Variant
    
    ControlNo.SetFocus
    f1 = ControlNo.Text & " "
    Fixed.SetFocus
    f14 = Fixed.Text
    DATECLOSE.SetFocus
    '***test to insure DATECLOSE has a value
    
    	If Not IsNull(DATECLOSE) And IsDate(DATECLOSE) Then
    		f13 = DATECLOSE.Text
    	End If
    	'***create sql string based on whether f13 is a date or not
    	If IsDate(f13) Then
    		strSQL = "UPDATE tblInspections SET tblInspections.Fixed = '" & f14 & "', tblInspections.DATECLOSE = #" & f13 & "#  WHERE tblInspections.ControlNo = '" & f1 & "'"
    	Else
    		strSQL = "UPDATE tblInspections SET tblInspections.Fixed = '" & f14 & "', tblInspections.dateclose = null WHERE tblInspections.ControlNo = '" & f1 & "'"
    	End If
    	CurrentDb.Execute strSQL
    
    End Sub
    Last edited by TonyT; 01-21-04 at 17:26.

  2. #2
    Join Date
    Jan 2004
    Location
    Australia
    Posts
    15
    Where abouts in your code does the error happen?

    Does it crash at the following line of code...
    Code:
    f14 = Fixed.Text
    Each control has it's own set of actions and values know as Methods and Data Members. When you are writing the code, when you type the controls name and press full stop a list of that controls methods and data members are diplayed. If it doesn't show up in the list it will error out when you run the code (there are some exceptions). The data member "Text" is not a member of the "Checkbox" control.

    Edit the code to the following and it should work.
    Code:
    f14 = Fixed.Value
    If the code is breaking else where let me know where.

  3. #3
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238

    Re: Updating with Checkboxes....

    Hi Tony!

    A yes/no field is a Boolean field... You can only have values of True or False, Yes or No, -1 or 0 in that field...

    1) If f14 is to hold the value from a checkbox... Then f14 should be defined as Boolean instead of String...

    2) A Webster is right... There is no text property for a checkbox... You could put .Value, but that's the default so just "f14 = Fixed" should be good enough in your code...

    3) And in your Update SQL strings, you can't surround f14's values with ' (single quotes) like you would for a string... True is not the same as 'True' in this case... You want the value for the [Fixed] yes/no field in the table to equal True or False...

    HTH

  4. #4
    Join Date
    Jul 2003
    Posts
    292
    Thanks Trudi and A WEbster for the response !

    A Webster,

    The line you pointed out was the line that was giving me errors.. after i changed it .. The error went away.. but the table did not update. I tried updating a different field on my form.. now im getting an error that points back to the line you ask me to change. The error is .. invalid use of null.. now when i comment out the "fixed" field.. the program runs smooth everything updates.. soo i think its that line that we changed.

    Trudi,

    I changed f14 to boolean.. and i also removed .value to see if that would make a difference.. but i get the same error as mentioned above..


    any thoughts ?

  5. #5
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238
    Good Morning Tony!

    Check that you have set the default value property for the checkbox to False or 0... Checkboxes are a bit weird, in that they have 3 possible values... True/False/Null... or in control terms... checked/unchecked/waiting (I'm sure I read that "waiting" term somewhere... lol... I think?) ... Boolean fields in the table can't have Null values... They're either True/False, Yes/No, -1/0...

    If you make sure that the value of the checkbox is always False if it's not True, then the Invalid use of null error should disappear...

    HTH

  6. #6
    Join Date
    Jul 2003
    Posts
    292

    Talking

    Hey Trudi! good morning to you too

    Ok i tried what you suggested with the default values.. and I'm still getting invalid use of null..

    What I suspect is... My field "fixed" is not bound to anything. I just clicked on created a checkbox, placed it on my form and renamed it to fix... So what I did was dragged Fixed from my field list this time and BAM it worked..

    but the reason why I wanted to be unbound was that wheel on the mouse... when you scroll with it, it changes records! AHH drives me crazy.

    thanks so much for the response.

  7. #7
    Join Date
    Jul 2003
    Posts
    292
    This is the updated code.. I also removed the single quotes surrounding "fixed" in my SQL...

    Code:
    Private Sub Command62_Click()
    Dim f1 As String
    Dim f14 As Boolean
    '***changed to variant data type
    Dim f13 As Variant
    
    ControlNo.SetFocus
    f1 = ControlNo.Text & " "
    Fixed.SetFocus
    f14 = Fixed.Value
    DATECLOSE.SetFocus
    '***test to insure DATECLOSE has a value
    
    	If Not IsNull(DATECLOSE) And IsDate(DATECLOSE) Then
    		f13 = DATECLOSE.Text
    	End If
    	'***create sql string based on whether f13 is a date or not
    	If IsDate(f13) Then
    		strSQL = "UPDATE tblInspections SET tblInspections.Fixed = " & f14 & ", tblInspections.DATECLOSE = #" & f13 & "#  WHERE tblInspections.ControlNo = '" & f1 & "'"
    	Else
    		strSQL = "UPDATE tblInspections SET tblInspections.Fixed = " & f14 & ", tblInspections.dateclose = null WHERE tblInspections.ControlNo = '" & f1 & "'"
    	End If
    	CurrentDb.Execute strSQL
    
    End Sub
    thanks ! =)

  8. #8
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238
    Originally posted by TonyT
    This is the updated code.. I also removed the single quotes surrounding "fixed" in my SQL...

    Code:
    Private Sub Command62_Click()
    Dim f1 As String
    Dim f14 As Boolean
    '***changed to variant data type
    Dim f13 As Variant
    
    ControlNo.SetFocus
    f1 = ControlNo.Text & " "
    Fixed.SetFocus
    f14 = Fixed.Value
    DATECLOSE.SetFocus
    '***test to insure DATECLOSE has a value
    
    	If Not IsNull(DATECLOSE) And IsDate(DATECLOSE) Then
    		f13 = DATECLOSE.Text
    	End If
    	'***create sql string based on whether f13 is a date or not
    	If IsDate(f13) Then
    		strSQL = "UPDATE tblInspections SET tblInspections.Fixed = " & f14 & ", tblInspections.DATECLOSE = #" & f13 & "#  WHERE tblInspections.ControlNo = '" & f1 & "'"
    	Else
    		strSQL = "UPDATE tblInspections SET tblInspections.Fixed = " & f14 & ", tblInspections.dateclose = null WHERE tblInspections.ControlNo = '" & f1 & "'"
    	End If
    	CurrentDb.Execute strSQL
    
    End Sub
    thanks ! =)
    Okay... If Access is going to be stubborn about it, we're going to be commanding and make it work...

    Change... f14 = Fixed.Value
    To... f14 = IIf(IsNull(Fixed), False, True)
    and see what happens...

    P.S. What's the reason for all those SetFocus statements in an On Click Event?? Just curious...

  9. #9
    Join Date
    Jul 2003
    Posts
    292
    Trudi,

    When I changed the code to what you suggested.. it worked !! I click on the checkmark box hit update and it went through.. I went into my table checked to see if the mark was there .. and it was !

    now i go back into my form.. uncheck the box click update and it said it updated it.. now i go to my table.. and still see the mark there.. hmm thats wierd..

    So I think maybe somethings wrong in my SQL ? I dunno..

    ps.. A co-worker helped me out with this.. and she suggested it.. don't really know why -=0) do i really need it ?

    thanks !

  10. #10
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238
    Tony,

    Oops... My mistake... (I'm just not with it today... lol)
    Change the line to...
    f14 = IIf(Fixed = True, True, False)

    This way if the checkbox is Null OR False, it's False, and if it's True, it's True... Make sense?

    Sorry about that!!

    P.S. It's not your SQL ...and I don't know if you need it or not... I would have said no... since if I remember correctly you already had a combo box with yes/no values next to the checkbox... but I don't know all of the details so I wouldn't want to guess...

  11. #11
    Join Date
    Jul 2003
    Posts
    292

    Wink

    Trudi ! Trudi ! Trudi !

    It works !! as if you had any doubt huh? lol

    Thank you so much for your help!

  12. #12
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238
    lol... It takes a while when I don't have the database to work with, but eventually we got there...

    Have a great day!

Posting Permissions

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