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

[regnewby2465]

This is how I made that table I believe you are referring to as the "action table".

 

[regnewby2465]

With the following code in the BeforeUpdate of the subform CAD_Log_DispF, that subform acts correctly.

Private Sub Form_BeforeUpdate(Cancel As Integer)

    Me.EmployeeID = DLookup("EmployeeID", "LocalUserT")
  
    If IsNull([EntryDateTime]) Then
        [EntryDateTime] = Now()
    End If
  
  
    Dim strUnitAvail As Integer
    strUnitAvail = Me.ActionID.Value
    Select Case ActionID
        Case Is = 1: UnitAvailable = 0
        Case Is = 2: UnitAvailable = 0
        Case Is = 3: UnitAvailable = 0
        Case Is = 6: UnitAvailable = 1
        Case Is = 7: UnitAvailable = 1
        Case Is = 8: UnitAvailable = 1
        Case Is = 10: UnitAvailable = 1
            
    End Select
  
  
  
End Sub

So far, so good. What I am needing is the UnitAvailable value in the TourT (which is the source table for the parent form CAD_CallDispSplitF) to change to either 1 or 0 to match the current value of 1 or 0 in the subform. I am confused as to what to LEFT JOIN. Do I want to make change the record source on the parent form to a query that has the join, or change the record source for the subform to have the query for the LEFT JOIN?

 

[regnewby2465]

a Yes/No field has TWO possible values. True and False. "blank" isn't even a value let alone a valid one for this data type. I see you've changed the data type to Number. Showing users 0 and 1 instead of True and False or Yes and No is pretty unfriendly. You can use a combo to convert 1, 0, Null to something meaningful to the user.

A Left join is used whenever a FK value is optional. If the ActionID is defined as required, you can use the default Inner join.

Select Maintbl.TourID, Maintbl.ActionID, ..., DispActT.ActionFlg & "" As ActionFlgNotUpdateable
From Maintbl Left Join Maintbl.ActionID = DispActT.ActionID;

Don't use ID as the name of your PK. That makes no sense to anyone looking at the schema. A developer should be able to pick out which table a FK points to because its name matches the name of the PK in some table.

You are probably also using a lookup field. Your label says "ActionID" but the value shown is "Unicode". There's lots of warnings about why you should not use lookup fields on tables. Combos on forms are great lookup fields on tables are bad

Fix the query with the correct fields and then replace the existing RecordSource of the form with the new query. When you select an ActionID from the combo, the ActionFlgNotUpdateable field will AUTOMATICALLY populate. NO CODE

Is this what you mean?

SELECT CADLogT.TourID, CADLogT.ActionID, DispActionT.ActionFlg & "" As ActionFlgNotUpdateable
FROM CADLogT LEFT JOIN CADLogT.ActionID = DispActionT.ActionID;

 

[regnewby2465]

You need to include ALL the columns, not just the few I mentioned. AND you need to change the PK name of DispActionT if you are going to take my advice.

Although I understand your reasoning for making the PK a meaningful name, because I do not want to go and change the whole structure once again after doing it yesterday for many hours, I am going to keep using the system generated ID. To that end, this is what I wrote, but I get an error on the = sign when executing it.

SELECT CADLogT.ID, CADLogT.EntryDateTime, CADLogT.Notes, CADLogT.ActionID, CADLogT.TourID, CADLogT.Dispo, CADLogT.EmployeeID, CADLogT.ID_Activity, CADLogT.UnitAvailable &"" As ActionFlgUpdateable
FROM CADLogT LEFT JOIN CADLogT.ActionID = DispActionT.ID;

 

[regnewby2465]

OK, did you try it? I have no way of knowing if the query is correct by just looking at it. Save it as a querydef and run it? Do you get the expected results, can you update the ActionFlgUpdateable field? You shouldn't be able to. Bind it to the form and bind the ActionFlgUpdateable field to the control.

I executed it from the query design and got this error

SELECT CADLogT.ID, CADLogT.EntryDateTime, CADLogT.Notes, CADLogT.ActionID, CADLogT.TourID, CADLogT.Dispo, CADLogT.EmployeeID, CADLogT.ID_Activity, CADLogT.UnitAvailable &"" As ActionFlgUpdateable
FROM CADLogT LEFT JOIN CADLogT.ActionID = DispActionT.ID;

 

[regnewby2465]

My fault. it is "Left JOIN ON"

Always build your queries using QBE to avoid silly typos like this.

No worries... but still an error

 

[regnewby2465]

No worries... but still an error

View attachment 102623

Do you mean the entire FROM line?

 

[regnewby2465]

REPLACE what you have with what I posted. That removed the "join". QBE will give you a graphic interface where you can draw a join line from one table to the other. Right click on the join line to choose the "left" join option. Read the three options carefully so you can choose the correct option. "left" is determined by how you added objects to the window which is why you need to read the options to choose correctly.

If this is what you wanted me to try, it will not get past the error to let me get to the window where i can set the joins.

SELECT CADLogT.ID, CADLogT.EntryDateTime, CADLogT.Notes, CADLogT.ActionID, CADLogT.TourID, CADLogT.Dispo, CADLogT.EmployeeID, CADLogT.ID_Activity, CADLogT.UnitAvailable &"" As ActionFlgUpdateable
FROM CADLogT,CADLogT.ActionID = DispActionT.ID;

SELECT CADLogT.ID, CADLogT.EntryDateTime, CADLogT.Notes, CADLogT.ActionID, CADLogT.TourID, CADLogT.Dispo, CADLogT.EmployeeID, CADLogT.ID_Activity, CADLogT.UnitAvailable &"" As ActionFlgUpdateable
FROM CADLogT,CADLogT.ActionID = DispActionT.ID;

 

[The_Doc_Man]

The correct form of a joining FROM clause is

SELECT ... FROM table1 INNER JOIN  (or LEFT JOIN or RIGHT JOIN) table2 ON table1.keyfield = table2.matchingfield .... other parts

When we talk about relational databases, the above is a representation. Here are the key parts:

In any query, you use the SELECT field, field, ..., field clause to show specifically what fields you want.

The FROM keyword introduces the clause that identifies source tables.

In my example, you are trying to get data from two tables. The JOIN keyword (with one of the three possible prefixes) identifies to SQL that you are combining two tables via JOINing. You could have been joining in other ways OR could have been going for what is called a Cartesian JOIN - which paradoxically doesn't use the word JOIN. But you are not doing that. You actually DO want a relation-based JOIN. A corollary of using a JOIN keyword is that you MUST have two tables named in that clause.

The ON keyword is another "relational" part. You presume there is a relationship between the two tables, and that relation is identified by saying that a key field in one table should match a field in another table. So the phrase "...JOIN ... ON..." is identifying the specific connection between the two related tables. A corollary of using the ON clause is that you must specify two fields by qualifying their tables of origin along with the field names.

What follows the ON clause would include WHERE, ORDER BY, and other syntax for other things you do with SQL.

Your last presentation was FROM CADLogT,CADLogT.ActionID = DispActionT.ID;

I'm going to GUESS that it should look more like

FROM CADLogT INNER JOIN DispActionT ON CADLogT.ActionID = DispActionT.ID

Read through my discussion and identify the parts I mentioned so that you can see how that works. I used INNER JOIN because from previous discussions, I know that you should never have an entry in your CAD Log that is unmatched in the Disp Action table. If you intended for gaps to exist, there might be a case to use LEFT JOIN.

 

[regnewby2465]

The correct form of a joining FROM clause is

SELECT ... FROM table1 INNER JOIN  (or LEFT JOIN or RIGHT JOIN) table2 ON table1.keyfield = table2.matchingfield .... other parts

When we talk about relational databases, the above is a representation. Here are the key parts:

In any query, you use the SELECT field, field, ..., field clause to show specifically what fields you want.

The FROM keyword introduces the clause that identifies source tables.

In my example, you are trying to get data from two tables. The JOIN keyword (with one of the three possible prefixes) identifies to SQL that you are combining two tables via JOINing. You could have been joining in other ways OR could have been going for what is called a Cartesian JOIN - which paradoxically doesn't use the word JOIN. But you are not doing that. You actually DO want a relation-based JOIN. A corollary of using a JOIN keyword is that you MUST have two tables named in that clause.

The ON keyword is another "relational" part. You presume there is a relationship between the two tables, and that relation is identified by saying that a key field in one table should match a field in another table. So the phrase "...JOIN ... ON..." is identifying the specific connection between the two related tables. A corollary of using the ON clause is that you must specify two fields by qualifying their tables of origin along with the field names.

What follows the ON clause would include WHERE, ORDER BY, and other syntax for other things you do with SQL.

Your last presentation was FROM CADLogT,CADLogT.ActionID = DispActionT.ID;

I'm going to GUESS that it should look more like

FROM CADLogT INNER JOIN DispActionT ON CADLogT.ActionID = DispActionT.ID

Read through my discussion and identify the parts I mentioned so that you can see how that works. I used INNER JOIN because from previous discussions, I know that you should never have an entry in your CAD Log that is unmatched in the Disp Action table. If you intended for gaps to exist, there might be a case to use LEFT JOIN.

Thank you Doc. I will try it, and you are correct regarding the relationship between the CADLogT and the ActionID from the DispActionT as shon here.

The correct form of a joining FROM clause is

SELECT ... FROM table1 INNER JOIN  (or LEFT JOIN or RIGHT JOIN) table2 ON table1.keyfield = table2.matchingfield .... other parts

When we talk about relational databases, the above is a representation. Here are the key parts:

In any query, you use the SELECT field, field, ..., field clause to show specifically what fields you want.

The FROM keyword introduces the clause that identifies source tables.

In my example, you are trying to get data from two tables. The JOIN keyword (with one of the three possible prefixes) identifies to SQL that you are combining two tables via JOINing. You could have been joining in other ways OR could have been going for what is called a Cartesian JOIN - which paradoxically doesn't use the word JOIN. But you are not doing that. You actually DO want a relation-based JOIN. A corollary of using a JOIN keyword is that you MUST have two tables named in that clause.

The ON keyword is another "relational" part. You presume there is a relationship between the two tables, and that relation is identified by saying that a key field in one table should match a field in another table. So the phrase "...JOIN ... ON..." is identifying the specific connection between the two related tables. A corollary of using the ON clause is that you must specify two fields by qualifying their tables of origin along with the field names.

What follows the ON clause would include WHERE, ORDER BY, and other syntax for other things you do with SQL.

Your last presentation was FROM CADLogT,CADLogT.ActionID = DispActionT.ID;

I'm going to GUESS that it should look more like

FROM CADLogT INNER JOIN DispActionT ON CADLogT.ActionID = DispActionT.ID

Read through my discussion and identify the parts I mentioned so that you can see how that works. I used INNER JOIN because from previous discussions, I know that you should never have an entry in your CAD Log that is unmatched in the Disp Action table. If you intended for gaps to exist, there might be a case to use LEFT JOIN.

Doc, that worked when I ran it from the Query design window, however, when I used it as the Record Source for CAD_Log_DispF, it got stuck on Case 1 in the case ladder.

Private Sub Form_BeforeUpdate(Cancel As Integer)

    Me.EmployeeID = DLookup("EmployeeID", "LocalUserT")
    
    If IsNull([EntryDateTime]) Then
        [EntryDateTime] = Now()
    End If
    
    
    '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
    
End Sub

 

[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.

 

[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?

 

[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.

 

[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.

 

[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.