Creating a new field on a from by means of SQL string or some other means

[GeorgeJ]

Hello. I have a question. I have a tabular form that has as its record source an SQL string. The SQL string takes its records from one table. What I would like to do is add an extra field in the tabular form (a boolean field) in the detail area of the form which does not correspond to any field in the table from which the records are drawn, but which will appear on the same level as a specific record. I want to do this so the user can check this field for desired records to select a group of the displayed records for special attention. How might I do this? Can I modify the SQL string to create an extra field just for the form which does not correspond to a field in the table from which the records come?



Thank you in advance

[June7]

No, the constructed field is not editable and would show the same value for every record.

The checkbox would have to be bound to a field, otherwise every record will show the same value

[GeorgeJ]

Thank you. So if I understand you, the only way to do what I want to do would be to create a temporary table (TempTable) with the relevant fields from the
main table plus the new Boolean field, then populate this table by means of code with the records which would have been grabbed by the form's SQL statement, and have the record source of the form be this new table instead of the SQL statement. Actually, now that I think about it, the best thing would be to include this dummy Boolean field in the original table, and have it included by the SQL statement, and then have the Form_Load have code like this...


sub Form_Load
Dim db as DAO.database, RST as DAO.recordset
set db = currentdb
set RST = db.openrecordset(me.SQL)
do while not rst.eof
rst.edit
rst!dummy = false
rst.update
rst.movenext
loop
end sub

This seems cumbersom but maybe its the best way.

What do you think?

[June7]

If you want to make sure the form opens with all records set to False for the Yes/No field, try:

Sub Form_Load()
CurrentDb.Execute "UPDATE tablename SET dummy=False"
Me.Refresh
End Sub

Is this a multi-user db?

[GeorgeJ]

1) That works great, Thanks
2) No, its not multi-user


It doesn't add significantly to the DB size to add the Boolean field to the table, and the update command you gave me is very fast. In my sample table I have put 10,000 records, probably many times more than I will ever need, and updating the field to false in all 10,000 records occurred almost instantaneously. Thanks again!