UtterAccess Discussion Forums > Dynamically Populate Datasheet Form With Fields

Full Version: Dynamically Populate Datasheet Form With Fields

UtterAccess Discussion Forums > Microsoft® Access > Access Forms

Pages: 1, 2

corrieann

Mar 26 2012, 04:40 PM

Is there a way to dynamically create a form using VB?

I know I can do all sorts of things with tables:

DoCmd.TransferSpreadsheet
DoCmd.RunSQL "DROP TABLE <table name."
Append records with INSERT INTO

blah, blah, blah.....but what can I do with forms???

Scenario:

My main form is allowing the user to do some data entry. There is a frequent need for the user to view the orginal data table to see what is in it prior to making a decision. So, instead of forcing the user to bounce around within the database, I have created a form with an embedded subform. The subform is merely a datasheet view of the main table.

The reason this has to be DYNAMIC is that the main table and it's field names change with every set of data that is loaded into the database. We never know the exact field names that may come over to us, thus this subform cannot be hard-coded as it is dependent on an ever-changing table.

Parts List:

Main table: tblParticipant_Client_Data_Original
Main form: ParticipantsbfrmClientFieldMAPPING
Subform: frmParticipant_Client_Data_ORIGINAL

In layman's terms:

Create a datasheet form called frmParticipant_Client_Data_ORIGINAL using all the fields found in tblParticipant_Client_Data_Original

I took a stab at starting the code:

CODE

'Create new form
db.Execute "CREATE FORM frmParticipant_Client_Data_ORIGINAL"

Set rs = db.OpenRecordset("tblParticipant_Client_Data_ORIGINAL")
    Set td = db.TableDefs("tblParticipant_Client_Data_ORIGINAL")
       For Each fld In td.Fields

theDBguy

Mar 26 2012, 04:59 PM

Hi corrieann,

What version of Access are you using? Please remember to select the version number when posting questions in case it becomes relevant to the discussion.

Take a look at this previous discussion.

Just my 2 cents...

corrieann

Mar 26 2012, 05:12 PM

Sorry, 2003

corrieann

Mar 26 2012, 06:03 PM

Thanks for the link. I took this peice of code from it:

CODE

Dim frm As Form

DoCmd.DeleteObject acForm, "Form2"  <---doing this in the event the form we are about to create already exists
Set frm = CreateForm(, "sfrmProjectIntakeHrs") <---not EXACTLY sure what this does, but I assume it is the command to actually Create the form
frm.RecordSource = "temp_ProjHrsByTeam" <---where to get the field names to include on the form 
frmName = "sfrmProjectIntakeHrs" <---????????
Forms!Form2.DefaultView = 2 <---to create the form in datasheet view
fName = "Form2"  <---what to name the form 

I am a little confused. What does frmName = "sfrmProjectIntakeHrs" accomplish?
.
.
.

theDBguy

Mar 27 2012, 04:14 PM

Hi,

QUOTE (corrieann @ Mar 26 2012, 04:03 PM)

I am a little confused. What does frmName = "sfrmProjectIntakeHrs" accomplish?

I think this specific post further down in that conversation may explain the answer to your question.

Just my 2 cents...

missinglinq

Mar 27 2012, 04:53 PM

QUOTE (corrieann @ Mar 26 2012, 05:40 PM)

...The reason this has to be DYNAMIC is that the main table and it's field names change with every set of data that is loaded into the database. We never know the exact field names that may come over to us

Dynamically creating Forms is almost always a bad idea, except for those cases in which it is a terrible idea! This sounds suspiciously like an attempt to take data from a spreadsheet program and cram it into a database! How in the world could this database ever be Normalized properly?

Linq ;0)>

corrieann

Mar 27 2012, 06:36 PM

Because it isn't being use as database as much as it is being used as a tool to conform data received from outside sources into my client's needed format.

corrieann

Mar 27 2012, 07:06 PM

QUOTE

Parts List:

Main table: tblParticipant_Client_Data_Original
Main form: ParticipantsbfrmClientFieldMAPPING
Subform: frmParticipant_Client_Data_ORIGINAL

Ok, so I got this far: I can delete the old form, open a new, blank form in design mode with the desired table (tblParticipant_Client_Data_Original) linked to it as it's datasource. What I can't do it get it to put all the fields in table onto the form and save the form. the green are my GUESSES at what each line does

CODE

Private Sub CreateClientDataSubForm()
Dim frm As Form

'DoCmd.DeleteObject acForm, "frmParticipantClientDataORIGINAL"  '<---doing this in the event the form we are about to create already exists
Set frm = CreateForm() 'command to actually Create the form
frm.RecordSource = "tblParticipant_Client_Data_ORIGINAL" '<---where to get the field names to include on the form
frm.Name = "frmParticipant_Client_Data_ORIGINAL" '<---subform to put the data
Forms!frmParticipantClientDataORIGINAL.DefaultView = 2 '<---to create the form in datasheet view
fName = "frmParticipantClientDataORIGINAL"  '<---what to name the form

Set db = CurrentDb
Set MyForm = Forms(fName)
Set Mycontrol = CreateControl(fName, acTextBox, acDetail, , "ClientData")
Mycontrol.Name = "ClientData"
Mycontrol.FontName = "Arial"
Mycontrol.FontSize = 10
Mycontrol.ColumnWidth = -2

DoCmd.Close acForm, fName, acSaveYes
End Sub

When I run the code I get the following error: "This property is read only and can't be set." After clicking Debug, this line is highlighted:

CODE

frm.Name = "frmParticipant_Client_Data_ORIGINAL" 

Any direction would be greatly appreciated.

MikeLyons

Mar 29 2012, 06:44 PM

What you could do is have a blank form, or at least a form set up as generic as possible, and saved as a particular name. Treat this as a template.

Instead of using CreateForm(), use DoCmd.CopyObject to make a copy of the template object under the desired new name. Then, use DoCmd.OpenForm, specifying the design mode as the view, and then add the recordsource and controls.

Mike

corrieann

Mar 29 2012, 08:30 PM

Adding the controls is what I can't seem to do. How do I say, "Add all the fields in the tblParticipant_Client_Data_Original, which is the recordsource?

theDBguy

Mar 29 2012, 08:57 PM

Hi,

QUOTE (corrieann @ Mar 29 2012, 06:30 PM)

Adding the controls is what I can't seem to do. How do I say, "Add all the fields in the tblParticipant_Client_Data_Original, which is the recordsource?

If you use the CreateForm() method to create the form, you can use the CreateControl() method to add the controls to the new form.

Just my 2 cents...

MikeLyons

Mar 30 2012, 11:08 AM

QUOTE (theDBguy @ Mar 29 2012, 06:57 PM)

Hi,

If you use the CreateForm() method to create the form, you can use the CreateControl() method to add the controls to the new form.

Just my 2 cents...

Will it not work when opening an existing form in design mode?

Mike

theDBguy

Mar 30 2012, 11:41 AM

Hi Mike,

QUOTE (MikeLyons @ Mar 30 2012, 09:08 AM)

Will it not work when opening an existing form in design mode?

Mike

It might, but I was just referring to the approach we already discussed earlier. My intention was to point the OP back to the link where the method of adding the controls was already covered.

I just didn't have any time to create a demo or even do some testing at the moment.

Cheers

MikeLyons

Mar 30 2012, 11:47 AM

I actually tried it and it does allow creation of controls on a form opened using DoCmd.OpenForm with the acDesign view option.

Corrieann's earlier post seemed to indicate that she was not able to set the newly created form's Name property and so my approach of using CopyObject to copy a blank template form allows specifying the new name.

Mike

theDBguy

Mar 30 2012, 12:07 PM

Hi Mike,

That's good news. Perhaps you could post your demo to show the OP exactly how you did it.

Just my 2 cents...

MikeLyons

Mar 30 2012, 01:00 PM

QUOTE (theDBguy @ Mar 30 2012, 10:07 AM)

Hi Mike,

That's good news. Perhaps you could post your demo to show the OP exactly how you did it.

Just my 2 cents...

All I did was create a form called "frmButtonz" and put a few random buttons on it. This was my template form.

Then a test module:

CODE

Option Compare Database
Option Explicit

Sub testNewForm()
  Const NEWFORM = "frmNewForm"

  makeNewForm "frmButtonz", NEWFORM

  Dim f As Form

  Set f = Forms(NEWFORM)
  f.RecordSource = "tblWebs"

  Dim c As Control

  Set c = CreateControl(NEWFORM, acTextBox, acDetail, , "SiteURL", 5000, 2000, 6000, 1440)
  c.Name = "txtNewText"
  Set c = CreateControl(NEWFORM, acListBox, acDetail, , , 5000, 5000, 6000, 1440)
  c.Name = "lstNewListbox"

End Sub
Sub makeNewForm(strTemplate As String, strNewName As String)
  DoCmd.CopyObject , strNewName, acForm, strTemplate
  DoCmd.OpenForm strNewName, acDesign
End Sub

When I ran testNewForm from the Immediate Window, I had a brand new copy of frmButtonz, called "frmNewForm" that had the buttons AND the new controls, a bound textbox and unbound listbox.

Mike

corrieann

