Is there an easier way to access an unbound field on a form?

[theDBguy]

@theDBguy and @hkc94501
NO you can not do that. Please see thread 15.

One more time
Fields are added as PROPERTIES to the Form Class ONLY when you add a recordsource at DESIGN time.
I show that if you reassign the recordsource at RUN TIME the FIELDS are not added as PROPERTIES to the Forms Class. (this behavior should be expected) You must use late binding with the ! notation.

In DESIGN TIME if you add a field to a table since you last assigned the recordsource, you must reselect the recordsource. It is that action that creates the properties.

See thread 13 for the long explanation of this explained by SONIC8

Sorry, I missed the "design" part. Would opening the form in design view in code and reassigning the record source have the same effect as when doing it manually? Just curious...

 

[MajP]

Would opening the form in design view in code and reassigning the record source have the same effect as when doing it manually? Just curious...

I would have to try, but the fact that this happens at all has is happening behind the scenes. I am thinking maybe not because something has to trigger this, almost like a wizard.

Imagine a completely blank form (no controls) with no recordsource. Then you add a recordsource and now all the fields are properties of the form. All you did was put a string in the recordsource property. Something then had to key access to then read all the fields and build these as properties. With a control that is created when you drop the control on a form.

If you ever worked in vb.net you can see how this is done behind the scenes. Because you can actually see the code that builds the form in the form designer. This is not what we call the form module, but the thing that builds the form. In Access this is not visible, but probably exists in some hidden format. Normally you never touch this
So if I create a cmbobox on the form this code is created behind the scenes in the form designer

   'cmboN
        '
        Me.cmboN.DisplayMember = "2"
        Me.cmboN.DropDownStyle = System.Windows.Forms.ComboBoxStyle.DropDownList
        Me.cmboN.FormattingEnabled = True
        Me.cmboN.Items.AddRange(New Object() {"2", "3", "4", "5", "6", "7", "8", "9", "10"})
        Me.cmboN.Location = New System.Drawing.Point(171, 614)
        Me.cmboN.Name = "cmboN"
        Me.cmboN.Size = New System.Drawing.Size(40, 21)
        Me.cmboN.TabIndex = 11

It is telling it to build a combobox in that loaction, etc.

Something is similarly happening in Acces. I am guessing some code is being written in a hidden designer. Not sure if setting this in code will do it, but maybe.

 

[Pat Hartman]

When you export forms and reports as text, you can see something similar that defines all the properties.

Public Sub ExportDatabaseObjects(ExportType As String, sExportLocation As Variant, strDatabase As Variant)
On Error GoTo Err_ExportDatabaseObjects
    
    Dim db As DAO.Database
    Dim td As DAO.TableDef
    Dim d As Document
    Dim c As Container
    Dim i As Integer
    
     If strDatabase & "" = "" Then
        Set db = CurrentDb()
    Else
        Set db = DBEngine.Workspaces(0).OpenDatabase(strDatabase)
    End If

    Select Case ExportType
        Case "Tables"
            For Each td In db.TableDefs 'Tables
                If Left(td.Name, 4) <> "MSys" Then
                    DoCmd.TransferText acExportDelim, , td.Name, sExportLocation & "Table_" & td.Name & ".txt", True
                End If
            Next td
        Case "Forms"
            Set c = db.Containers("Forms")
            For Each d In c.Documents
                Application.SaveAsText acForm, d.Name, sExportLocation & "Form_" & d.Name & ".txt"
            Next d
        Case "Reports"
            Set c = db.Containers("Reports")
            For Each d In c.Documents
                Application.SaveAsText acReport, d.Name, sExportLocation & "Report_" & d.Name & ".txt"
            Next d
        Case "Macros"
            Set c = db.Containers("Scripts")
            For Each d In c.Documents
                Application.SaveAsText acMacro, d.Name, sExportLocation & "Macro_" & d.Name & ".txt"
            Next d
        Case "Modules"
            Set c = db.Containers("Modules")
            For Each d In c.Documents
                Application.SaveAsText acModule, d.Name, sExportLocation & "Module_" & d.Name & ".txt"
            Next d
        Case "Queries"
            For i = 0 To db.QueryDefs.Count - 1
                Application.SaveAsText acQuery, db.QueryDefs(i).Name, sExportLocation & "Query_" & db.QueryDefs(i).Name & ".txt"
            Next i
        Case Else
    End Select

    Set db = Nothing
    Set c = Nothing
    
    MsgBox "Selected objects have been exported as a text file to " & sExportLocation, vbInformation
    
