Help in refreshing/repainting a form and passing updated data to another form (1 Viewer)

[alan2013]

I've posted this in 'Modules & VBA' as essentially I think the solution is in the VBA code..

I have the very beginnings of a compensation claims database. Please see the attached.

I want to be able to :
Go to Form A, and click on the right-arrow buttons at the right-hand side. On clicking, I'd like the claim step ID on Form A to change from 'S1 S3' to 'S1 S4' and then for the step meaning field alongside to update accordingly. Form A should stay open in the background, while the 'frmNextStep_S1 S3 to S1 S4' Form opens and the step ID updates (to 'S1 S4') on THAT Form too (signifying a move one step down the claims processing pipeline). And I'd like the change-over to S1 S4 to be recorded in the underlying Table.

I'd be very grateful for guidance on this. I've tried various things in the VBA code behind the right-arrow button (I guess that's where the solution lies?), but haven't been able to achieve the desired result.

Thank you

 

[MajP]

The problem is that both your tables have the same name for your txtClaimCurrentStepID PK and FK. This can become a problem as you see here. You want to update your FK txtClaimCurrentStepID, but it is not even included in the query. So you are inadvertently attempting to change the wrong field in the wrong table. That is the PK txtClaimCurrentStepID in the tblClaimsCurrentSteps.

In form A's query I aliased in txtCurrentClaimID, but if it was me I would change the names in my tables instead. I NEVER have the PK and FK with the same name. I always have StudentID_PK and StudentID_FK. Solves a lot of problems and simplifies code. Sure you can get around this with TableName.FieldName but that is extra work.

SELECT tblclaims.intclaimreferenceid,
       tblclaims.txtclaimantfirstname,
       tblclaims.txtclaimantsurname,
       tblclaims.txtclaimcurrentstage,
       tblclaimscurrentsteps.txtclaimcurrentstepid,
       tblclaimscurrentsteps.txtclaimcurrentstepmeaning,
 
      tblclaims.txtclaimcurrentstepid AS ClaimStepID_FK

FROM   tblclaimscurrentsteps
       INNER JOIN tblclaims
               ON tblclaimscurrentsteps.txtclaimcurrentstepid =
                  tblclaims.txtclaimcurrentstepid
WHERE  (( ( tblclaimscurrentsteps.txtclaimcurrentstepid ) = "s1 s1"
           OR ( tblclaimscurrentsteps.txtclaimcurrentstepid ) = "s1 s2"
           OR ( tblclaimscurrentsteps.txtclaimcurrentstepid ) = "s1 s3" ));

Private Sub btnPerformNextStep_Click()
    MsgBox Me.ClaimReferenceID
    Me.ClaimStepID_FK = "S1 S4"  ' must add this alias field in the query
    Me.Refresh
    DoCmd.OpenForm "frmNextStep_S1 S3 to S1 S4", , , "intClaimReferenceID = " & Me.ClaimReferenceID
End Sub

The other alternative is to uniquely specify which field by TableName.FieldName or simply only including in the query the foreign key txtClaimCurrentStepID from tblClaims and not from tblClaimsCurrentSteps.

 

[MajP]

One more thing. Traditionally you would save a step and the date completed. This way you have a history of when each step was completed. You lose a lot of information storing only the current step. To do that you would need a junction table

tblClaims_Steps
-- ClaimID_FK
--StepID_FK
--Step_Complete_Date
--Step_Notes

if claim 152 completes S1_S3 on 1/1/2022 then the entry is

152     S1_S3  1/1/2022

Also to simplify coding and design I would just use an autonumber PK and add some helpful fields.
tblClaimSteps tblClaimSteps

ClaimID_PK	StepName	StepDescription	StageNumber	StepNumber
1​	S1 S1	Stage 1, Step 1 (bla bla)	1​	1​
2​	S1 S2	Stage 1, Step 2 (such and such)	1​	2​
3​	S1 S3	Stage 1, Step 3 (so and so)	1​	3​
4​	S1 S4	Stage 1, Step 4 (something else)	1​	4​
5​	S1 S5	Stage 1, Step 5 (yada)	1​	5​
6​	S1 S6	Stage 1, Step 6 (rhubarb)	1​	6​
7​	S2 S1	Stage 2. Step 1	2​	1​

With this design you can simplify your queries. Not saying you will need to do this, but you can if you do.
Show all claims grouped by stage
Show percent of steps complete in stage
Show all claims that have not gotten to stage 3 step 2
etc.

 

[MsAccessNL]

One more thing. Traditionally you would save a step and the date completed. This way you have a history of when each step was completed. You lose a lot of information storing only the current step. To do that you would need a junction table

tblClaims_Steps
-- ClaimID_FK
--StepID_FK
--Step_Complete_Date
--Step_Notes

if claim 152 completes S1_S3 on 1/1/2022 then the entry is

152     S1_S3  1/1/2022

Also to simplify coding and design I would just use an autonumber PK and add some helpful fields.
tblClaimSteps tblClaimSteps

ClaimID_PK	StepName	StepDescription	StageNumber	StepNumber
1​	S1 S1	Stage 1, Step 1 (bla bla)	1​	1​
2​	S1 S2	Stage 1, Step 2 (such and such)	1​	2​
3​	S1 S3	Stage 1, Step 3 (so and so)	1​	3​
4​	S1 S4	Stage 1, Step 4 (something else)	1​	4​
5​	S1 S5	Stage 1, Step 5 (yada)	1​	5​
6​	S1 S6	Stage 1, Step 6 (rhubarb)	1​	6​
7​	S2 S1	Stage 2. Step 1	2​	1​

With this design you can simplify your queries. Not saying you will need to do this, but you can if you do.
Show all claims grouped by stage
Show percent of steps complete in stage
Show all claims that have not gotten to stage 3 step 2
etc.

Hello Majp,

A small side question. How do i get my table/query headers look like yours with the color and the blue line. Or is this a continuous form?

 

[arnelgp]

A small side question. How do i get my table/query headers

open your table and select all records.
Copy and Paste it to this forum and it will look like what you are seeing.

 

[MajP]

As @arnelgp said that is a feature when you paste into this site and not a feature of Access. You can do some limited datasheet formatting

 

[alan2013]

One more thing. Traditionally you would save a step and the date completed. This way you have a history of when each step was completed. You lose a lot of information storing only the current step. To do that you would need a junction table

tblClaims_Steps
-- ClaimID_FK
--StepID_FK
--Step_Complete_Date
--Step_Notes

if claim 152 completes S1_S3 on 1/1/2022 then the entry is

152     S1_S3  1/1/2022

Also to simplify coding and design I would just use an autonumber PK and add some helpful fields.
tblClaimSteps tblClaimSteps

ClaimID_PK	StepName	StepDescription	StageNumber	StepNumber
1​	S1 S1	Stage 1, Step 1 (bla bla)	1​	1​
2​	S1 S2	Stage 1, Step 2 (such and such)	1​	2​
3​	S1 S3	Stage 1, Step 3 (so and so)	1​	3​
4​	S1 S4	Stage 1, Step 4 (something else)	1​	4​
5​	S1 S5	Stage 1, Step 5 (yada)	1​	5​
6​	S1 S6	Stage 1, Step 6 (rhubarb)	1​	6​
7​	S2 S1	Stage 2. Step 1	2​	1​

With this design you can simplify your queries. Not saying you will need to do this, but you can if you do.
Show all claims grouped by stage
Show percent of steps complete in stage
Show all claims that have not gotten to stage 3 step 2
etc.

MajP, thank you for this and for your earlier response. I'm making progress again now.