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
7 4916 NeoPa 32,556
Expert Mod 16PB
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?
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 32,556
Expert Mod 16PB
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 32,556
Expert Mod 16PB
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.
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 32,556
Expert Mod 16PB @chrismaliszewski
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. @chrismaliszewski
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.
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 :)
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Arno R |
last post by:
Since I have seen Tony T. around the last week I thought I'll try once more ;-)
Hi all,
I still have clients with apps running on Access 2.0
It would be nice if I could use the Auto FE...
|
by: Wayne Aprato |
last post by:
I have a client who is running several Access 97 databases that I have
written for them. They are about to upgrade to Access 2003. Is the
default file format of Access 2003 still Access 2000 the...
|
by: Rob |
last post by:
Scenario:
O/S: Win XP Professional
Back-end: Access 2002 on network server
I have an Access 97 application, in production on our network, that
takes appoximately 5 minutes to process monthly...
|
by: stu |
last post by:
I have several databases that are opened using various versions of
Access and VB. Up till recently everything worked fine, then I started
getting a variety of lock file error messages, both on my...
|
by: Br |
last post by:
We're using an Access2000 ADP with an SQL2000 back-end. Because SQL2000
was released after Access2000 you need to be running Access2000 SP1 (2
or 3) for it to work properly.
Is there an easy way...
|
by: Tony |
last post by:
Hello everyone,
I am using the Access.Application class in my program to import external
data from an excel spreadsheet to my access DB, it's working fine on any
system that has Office 2002...
|
by: Chris Fulstow |
last post by:
Hi,
I need to restirict access at the page level to a range of IP
addresses. What's the best approach? I thought of building an HTTP
module that could compare the requesting IP with a...
|
by: Mell via AccessMonster.com |
last post by:
I created databases on Access 2003 and I want to deploy them to users. My
code was also done using 2003.
If they have Ms Access 2000 or higher, will they be able to use these dbs
with all code,...
|
by: Dedalus |
last post by:
If I want to retrieve a full version information for an ms access
databases, I use (from vb6 code) the "Access.Application" object and
SysCmd method. Whit the number returned by the method I can...
|
by: Harel |
last post by:
I have a report SQL which have been working for a few weeks. Its large
and the target database is large. It has 59 join, and I dont think
there is anything I can do about this, since the DB I use...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |