更新时间:2023-11-12 08:32:58
这里的问题是,窗体上显示为连续表单的未绑定文本框将始终显示每个记录相同的值。您可能适用于未绑定文本框的任何VBA操作将仅使用当前记录中的数据。
解决方法是在Access中创建保存的查询计算每个记录的状态,然后根据查询的形式,将文本框绑定到计算的[EmptyFields]字段。
就是样本数据在[Table1]
id名称姓氏地址
- ----- -------- ---------------------
1 Gord Thompson 123 Main St
2荷马742长荣露台
3佛兰德斯
您可以创建一个这样保存的查询
SELECT
[id],
[Name],
[Surname],
[Address],
Mid(IIf(IsNull([Name])),Name,)& IIf(IsNull([Surname]),,Surname )& IIf(IsNull([Address]),,Address,),3)AS EmptyFields
FROM Table1
返回
id名称姓氏地址EmptyFields
- - ----- -------- --------------------- -------------
1 Gord Thompson 123 Main St
2荷马742长荣露台姓氏
3佛兰德斯名字,地址
,您可以将该查询用作表单的记录来源
。
I have a table (id, Name, Surname, Address etc.). All the fields (except id) can be NULL. I want to make a form where I can find all the records that have at least a NULL field. I have made a query (with the query designer) and then I "linked" a continuous form to it.
In the Detail part of the form I put a textbox ID (linked to the query) so I can have all the IDs that have at least a field NULL. So far so good, it works.
I would like to inform the user, after the ID, which fields are blank. So I put another textbox (named txt) and making controls like If isNull(Me.Name) then Me.txt.Value ="Name field is blank". It works perfectly but only for the first record. All the other records have the same message in the textbox txt.
The code is like this (of course stringW and lngth are declared)
If IsNull(Me.Name.Value) Then
stringW = stringW & " Name field,"
End If
..... (the same for Surname, Tel number etc)
lngth = Len(stringW) - 1
stringW = Left$(stringW, lngth)
Me.txt.Value = stringW
It seems that the form loads (I put the code in the load section), it makes the controls once and then copies the content of the stringW in the txt textbox for every record.
How could I resolve it? I mean, how can I have a textbox in the continuous form that changes its' contents informing the user for the blank fields of the record?
The problem here is that an unbound Text Box on a form that is displayed as Continuous Forms will always display the same value for every record. Any VBA manipulations you might apply to the unbound Text Box will only use the data from the current record.
A workaround for this would be to create a saved query in Access that calculates the status for each record, then base your form on the query and have the Text Box bound to the calculated [EmptyFields] field.
That is, for the sample data in [Table1]
id Name Surname Address
-- ----- -------- ---------------------
1 Gord Thompson 123 Main St
2 Homer 742 Evergreen Terrace
3 Flanders
you could create a saved query like this
SELECT
[id],
[Name],
[Surname],
[Address],
Mid(IIf(IsNull([Name]),", Name","") & IIf(IsNull([Surname]),", Surname","") & IIf(IsNull([Address]),", Address",""),3) AS EmptyFields
FROM Table1
which returns
id Name Surname Address EmptyFields
-- ----- -------- --------------------- -------------
1 Gord Thompson 123 Main St
2 Homer 742 Evergreen Terrace Surname
3 Flanders Name, Address
and you could use that query as the Record Source
for your form.