Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 7 Access 2000
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    672

    Query not functioninng Properly

    I have a query, that runs when you push a button on my form. It is an update query. I want the user to be able to input one value, and it will take the value from a textbox for the other value. Here is the SQL for the query:

    UPDATE tbl123 SET tbl123.status = [Enter Status]


    WHERE (((tbl123.program)=[Forms]![form1]![ProgramName]));

    The problem is, it prompts for status, but it actually won't write to the table?????

  2. #2
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    so it won't do it. any more info? like what data type STATUS is? and what the circumstances were on a test run?

    I don't answer your ?'s Jo, because you ask too many of them and solve a lot anyway before we answer here, but I'll give you some time on this one.

  3. #3
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 7 Access 2000
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    672
    They are both text data types.

    If it helps, here is the onclick() event that was written when I created the button.
    Code:
    Private Sub Command4_Click()
    On Error GoTo Err_Command4_Click
    
        Dim stDocName As String
    
        stDocName = "Qry_Update_Status"
        DoCmd.OpenQuery stDocName, acNormal
    
    Exit_Command4_Click:
        Exit Sub
    
    Err_Command4_Click:
        MsgBox Err.Description
        Resume Exit_Command4_Click
        
    End Sub

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,618
    I don't use input parameter prompts (too hard to control entry validation). I have user input value in form control and reference the control for the value. Also don't run saved queries, I use RunSQL.

    DoCmd.SetWarnings = False
    DoCmd.RunSQL "UPDATE tbl123 SET status ='" & Me.tbxStatus & "' WHERE program='" & Me.tbxProgramName & "'"
    DoCmd.SetWarnings = True

    Apostrophe delimiters needed if field is text datatype, # if date, nothing if number.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 7 Access 2000
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    672
    When I run this line of the code:
    Code:
    DoCmd.SetWarnings = False
    I get a Debug Error Of:
    It tells me Compile Error: Argument Not Optional

    It is also asking me to input this data, it is not taking it directly from the form like needed?
    I included the apastrophy's because they are text data types.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,618
    Sorry, that's what I get for relying on memory. Drop the = sign.

    In place of tbxStatus and tbxProgramName use the real names of controls from your form.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 7 Access 2000
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    672
    Dropping the equal sign took care of the object error, but I am still experiancing issues with:

    It is also asking me to input this data, it is not taking it directly from the form like needed?
    I included the apastrophy's because they are text data types.

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,618
    Don't know what to say. This code structure works for me all the time. The code is in button event on the same form that users enter the values? Post your complete new procedure.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 7 Access 2000
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    672
    Attached is my db that I am using the code in....

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,618
    Can't update a record that doesn't exist. The table is empty.

    Also, the field name is ProgramName instead of program.

    And the Menu command to Refresh is not needed becuase this form is not bound. It is source of the error.

    I would use this to requery/refresh a bound form: Me.Requery or Me.Refresh. Neither of these error even with the unbound form.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 7 Access 2000
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    672
    Apalogies, let me clarify a little more....

    Sometimes the data will not already exist in the table. How would I set it to write to the table if null, or update if the record exists?

    I need this to execute as the onclick() event of a button, if I do not choose a refresh button, how else will the code execute? I was told that was the easiest way to get code to execute?!

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,618
    Need code to check for the record and then either INSERT or UPDATE. Why don't you just use bound form and enter/edit data through the form?

    If must use code, something like:
    DoCmd.SetWarnings = False
    If IsNull(DLookup("ProgramName","tbl123","ProgramName ='" & Me.ProgramName & "'")) Then
    DoCmd.RunSQL "INSERT INTO tbl123(ProgramName, Status) VALUES('" & Me.ProgramName & "', '" & Me.Status & "')"
    Else
    DoCmd.RunSQL "UPDATE tbl123 SET Status ='" & Me.tbxStatus & "' WHERE ProgramName='" & Me.tbxProgramName & "'"
    End If
    DoCmd.SetWarnings = True
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 7 Access 2000
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    672
    If I can fully grasp how this code will run, other than a refresh button I think the best route would be using code. How would I set this code to execute without the click of a button?

    And to answer your other question....Why don't you just use bound form and enter/edit data through the form?
    This is a form that is similar to a installation screen, and the user will check what all options they want to install, and then depending on the tick boxes that are checked, those items will be installed. All the form really does is run code to create/add/edit/delete from the tables/forms/queries. If that makes any sense. It is the WORST setup I have probably ever seen! I am just working on a quick "mod" to the current set-up, and then beginning to design a more functional db from the ground up.

  14. #14
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,618
    I didn't mean that the button wasn't needed, but the line in the code that runs Menu item is what was causing error. Of course some event has to trigger the procedure, button click is appropriate.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  15. #15
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 7 Access 2000
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    672
    Oh, I see what you are saying! I removed that portion of code, and it will write the programName as well as status to the table IF they are already not contained in the table. If they are in the table, it will pop up and ask me for the ProgramName, and when I input the program name it updates ALL status' in the table to the value of status on the form?

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Time not Sorting Properly in Report
    By KrenzyRyan in forum Reports
    Replies: 3
    Last Post: 06-24-2011, 08:56 PM
  2. Report does not update properly
    By Tari in forum Reports
    Replies: 7
    Last Post: 03-08-2011, 05:20 PM
  3. Cannot split a DB properly
    By Swarland in forum Access
    Replies: 3
    Last Post: 12-17-2010, 04:44 AM
  4. Delete SQL statement not working properly
    By Alexandre Cote in forum Programming
    Replies: 3
    Last Post: 10-18-2010, 12:56 PM
  5. Query on chart not working properly!
    By Sim_ in forum Queries
    Replies: 0
    Last Post: 10-28-2009, 09:38 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums