Query not functioninng Properly

[jo15765]

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?????

[ajetrumpet]

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.

[jo15765]

They are both text data types.

If it helps, here is the onclick() event that was written when I created the button.

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

[June7]

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.

[jo15765]

When I run this line of the 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.

[June7]

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.

[jo15765]

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.

[June7]

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.

[jo15765]

Attached is my db that I am using the code in....

[June7]

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.

[jo15765]

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?!

[June7]

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

[jo15765]

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.

[June7]

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.

[jo15765]

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?