Apr 2 2012, 11:19 AM

Ok, correct me if I am wrong here: doesn't the code below force me to create each control in the script?

CODE

  Dim c As Control

  Set c = CreateControl(NEWFORM, acTextBox, acDetail, , "SiteURL", 5000, 2000, 6000, 1440)
  c.Name = "txtNewText"
  Set c = CreateControl(NEWFORM, acListBox, acDetail, , , 5000, 5000, 6000, 1440)
  c.Name = "lstNewListbox"

I can't take this approach because I never know how many fields I will be recieving from my outside data source. Plus, all I need is to display the data in a datasheet form. There won't be anything fancy here.

CODE

Forms!frmParticipantClientDataORIGINAL.DefaultView = 2 '<---to create the form in datasheet view

MikeLyons

Apr 2 2012, 12:02 PM

QUOTE (corrieann @ Apr 2 2012, 09:19 AM)

Ok, correct me if I am wrong here: doesn't the code below force me to create each control in the script?

CODE

  Dim c As Control

   Set c = CreateControl(NEWFORM, acTextBox, acDetail, , "SiteURL", 5000, 2000, 6000, 1440)
   c.Name = "txtNewText"
   Set c = CreateControl(NEWFORM, acListBox, acDetail, , , 5000, 5000, 6000, 1440)
   c.Name = "lstNewListbox"

CODE

Forms!frmParticipantClientDataORIGINAL.DefaultView = 2 '<---to create the form in datasheet view

What you are looking at is some test/example code I wrote to demonstrate how code can create the controls on a form that you have created by copying a template form.

In your actual production code, what you could do is set the Recordsource of the form, then loop through the source table's fields like we did in a previous thread, and for each one, use the CreateControl() method to add a textbox (acTextBox) with the control source set as the name of the field, and then the name of the new control should be something like "txt" plus the field name.

Make sense?

Mike

corrieann

Apr 4 2012, 11:43 AM

Ok, I have this so far:

CODE

Private Sub CreatefrmParticipant_Client_Data_Datasheet()

Const NEWFORM = "frmParticipant_Client_Data_ORIGINAL"

Dim f As Form
Dim db As Database, td As TableDef
Dim rs As DAO.Recordset
Dim fld As Field
n = 0

Set db = CurrentDb

'Create new Form

Set f = Forms(frmParticipant_Client_Data_ORIGINAL)

f.RecordSource = "tblParticipant_Client_Data_ORIGINAL"
f.Name = "frmParticipant_Client_Data_ORIGINAL"
f.DefaultView = 2

Set rs = db.OpenRecordset("tblParticipant_Client_Data_ORIGINAL")

    For Each fld In rs.Fields
    Set Mycontrol = CreateControl(frmParticipant_Client_Data_ORIGINAL, acTextBox, acDetail, , rs.Fields(0))
    Mycontrol.Name = rs.Fields(0)
    Next fld

       Resume Next
    rs.MoveNext

rs.Close
db.Close

DoCmd.Close acForm, fName, acSaveYes

End Sub

Of course, it doesn't work...but I think I am close. Any help?
...

MikeLyons

Apr 4 2012, 12:18 PM

I think this block of code is wrong:

CODE

Set rs = db.OpenRecordset("tblParticipant_Client_Data_ORIGINAL")

     For Each fld In rs.Fields
     Set Mycontrol = CreateControl(frmParticipant_Client_Data_ORIGINAL, acTextBox, acDetail, , rs.Fields(0))
     Mycontrol.Name = rs.Fields(0)
     Next fld

        Resume Next
     rs.MoveNext

rs.Close
db.Close

Try:
(edited after testing my original approach)

CODE

Dim strFieldName As String
Dim i As Integer

For i = 0 To CurrentDb.TableDefs("tblParticipant_Client_Data_ORIGINAL").Fields.Count - 1
    strFieldName = CurrentDb.TableDefs("tblParticipant_Client_Data_ORIGINAL").Fields(i).Name
     Set Mycontrol = CreateControl(frmParticipant_Client_Data_ORIGINAL, acTextBox, acDetail, , strFieldName)
     Mycontrol.Name = "txt" & strfieldName
Next i

Mike

corrieann

Apr 4 2012, 01:41 PM

I keep getting an error on this line:

CODE

f.Name = "frmParticipant_Client_Data_ORIGINAL"

The error reads: This property is read-only and can't be set.

If I comment out that line of code I then get an error on this line:

CODE

f.DefaultView = 2

That error reads: To se this property, open the form or report in Design view.
...

MikeLyons

Apr 4 2012, 03:19 PM

QUOTE (corrieann @ Apr 4 2012, 11:41 AM)

