Change YesNo Value in field in Tbl1 based on value in control an active form bound to Tbl2

[Pat Hartman]

What part of NO CODE did you not understand. The query with the join populates the flag. I meant to name it "ActionFlgNOTUpdateable" to distinguish it from the non-calculated field so please change its name. If you created the query correctly, the ActionFlg should be showing the value from the table. you do NOT need to populate it. That was the whole point of adding the join to the query. REMOVE this code:

    'Dim strUnitAvail As Integer
    'strUnitAvail = Me.ActionID.Value
    Select Case ActionID
        Case Is = 1: ActionFlgUpdateable = 0
        Case Is = 2: ActionFlgUpdateable = 0
        Case Is = 3: ActionFlgUpdateable = 0
        Case Is = 6: ActionFlgUpdateable = 1
        Case Is = 7: ActionFlgUpdateable = 1
        Case Is = 8: ActionFlgUpdateable = 1
        Case Is = 10: ActionFlgUpdateable = 1
              
    End Select

You also didn't follow my directions on how to start to fix the SQL. Doc helped you out by giving you the code to fix it.

 

[regnewby2465]

What part of NO CODE did you not understand. The query with the join populates the flag. I meant to name it "ActionFlgNOTUpdateable" to distinguish it from the non-calculated field so please change its name. If you created the query correctly, the ActionFlg should be showing the value from the table. you do NOT need to populate it. That was the whole point of adding the join to the query. REMOVE this code:

    'Dim strUnitAvail As Integer
    'strUnitAvail = Me.ActionID.Value
    Select Case ActionID
        Case Is = 1: ActionFlgUpdateable = 0
        Case Is = 2: ActionFlgUpdateable = 0
        Case Is = 3: ActionFlgUpdateable = 0
        Case Is = 6: ActionFlgUpdateable = 1
        Case Is = 7: ActionFlgUpdateable = 1
        Case Is = 8: ActionFlgUpdateable = 1
        Case Is = 10: ActionFlgUpdateable = 1
             
    End Select

You also didn't follow my directions on how to start to fix the SQL. Doc helped you out by giving you the code to fix it.

I put in the FROM syntax that he gave me and it locked up.

I changed the The SQL to show name ActionFlgNOTUpdateable.

The SQL executes okay and shows me all the fields in the CADLogT, and the ActionFlgNOTUpdateable field I just added.

I removed the code as you just told me to. Nothing locks up but there is no change in the ActionFlgNOTUpdateable control or on the source table in regards to the unit being available or not. (1 or 0), nor does it change the availability of the UnitAvailable field in the parent form or its table.

I appreciate all time time you and Doc gave me but this is not going anywhere. I GIVE UP, but again thanks.

 

[Pat Hartman]

There is no reason to show the flag on the record. That is redundant. Remove the field from the table and use the technique with the join whenever you need to see it.

If you insist on storing it (which is wrong), then bind the control to the original field which is ActionFlg rather than ActionFlgNotUpdateable. The code then becomes a single line:

ActionFlg = ActionFlgNotUpdateable

You don't need the case statement because the field we added to the query contains the correct value for that record. No decision is necessary.

I say it again - storing redundant data violates normalization rules and leads you to data anomalies. It is poor practice.

 

[regnewby2465]

There is no reason to show the flag on the record. That is redundant. Remove the field from the table and use the technique with the join whenever you need to see it.

If you insist on storing it (which is wrong), then bind the control to the original field which is ActionFlg rather than ActionFlgNotUpdateable. The code then becomes a single line:

ActionFlg = ActionFlgNotUpdateable

You don't need the case statement because the field we added to the query contains the correct value for that record. No decision is necessary.

I say it again - storing redundant data violates normalization rules and leads you to data anomalies. It is poor practice.

This is all I have as the record source for the CAD_Log_DispF

which has the following SQL statement of course

SELECT CADLogT.ID, CADLogT.EntryDateTime, CADLogT.Notes, CADLogT.ActionID, CADLogT.TourID, CADLogT.Dispo, CADLogT.EmployeeID, CADLogT.ID_Activity, [CADLogT].[UnitAvailable] & "" AS ActionFlgNOTUpdateable
FROM CADLogT INNER JOIN DispActionT ON CADLogT.ActionID = DispActionT.ID;

