Solved Set textbox controlsource via VBA

[LjushaMisha]

Hi.
I have an unboud text box Text0 on a continuous form.
I would like to "define" control source for this control after the form is opened - - - >
DoCmd.OpenForm("formName", acNormal) like
Me.Text0.controlsource = rs(0)
where rs.recordcount = 1

Is this anyway possible?
Thx in advance

 

[Jason Lee Hayes]

Sub OpenAndSetControlSource()
' Declare variables
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim formName As String

' Set the form name
formName = "YourFormName" ' Replace with your actual form name

' Open the form
DoCmd.OpenForm formName, acNormal

' Set references to the database and recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT YourField FROM YourTable WHERE [Condition]")

' Check if the recordset has exactly one record
If rs.RecordCount = 1 Then
rs.MoveFirst ' Ensure we are at the first record
' Set the ControlSource of the text box on the opened form
Forms(formName).Controls("Text0").ControlSource = "=" & rs.Fields(0).Value
Else
MsgBox "The recordset does not contain exactly one record.", vbExclamation
End If

' Close the recordset
rs.Close
Set rs = Nothing
Set db = Nothing
End Sub

 

[The_Doc_Man]

Possible? Yes. See this article on the subject.

ControlSource Property - Microsoft Support

support.microsoft.com

Note, however, that it is possible that if you change a .ControlSource, you would need to issue either a .Repaint or at worst a .Refresh after you change something structural like that. Also, there is the issue that you mentioned a "continuous form." Be aware that there is only one control in the detail area of a continuous form and it just gets re-used for each navigation to a new record. So if you changed the cursor to the next record, that new .ControlSource is going to remain in play.

 

[Gasman]

Plus an unbound control is going to show the same value for ever record in a CF form, is it not?

 

[Gasman]

If rs.RecordCount = 1 Then
rs.MoveFirst ' Ensure we are at the first record

You have just checked that there is only one record in the recordset?
Where else are you going to be?, other than on the first record?

I have yet to have anyone ever prove to me, that after immediately opening a recordset, where at least one record exist, that one is not on the first record?

 

[LjushaMisha]

Sub OpenAndSetControlSource()
' Declare variables
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim formName As String

' Set the form name
formName = "YourFormName" ' Replace with your actual form name

' Open the form
DoCmd.OpenForm formName, acNormal

' Set references to the database and recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT YourField FROM YourTable WHERE [Condition]")

' Check if the recordset has exactly one record
If rs.RecordCount = 1 Then
rs.MoveFirst ' Ensure we are at the first record
' Set the ControlSource of the text box on the opened form
Forms(formName).Controls("Text0").ControlSource = "=" & rs.Fields(0).Value
Else
MsgBox "The recordset does not contain exactly one record.", vbExclamation
End If

' Close the recordset
rs.Close
Set rs = Nothing
Set db = Nothing
End Sub

Worked as nothing. Great help. I was really only missing the correct syntax for Forms(formName).Controls("Text0").ControlSource = "=" & rs.Fields(0).Value. All the rest I already did. I wrote only shorter version of the thread. Sorry not mentioned. But you immeddiatelly knew what I need.

 

[MajP]

I have yet to have anyone ever prove to me, that after immediately opening a recordset, where at least one record exist, that one is not on the first record?

Concur, I never understood that either
But this should not be confused referencing an existing RS

dim rs as dao.recordset
set RS = me.recordset

That will open to the current record which may not be

More importantly you never want to move in a recordset or take the count of a recordset without checking that a record exists first

If not RS.EOF then ' check there are records if not will get an error
  rs.moveLast  'depending on type of recordset may have to first move last to get an accurate count
  rs.moveFirst
  count = rs.recordcount
else
   count = 0
end if

 

[cheekybuddha]

set RS = me.forms

That will open to the current record which may not be

Did these lines get truncated?

 

[MajP]

Did these lines get truncated?

Oops. No, just typing and thinking at same time.

 

[Gasman]

Concur, I never understood that either
But this should not be confused referencing an existing RS

dim rs as dao.recordset
set RS = me.recordset

That will open to the current record which may not be

More importantly you never want to move in a recordset or take the count of a recordset without checking that a record exists first

If not RS.EOF then ' check there are records if not will get an error
  rs.moveLast  'depending on type of recordset may have to first move last to get an accurate count
  rs.moveFirst
  count = rs.recordcount
else
   count = 0
end if

MoveLast and MoveFirst. I see all the time after opening a recordset, and the recordcount is never looked at.
I believe you and I have had this discussion before, byt my comment is only for immediately opening a new recordset.

The same is my belief that if you open a empty recordset, then both .BOF and .EOF are true, but I only ever test for .EOF,

 

[LjushaMisha]

Plus an unbound control is going to show the same value for ever record in a CF form, is it not?

That's exactly what I need (among other controls for which recordsource established with another RS. Anyway, thanks for your reminder.

 

[LjushaMisha]

Oops. No, just typing and thinking at same time.

set RS = me.forms

Don't understand this. What you had in mind. I can understand it as "recordset is defined with entries in the me.forms".

 

[The_Doc_Man]

set RS = me.forms

Don't understand this. What you had in mind. I can understand it as "recordset is defined with entries in the me.forms".

This is one way of copying a recordset - the one that is being used by the form hosting the VBA code that does this. And as a side-effect, it also copies the data of the recordset including its bookmark. Thus, RS becomes a clone of the bound form's recordset and copies any attributes as well. After you do this, you have TWO paths to the same data... me.Recordset and RS - both of which point to the same thing. However, they can be moved independently after the SET command finishes.

 

[Mark_]

That's exactly what I need (among other controls for which recordsource established with another RS. Anyway, thanks for your reminder.

I've a form that exploits this also. I use a button to manually add to a second table and I use an unbound field to hold the semester the students are going in to. I also update the existing table so I can refresh and have the record I've just moved disappear from the list the user (me) sees! Helps IMMENSELY in adding the new semester records!