Calling sub or module to bind txt boxes on frmMain to ADO Recordset

[evoluder]

Hello, let me explain my problem.

I have two forms. The first form contains text boxes and a datagrid that are bound controls to an ADO recordset. I want users to be able to pull up a second form that allows them to select an integer, hit "OK" and have all of the bound controls be initialized with a recordset that is built off of this integer passed as an argument to the sub: BuildRS.

I can get everything to work except for the binding of the fields. If I ask for a field of the rs recordset, it returns the correct value, but I cannot get it to bind to the text boxes on frmMain. I've tried all of the subs in modules, I can connect to the database, build the recordset, but I can't bind the fields. If I call the BindFields() sub from another form it doesn't work, if I call it from frmMain it works fine.

Consider the following:

--------------------------------
frmMain
Public WithEvents rs As ADODB.Recordset
'I've also tried declaring this public in a .bas module
'(without WithEvents).

Public Sub ConnectDB(fileName As String)
'Code the sets up connection to database
'This works fine in a .bas module or in frmMain.
End Sub

Public Sub BuildRS(Data As Integer)
'Code that executes SQL select to build RecordSet.
'This works fine in a .bas module or in frmMain.
End Sub

Public Sub BindFields()
'Code that binds textbox and datagrid fields on frmMain to
'rs. This works if placed in frmMain, but it must be
'called from frmMain, it doesn't work if called externally,
'and it doesn't work in .bas module even if the text
'controls are called with the frmMain.etc.

Set datagrid.datasource = rs
End Sub
---------------------------
frmSecond
'Another form that is executed by a menu command. User
'selects a number which corresponds to data they wish to
'retrieve. This number is passed to BuildRS to build the
'recordset.

Sub cmdOK_Click()
Dim Selection As Integer
Selection = "1"
Call BuildRS(Selection) 'Works fine
Call BindFields() 'Doesn't work
Unload Me
End Sub
---------------------------

So the crux of the problem is I need a second form to be able to call the subs that build the recordset and bind the fields.

Thank you for any assistance.

Regards.

 

[StewartGW]

Currently as written, the procedures called by secondary form, can only be seen by the frmMain. To call these functions, you must first identify the form object and the VB Intellisense will show you these methods. The code should for the second form should like the following:

Sub cmdOK_Click()
Dim Selection As Integer
Selection = "1"
Call frmMain.BuildRS(Selection) 'Works fine
Call frmMain.BindFields() 'Doesn't work
Unload Me
End Sub

 

[evoluder]

You are correct, I actually have it as you describe, I just failed to include it correctly in my post.

The issue appears to be setting the properties of the controls on frmMain. If on form2 I put the following in the cmdOK_Click procedure: frmMain.txtBox.Text = "Test" I get no change to frmMain.txtBox.

Is there some reason I can't modify controls on frmMain from outside procedures? Am I missing something fundamental?

Thanks again for your help.

 

[MrEGuest]

Just a stab in the dark-
Are using Option Explicit? If you're not you should go to Tools -> Options and check the Require Variable declaration option on the Editor tab. This will automatically insert the Option Explicit statement in your project files

If you're not using Option explicit it is possible that your second form may be creating the variable "rs" on the fly and not referencing the ADO recordset from form one.

Also, unless you are actually going to be programming the recordsets events it's not necessary to declare it withevents

Good luck with your issue
Josh

 

[Tspoon]

Personally, in order to make your code more reusable, I would open the connection in your code module (.bas) and create a class to open the recordset. Eliminate the bound ADODB control and populate your textboxes programmatically using paired property procedures (also will be located in your class). Your calls end up looking something like this... frmMain.txtBox = classObject.yourGetProcedureOfField

An additional benefit of this is that users have access to the 'values' in the database, but not the database directly.

Hope this helps.

Troy

 

[evoluder]

Thank you everyone. I figured out what the issue was. I had used the VB application wizard when I started this project to see what it could do. I've removed much of the code it inserted but I missed one piece, the formload module that did this:

Public fMainForm As frmMain

Sub Main()
Set fMainForm = New frmMain
Load fMainForm
fMainForm.Show
End Sub

So referencing frmMain wasn't doing what I thought it was.

Tspoon, I agree with your comment about building the class, I am still learning this stuff so that would be a great next step for me.

Again, thanks for your comments, this was a silly problem that I should have figured out sooner.

Regards.