Get the textbox value and use in Update Query using Macro 1

[ctopia88]

Hi,

I have an update query wherein it will change the value of a certain column in the database as long as it will satisfy a certain value. The value will come from the current open form.

here is the flow if the events:
Open the form which has record in it, there is a
button that upon pressing will call a macro and the macro will call the update query. The update query's value is dependent on the form's textbox.

I am using this code in the update query "[FORMS]![F1]![CASE_ID]" where F1 is the name of the form and CASE_ID is the name of the textbox.

It is not working for it always pops a window that shows "[FORMS]![F1]![CASE_ID]" as a message and a textbox the requires user input. If I put the value of the case id, then it starts to update.

I want it to be automated. That when I press the buton, it will automatically pick the value of the CASE_ID textbox and update the database.

Please help!

Thanks,

 

[SkipVought]

hi,

...and your code?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ctopia88]

Hi,

here's my code for pressing the button:

Private Sub Closure_btn_Click()
Me.CSTATUS_Cmb.Value = "CLOSED"
DoCmd.RunMacro "UPDATESTATUS", , ""
End Sub

That is because I am calling the macro named "UPDATESTATUS"

LA

 

[dhookom]

What is the SQL view of the query?

Duane
Hook'D on Access
MS Access MVP

 

[ctopia88]

Hi,

this is the SQL of the query:

UPDATE PHONE_DATA SET PHONE_DATA.CASE_STATUS = "CLOSED"
WHERE (((PHONE_DATA.LINKED_CID)=[FORMS]![F1]![CASE_ID]));


Please bear with me for I am new for this. I believe my query is wrong.

All I want to do is when the user press a button in the form, the value in the textbox named "CASE_ID" should be carried on to the query. The query will update all the records if the textbox value is equal to the value under the "LINKED_CID" column. Those that are equal, the "CASE_STATUS" value will be changed into "CLOSED".

Thanks,

 

[dhookom]

I would use code to run a SQL statement:

Dim strSQL as String
strSQL = "UPDATE PHONE_DATA SET PHONE_DATA.CASE_STATUS = 'CLOSED' " & _
    "WHERE LINKED_CID = " & Me.[CASE_ID]
CurrentDb.Execute strSQL, dbFailOnerror

If CASE_ID is text, try:

Dim strSQL as String
strSQL = "UPDATE PHONE_DATA SET PHONE_DATA.CASE_STATUS = 'CLOSED' " & _
    "WHERE LINKED_CID = """ & Me.[CASE_ID] & """"
CurrentDb.Execute strSQL, dbFailOnerror

Duane
Hook'D on Access
MS Access MVP

 

[ctopia88]

thanks dhookom, the second one works