VBA Show query in a form/subform

Gregorys05 · Mar 5, 2012

HI Guy's
I am have great difficulty with getting a subform to show data.

How I Ideally want this to work Is that I have one form/subform that I can then pass the results for query’s to via VBA every time buttons on some of my other forms are pressed.

E.g

Form 1 button click
opens the form/subform wIth the query
Select * from tbltest

Form 2 button click
opens the same form/subform but has the results from
Select * from tblretest

Is there a way to do thIs so that I can have one form/subform and just pass query results for It to display.

I really don’t want to have to make a form for each Individual query

Thanks In advance

Kreszch68 · Mar 5, 2012

Do the queries have the same fields? Or are the fields also different?

Gregorys05 · Mar 5, 2012

The fields are different, every time.

Thanks

Kreszch68 · Mar 5, 2012

ok, and you want to show all fields from your query on the subform?
Would it be ok that the subform is displayed as a datasheet?

Gregorys05 · Mar 5, 2012

Yes that would be perfect

Kreszch68 · Mar 5, 2012

Ok, well here is a solution for you.
What you need to do is, create a form and set the display option to DataSheet view. It's up to you to set other options, like display navigation or not. You don't have to set a recordsource or add any controls, this will be done by code later on.

Then save the form and add it as a subform to another form. (the form where you have the buttons to set the queries).

In a module, add this code

Code:

Option Compare Database
Public sFrmNameParent As String
Const sFrmName As String = "[B][COLOR=red]frmTheSubForm[/COLOR][/B]" 'replace with name of your subform
 
Public Sub SetNewSubForm(ByVal sSQL As String)
 
Dim oFrm As Form
Dim fld As Field
Dim c As Control
Dim vControls() As Variant
Dim iCnt As Integer
Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Set dbs = CurrentDb
Set rs = dbs.OpenRecordset(sSQL)
DoCmd.OpenForm (sFrmName), acDesign
Set oFrm = Forms(sFrmName)
With oFrm
    .Caption = "DisplayResults"
    'Create array for all current controls on the subform
    ReDim vControls(.Controls.Count - 1)
    iCnt = 0
    For Each c In .Controls
        vControls(iCnt) = c.Name
        iCnt = iCnt + 1
    Next c
    'delete all existing controls
    For iCnt = 0 To UBound(vControls)
            DeleteControl .Name, vControls(iCnt)
     Next iCnt
    'set the recordsource
    .RecordSource = sSQL
    'Create the new controls
    For Each fld In rs.Fields
        Set c = CreateControl(.Name, acTextBox, acDetail, , fld.Name)
        c.Name = fld.Name
    Next fld
    'close recordset
    rs.Close
    'Save the form to avoid dialog to save form
    DoCmd.Save acForm, sFrmName
    DoCmd.Close
End With
DoCmd.OpenForm sFrmNameParent, acNormal
Set dbs = Nothing
Set rs = Nothing
End Sub

And in the click event of you buttons call the routine like this

Code:

Private Sub btnNewSub_Click()
Dim sSQL As String
sSQL = "Select * from tEmployees"
sFrmNameParent = Me.Name
SetNewSubForm sSQL
End Sub

What happens is that the routine opens the subform in designmode, deletes all existing controls, adds the new controls from the recordsource and the reopens the parent form to display the subform.
This will not work correctly if you have multiple users on the same form for obvious reasons.

Gregorys05 · Mar 5, 2012

i get a subscript out of range on the line

ReDim vControls(.Controls.count - 1)

within the function

Kreszch68 · Mar 5, 2012

Hi Greg, my bad, I told you not to place any controls on the subform, but you should place just one control. Simply add an unbound textbox and save the form.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com

ffice

ffice" /><o

></o

>

Gregorys05 · Mar 5, 2012

ray: That is ******* amazing

Thank you so much

you dont understand how many hours i have been trying to get this to work.

Thank you

VBA Show query in a form/subform

Gregorys05

Board Regular

Excel Facts

Kreszch68

Active Member

Gregorys05

Board Regular

Kreszch68

Active Member

Gregorys05

Board Regular

Kreszch68

Active Member

Gregorys05

Board Regular

Kreszch68

Active Member

Gregorys05

Board Regular

Similar threads

Forum statistics

Share this page

VBA Show query in a form/subform

Board Regular

Excel Facts

Active Member

Board Regular

Active Member

Board Regular

Active Member

Board Regular

Active Member

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock