Help - Change Field Value in different table based on field selection in a form/query

[vicsalt]

I have a couple of tables - one contains work requests and one contains the engineering data. I use various query's for the engineer to select the job . Via a form based on a query. When the engineer select the job I want to set a Yes/No field in the Works Order table to "true". I need advice on the code you would use to use. and if its possible.
My incorrect sample code in red is what im hopping to do . Note the form/query Im using does not contain the field i want to populate.

Private Sub selectjob_btn_Click()
[Tablename], [Cellname] = True

Please note IM not a coder at all simple try to google solutions etc so please be gentle

 

[GaP42]

VicSalt - You are trying to update a value in the WorkOrder table that is not represented/displayed on the Job/WorkRequest form. The click of the button is the action that indicates the WorkRequest has been accepted, however the Yes/No field for accepting a WorkOrder is in the Work Order table - please confirm.

Q: How is it known that the WorkRequest has been "accepted" - does a field exist for this - like, does the id for the person get written to a column on this table when the button is clicked?
Q. Does a WorkOrder relate to many WorkRequests? eg a WorkOrder for repair of, say a ship, potentially spawn many work requests: navigation repair, steerin sytem reapir, engine repair etc? If so then updating the Yes/No field in the WorkOrderTable does not make sense - updating may occur from any WorkRequest.