I don't have the ActionFlg on the form either

This is the only code on the form

Private Sub Form_BeforeUpdate(Cancel As Integer)

    Me.EmployeeID = DLookup("EmployeeID", "LocalUserT")
    
    If IsNull([EntryDateTime]) Then
        [EntryDateTime] = Now()
    End If
        
End Sub

I don't have any ActionFlg field on the source table for the CAD_Log_DispF. The only thing I have is the ActionID.

Taking one step at a time.... Is this correct so far?

 

[Pat Hartman]

I think you called the unnecessary field Id_Activity. But I can't see its full name in the form view and you cut off the table description in the query so I can't see the actual name of the field there either so I am only guessing.

Storing redundant data is poor practice.

 

[regnewby2465]

I think you called the unnecessary field Id_Activity. But I can't see its full name in the form view and you cut off the table description in the query so I can't see the actual name of the field there either so I am only guessing.

Storing redundant data is poor practice.

ID_Activity is a FK that is required to link that subform to the parent form, which has a table source called ActivityT. ActivityT has a PK of ID. The Master link is ID, and the child link is ID_Activity.

 

[Pat Hartman]

WHAT IS THE NAME OF THE FIELD THAT YOU WERE STORING THE YES/NO VALUE IN?????? That is the field the control needs to be bound to. That was the field the form used to be bound to before I asked you to change it. I CAN'T SEE YOUR DATABASE and you are not helping.

If you insist on the poor practice of storing redundant data, the ONE LINE OF CODE you need is:

SOMEFIELDNAMETHATIDONTKNOW= ActionFlgNotUpdateable

I doubt I'll be back so hope that works.

 

[regnewby2465]

WHAT IS THE NAME OF THE FIELD THAT YOU WERE STORING THE YES/NO VALUE IN?????? That is the field the control needs to be bound to. That was the field the form used to be bound to before I asked you to change it. I CAN'T SEE YOUR DATABASE and you are not helping.

If you insist on the poor practice of storing redundant data, the ONE LINE OF CODE you need is:

SOMEFIELDNAMETHATIDONTKNOW= ActionFlgNotUpdateable

I doubt I'll be back so hope that works.

I think there is a disconnect on what you think I am wanting to achieve and what I am wanting to achieve.... but again that is the difficulty in typing messages back and forth.... the context and detail gets lost. The other problem is that I am only learning VBA. The reason I came to this forum is to seek guidance and to learn... but it seems that everyone is trying to explain it at a level above my understanding. Giving bits and pieces of the entire process only confuses me at my level. That's why I wanted to go step by step. But we have both wasted enough time on this.... I guess I will find another way to get what I want. To answer your questions though, the field is UnitAvailable in the CADLogT as shown below.

I don't see any way, without code, to get the ActionID to change the value in the UnitAvailable field to either 1 or 0. It was working before with the CASE ladder, but that was not achieving my ultimate objective. That being to change the UnitAvailable field in the TourT table to toggle 1 or 0 based on the ActionID in this form. But I appreciate you trying to help me... but I will find another way.

 

[The_Doc_Man]

@regnewby2465

It is clear to me that you are in a situation where you are trying like crazy to get something done and it is elusive to you. This is actually one of the dangers of Access. It is FAR too easy to get something started that looks good but then you realize it is incomplete - and when you start delving into how to complete it, you suddenly realize that you lack the necessary skills. Please don't take that as a knock, because I got thrown into a database problem myself for my first database and it took me a while to wrap my head around the concepts involved. The learning curve on Access (or in fact on ANY relational database) is a true beast, incredibly steep, and at the moment you are frustrated by seemingly getting nowhere.

A logistics problem is that it would be inappropriate for us to write this for you and if we did, you would have to come back to us if something goes wrong or needs a new feature. That kind of obligation belongs with someone who is salaried or on long-term retainer as a consultant. (No, I'm not going to volunteer for that - the responsibility is too high and I'm too old.) Someone on the forum COULD send you a private message (profile message) to offer services but I don't know if that is what you want.

