Update Table from form linked to different table

[SimoneRene]

Hi,

So I have two Tables a "Master list" and an "Up issue list" and two corresponding forms.

The User inputs new records into the Master list using the form, the columns/fields are; Document number, Document name, Document status and Date.

The initial Document status of all documents is IN WORK.

Once the Document is finished the User uses "Up issue form".
A combo box is used to select the Document number (linked to the Master list, hence, you cannot Up issue until a document is raised in the Master list).
This CboBox on change auto fills Document name.

There is also a Document Status text box on the Up Issue form which has a default value of ISSUED. The user fills in the other boxes on the form and the information is saved in the 'Up issue table'.

What I want to happen is, when a record is added to the Up Issue form and table the Document status becomes ISSUED I need the Master list Document status to change from IN WORK to ISSUED.

Any idea's would be greatly appreciated. I'm new to Access and VBA.

I was thinking of adding some code on 'after update' on Up Issue form Document status box. Possibly use Openrecordset to access the master list but I'm not sure how to do this.
Alternatively run an query to update after a change once again not sure how this would work to replace the existing "IN WORK" data to match the other table.

Thanks in advance!!

 

[isladogs]

Hi

Either update query or recordset will do this but the update query will be simpler

Colin

 

[SimoneRene]

Hi

Either update query or recordset will do this but the update query will be simpler

Colin

Hi Colin,

Any advice on how?

Thanks.

 

[isladogs]

OK - in the document status textbox after update event, add code something like this:
===============================================================

DoCmd.SetWarnings False

DoCmd.RunSQL "UPDATE TableName SET TableName.DocumentStatus = 'Issued'" & _
" WHERE (((TableName.ID)=Me.txtDocumentID));"

DoCmd.SetWarnings True


=========================================

Obviously, replace TableName & txtDocumentID with the appropriate names for your database & form
This assumes there is a second textbox txtDocumentID with the primary key field for that document & that the ID field is a number
If the ID field is text, you may need to change the query to something like:

DoCmd.RunSQL "UPDATE TableName SET TableName.DocumentStatus = 'Issued'" & _
" WHERE (((TableName.ID)='" & Me.txtDocumentID & "'));"

 

[SimoneRene]

Hi Colin,

OK - in the document status textbox after update event, add code something like this:
===============================================================

DoCmd.SetWarnings False

DoCmd.RunSQL "UPDATE TableName SET TableName.DocumentStatus = 'Issued'" & _
" WHERE (((TableName.ID)=Me.txtDocumentID));"

DoCmd.SetWarnings True


=========================================

Obviously, replace TableName & txtDocumentID with the appropriate names for your database & form
This assumes there is a second textbox txtDocumentID with the primary key field for that document & that the ID field is a number
If the ID field is text, you may need to change the query to something like:

DoCmd.RunSQL "UPDATE TableName SET TableName.DocumentStatus = 'Issued'" & _
" WHERE (((TableName.ID)='" & Me.txtDocumentID & "'));"

Thank you very much!

I am working on this now, can you please clarify if I am understanding the code correctly:

-TableName after UPDATE is the table I want to change
-TableName after SET is the same (table I want to change) DocumentStatus is the column I want to change.
- ='Issued' is what I want to change it to.
-TableName.ID after WHERE is the primary key of the table I want to change
-Me.txtDocumentID is the text box ID field on the from.

Is this assuming the ID field on the form is the same as the ID Field in the table I want to change? It is not.

Does this part of the code match up the records in the form to the corresponding record in the table?

"WHERE (((TableName.ID)='" & Me.txtDocumentID & "

Thank you,

Simone

 

[isladogs]

Hi
See comments in RED below

Hi Colin,



Thank you very much!

I am working on this now, can you please clarify if I am understanding the code correctly:

-TableName after UPDATE is the table I want to change Yes
-TableName after SET is the same (table I want to change) DocumentStatus is the column I want to change. Yes
- ='Issued' is what I want to change it to. Yes
-TableName.ID after WHERE is the primary key of the table I want to change Yes
-Me.txtDocumentID is the text box ID field on the from. Yes

Is this assuming the ID field on the form is the same as the ID Field in the table I want to change? It is not. That may be your problem

Does this part of the code match up the records in the form to the corresponding record in the table?

"WHERE (((TableName.ID)='" & Me.txtDocumentID & "

That's what you need

Sounds to me like you can probably get there yourself now but if not get back to me. No rush. I'm logging off in a minute

Thank you,

Simone

 

[SimoneRene]

Thank you for your help!! Got it working

 

[isladogs]

That's great. Glad to help