It is probably best to set its the AcceptWorkRequest (Y/N) value via an update query. To do this you need identify which Work Order record is to be updated. It needs to be specific - what is the field that identifies the WorkOrder record from (I presume) the WorkRequest table? (eg WorkOrderID is the PK in the Work Order table and is a foreign key value (WorkOrderFK) in the Work Order table.
If the YesNo field is AcceptWorkRequest then, when the button is pressed

1. Establish the value of the WorkOrder PK
2. Write SQL String something like : strSQL = "UPDATE WorkOrder SET WorkOrder.WorkOrderAccepted = True WHERE WorkOrderID = & Me.WorkOrderID;"
3. Execute the query using DoCmd.runSQL strSQL

Private Sub btnAccept_Click()
Dim WOID As Integer
Dim strSQL As String

WOID = Me.WorkOrderFK
strSQL = "UPDATE WorkOrder SET WorkOrder.WorkOrderAccepted = True WHERE WorkOrderID = " & WOID & ";"

DoCmd.RunSQL strSQL

End Sub

 

[cheekybuddha]

Before pursuing GaP42's suggestion, you might be wise to look at your tables' design.

This is the sort of thing that relational databases handle natively though one-to-many or many-to-many related tables.

Please can you post a screenshot of your relationships window.

 

[vicsalt]

VicSalt - You are trying to update a value in the WorkOrder table that is not represented/displayed on the Job/WorkRequest form. The click of the button is the action that indicates the WorkRequest has been accepted, however the Yes/No field for accepting a WorkOrder is in the Work Order table - please confirm.

2 tables
WorkRequests_tbl - This is when the Key field is created "WOID"
EngineerWO_tbl- This is where all the engineer data is being stored

The WOID is a 1 to many relationship , there is 1 work order, but there can be several engineers logging on and off the job until its fixed. The engineer selects the job, its at this point the WOID is copied to the EngineerWO-tbl , and a field within the EngineerWO_tbl is made Live so I can filter live jobs etc.

When a engineer looks at open jobs my query shows all OPEN jobs including those "Live" jobs that are already being worked on. I would like to filter these out using a new field in my WorkRequest_tbl. I can simply exclude "Live" jobs as the WOID field is established at the point the engineer selects a open job.

Q: How is it known that the WorkRequest has been "accepted" - does a field exist for this - like, does the id for the person get written to a column on this table when the button is clicked?

As above - on Selection of Job a "Live" flag is set

Q. Does a WorkOrder relate to many WorkRequests? eg a WorkOrder for repair of, say a ship, potentially spawn many work requests: navigation repair, steerin sytem reapir, engine repair etc? If so then updating the Yes/No field in the WorkOrderTable does not make sense - updating may occur from any WorkRequest.

Yes Engineers need to log off jobs end of shift and following shift carry on, there can also be 2 engineers on a single job

It is probably best to set its the AcceptWorkRequest (Y/N) value via an update query. To do this you need identify which Work Order record is to be updated. It needs to be specific - what is the field that identifies the WorkOrder record from (I presume) the WorkRequest table? (eg WorkOrderID is the PK in the Work Order table and is a foreign key value (WorkOrderFK) in the Work Order table.
If the YesNo field is AcceptWorkRequest then, when the button is pressed

1. Establish the value of the WorkOrder PK
2. Write SQL String something like : strSQL = "UPDATE WorkOrder SET WorkOrder.WorkOrderAccepted = True WHERE WorkOrderID = & Me.WorkOrderID;"
3. Execute the query using DoCmd.runSQL strSQL

Private Sub btnAccept_Click()
Dim WOID As Integer
Dim strSQL As String

WOID = Me.WorkOrderFK
strSQL = "UPDATE WorkOrder SET WorkOrder.WorkOrderAccepted = True WHERE WorkOrderID = " & WOID & ";"

DoCmd.RunSQL strSQL

End Sub

 

[vicsalt]

Before pursuing GaP42's suggestion, you might be wise to look at your tables' design.

This is the sort of thing that relational databases handle natively though one-to-many or many-to-many related tables.

Please can you post a screenshot of your relationships window.

Please see detail in Gap42 response,

 

[vicsalt]

All I want to do is enable a WIP filed in the WorksRequest_tbl to "True" so i can filter out those records when Engineers review "Open" jobs

 

[cheekybuddha]

Please can you post a screenshot of your relationships window.

 

[vicsalt]

As mentioned the only relationship between both tables is the WOID field 1 to many as in 1 WO in the worksrequest table to many in the engineer table

 

[cheekybuddha]

Sometimes it helps those trying to help you to be able to visualise what's going on.

I'll leave it to others more able to imagine your setup to assist you

 

[GaP42]

@vicsalt :
Thank you for providing some view of the forms and data.
I note that your request has changed from originally:
Post #1

When the engineer select the job I want to set a Yes/No field in the Works Order table to "true". I need advice on the code you would use to use. and if its possible.

In Post # 6

All I want to do is enable a WIP filed in the WorksRequest_tbl to "True" so i can filter out those records when Engineers review "Open" jobs

It is important when requesting assistance that the requirement is expressed clearly. WorkRequest and Work Order may be interchangeable to you but we do not know if that is the case.
A screenshot of the relationships window would be very useful - it helps us know what table and field names you use and to know whether you have established relationships between the tables in the database (not a link drawn between tables in a query). That is still not clear.
Is the WIP field the STATUS field in the WorkRequest table? If so modify below code to suit, adjusting the names of tables too

Operating from your Engineer_WO (Repairs Form) - to update WIP in the WorkRequest table for the selected WOID:

Private Sub btnAccept_Click()
Dim WOIDAccept As Integer
Dim strSQL As String

WOIDAccept = Me.WOID    ' name of the control with the WOID
strSQL = "UPDATE WorkRequest SET WorkRequest.WIP = True WHERE WOID = " & WOIDAccept & ";"

DoCmd.RunSQL strSQL
End Sub

Now - your Repairs Form requires the Engineer to select their name from a combobox. The login name of the person on the PC can be correlated to a Person Name in a table so that they do not have to select from the list - or at least defaults to that name. Assumption: users of the application login to the PC / Network and (generally) do not have other users access the application from their PC.

Your query to exclude the Live jobs (WIP = true) should now be simple to establish

Good Luck

 

[vicsalt]

@vicsalt :
Thank you for providing some view of the forms and data.
I note that your request has changed from originally:
Post #1

In Post # 6

It is important when requesting assistance that the requirement is expressed clearly. WorkRequest and Work Order may be interchangeable to you but we do not know if that is the case.
A screenshot of the relationships window would be very useful - it helps us know what table and field names you use and to know whether you have established relationships between the tables in the database (not a link drawn between tables in a query). That is still not clear.
Is the WIP field the STATUS field in the WorkRequest table? If so modify below code to suit, adjusting the names of tables too

Operating from your Engineer_WO (Repairs Form) - to update WIP in the WorkRequest table for the selected WOID:

Private Sub btnAccept_Click()
Dim WOIDAccept As Integer
Dim strSQL As String

WOIDAccept = Me.WOID    ' name of the control with the WOID
strSQL = "UPDATE WorkRequest SET WorkRequest.WIP = True WHERE WOID = " & WOIDAccept & ";"

DoCmd.RunSQL strSQL
End Sub

Now - your Repairs Form requires the Engineer to select their name from a combobox. The login name of the person on the PC can be correlated to a Person Name in a table so that they do not have to select from the list - or at least defaults to that name. Assumption: users of the application login to the PC / Network and (generally) do not have other users access the application from their PC.

Your query to exclude the Live jobs (WIP = true) should now be simple to establish

Good Luck

@vicsalt :
Thank you for providing some view of the forms and data.
I note that your request has changed from originally:
Post #1

In Post # 6

It is important when requesting assistance that the requirement is expressed clearly. WorkRequest and Work Order may be interchangeable to you but we do not know if that is the case.
A screenshot of the relationships window would be very useful - it helps us know what table and field names you use and to know whether you have established relationships between the tables in the database (not a link drawn between tables in a query). That is still not clear.
Is the WIP field the STATUS field in the WorkRequest table? If so modify below code to suit, adjusting the names of tables too

Operating from your Engineer_WO (Repairs Form) - to update WIP in the WorkRequest table for the selected WOID:

Private Sub btnAccept_Click()
Dim WOIDAccept As Integer
Dim strSQL As String

WOIDAccept = Me.WOID    ' name of the control with the WOID
strSQL = "UPDATE WorkRequest SET WorkRequest.WIP = True WHERE WOID = " & WOIDAccept & ";"

DoCmd.RunSQL strSQL
End Sub

Now - your Repairs Form requires the Engineer to select their name from a combobox. The login name of the person on the PC can be correlated to a Person Name in a table so that they do not have to select from the list - or at least defaults to that name. Assumption: users of the application login to the PC / Network and (generally) do not have other users access the application from their PC.

Your query to exclude the Live jobs (WIP = true) should now be simple to establish

Good Luck

Sorry for any confusion, no nothings changed just my wording , in that i need to create some sort of flag in my Works Request table that I can use to filter out those that are live etc.

I have no relationships set between tables only within query's ? not something I have ever done ? I will try your solution later, and thankyou for assisting .

 

[vicsalt]

@vicsalt :
Thank you for providing some view of the forms and data.
I note that your request has changed from originally:
Post #1

In Post # 6

It is important when requesting assistance that the requirement is expressed clearly. WorkRequest and Work Order may be interchangeable to you but we do not know if that is the case.
A screenshot of the relationships window would be very useful - it helps us know what table and field names you use and to know whether you have established relationships between the tables in the database (not a link drawn between tables in a query). That is still not clear.
Is the WIP field the STATUS field in the WorkRequest table? If so modify below code to suit, adjusting the names of tables too

Operating from your Engineer_WO (Repairs Form) - to update WIP in the WorkRequest table for the selected WOID:

Private Sub btnAccept_Click()
Dim WOIDAccept As Integer
Dim strSQL As String

WOIDAccept = Me.WOID    ' name of the control with the WOID
strSQL = "UPDATE WorkRequest SET WorkRequest.WIP = True WHERE WOID = " & WOIDAccept & ";"

DoCmd.RunSQL strSQL
End Sub

Now - your Repairs Form requires the Engineer to select their name from a combobox. The login name of the person on the PC can be correlated to a Person Name in a table so that they do not have to select from the list - or at least defaults to that name. Assumption: users of the application login to the PC / Network and (generally) do not have other users access the application from their PC.

Your query to exclude the Live jobs (WIP = true) should now be simple to establish

Good Luck

Im getting a error at the start (Red Text) it maybe because the WOID in the WorkRequest_tbl doesn't exist until the form is closed ? Thoughts please

Private Sub selectjob_btn_Click()
EngStartTimeDate = Now()
Live = True
Dim WOIDAccept As Integer
Dim strSQL As String

WOIDAccept = Me.WOID ' name of the control with the WOID
strSQL = "UPDATE WorkRequest_tbl SET WorkRequest_tbl.WIP = True WHERE WOID = " & WOIDAccept & ";"

DoCmd.RunSQL strSQL

DoCmd.Close acForm, "Repairs_frm"
DoCmd.Close acForm, "OpenEvents_frm"

 

[Gasman]

PMFJI and as an aside, but do you Have Option Explicit at the top of all your modules?

 

[GaP42]

Error messages are diagnostic - please provide the detailed error message.
1. As Gasman says use Option Explicit
2. What is the name of the control on the form WorkOrders that displays the WOID - does it match that in the code?
3. Did you set up the relationship between the two tables? Doing so establishes referential integrity - a WorkOrder must exist for an Engineers_WO record to exist. The entry of a WOID in the Engineers_WO table requires that the WOID exists in the WorkOrder table. Closing the WorkORderfrom is not needed for the WOID to exist. If the record in the WorkOrder table is new, moving to another WorkOrder record or explicitly saving the record will establish the WOID

 

[gemma-the-husky]

You would also be better having Woidaccept as a Boolean rather than an integer

Maybe also

Woidaccept = NZ(me.woid,false)

But that doesn't explain exactly why you get the error, which you do need to resolve.

 

[GaP42]

WOID is the PK of the WorkOrder table. WOIDAccept is simply declaring a variable to hold the value of the WOID (from the Engineer Repairs from when they accept Work) for use in the SQL to locate the record to set the WIP. It is not the "Live" Flag, or the WIP flag.

 

[vicsalt]

PMFJI and as an aside, but do you Have Option Explicit at the top of all your modules?

Added
Option Compare Database
Option Explicit

Error messages are diagnostic - please provide the detailed error message.
1. As Gasman says use Option Explicit

Compile error: Method or data member not found

Option Compare Database
Option Explicit - I added this line
Private Sub selectjob_btn_Click() - This Line is now highlighted ?
EngStartTimeDate = Now()
Live = True
Dim WOIDAccept As Integer
Dim strSQL As String

WOIDAccept = Me.WOID ' name of the control with the WOID - This is Blue
strSQL = "UPDATE WorkRequest_tbl SET WorkRequest_tbl.WIP = True WHERE WOID = " & WOIDAccept & ";"

DoCmd.RunSQL strSQL

DoCmd.Close acForm, "Repairs_frm"
DoCmd.Close acForm, "OpenEvents_frm"

2. What is the name of the control on the form WorkOrders that displays the WOID - does it match that in the code?

Bothe tables - WorkRequests_tbl & EngineerWO_tble WOID the same as line in code

3. Did you set up the relationship between the two tables? Doing so establishes referential integrity - a WorkOrder must exist for an Engineers_WO record to exist. The entry of a WOID in the Engineers_WO table requires that the WOID exists in the WorkOrder table. Closing the WorkORderfrom is not needed for the WOID to exist. If the record in the WorkOrder table is new, moving to another WorkOrder record or explicitly saving the record will establish the WOID

ha

I have created a relashionship between the 2 tables as in Joined the WOID fields as above

 

[GaP42]

1. The relationship seems to be incorrectly set. Confirm that a Work Order must exist before an Engineer can accept a repair, and that many repairs may be performed to complete a WorkOrder. Join properties : Option 2 - allows the work order to exist without an Engineer repair record existing.

2. The name of the control for displaying the WOID is not the name of the field in the table. Use Form Design view and click on the controls that display the WOID - what are they named?

3. The error message is indicating it cannot find the button with the name selectjob_btn - check the name of the button in design view on the Engineer's Repairs form. Does that button have the above procedure associated with it?

 

[vicsalt]

1. The relationship seems to be incorrectly set. Confirm that a Work Order must exist before an Engineer can accept a repair, and that many repairs may be performed to complete a WorkOrder. Join properties : Option 2 - allows the work order to exist without an Engineer repair record existing.

Yes Step 1 - Raise WO that gets a unique no "WOID"
Yes Engineers may not fix the issues and log off the job, another engineer can then select the same WO.
Yes Work orders exist , and no record will exist in the engineer table until its selected

2. The name of the control for displaying the WOID is not the name of the field in the table. Use Form Design view and click on the controls that display the WOID - what are they named?

the control name in the WO table and the Engineer table are the same "WOID"

3. The error message is indicating it cannot find the button with the name selectjob_btn - check the name of the button in design view on the Engineer's Repairs form. Does that button have the above procedure associated with it?

Yes the selectjob_btn does the following Sets the Live field to true in the engineer table and the "EngineerStartDateTime" value, then closes both forms as shown in the code below. I have comma out the code that isn't working .


Private Sub selectjob_btn_Click()
EngStartTimeDate = Now()
Live = True
'Dim WOIDAccept As Integer
'Dim strSQL As String

'WOIDAccept = Me.WOID ' name of the control with the WOID
'strSQL = "UPDATE WorkRequest_tbl SET WorkRequest_tbl.WIP = True WHERE WOID = " & WOIDAccept & ";"

'DoCmd.RunSQL strSQL

DoCmd.Close acForm, "Repairs_frm"
DoCmd.Close acForm, "OpenEvents_frm"

End Sub
'Private Sub btnAccept_Click()
'Dim WOIDAccept As Integer
'Dim strSQL As String

'WOIDAccept = Me.WOID ' name of the control with the WOID
'strSQL = "UPDATE WorkRequest_tbl SET WorkRequest_tbl.WIP = True WHERE WOID = " & WOIDAccept & ";"

'DoCmd.RunSQL strSQL
'End Sub

 

[GaP42]

Can you attach a copy of the database? Sample data only is needed.