We will be here if you have Access or concept questions that we can answer, but I sense some question about whether you trust our answers. I get that based on the back-and-forth discussions I'm seeing here. And there, we have a barrier to success. If you don't trust the answers or if you don't understand the answers, what good are we doing? And realize this: If we have to hold your hand every step of the way for a big project, how can we be fair to the rest of our members?

I think one improvement here is for you to frame simple questions that don't take a walk through the forest when you need to be guided past the next tree. You said you were a beginner. We need to answer beginner questions but sometimes your questions aren't beginner level because you are so anxious to jump ahead to get your project moving forward. I have no doubt you can manage this but I must emphasize that it is never a fast process for new Access designers.

Like I said, it isn't intended as a hard knock against you. Access itself supplies all the hard knocks anyone ever needs. But if we cannot find a meeting of the minds on how to help you, nobody wins here. And we don't like that. It is up to you.

 

[Pat Hartman]

I know what you want to achieve. And I told you it was wrong. Then I told you how to do it anyway. I just don't know the name of the field you want to save the duplicate data in.

Then the ONE line of code is

Me.UnitAvailable = Me.ActionFlgNotUpdateable

 

[regnewby2465]

@regnewby2465

It is clear to me that you are in a situation where you are trying like crazy to get something done and it is elusive to you. This is actually one of the dangers of Access. It is FAR too easy to get something started that looks good but then you realize it is incomplete - and when you start delving into how to complete it, you suddenly realize that you lack the necessary skills. Please don't take that as a knock, because I got thrown into a database problem myself for my first database and it took me a while to wrap my head around the concepts involved. The learning curve on Access (or in fact on ANY relational database) is a true beast, incredibly steep, and at the moment you are frustrated by seemingly getting nowhere.

A logistics problem is that it would be inappropriate for us to write this for you and if we did, you would have to come back to us if something goes wrong or needs a new feature. That kind of obligation belongs with someone who is salaried or on long-term retainer as a consultant. (No, I'm not going to volunteer for that - the responsibility is too high and I'm too old.) Someone on the forum COULD send you a private message (profile message) to offer services but I don't know if that is what you want.

We will be here if you have Access or concept questions that we can answer, but I sense some question about whether you trust our answers. I get that based on the back-and-forth discussions I'm seeing here. And there, we have a barrier to success. If you don't trust the answers or if you don't understand the answers, what good are we doing? And realize this: If we have to hold your hand every step of the way for a big project, how can we be fair to the rest of our members?

I think one improvement here is for you to frame simple questions that don't take a walk through the forest when you need to be guided past the next tree. You said you were a beginner. We need to answer beginner questions but sometimes your questions aren't beginner level because you are so anxious to jump ahead to get your project moving forward. I have no doubt you can manage this but I must emphasize that it is never a fast process for new Access designers.

Like I said, it isn't intended as a hard knock against you. Access itself supplies all the hard knocks anyone ever needs. But if we cannot find a meeting of the minds on how to help you, nobody wins here. And we don't like that. It is up to you.

I appreciate your encouragement.

 

[Gasman]

We are 50+ posts into this and no further forward.
Might be worth uploading a sample db with the issue and someone will likely amend it for you.
It would then be for you to implement into your DB?

Alternatively, if your DB is small enough and not confidential, upload it zipped.

 

[regnewby2465]

We are 50+ posts into this and no further forward.
Might be worth uploading a sample db with the issue and someone will likely amend it for you.
It would then be for you to implement into your DB?

Alternatively, if your DB is small enough and not confidential, upload it zipped.

Good idea. I did not know that was possible. It is all sample data other than addresses. It is 22 meg. If I zip it, can it be attached using the Attach Files button below? How do I know who will get it and who might work on it?

 

[Gasman]

Hmm 22MB even zipped would likely be too big.
What do you care who gets it, as long as someone works on it?
It would be available to all though, that is why no confidential data.
Best create a sample db with enough records to show the problem. Make sure the problem still exists in the sample db, then compact, zip and upload.

 

[regnewby2465]

I just didn't know if I was to send it to someone in particular or just post it for who ever.

 

[Pat Hartman]

You would only send a db to an expert if you expected to pay for his time.