[Access-VBA] How to create form which record source are FEW (not only one) tables?

[chrismaliszewski]

Hi.

I created code which makes dynamically form with bounded controls for all columns. I show it to you below.

My problem is, how I have to change this code to create form which record source are few (e.x. 2) tables?

Thank you for help

If you want to use this code, you only have to change value of:
- nazwaTabeli (table's name of which you want to create form)
- nazwaForlumarza (form's name that you want to create)

And code:

Option Compare Database

Sub Formularz()
    Dim Formularz As form
    
    Dim ctlLabel As Control, ctlText As Control
    
    Dim polTextX As Integer, polTextY As Integer
    Dim polLabelX As Integer, polLabelY As Integer
    Dim roznicaX As Integer, roznicaY As Integer, licznik As Integer
    Dim nazwaTabeli As String, nazwaFormularza As String

    'Ustawienia nazw tabeli i formularza
    nazwaTabeli = "Zamowienia_"
    nazwaFormularza = "Formularz"

    'Check if form of table you want to create is open
If CurrentProject.AllForms(nazwaFormularza).IsLoaded = False Then
    
    'Check if form you want to create exist
    Dim FormularzAktualny As AccessObject
    For Each FormularzAktualny In Application.CurrentProject.AllForms
        If FormularzAktualny.Name = nazwaFormularza Then DoCmd.DeleteObject acForm, nazwaFormularza
        Exit For
    Next FormularzAktualny
    
    ' Position of new formants
    polLabelX = 100
    polLabelY = 100
    polTextX = 1000
    polTextY = 100
    roznicaY = 300
    roznicaX = 2500
    'Numer pola-1
    licznik = 0
    
    'Creation of new form
    Set Formularz = CreateForm
    Formularz.RecordSource = nazwaTabeli
    
    ' Creation of controls for all columns in table
    Dim bazadanych As Database
    Dim pola As Field
    Dim tabela As TableDef
    
    Set bazadanych = CurrentDb
    Set tabela = bazadanych.TableDefs(nazwaTabeli)
    
    For Each pola In tabela.Fields
        'Textboxes
        Set ctlText = CreateControl(Formularz.Name, acTextBox, , "", pola.Name, _
            polTextX + roznicaX, polTextY + roznicaY * licznik)
        'Labels
        Set ctlLabel = CreateControl(Formularz.Name, acLabel, , _
            ctlText.Name, pola.Name, polLabelX, polLabelY + roznicaY * licznik)
        licznik = licznik + 1
    Next
    
    'Set ctlText = CreateControl(frm.Name, acTextBox, , "", nazwaPola, _
        'intDataX, intDataY)
    ' Create child label control for text box.
    'Set ctlLabel = CreateControl(frm.Name, acLabel, , _
         'ctlText.Name, nazwaLabela, intLabelX, intLabelY)
    ' Restore form. - niepotrzebne
    'DoCmd.Restore
    
    'Saving, closing, changing name and opening once more form that was created
    DoCmd.Save acForm, "Formularz1"
    DoCmd.Close acForm, "Formularz1", acSaveYes
    DoCmd.Rename nazwaFormularza, acForm, "Formularz1"
    DoCmd.OpenForm nazwaFormularza, , , , , acWindowNormal

'If form of a name you want to create is open how msg
Else: MsgBox "Formularz jest już uruchomiony", vbOKOnly, "Uwaga"

End If

End Sub

P.S. sory for my english

[NeoPa]

I'm not sure I understand you clearly, but forms have a single RecordSource only (at any one time). That can be a table or a query (queries can have more than one table as their source). It is also possible to change the RecordSource of a form using code. This can even be done while the form is already open.

Does that help at all?

[chrismaliszewski]

I tried to change RecordSource of a form while the form is already in creation but after that there were error in form (unrecognisable source of data).

I was thinking about query, but I dont know how to place query as a record source. I know how to do that in graphical interface but not in VBA :( Could you re-write part of a code below to show me how to do this?

'Creation of new form
    Set Formularz = CreateForm
    Formularz.RecordSource = nazwaTabeli
 
    ' Creation of controls for all columns in table
    Dim bazadanych As Database
    Dim pola As Field
    Dim tabela As TableDef
 
    Set bazadanych = CurrentDb
    Set tabela = bazadanych.TableDefs(nazwaTabeli)
 
    For Each pola In tabela.Fields
        'Textboxes
        Set ctlText = CreateControl(Formularz.Name, acTextBox, , "", pola.Name, _
            polTextX + roznicaX, polTextY + roznicaY * licznik)
        'Labels
        Set ctlLabel = CreateControl(Formularz.Name, acLabel, , _
            ctlText.Name, pola.Name, polLabelX, polLabelY + roznicaY * licznik)
        licznik = licznik + 1
    Next

I'd be grateful. Thank you for help.

[NeoPa]

I'll have a look to see what I can do. In the mean-time please note you should be using the [ CODE ] tags for code.

PS. Tags are done as matching pairs where the opening one is surrounded by [...] and the closing one by [/...]. A set of buttons is available for ease of use in the Standard Editor (Not the Basic Editor). The one for the [ CODE ] tags has a hash (#) on it. You can choose which editor to use in your Profile Options (Look near the bottom of the page).

[NeoPa]

I've looked at your code and it tells me nothing. There is nothing there which changes the RecordSource. There is no indication anywhere of what you want to use as a RecordSource even. Lastly, there is nothing to indicate where this code comes from. I can do nothing with this.

[chrismaliszewski]

You're wrong.
Below I give you me newest version of a code of this module, which work (create form) FOR 1 table. You can use it in every database and it'll create you form of name nazwaFormularza value.

But before it, im asking - how cant i change this code to create form of more tables?
I tried to create unbound form with bounded controls using code below:

 'Formularz.RecordSource = nazwaTabeli ' UNBOUND form
    For Each pola In tabela.Fields
        'Tworzenie textboxow
        Set ctlText = CreateControl(Formularz.Name, acTextBox, , "", pola.Name, _
            polTextX + roznicaX, polTextY + roznicaY * licznik)
        ctlText.Name = nazwaTabeli & " " & pola.Name
'Here i'm trying to create BOUNDED controls but it doesnt work even for 1 table, what is wrong here?
        Formularz.form.Controls(nazwaTabeli & " " & pola.Name).ControlSource = nazwaTabeli & " " & pola.Name
        'ctlText.Name = pola.Name
        'Formularz.form.Controls(pola.Name).ControlSource = pola.Name
        'Tworzenie labeli dla textboxow
        Set ctlLabel = CreateControl(Formularz.Name, acLabel, , _
            ctlText.Name, pola.Name, polLabelX, polLabelY + roznicaY * licznik)
        'ctlLabel.Name = "E" & pola.Name
        ctlLabel.Name = nazwaTabeli & "E " & pola.Name
        licznik = licznik + 1
    Next

but it doesnt work :(

And my working module, which creates form:

Option Compare Database

Sub Formularz()
    Dim Formularz As form
    
    Dim ctlLabel As Control, ctlText As Control
    
    Dim polTextX As Integer, polTextY As Integer
    Dim polLabelX As Integer, polLabelY As Integer
    Dim roznicaX As Integer, roznicaY As Integer, licznik As Integer
    Dim nazwaTabeli As String, nazwaFormularza As String

    'Ustawienia nazw tabeli i formularza
    nazwaTabeli = "ZAMOWIENIA"
    nazwaFormularza = "Formularz"

    'Sprawdzenie czy formularz jest juz uruchomiony
If IsOpen(nazwaFormularza) = False Then
    
    'Jezeli istnieje formularz o nazwie nazwaFormularza jest on usuwany
    If DCount("*", "[MSysObjects]", "Type = -32768 AND Name='" & nazwaFormularza & "'") > 0 Then
        DoCmd.DeleteObject acForm, nazwaFormularza
    End If
    
    ' Ustawienia wartosci pozycji nowych formantow
    polLabelX = 100
    polLabelY = 100
    polTextX = 1000
    polTextY = 100
    roznicaY = 300
    roznicaX = 2500
    'Numer pola-1
    licznik = 0
    
    ' Tworzenie nowego formularza o zrodle danych nazwaTabeli
    Set Formularz = CreateForm
    Formularz.RecordSource = nazwaTabeli
    
    ' Tworzenie zwiazanych formantow dla wszystkich pol tabeli nazwaTabeli
    Dim bazadanych As Database
    Dim pola As Field
    Dim tabela As TableDef
    
    Set bazadanych = CurrentDb
    Set tabela = bazadanych.TableDefs(nazwaTabeli)
    
    For Each pola In tabela.Fields
        'Tworzenie textboxow
        Set ctlText = CreateControl(Formularz.Name, acTextBox, , "", pola.Name, _
            polTextX + roznicaX, polTextY + roznicaY * licznik)
        ctlText.Name = nazwaTabeli & " " & pola.Name
        'Tworzenie labeli dla textboxow
        Set ctlLabel = CreateControl(Formularz.Name, acLabel, , _
            ctlText.Name, pola.Name, polLabelX, polLabelY + roznicaY * licznik)
        ctlLabel.Name = nazwaTabeli & "E " & pola.Name
        licznik = licznik + 1
    Next
    
    'Listing formantow
    'Dim kontr As Control
    'For Each kontr In Formularz.Controls
    '        MsgBox kontr.Name
    '    Next
    
    'Zapisywanie utworzonego formularza, zamykanie go, zmiana jego nazwy i ponowne otwarcie go
    DoCmd.Save acForm, "Formularz1"
    DoCmd.Close acForm, "Formularz1", acSaveYes
    DoCmd.Rename nazwaFormularza, acForm, "Formularz1"
    DoCmd.OpenForm nazwaFormularza, , , , , acWindowNormal

'W przypadku jesli formularz jest wlaczony:
Else: MsgBox "Formularz jest już uruchomiony", vbOKOnly, "Uwaga"

End If

End Sub

Function IsOpen(strName As String, Optional objtype As Integer = acForm)
    IsOpen = (SysCmd(acSysCmdGetObjectState, objtype, strName) <> 0)
End Function

Please, help me if u can.

[NeoPa]

You're wrong.

That may be true. We all make mistakes. You don't say why you think I may be though, so it's hard to be convinced.

Please, help me if u can.

I wish I could understand you well enough to.

Until you decide to respond to my comments though, I see no way forward.

I appreciate there is a language barrier, but I don't see how I can help you if we cannot understand each other clearly.

[chrismaliszewski]

Sorry that I told 'you're wrong'. I wrongly read 'Lastly, there is nothing to indicate where this code comes from.' (i read comes to). I apologies you.

You said:

I've looked at your code and it tells me nothing. There is nothing there which changes the RecordSource.

It is because I have two ideas to solve my problem:
1) Create unbounded form and bound ONLY controls on it (form)
2) Create bounded with first table form and next change record source table/query to another.
Code I showed you above is working one. I thought that u will see my problem. Sorry that I didn't showed it clearly.
If u want to try to solve problem in 1st way I show you below code with this type of problem:

Option Compare Database

Sub Formularz()
    Dim Formularz As form
    
    Dim ctlLabel As Control, ctlText As Control
    
    Dim polTextX As Integer, polTextY As Integer
    Dim polLabelX As Integer, polLabelY As Integer
    Dim roznicaX As Integer, roznicaY As Integer, licznik As Integer
    Dim nazwaTabeli As String, nazwaFormularza As String

    'Ustawienia nazw tabeli i formularza
    nazwaTabeli = "ZAMOWIENIA"
    nazwaFormularza = "Formularz"

    'Sprawdzenie czy formularz jest juz uruchomiony
If IsOpen(nazwaFormularza) = False Then
    
    'Jezeli istnieje formularz o nazwie nazwaFormularza jest on usuwany
    If DCount("*", "[MSysObjects]", "Type = -32768 AND Name='" & nazwaFormularza & "'") > 0 Then
        DoCmd.DeleteObject acForm, nazwaFormularza
    End If
    
    ' Ustawienia wartosci pozycji nowych formantow
    polLabelX = 100
    polLabelY = 100
    polTextX = 1000
    polTextY = 100
    roznicaY = 300
    roznicaX = 2500
    'Numer pola-1
    licznik = 0
    
    ' Tworzenie nowego formularza o zrodle danych nazwaTabeli
    Set Formularz = CreateForm
    Formularz.Caption = nazwaFormularza
    Formularz.AutoCenter = True
    
    ' Tworzenie zwiazanych formantow dla wszystkich pol tabeli nazwaTabeli
    Dim bazadanych As Database
    Dim pola As Field
    Dim tabela As TableDef
    
    Set bazadanych = CurrentDb
    Set tabela = bazadanych.TableDefs(nazwaTabeli)
    
    'Formularz.RecordSource = nazwaTabeli ' WITHOUT BOUNDING FORM
    For Each pola In tabela.Fields
        'Tworzenie textboxow
        Set ctlText = CreateControl(Formularz.Name, acTextBox, , "", pola.Name, _
            polTextX + roznicaX, polTextY + roznicaY * licznik)
        ctlText.Name = nazwaTabeli & " " & pola.Name
'WITH BOUNDING CONTROLS (IT DOESNT WORK)
        Formularz.form.Controls(nazwaTabeli & " " & pola.Name).ControlSource = nazwaTabeli & " " & pola.Name
        'ctlText.Name = pola.Name
        'Formularz.form.Controls(pola.Name).ControlSource = pola.Name
        'Tworzenie labeli dla textboxow
        Set ctlLabel = CreateControl(Formularz.Name, acLabel, , _
            ctlText.Name, pola.Name, polLabelX, polLabelY + roznicaY * licznik)
        'ctlLabel.Name = "E" & pola.Name
        ctlLabel.Name = nazwaTabeli & "E " & pola.Name
        licznik = licznik + 1
    Next
    
    'Zapisywanie utworzonego formularza, zamykanie go, zmiana jego nazwy i ponowne otwarcie go
    DoCmd.Save acForm, "Formularz1"
    DoCmd.Close acForm, "Formularz1", acSaveYes
    DoCmd.Rename nazwaFormularza, acForm, "Formularz1"
    DoCmd.OpenForm nazwaFormularza, , , , , acWindowNormal

'W przypadku jesli formularz jest wlaczony:
Else: MsgBox "Formularz jest już uruchomiony", vbOKOnly, "Uwaga"

End If

End Sub

Function IsOpen(strName As String, Optional objtype As Integer = acForm)
    IsOpen = (SysCmd(acSysCmdGetObjectState, objtype, strName) <> 0)
End Function

If u want to see and solve 2nd problem, code below:

Option Compare Database

Sub Formularz()
    Dim Formularz As form
    
    Dim ctlLabel As Control, ctlText As Control
    
    Dim polTextX As Integer, polTextY As Integer
    Dim polLabelX As Integer, polLabelY As Integer
    Dim roznicaX As Integer, roznicaY As Integer, licznik As Integer
    Dim nazwaTabeli As String, nazwaFormularza As String

    'Ustawienia nazw tabeli i formularza
    nazwaTabeli = "ZAMOWIENIA"
    nazwaFormularza = "Formularz"

    'Sprawdzenie czy formularz jest juz uruchomiony
If IsOpen(nazwaFormularza) = False Then
    
    'Jezeli istnieje formularz o nazwie nazwaFormularza jest on usuwany
    If DCount("*", "[MSysObjects]", "Type = -32768 AND Name='" & nazwaFormularza & "'") > 0 Then
        DoCmd.DeleteObject acForm, nazwaFormularza
    End If
    
    ' Ustawienia wartosci pozycji nowych formantow
    polLabelX = 100
    polLabelY = 100
    polTextX = 1000
    polTextY = 100
    roznicaY = 300
    roznicaX = 2500
    'Numer pola-1
    licznik = 0
    
    ' Tworzenie nowego formularza o zrodle danych nazwaTabeli
    Set Formularz = CreateForm
    Formularz.Caption = nazwaFormularza
    Formularz.AutoCenter = True
    
    ' Tworzenie zwiazanych formantow dla wszystkich pol tabeli nazwaTabeli
    Dim bazadanych As Database
    Dim pola As Field
    Dim tabela As TableDef
    
    Set bazadanych = CurrentDb
    Set tabela = bazadanych.TableDefs(nazwaTabeli)
    
    Formularz.RecordSource = nazwaTabeli ' BOUND HERE
    For Each pola In tabela.Fields
        'Tworzenie textboxow
        Set ctlText = CreateControl(Formularz.Name, acTextBox, , "", pola.Name, _
            polTextX + roznicaX, polTextY + roznicaY * licznik)
        ctlText.Name = nazwaTabeli & " " & pola.Name
        Formularz.form.Controls(nazwaTabeli & " " & pola.Name).ControlSource = nazwaTabeli & " " & pola.Name
        'ctlText.Name = pola.Name
        'Formularz.form.Controls(pola.Name).ControlSource = pola.Name
        'Tworzenie labeli dla textboxow
        Set ctlLabel = CreateControl(Formularz.Name, acLabel, , _
            ctlText.Name, pola.Name, polLabelX, polLabelY + roznicaY * licznik)
        'ctlLabel.Name = "E" & pola.Name
        ctlLabel.Name = nazwaTabeli & "E " & pola.Name
        licznik = licznik + 1
    Next
    
    'AND BOUND HERE
    nazwaTabeli = "DP_AMZPOTSP"
    Set tabela = bazadanych.TableDefs(nazwaTabeli)
    Formularz.RecordSource = nazwaTabeli
    'For Each pola In tabela.Fields
    '    'Tworzenie textboxow
        Set ctlText = CreateControl(Formularz.Name, acTextBox, , "", pola.Name, _
            polTextX + roznicaX, polTextY + roznicaY * licznik)
        ctlText.Name = nazwaTabeli & " " & pola.Name
    '    'Tworzenie labeli dla textboxow
        Set ctlLabel = CreateControl(Formularz.Name, acLabel, , _
            ctlText.Name, pola.Name, polLabelX, polLabelY + roznicaY * licznik)
        ctlLabel.Name = nazwaTabeli & "E " & pola.Name
        licznik = licznik + 1
    Next
    
    'Zapisywanie utworzonego formularza, zamykanie go, zmiana jego nazwy i ponowne otwarcie go
    DoCmd.Save acForm, "Formularz1"
    DoCmd.Close acForm, "Formularz1", acSaveYes
    DoCmd.Rename nazwaFormularza, acForm, "Formularz1"
    DoCmd.OpenForm nazwaFormularza, , , , , acWindowNormal

'W przypadku jesli formularz jest wlaczony:
Else: MsgBox "Formularz jest już uruchomiony", vbOKOnly, "Uwaga"

End If

End Sub

Function IsOpen(strName As String, Optional objtype As Integer = acForm)
    IsOpen = (SysCmd(acSysCmdGetObjectState, objtype, strName) <> 0)
End Function

And there is 3rd way to solve my problem: use query, as I and u said before. But I don't know how to 'bound'(?) query to form in VBA, OR how to use SQL code to bound it to form.

Once more I tell u (and to other people) my problem: how to create bound form which record source are few (not only one) tables/queries OR how to create unbound form with bounded controls?

I hope it'll help you to help me :)