Option Compare Database
Option Explicit
Private Sub Comando0_Click()
'make form
Dim newForm As Form
Set newForm = Application.CreateForm
'define some utilitary variables
Dim i As Long, j As Long, fieldCount As Long
Dim someValues As String
'set an imaginary number of textboxes
'could be your field count
fieldCount = 10
'define placeholder variable for combos
Dim currCombo As ComboBox
'loop field count
For i = 1 To fieldCount
'make combos
Set currCombo = Application.CreateControl(newForm.Name, acComboBox)
'give them some props
currCombo.RowSourceType = "Value List"
currCombo.Top = i * 500
currCombo.Left = 600
currCombo.Width = 5000
currCombo.Height = 300
'add some data for the combo
'guess it'll be dynamic
For j = 1 To 3
someValues = someValues & " something in combo " & i & " (" & j & ")" & ","
Next j
currCombo.RowSource = someValues
'restart strings for next combo
someValues = ""
Next i
'give it some name for show
newForm.Caption = "MyForm"
newForm.HasModule = True
newForm.OnClose = "[Event Procedure]"
newForm.Module.AddFromString _
"Private Sub Form_Close()" & vbCrLf & _
" MsgBox ""Handle this somehow""" & vbCrLf & _
"End Sub"
'open form
DoCmd.OpenForm newForm.Name
'you don't have to save it
End Sub
Now, of course, you'd have to adapt it to your particular scenario. I don't recommend that you save the newly created form, instead, it should be created on the fly and discarded the same way to avoid hitting Access' limits.
Is this the best approach? We are not even sure what you want to do. I have other methods for dynamic control creation using the almighty web browser control, but this should suffice... maybe
Thx a bundle. Do you think that the method would create a problem with the loggin of info in Access. Cannot find anything that says Access saves the info if the form is deleted. ???
Do you think that the method would create a problem with the loggin of info in Access. Cannot find anything that says Access saves the info if the form is deleted. ???
When you delete a form, rest assured that no data is lost. All your information is safely retained in the tables of your database. The tables don't care if you make or remove forms that work with them, so there's no issue with the method. Just double check that the info you put in the form is saved in the table before you delete the form and you'll be fine.
You can disable the close button of the form and make a button that takes care of the closing, like:
Rich (BB code):
Private Sub Comando0_Click()
'make form
Dim newForm As Form
Set newForm = Application.CreateForm
'define some utilitary variables
Dim i As Long, j As Long, fieldCount As Long
Dim someValues As String
'set an imaginary number of textboxes
'could be your field count
fieldCount = 10
'define placeholder variable for combos
Dim currCombo As ComboBox
'loop field count
For i = 1 To fieldCount
'make combos
Set currCombo = Application.CreateControl(newForm.Name, acComboBox)
'give them some props
currCombo.RowSourceType = "Value List"
currCombo.Top = i * 500
currCombo.Left = 600
currCombo.Width = 5000
currCombo.Height = 300
'add some data for the combo
'guess it'll be dynamic
For j = 1 To 3
someValues = someValues & " something in combo " & i & " (" & j & ")" & ","
Next j
currCombo.RowSource = someValues
'restart strings for next combo
someValues = ""
Next i
Dim closeButton As CommandButton
Set closeButton = Application.CreateControl(newForm.Name, acCommandButton)
closeButton.Caption = "Close form"
closeButton.OnClick = "=CloseWithoutSave(""" & newForm.Name & """)"
'give it some name for show
newForm.Caption = "MyForm"
'add code
newForm.HasModule = True
'open form
DoCmd.OpenForm newForm.Name
'you don't have to save it
End Sub
And in a module:
Code:
Public Function CloseWithoutSave(frmName As String)
DoCmd.Close acForm, frmName, acSaveNo
End Function
That way you discard the pop up asking you to save changes. The form needs to be discarded, if you save it, it will eventually become unusable.
I have not analyzed your actual needs, but I'm sure that you and everyone reading the thread will benefit from a sample of the data in its initial state and its target state, so we can all come up with a reasonable workflow for you.
When you delete a form, rest assured that no data is lost. All your information is safely retained in the tables of your database. The tables don't care if you make or remove forms that work with them, so there's no issue with the method. Just double check that the info you put in the form is saved in the table before you delete the form and you'll be fine.
You can disable the close button of the form and make a button that takes care of the closing, like:
Rich (BB code):
Private Sub Comando0_Click()
'make form
Dim newForm As Form
Set newForm = Application.CreateForm
'define some utilitary variables
Dim i As Long, j As Long, fieldCount As Long
Dim someValues As String
'set an imaginary number of textboxes
'could be your field count
fieldCount = 10
'define placeholder variable for combos
Dim currCombo As ComboBox
'loop field count
For i = 1 To fieldCount
'make combos
Set currCombo = Application.CreateControl(newForm.Name, acComboBox)
'give them some props
currCombo.RowSourceType = "Value List"
currCombo.Top = i * 500
currCombo.Left = 600
currCombo.Width = 5000
currCombo.Height = 300
'add some data for the combo
'guess it'll be dynamic
For j = 1 To 3
someValues = someValues & " something in combo " & i & " (" & j & ")" & ","
Next j
currCombo.RowSource = someValues
'restart strings for next combo
someValues = ""
Next i
Dim closeButton As CommandButton
Set closeButton = Application.CreateControl(newForm.Name, acCommandButton)
closeButton.Caption = "Close form"
closeButton.OnClick = "=CloseWithoutSave(""" & newForm.Name & """)"
'give it some name for show
newForm.Caption = "MyForm"
'add code
newForm.HasModule = True
'open form
DoCmd.OpenForm newForm.Name
'you don't have to save it
End Sub
And in a module:
Code:
Public Function CloseWithoutSave(frmName As String)
DoCmd.Close acForm, frmName, acSaveNo
End Function
That way you discard the pop up asking you to save changes. The form needs to be discarded, if you save it, it will eventually become unusable.
I have not analyzed your actual needs, but I'm sure that you and everyone reading the thread will benefit from a sample of the data in its initial state and its target state, so we can all come up with a reasonable workflow for you.
Sorry, i must of not be clear. And BTW, thank you for taking the time to discuss with me, that goes for all of the users that commented as well!!!
I understand that if there is no change to tables and no save, everything should stay intact. Anyways, the form created would not be bound to any tables, thus cannot change values in any tables. My intent is to pull what I need using VBA and action it also through VBA.
What I was referring to is the comment of MajP about the form maxing out eventually. I am trying to read more about it but not much is available on what access actually keeps track of. It seems, fromprevious comments made, that MSAccess keeps logging data, even if you discard the form, which I do not understand. If the form and all associated controls are never even saved, where is this data logging residing? And why would MSAccess keep info on a deleted form that was never ever saved? Seems odd to me.
Attached is a sample of the Tbl_sow table in a pre and post status from an update. Note that the post result would be an excel pulled in and the update would update the table from that excel. All good when fields headers match. What i would like is to pull in any excel file, show the table and the file headers, and then allow the user to choose what field of the Excel is to be matched with the table field. Instead of hard coding things. I will lots of files coming in to update various tables and i do not want to have to alter the file to match headers.
Microsoft Access 2016: maximum limits and specifications ... Microsoft Access training in Toronto, Ontario, Canada. Our instructor-led courses are offered in downtown Toronto.
Microsoft Access 2016: maximum limits and specifications ... Microsoft Access training in Toronto, Ontario, Canada. Our instructor-led courses are offered in downtown Toronto.
Thx for looking this up! So from what you circled I can understand that if you save and re-use the form, it will actually keep in mind what was added and remove fromit.
So, the debate is if the form is never saved, there would be no lifetime to start with, hence the data would be discarded, right?
So, no maxing out, for forms that will never be saved?
Your guess is a good as mine. This is not something I would ever do. If you are trying to build a tool for yourself, have fun but this design does not belong in an application built for others. Access doesn't clean up after itself so creating/deleting objects can cause major bloating. PLUS, since you need to work with the form in design view, you can never use the Access runtime engine, not to mention the fact that you would have to set the compact on close to yes so I hope you are always running from a local drive because compacting a database that is sitting on a server is also problematic.
Access is an excellent tool for lots of applications but creating objects on the fly is not its strong point.
If you want to update multiple different file types, why involve a middle-man? Just open the file and update it. Adding Access to the middle adds complexity but doesn't otherwise appear to add value.
If you want to update multiple different file types, why involve a middle-man? Just open the file and update it. Adding Access to the middle adds complexity but doesn't otherwise appear to add value.
I don't know what this means. If you are not connected to the network, how are you accessing the files?
The Excel files are given to me via USB stick to be imported into access. The Excel given to me are the main files used to update the tables and get a snapshot of the connected network. There is no middle-man here, simply an app in access that shows the data we need (intranet) to see from the connected network (Internet).
If you know the formats of the spreadsheets, why would you have to create forms on the fly? How many different variations of format do you have? why did you say you were updating the spreadsheets themselves? Importing data from spreadsheets that are created programmatically, is generally pretty safe. If the spreadsheets are created manually, it is amazing on how much junk a sloppy user can interject. I have lots of applications that import data from other applications. The format is always known so I can create procedures to look for certain types of data anomalies or missing data. Sometimes, I have to append the data to temp tables that are kept in separate BEs to avoid bloating the FE or BE. The files are appended to the temp BE and then cleaned up there. Each time a new import process starts, the temp BE is deleted and replaced by a fresh empty one so bloat doesn't become an issue.
Attached is a sample of the Tbl_sow table in a pre and post status from an update. Note that the post result would be an excel pulled in and the update would update the table from that excel. All good when fields headers match. What i would like is to pull in any excel file, show the table and the file headers, and then allow the user to choose what field of the Excel is to be matched with the table field. Instead of hard coding things. I will lots of files coming in to update various tables and i do not want to have to alter the file to match headers.
I checked your files. I think I understand what you need. I don't have time left today, but I can give it a try tomorrow. Nothing will be stored in Access, right? you just want to manage your files. Is that correct?
In the example you provided, the two sheets have matching headers, can you describe 3 scenarios of what you need? Like... first I import a source workbook, the form parses its headers, then I import a target workbook and the form parses its headers too... then I choose what ranges from the source workbook go into the target workbook... etc... something like that?
I checked your files. I think I understand what you need. I don't have time left today, but I can give it a try tomorrow. Nothing will be stored in Access, right? you just want to manage your files. Is that correct?
In the example you provided, the two sheets have matching headers, can you describe 3 scenarios of what you need? Like... first I import a source workbook, the form parses its headers, then I import a target workbook and the form parses its headers too... then I choose what ranges from the source workbook go into the target workbook... etc... something like that?
I need to import data into an Access table using an Excel file.
The scope is two fold:
1 - Using an Excel file with matching headers (verified header match prior to insert) This part I got it workng
2 - Using an Excel without matching headers. At that point, I need the user to pick what Excel header column goes with the Access table
That's the task. In addition, once it works, I will prompt the user to select what table is to be update with the selected file.
Setup in access is linked tables hosted on a shared drive. I have some problem posting some stuff here as it gives me errors when changing location.
That's a different take on the problem and would not require building forms and controls on the fly as you envisioned.
However, I question this as a design option. Importing data is not as simple as matching columns. Data imported this way should be validated as it is imported. Some columns will be required, there may be instances where you need to prevent a file from being imported multiple times, some columns might need specific types of validation. You can, without too much difficulty Pick a spreadsheet and then using a combo, pick a table. Then you have a subform bound to a cross reference table. The first column has a combo that lists column names from the spreadsheet. Then you have a combo that is dependent on the table combo in the parent form that lists the columns of the selected table. It is the subform that lets you match which columns from the spreadsheet go to which columns in the selected table. You can even run a query after the table is selected that populates the subform with all columns that match by name so the user only has to pick the odd columns.
But, what if you don't have a required field, what if the required field is empty in some rows? Where are you really going with this? You need to put this into context so we have a sense of how best to suggest making this mapping table. Once you have a mapping table, then it is pretty simple to build a query. You can even store the query and store its name in the header table of the cross reference. That way, the user only needs to do the mapping once.
That's a different take on the problem and would not require building forms and controls on the fly as you envisioned.
However, I question this as a design option. Importing data is not as simple as matching columns. Data imported this way should be validated as it is imported. Some columns will be required, there may be instances where you need to prevent a file from being imported multiple times, some columns might need specific types of validation. You can, without too much difficulty Pick a spreadsheet and then using a combo, pick a table. Then you have a subform bound to a cross reference table. The first column has a combo that lists column names from the spreadsheet. Then you have a combo that is dependent on the table combo in the parent form that lists the columns of the selected table. It is the subform that lets you match which columns from the spreadsheet go to which columns in the selected table. You can even run a query after the table is selected that populates the subform with all columns that match by name so the user only has to pick the odd columns.
But, what if you don't have a required field, what if the required field is empty in some rows? Where are you really going with this? You need to put this into context so we have a sense of how best to suggest making this mapping table. Once you have a mapping table, then it is pretty simple to build a query. You can even store the query and store its name in the header table of the cross reference. That way, the user only needs to do the mapping once.
Lots to take into consideration. But for the data quality, I am not too worried as the excel are produce by a system and not a human (IBM DOORS). The validation should of been done there. But I would verify some column. As for the overwrite and multiple upload, it is not too important because there can only be one row ID that exist in the table, thus the line is overwritten with the excel line if it exists.
You gave me a good idea though, I could populate a table and then simply present them in a continuous subform using the header count.
The exact thing but using a field mapping technique. You had me think yesterday and i think I will avoid object creator and opt for the setup of a temp table containing some of the information. Then I will pull it in a continuous subform for mapping.
On a similar topic warning things never to attempt in Access / VBA....
Several years ago I was involved in a discussion in this form about the possibility of getting an Access Form bound to an ADO.RecordSet rather than a DAO.RecordSet object. I still have the prototype form we arrived at.... as the best we could get it working.... so I was hoping the filename would assist in relocating the thread. Alas, no...
Oh!!! Here it is.... I changed search keywords, and located it....
Lagbolt has kindly provided an example of a fabricated ADO recordset bound to an ADO recordset here: http://www.access-programmers.co.uk/forums/showthread.php?t=258210 and it is reattached here. I have been unable to discover how to add records using the form. So, what needs to be changed, so...
www.access-programmers.co.uk
My example code is attached to the thread.
I have never used Access Forms bound to an ADO.RecordSet out side this thread / prototype. It is just not safe / reliable.
www.avantixlearning.ca