I keep getting an error on this line:

CODE

f.Name = "frmParticipant_Client_Data_ORIGINAL"

The error reads: This property is read-only and can't be set.

That is why I had suggested using the CopyObject method instead -- you get to specify the name of the new form in a parameter of that method so that you don't need to set the Name property.

QUOTE

If I comment out that line of code I then get an error on this line:

CODE

f.DefaultView = 2

That error reads: To se this property, open the form or report in Design view.
...

you need to explicitly open the new form in design view in code using the acDesign option in DoCmd.OpenForm. Then set your reference to the form. After that it should work.

Mike

corrieann

Apr 4 2012, 04:34 PM

So far:

CODE

Sub testNewForm()
Const NEWFORM = "frmParticipant_Client_Data_ORIGINAL"

makeNewForm "frmParticipant_EV_Template_Sample_Data", NEWFORM   '<--This is an existing form in my database that I chose as a template

Dim f As Form
Dim db As Database, td As TableDef
Dim rs As DAO.Recordset
Dim fld As Field

DoCmd.OpenForm "frmParticipant_Client_Data_ORIGINAL", acDesign
  Set db = CurrentDb
  Set f = Forms(frmParticipant_Client_Data_ORIGINAL)
  f.RecordSource = "tblParticipant_Client_Data_ORIGINAL"
  f.DefaultView = 2

Dim c As Control
Dim strFieldName As String
Dim i As Integer

For i = 0 To CurrentDb.TableDefs("tblParticipant_Client_Data_ORIGINAL").Fields.Count - 1
    strFieldName = CurrentDb.TableDefs("tblParticipant_Client_Data_ORIGINAL").Fields(i).Name
     Set c = CreateControl(frmParticipant_Client_Data_ORIGINAL, acTextBox, acDetail, , strFieldName)
     c.Name = "txt" & strFieldName
Next i

DoCmd.Close acForm, fName, acSaveYes

End Sub
Sub makeNewForm(strTemplate As String, strNewName As String)
  DoCmd.CopyObject , strNewName, acForm, strTemplate
  DoCmd.OpenForm strNewName, acDesign
End Sub

It errors out on this line:

CODE

Set f = Forms(frmParticipant_Client_Data_ORIGINAL)

With the error reading: Variable not defined
...

MikeLyons

Apr 4 2012, 04:52 PM

In that line that fails, instead of the name of the form try just using NEWFORM.

CODE

Set f = Forms(NEWFORM)

Mike

corrieann

Apr 4 2012, 05:11 PM

So far:

CODE

Sub testNewForm()
Const NEWFORM = "frmParticipant_Client_Data_ORIGINAL"

makeNewForm "frmParticipant_EV_Template_Sample_Data", NEWFORM

Dim f As Form
Dim db As Database, td As TableDef
Dim rs As DAO.Recordset
Dim fld As Field

DoCmd.OpenForm "frmParticipant_Client_Data_ORIGINAL", acDesign
  Set db = CurrentDb
  Set f = Forms(NEWFORM)
  f.RecordSource = "tblParticipant_Client_Data_ORIGINAL"
  f.DefaultView = 2

Dim c As Control
Dim strFieldName As String
Dim i As Integer

For i = 0 To CurrentDb.TableDefs("tblParticipant_Client_Data_ORIGINAL").Fields.Count - 1
    strFieldName = CurrentDb.TableDefs("tblParticipant_Client_Data_ORIGINAL").Fields(i).Name
     Set c = CreateControl(NEWFORM, acTextBox, acDetail, , strFieldName)
     c.Name = "txt" & strFieldName
Next i

DoCmd.Close acForm, NEWFORM, acSaveYes

End Sub
Sub makeNewForm(strTemplate As String, strNewName As String)
  DoCmd.CopyObject , strNewName, acForm, strTemplate
  DoCmd.OpenForm strNewName, acDesign
End Sub

It runs and creates the desired form. However, it is not deleting the controls (fields) that were on the template form and I get all sorts of errors on the form itslef. They all say "Invalid Control Property: Control Source. No such field in the field list.

The new form's record source is tblParticipant_Client_Data_ORIGINAL, which is correct. It is in datasheet layout, which is correct. It also has the right # of records. But the form has "#Name?" in all the records/fields due to the error I mentioned.

How do I delete all the existing controls from the template?

corrieann

Apr 4 2012, 05:19 PM

I added this:

CODE

On Error Resume Next
  For Each ctl In NEWFORM
     ctl.value = ctl.DefualtValue
  Next

  Set ctl = Nothing

But it doesn't work. I get an error: For Each may only iterate over a collection object or an array. And it highlights this:

CODE

For Each ctl In NEWFORM

MikeLyons

Apr 4 2012, 05:49 PM

QUOTE (corrieann @ Apr 4 2012, 03:11 PM)

Okay, the template form should not need to have anything deleted -- it should only have what is actually needed every time you create a form from it. Remember that the template form is just that -- something to make a copy of for a specific purpose. The idea is then that you dynamically add the controls for the underlying data.

Now, one thing to be careful of when you are adding the controls is that if your field names have spaces in them, you should surround the reference to them in square brackets.

CODE

Set c = CreateControl(NEWFORM, acTextBox, acDetail, , "[" & strFieldName & "]")

Otherwise you might get the #Name? error.

Mike

MikeLyons

Apr 4 2012, 05:53 PM

QUOTE (corrieann @ Apr 4 2012, 03:19 PM)

I added this:

CODE

On Error Resume Next
   For Each ctl In NEWFORM
      ctl.value = ctl.DefualtValue
   Next

   Set ctl = Nothing

But it doesn't work. I get an error: For Each may only iterate over a collection object or an array. And it highlights this:

CODE

For Each ctl In NEWFORM

Right... NEWFORM is a constant that equates to a string containing the name of the form, not a collection.

Try:

CODE

On Error Resume Next
   For Each ctl In Forms(NEWFORM).Controls
      ctl.value = ctl.DefaultValue
   Next

   Set ctl = Nothing

I also saw a misspelling of the DefaultValue property's name which have corrected.

Keep in mind, too, that this block of code will probably fail in design mode because I don't believe you can set the Value property in Design mode because no recordset has been loaded.

Mike

corrieann

Apr 5 2012, 12:57 PM

Ok, I have one final problem with my syntax: Syntax error in DROP TABLE or DROP INDEX.

CODE

DoCmd.RunSQL "DROP FORM frmParticipant_Client_Data_ORIGINAL"

I assumed I could use the same DROP TABLE command by simply changing it to FORM.

MikeLyons

Apr 5 2012, 01:36 PM

QUOTE (corrieann @ Apr 5 2012, 10:57 AM)

Ok, I have one final problem with my syntax: Syntax error in DROP TABLE or DROP INDEX.

CODE

DoCmd.RunSQL "DROP FORM frmParticipant_Client_Data_ORIGINAL"

I assumed I could use the same DROP TABLE command by simply changing it to FORM.

No the DDL (Data Definition Language) commands only work with database engine objects such as tables. They have no knowledge of a form, which is an Access-specific object.

Use the DoCmd.DeleteObject method to delete a form. Make sure it's closed already and you will want to trap the error if the form object does not already exist.

Mike

corrieann

Apr 5 2012, 02:20 PM

Is there a way to get Access NOT to show when it is "working"? Meaning, when I run the script to have the new form built it shows that it is building the form. I see the new, blank form pop up on my screen, hang out for a few seconds and then go away. Can I prevent this so that it looks seamless to the end user?

theDBguy

Apr 5 2012, 02:24 PM

Hi corrieann,

Pardon me for jumping in...

I am happy to hear that you are making great progress with this project but I just had to ask; if you are displaying the new data in "datasheet" anyway, wouldn't it be simpler to just display the new table (or a query based on the new table) directly?

Normally, I wouldn't recommend using that approach but just thought maybe it would make your life easier.

Just my 2 cents...

corrieann

Apr 5 2012, 02:35 PM

I tried that initially. But I coudn't go off of either because once it was designed everything (fields) would "stick." I needed it to be dynamic because the fields in the source table changes all the time.

theDBguy

Apr 5 2012, 03:00 PM

Hi,

QUOTE (corrieann @ Apr 5 2012, 12:35 PM)

I don't understand. If you destroy and create a table each time but keep the same name, you should be able to create a query that just uses the asterisk to get all the fields from the table and then open the query to the user. Couldn't you?

I haven't tried it, but it seems it should work.

Just my 2 cents...

MikeLyons

Apr 5 2012, 03:01 PM

QUOTE (corrieann @ Apr 5 2012, 12:20 PM)

Sure... in the DoCmd.OpenForm method, which you would use to open the form in design mode, there is another argument for the Window Mode -- use the acHidden option.

Mike

corrieann

Apr 5 2012, 04:34 PM

Ok, here is my code to-date:

CODE

Private Sub btnParticipantFileImport_Click() 'PARTICIPANT

Dim strFilter As String
Dim strInputFileName As String
Dim lngFlags As Long
Dim tname As String

'TRAP FOR ANY ERRORS
    On Error Resume Next
tname = " tblParticipant_Client_Data_ORIGINAL "