Exit_ExportDatabaseObjects:
    Exit Sub
    
Err_ExportDatabaseObjects:
    MsgBox Err.Number & " - " & Err.Description
    Resume Exit_ExportDatabaseObjects
    
End Sub

 

[hkc94501]

@theDBguy and @hkc94501
NO you can not do that. Please see thread 15.

One more time
Fields are added as PROPERTIES to the Form Class ONLY when you add a recordsource at DESIGN time.
I show that if you reassign the recordsource at RUN TIME the FIELDS are not added as PROPERTIES to the Forms Class. (this behavior should be expected) You must use late binding with the ! notation.

In DESIGN TIME if you add a field to a table since you last assigned the recordsource, you must reselect the recordsource. It is that action that creates the properties.

See thread 13 for the long explanation of this explained by SONIC8

Thank you. That explains it.
Sorry, not yet washed in the blood of Access VBA but I'm doing my penance.

 

[The_Doc_Man]

Then you add a recordsource and now all the fields are properties of the form. All you did was put a string in the recordsource property.

If I may, it is because if you change the contents of the .RecordSource, you also automatically change the contents of the .RecordSet (and its clone). Though I don't remember how long ago it was, we had a thread on the subject of what ELSE changes when you change the .RecordSource on a form or report.

 

[hkc94501]

If I may, it is because if you change the contents of the .RecordSource, you also automatically change the contents of the .RecordSet (and its clone). Though I don't remember how long ago it was, we had a thread on the subject of what ELSE changes when you change the .RecordSource on a form or report.

Sacrifice one goat.
Seriously this stuff should be documented. Maybe it is but it is not in the VBA language reference. Nor in the Access object documentation. I looked all over for some explanation of me. without success.

 

[The_Doc_Man]

Form.RecordSource property (Access)

Office VBA reference topic

docs.microsoft.com

Look at the purple-highlighted paragraph a couple of paragraphs from the top.

 

[Uncle Gizmo]

Form.RecordSource property (Access)
Look at the purple-highlighted paragraph a couple of paragraphs from the top

The article says:-
The next example changes a form's record source to a single record in the Customers table

An ID field normally does not have a "0" (zero) record. I use an SQL Statement that selects this nonexistent record, so no records are shown, just an empty form.

 

[The_Doc_Man]

UG, no doubt that works for you. It is a good way to speed up form loading for complex forms.

However, the question I was answering was the implied "Where is the documentation regarding changes of the .RecordSource and side effects thereof?" as implicitly posed in OP's #26 post.

 

[MajP]

If I may, it is because if you change the contents of the .RecordSource, you also automatically change the contents of the .RecordSet (and its clone).

Although the above might be true statement, it has absolutely nothing to do with this conversation and is irrelevant. And it is defintely not the reason for the issue with a field being a accessible property or not. Sorry, but you are just adding confusion and sometimes you got to know when you do not have to feel compelled to chime in. If you can find a better explanation anywhere on the internet for this specific case please show me, I would be glad to see it. If not, I think I got it and have explained this nuance fact pretty well. In fact I have provided a demo showing this.

Fields are made properties of the form upon assignment of the recordsource in the designer at design time. End of story. It has nothing to do with the recordset. In fact I do not even think you read it your own post correctly. That post points out the opposite. Which is very interesting IMO. If you have a form and at runtime assign a RECORDSET it will assign the RECORDSOURCE property. This is not very common to build a recordset (unless doing an external db using ADODB) and then assign it instead of using a recordsource. If interested see Demo 2. In this case I show that assigning a recordset to the form at runtime assigns the recordsource property equal to the NAME of the recordset.

Now the second thing that Demo shows it that your statement is wrong. According to you changing the recordset would make the property available. But as I have said the fields are added as properties at design time. I have changed the recordset at runtime to include the fields. Hit the button to assign the Mask and it will error with the error no such property or method.

 

[The_Doc_Man]

MajP, what I said made sense to me at the time. If it is wrong, so be it. But I was thinking in context of the prior discussion from some months ago which at least SEEMED relevant. How often have YOU made a post and then stepped back later and said, "Oh, yeah, maybe not..."? Give me credit for being human and therefore fallible sometimes. Cut back a little on the snark factor.

 

[hkc94501]

I found a similar instance. The same sort of thing seems to happen when the rowsource is a query. I changed the name of a query used as a rowsource. When I looked at the control that was using that query I found that it continued to work just fine even though there was no longer a query of that name in the database. So in this case too once the rowsource information was added to the control it did not need to go back to the source "file" (I know its not a file but I don't have the right word). Presumably, changing the query would also not have made any difference until it was re-assigned to the control after changes were made.

 

[MajP]

When I looked at the control that was using that query I found that it continued to work just fine even though there was no longer a query of that name in the database.

No that is something very different. That is a nice feature of Access (but sometimes unwanted and problematic). This is the Name Autocorrect feature. When you change an object name it changes that name in other places in the DB where it is used. So when you change the query name the name used in the rowsource automatically changes.
Set name AutoCorrect options - Access (microsoft.com)
Microsoft Access Flaws - Failures caused by Name Auto-Correct (allenbrowne.com)

 

[gemma-the-husky]

I am trying to use a group of optionbuttons to set individual bits of a mask. The buttons are not grouped in an optiongroup because I want them to operate independently rather than as radio buttons. The buttons are unbound because I want to control their appearance based on a single bit rather than the whole word. If they are all bound to the same field then setting any bit will cause them all to indicate False. Each button has an after_update event procedure that sets its bit based on the value of the button after update. What I was asking for was a simple way to refer to the unbound field of the current record of the form.

but you should have the mask bound to a say byte value which gives you 8 bits or flags, represented in a value from 0 to 255.
The individual bits should be read from the flag using the boolean and operator. (mask and 2^requiredbit)>0 indicates the bit is set.
Boolean operators are fiddly until you get used to them.

You can toggle a bit in the byte with the xor operator
If you click option button n to set bit n the value of the mask then becomes a boolean byte value of mask xor 2^(bitnumber-1)
So you need code in the after update event for the button click to re-evaluate the mask value.

so if your mask is currently 01001001 (decimal 73) for bits 8 down to 1 then
mask xor 2^0 will set or clear the 1st bit (value 1) and mask xor 2^3 will set or clear the 4th bit (value 8)

so 01001001 boolean xor'ed with 00001000 (8) will become 01000001 (65)
do it again, and it returns to the original value of 01001001 (73)

see this for various operations to set or check bits (although this is c not vba)
c++ - How do you set, clear, and toggle a single bit? - Stack Overflow

 

[Pat Hartman]

It really doesn't make sense to change the actual select clause of the RecordSource for a form or report at runtime unless you are using a crosstab that will produce different columns. (and I have a method to handle that). If what you are doing is trying to change the selection, use a query with arguments that take values. If your criteria is variable, I would use an intermediate form that produces a not updateable DS view form that lists the selected records and then use the doubleclick event of one of the columns to open the edit form to a specific record.

In an update form, you want your code to be able to use the Me. syntax so I don't recommend dynamic RecordSource changes. Use one of the methods above. By extension, if during development, you need to change the Select clause, delete the table/query and reselect it again to force Access up update its field list.