I appreciate the time you took to explain all that. I just need someone to please tell me step by step what I need to do to achieve what I am wanting to achieve using the table names, the field names and the form controls. If Code, what is the code and where does it go. If a JOIN, what field get joined in what tables or form record sources. Do I still need to use CASE, if so, what should the CASE ladder be and where should it go? Those are the things that I need explained or shown to me please.
I appreciate the time you are taking to try to help me with this, but I just need someone to please tell me step by step what I need to do to achieve what I am wanting to achieve using the table names, the field names and the form controls. If Code, what is the code and where does it go. If a JOIN, what field get joined in what tables or form record sources. Do I still need to use CASE, if so, what should the CASE ladder be and where should it go? Those are the things that I need explained or shown to me please.
I already have a table called DispActionT. It has basically a Yes/No field but using a numerical 1 for Yes and 0 for No. Perhaps you missed me explaining all that in my earlier reply to you. This is getting so convoluted in this thread that it is getting terribly frustrating.
As I explained, you had two values which were neither yes nor no. Are those values now gone?
Since you have the table already, then use it as I suggested. Join the table to the main table in the RecordSource of the form. In order to ensure the yes/no field will not accidentally get changed, make it a calculated value as I explained earlier. Did you try that?
With the following code in the BeforeUpdate of the subform CAD_Log_DispF, that subform acts correctly.
Code:
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?
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
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
SELECT CADLogT.TourID, CADLogT.ActionID, DispActionT.ActionFlg & "" As ActionFlgNotUpdateable
FROM CADLogT LEFT JOIN CADLogT.ActionID = DispActionT.ActionID;
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.
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.
Code:
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;
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.
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.
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.
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.
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.
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.
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.
Code:
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
lookup fields on tables are bad