'CLOSE TABLES IF OPEN
DoCmd.Close acTable, "tblParticipant_Client_Data_ORIGINAL", acSaveYes
DoCmd.Close acTable, "tblParticipant_Client_Data_MAPPED", acSaveYes
DoCmd.Close acTable, "tblParticipantFieldsMappedStatus", acSaveYes
DoCmd.Close acTable, "tblParticipant_Client_FieldNames", acSaveYes

'DELETE TABLES/FORMS THAT ARE DYNAMICALLY CREATED IF THEY ALREADY EXIST
DoCmd.SetWarnings False
   DoCmd.DeleteObject acForm, "frmParticipant_Client_Data_ORIGINAL"
   DoCmd.RunSQL "DROP TABLE tblParticipant_Client_Data_ORIGINAL"
   DoCmd.RunSQL "DROP TABLE tblParticipant_Client_Data_MAPPED"
   DoCmd.RunSQL "DROP TABLE tblParticipant_Client_FieldNames"
DoCmd.SetWarnings True

'CLEAR RECORDS FROM STATIC TABLES
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM tblParticipant_FieldMapping"
DoCmd.RunSQL "DELEETE * FROM tblParticipantFieldsMappedStatus"
DoCmd.RunSQL "DELETE * FROM tblParticipant_EV_Data"
DoCmd.RunSQL "DELETE * FROM tblParticipant_Client_Data_GAP"
DoCmd.SetWarnings True

'*********************************************************************
'  Step One:  Import the Participant Data file provided by the Client
'*********************************************************************

strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.XLS)", "*.XLS")
strFilter = ahtAddFilterItem(strFilter, "Access Files (*.mda, *.mdb)", "*.MDA;*.MDB")
strFilter = ahtAddFilterItem(strFilter, "dBASE Files (*.dbf)", "*.DBF")
strFilter = ahtAddFilterItem(strFilter, "Text Files (*.txt)", "*.TXT")
strFilter = ahtAddFilterItem(strFilter, "All Files (*.*)", "*.*")
strInputFileName = ahtCommonFileOpenSave( _
                Filter:=strFilter, OpenFile:=True, _
                DialogTitle:="Please select an input file...", _
                Flags:=ahtOFN_HIDEREADONLY)

'SELECT NEW CLIENT FILE -- THIS WILL REPLACE ALL DATA, NOT APPEND RECORDS TO EXISTING DATA!!
If Len(strInputFileName) > 0 Then
   DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel51, "tblParticipant_Client_Data_ORIGINAL", strInputFileName, True
   Else
   MsgBox "No File Selected"
End If

'******************************************************************
'  Step Two:  Create a table with the Client Data Field Names
'             This table will be used for the field mapping
'             excercises
'******************************************************************

Dim db As Database, td As TableDef
Dim rs As DAO.Recordset
Dim fielddescription As String
Dim FieldPosition As Long
Dim MappingStatus As String
Dim fld As Field
n = 0
Set db = CurrentDb
' Trap for any errors.
    On Error Resume Next
tname = "tblParticipant_Client_FieldNames"

'Create new tblTable
db.Execute "CREATE TABLE tblParticipant_Client_FieldNames(FieldName TEXT (55), FieldPosition DOUBLE, MappingStatus TEXT (25));"

Set rs = db.OpenRecordset("tblParticipant_Client_FieldNames")

    Set td = db.TableDefs("tblParticipant_Client_Data_ORIGINAL")
       For Each fld In td.Fields
          fielddescription = fld.Name
          FieldPosition = fld.OrdinalPosition
          MappingStatus = ""
          rs.AddNew
          rs!FieldName = fielddescription
          rs!FieldPosition = FieldPosition
          rs!MappingStatus = MappingStatus
          rs.Update
       Next fld

       Resume Next
    rs.MoveNext
rs.Close

'******************************************************************
' Step Three: Create a dynamic table with Client Data ORIGINAL records
'             This table will be used for reference/display in the
'             field mapping excercises
'******************************************************************

Const NEWFORM = "frmParticipant_Client_Data_ORIGINAL"

makeNewForm "frmTemplate", NEWFORM

Dim f As Form
Dim ctl As Control

DoCmd.OpenForm "frmParticipant_Client_Data_ORIGINAL", acDesign, , , , acHidden

  Set db = CurrentDb
  Set f = Forms(NEWFORM)
  f.RecordSource = "tblParticipant_Client_Data_ORIGINAL"
  f.DefaultView = 2

Dim c As Control
Dim strFieldName As String
Dim i As Integer

