I'm trying to get a form control that is doing the following as the Control Source to add it to a field in the associated table.
我正在尝试获取一个表单控件，它正在执行以下操作作为控件源将其添加到关联表中的字段。
=[Company] & " " & IIf([Cable]="Fiber","FO Cable","CC Cable") & " " & [CableStartPoint] & "-" & [CableEndPoint]
The above displays something along the following in the control on the form when associated fields are entered -> Company FO Cable Start-End
输入关联字段时，上面显示表格控件中的以下内容 - >公司FO电缆开始 - 结束
I have tried using the expression in the After Update and the other On ... properties. This left me with a blank value though.
我尝试使用After Update中的表达式和其他On ...属性。这给我留下了空白值。
My next go to was to use VBA and I tried using something along the lines of VBA from this question (Access form calculated fields) but my VBA skills seem to have diminished some and I'm not sure how to proceed to meet my needs... :(
我的下一步是使用VBA，我尝试使用VBA这个问题（访问表单计算字段），但我的VBA技能似乎减少了一些，我不知道如何继续满足我的需求。 .. :(
My main goal is to not have to manually enter the information for this to prevent typos and entering redundant information that is already stored in the database such as company, start point, and end point.
我的主要目标是不必手动输入此信息以防止打字错误并输入已存储在数据库中的冗余信息，例如公司，起点和终点。
If this could be done with SQL, I am interested in that solution as well. This is what I think I need for my SQL, although I'm not sure where it needs to go to update the record (After, Before, or just use the Code builder).
如果可以使用SQL完成，我也对该解决方案感兴趣。这就是我认为我需要的SQL，虽然我不确定它需要更新记录的位置（After，Before，或者只是使用Code构建器）。
UPDATE tblCableDetails
SET CompletedLabel=Form!CableDetails!Label.Value
WHERE CableDetailsID=Form!CableDetailsID.Value
The reason I would like to store these in the database vs running the expression when needed, is that I would like to use this value in another table as a lookup for another form to track specifics regarding the cable.
我想将这些存储在数据库中而不是在需要时运行表达式的原因是我想在另一个表中使用此值作为查找另一个表单以跟踪有关电缆的细节。
Thank you all for your time!
谢谢大家的时间！
2 个解决方案
#1
1
If all of the fields are in the same table, Access Tables have a Calculated data type accessible in the Design View of the table which will handle this, quite easily. This would be your simplest route; just use your expression in the calculated field as pictured below. Note that Calculated fields have other potential limitations down the road, but I would not worry too much about that. The name of your calculated field would be CompletedLabel, and Access will maintain its value based on the other fields current value in the expression.
如果所有字段都在同一个表中，则Access表具有可在表的设计视图中访问的计算数据类型，这将很容易处理。这将是你最简单的路线;只需在计算字段中使用您的表达式，如下图所示。请注意，计算字段在未来有其他潜在的限制，但我不会过分担心。计算字段的名称将为CompletedLabel，Access将根据表达式中的其他字段当前值维护其值。
If the fields are not in the same table and the Form is bound and Access is handling Saves (meaning the form has a RecordSource property, and you have Access handling the saving of the record when a user changes a value, they navigate to a new record, they close the form, etc.), you could add a VBA BeforeUpdate event that stamps the column just before Access auto-saves the field. Just add the CompletedLabel field to the form (it can be hidden), then create a Before update event:
如果这些字段不在同一个表中并且表单已绑定且Access正在处理保存（意味着表单具有RecordSource属性，并且您在用户更改值时让Access处理保存记录，则会导航到新的记录，他们关闭表单等），您可以添加一个VBA BeforeUpdate事件，在事件自动保存字段之前标记列。只需将CompletedLabel字段添加到表单（可以隐藏），然后创建Before更新事件：
Private Sub Form_BeforeUpdate(Cancel As Integer)
Me!CompletedLabel = [Company] & " " & IIf([Cable]="Fiber","FO Cable","CC Cable") & " " & [CableStartPoint] & "-" & [CableEndPoint]
End Sub
In some cases you will not even need the CompletedLabel textbox on the form, but it must be in the record source (query or table) that the form is based on. It must also be editable which is likely already the case.
在某些情况下，您甚至不需要表单上的CompletedLabel文本框，但它必须位于表单所基于的记录源（查询或表）中。它也必须是可编辑的，这可能已经是这种情况。
You could do it with SQL as you suggest, perhaps in an After Update event.
您可以按照建议使用SQL，也许在After Update事件中执行此操作。
UPDATE
tblCableDetails
SET
CompletedLabel=[Company] & " " & IIf([Cable]="Fiber","FO Cable","CC Cable") & " " & [CableStartPoint] & "-" & [CableEndPoint]
WHERE
CableDetailsID=Form!CableDetailsID.Value
Or with an external query when the form opens or closes, like:
或者在表单打开或关闭时使用外部查询，例如：
UPDATE
tblCableDetails
SET
CompletedLabel=[Company] & " " & IIf([Cable]="Fiber","FO Cable","CC Cable") & " " & [CableStartPoint] & "-" & [CableEndPoint]
WHERE
CompletedLabel = null Or Nz(CompletedLabel,"") = ""
You could also create a Query that contains the expression, and use it in the locations you need to see the calculated value.
您还可以创建包含表达式的查询，并在需要查看计算值的位置使用它。
Lots of possibilities. Good luck, Tag!!
很多可能性。祝你好运，标签！
#2
0
This is not done with expression in event property that does the calc.
这不是通过执行calc的event属性中的表达式来完成的。
Macro or VBA code is required to save calculated data, I prefer VBA.
保存计算数据需要宏或VBA代码，我更喜欢VBA。
Is this form bound to the table you want to save data into? If so, the appropriate approach is simply:
此表单是否绑定到要将数据保存到的表中？如果是这样，适当的方法就是：
Me!fieldname = Me.textboxname
我！fieldname = Me.textboxname
The real trick, as you have discovered, is figuring out the correct event to put this code into. Select [Event procedure] in preferred event property then click the ellipsis (...) to go to the VBA procedure and type the desired code.
正如您所发现的那样，真正的技巧是找出将此代码放入的正确事件。在首选事件属性中选择[事件过程]，然后单击省略号（...）转到VBA过程并键入所需的代码。
#1
1
If all of the fields are in the same table, Access Tables have a Calculated data type accessible in the Design View of the table which will handle this, quite easily. This would be your simplest route; just use your expression in the calculated field as pictured below. Note that Calculated fields have other potential limitations down the road, but I would not worry too much about that. The name of your calculated field would be CompletedLabel, and Access will maintain its value based on the other fields current value in the expression.
如果所有字段都在同一个表中，则Access表具有可在表的设计视图中访问的计算数据类型，这将很容易处理。这将是你最简单的路线;只需在计算字段中使用您的表达式，如下图所示。请注意，计算字段在未来有其他潜在的限制，但我不会过分担心。计算字段的名称将为CompletedLabel，Access将根据表达式中的其他字段当前值维护其值。
If the fields are not in the same table and the Form is bound and Access is handling Saves (meaning the form has a RecordSource property, and you have Access handling the saving of the record when a user changes a value, they navigate to a new record, they close the form, etc.), you could add a VBA BeforeUpdate event that stamps the column just before Access auto-saves the field. Just add the CompletedLabel field to the form (it can be hidden), then create a Before update event:
如果这些字段不在同一个表中并且表单已绑定且Access正在处理保存（意味着表单具有RecordSource属性，并且您在用户更改值时让Access处理保存记录，则会导航到新的记录，他们关闭表单等），您可以添加一个VBA BeforeUpdate事件，在事件自动保存字段之前标记列。只需将CompletedLabel字段添加到表单（可以隐藏），然后创建Before更新事件：
Private Sub Form_BeforeUpdate(Cancel As Integer)
Me!CompletedLabel = [Company] & " " & IIf([Cable]="Fiber","FO Cable","CC Cable") & " " & [CableStartPoint] & "-" & [CableEndPoint]
End Sub
In some cases you will not even need the CompletedLabel textbox on the form, but it must be in the record source (query or table) that the form is based on. It must also be editable which is likely already the case.
在某些情况下，您甚至不需要表单上的CompletedLabel文本框，但它必须位于表单所基于的记录源（查询或表）中。它也必须是可编辑的，这可能已经是这种情况。
You could do it with SQL as you suggest, perhaps in an After Update event.
您可以按照建议使用SQL，也许在After Update事件中执行此操作。
UPDATE
tblCableDetails
SET
CompletedLabel=[Company] & " " & IIf([Cable]="Fiber","FO Cable","CC Cable") & " " & [CableStartPoint] & "-" & [CableEndPoint]
WHERE
CableDetailsID=Form!CableDetailsID.Value
Or with an external query when the form opens or closes, like:
或者在表单打开或关闭时使用外部查询，例如：
UPDATE
tblCableDetails
SET
CompletedLabel=[Company] & " " & IIf([Cable]="Fiber","FO Cable","CC Cable") & " " & [CableStartPoint] & "-" & [CableEndPoint]
WHERE
CompletedLabel = null Or Nz(CompletedLabel,"") = ""
You could also create a Query that contains the expression, and use it in the locations you need to see the calculated value.
您还可以创建包含表达式的查询，并在需要查看计算值的位置使用它。
Lots of possibilities. Good luck, Tag!!
很多可能性。祝你好运，标签！
#2
0
This is not done with expression in event property that does the calc.
这不是通过执行calc的event属性中的表达式来完成的。
Macro or VBA code is required to save calculated data, I prefer VBA.
保存计算数据需要宏或VBA代码，我更喜欢VBA。
Is this form bound to the table you want to save data into? If so, the appropriate approach is simply:
此表单是否绑定到要将数据保存到的表中？如果是这样，适当的方法就是：
Me!fieldname = Me.textboxname
我！fieldname = Me.textboxname
The real trick, as you have discovered, is figuring out the correct event to put this code into. Select [Event procedure] in preferred event property then click the ellipsis (...) to go to the VBA procedure and type the desired code.
正如您所发现的那样，真正的技巧是找出将此代码放入的正确事件。在首选事件属性中选择[事件过程]，然后单击省略号（...）转到VBA过程并键入所需的代码。