For i = 0 To CurrentDb.TableDefs("tblParticipant_Client_Data_ORIGINAL").Fields.Count - 1
    strFieldName = CurrentDb.TableDefs("tblParticipant_Client_Data_ORIGINAL").Fields(i).Name
    Set c = CreateControl(NEWFORM, acTextBox, acDetail, , "[" & strFieldName & "]")
    c.Name = strFieldName
Next i

DoCmd.Close acForm, NEWFORM, acSaveYes

'******************************************************************
'  Step Four: Update text boxes on the Main Switchboard to reflect
'             the data you just imported
'******************************************************************

ParticipantCount = DCount("*", "tblParticipant_Client_Data_ORIGINAL")
ClientFieldNameCount = DCount("*", "tblParticipant_Client_FieldNames")
ParticipantGAPDataCount = DCount("*", "tblParticipant_Client_Data_GAP")
ParticipantMapCount = DCount("*", "tblParticipantFieldsMappedStatus", "Mapping Status = 'Mapped'")
ParticipantDNICount = DCount("*", "tblParticipantFieldsMappedStatus", "Mapping Status = 'Do Not Import'")
ParticipantACRCount = DCount("*", "tblParticipantFieldsMappedStatus", "Mapping Status = 'Awaiting Client Review'")

db.Close
End Sub

It is all working as expected with one tiny, tiny exception:

CODE

DoCmd.SetWarnings False
   DoCmd.DeleteObject acForm, "frmParticipant_Client_Data_ORIGINAL"
   DoCmd.RunSQL "DROP TABLE tblParticipant_Client_Data_ORIGINAL"
   DoCmd.RunSQL "DROP TABLE tblParticipant_Client_Data_MAPPED"
   DoCmd.RunSQL "DROP TABLE tblParticipant_Client_FieldNames"
DoCmd.SetWarnings True

It won't delete (drop) my table: tblParticipant_Client_Data_ORIGINAL. I am not sure why this stopped working. Thoughts?

MikeLyons

Apr 5 2012, 05:27 PM

QUOTE

DoCmd.SetWarnings False
DoCmd.DeleteObject acForm, "frmParticipant_Client_Data_ORIGINAL"
DoCmd.RunSQL "DROP TABLE tblParticipant_Client_Data_ORIGINAL"
DoCmd.RunSQL "DROP TABLE tblParticipant_Client_Data_MAPPED"
DoCmd.RunSQL "DROP TABLE tblParticipant_Client_FieldNames"
DoCmd.SetWarnings True
It won't delete (drop) my table: tblParticipant_Client_Data_ORIGINAL. I am not sure why this stopped working. Thoughts?

Any error messages?

Usually if something is failing to delete, then an error must have occurred that should be trappable. If you have any open forms or queries that use the table as its data source then it might prevent deletion.

I see that in your code you are using On Error Resume Next which will ignore any errors. Try putting a breakpoint (I showed you how previously) at the line that should delete the table. Then press F8 to execute that line only. In the immediate window, enter the following command:

? Err.Description

If no error, there should be nothing printed. If an error occurred, it should give us an idea what went wrong.

Mike

corrieann

Apr 5 2012, 06:25 PM

I followed your instructions and no error was evident. So, I moved the code around a little:

CODE

DoCmd.DeleteObject acForm, "frmParticipant_Client_Data_ORIGINAL"
DoCmd.SetWarnings False
DoCmd.RunSQL "DROP TABLE tblParticipant_Client_Data_ORIGINAL"
DoCmd.RunSQL "DROP TABLE tblParticipant_Client_Data_MAPPED"
DoCmd.RunSQL "DROP TABLE tblParticipant_Client_FieldNames"
DoCmd.SetWarnings True

...thinking I would be able to see an error if it occurred. A warning appears saying that I am about to delete 0 rows from the specified table. I don't want to delete rows. I want to delete the whole table.

MikeLyons

Apr 5 2012, 06:41 PM

Try this:

Add this procedure I wrote for you to your code:

CODE

Sub killTable(ByVal strTable As String)
  '
  ' Delete a table
  '
  On Error GoTo Er

  CurrentDb.TableDefs.Delete strTable
Ex:
  Exit Sub

Er:
  MsgBox Err.Description, vbExclamation
  Resume Ex
End Sub

Now, replace the code that issues the DROP TABLE commands with the following:

CODE

killTable "tblParticipant_Client_Data_ORIGINAL"
killTable "tblParticipant_Client_Data_MAPPED"
killTable "tblParticipant_Client_FieldNames"

You can also get rid of the Set Warnings directives before and after these procedure calls... they are not needed.

If there is an error attempting to delete a table you will see an error message that will (hopefully) explain the problem.

Mike

corrieann

Apr 13 2012, 02:40 PM

I implemented your changes, but now when I try to execute my script I get this error: The database engine could not lock table 'tblParticipant_Client_Data_ORIGINAL' because it is already in use by another person or process.

DEBUG = highlighted line: CurrentDb.TableDefs.Delete strTable

CODE

Sub killTable(ByVal strTable As String)
   '
   ' Delete a table
   '
   On Error GoTo Er

   CurrentDb.TableDefs.Delete strTable
Ex:
   Exit Sub

Er:
   MsgBox Err.Description, vbExclamation
   Resume Ex
End Sub

darnellk

Apr 13 2012, 03:02 PM

I don't want to interrupt what you guys are working on, but just wanted to throw an idea out there in case it hasn't been mentioned.

If you know the maximum number of fields you would ever get in your table (say 40), couldn't you just create your subform with a bit of a buffer (say 50 textboxes) and just loop through the textboxes and table fields on Form Open, then set the 'Control Source' and 'Datasheet Caption' to match that of your table fields? When you are at the end of your table fields, just keep looping through the form controls and set the remaining 'Visible' property to FALSE.

This way you wouldn't need to create or delete any forms, tables, queries.

Just a suggestion - I'm still interested in seeing how this turns out.

corrieann

Apr 13 2012, 04:05 PM

Darnel,

I would love it if this was all that simple. But everytime I try to envission a "max parameter" for possible datasets, someone proves me wrong. I got a file the other day with 215 fields!!

theDBguy

Apr 13 2012, 04:16 PM

Hi corrieann,

QUOTE (corrieann @ Apr 13 2012, 02:05 PM)

Darnel,

I would love it if this was all that simple. But everytime I try to envission a "max parameter" for possible datasets, someone proves me wrong. I got a file the other day with 215 fields!!

Just wondering if you have considered what I said the other day about just opening up the query to the user? Or, is that not applicable to your setup?

Just my 2 cents...

darnellk

Apr 13 2012, 04:18 PM

Dang!

My only question then would be - is there really a case where you could look through 215 fields and interpret the data without any error? It sounds like you need to compare this data to something in your database, but could you then provide a makeshift wizard that would allow the user to select which fields they actually need to see. I'm not trying to downplay the scenario, but just know that I would have a very difficult time with having to look through that many fields, and the human error factor would come into play.

corrieann

Apr 16 2012, 01:36 PM

@ Darnel: I already have a "tool" in the database for the user to map fields, but it is for a completely different application. The problem with the import is that we just don't know what we are getting until we see the data within the fields. And I need that data displayed in a subform on a main form so that the user can make the decision of whether or not it is needed as they go through the mapping exercise. But to do this on import just isn't feasable.

@theDBGuy: Like I said, I can't seem to get the form to create the way you suggested. It will work the first time, but when it runs a second time, the field names from the previous run "stick" to the form. That was my first approach, but due to this behavior, I have had to find a work-around.

theDBguy

Apr 16 2012, 01:44 PM

Hi corrieann,

QUOTE (corrieann @ Apr 16 2012, 11:36 AM)

@theDBGuy: Like I said, I can't seem to get the form to create the way you suggested. It will work the first time, but when it runs a second time, the field names from the previous run "stick" to the form. That was my first approach, but due to this behavior, I have had to find a work-around.

I think we have a "disconnect." I was trying to tell you not to use a form; instead, just use the query.

Just my 2 cents...

MikeLyons

Apr 18 2012, 11:07 AM

QUOTE (corrieann @ Apr 13 2012, 12:40 PM)

Sorry for the late reply -- I have been off sick.

The error now gives us something to go on -- that error indicates that something currently is using that table, be it a query, or an open form or report, or maybe the table is open directly in Access or is open in a recordset somewhere.

You need to close whatever has the table in use before access will allow the table to be deleted.

Mike

corrieann

Apr 27 2012, 11:39 AM

Mike,

I understand that much, but there is nothing open other than the switchboard. I can't determine what is open. Is there some code I can include in the script that will close EVERYTHNG in order to prevent this from happening?

corrieann

Apr 27 2012, 11:44 AM

This is the module that contains the error:

CODE

Sub killTable(ByVal strTable As String)
   '
   ' Delete a table
   '
   On Error GoTo Er

   CurrentDb.TableDefs.Delete strTable
Ex:
   Exit Sub

Er:
   MsgBox Err.Description, vbExclamation
   Resume Ex
End Sub

corrieann

Apr 27 2012, 12:14 PM

It is the switchboard that is using it. I couldn't even manually delete the table from teh Objects window without getting the same error. Once I closed the switchboard form, then I could delete it.

Ideas?

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.