MS Access 2002 Form Displays no data...

  • Thread starter Thread starter John
  • Start date Start date
J

John

After changing the data source of an existing form with a
module, when I open the form with the same module the data
will not display. If I close the form and then open it
again maually, the data does display. Does anyone know why
this happens? I have tried, refresh, repaint and requery.
This problem also happens for reports where the data
source is changed dynamically with code.
Thanks for any help..
 
John,

Can you share the code that you are using as well as the
original recordsource?

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
 
Sure, below is the code.
The user chooses a file from their file system into the
Importfile selector form and the form then activates the
GetFieldList module.
The record source is created by the code and populated
with the data from the file the user provided.
The new data source will have only the field names and a
sample of the data from the first line.
The new dataset would look something like this:
FieldName Sample
F1 john
F2 andrew
F3 doe

Public Sub GetFieldList(ImportFile As Variant,
ImportSymbol As Variant)
Dim cnn1 As ADODB.Connection
Dim rstSADJ As ADODB.Recordset
Dim rstOUT As ADODB.Recordset
Dim fldLoop As Field
Dim strCnn As String, tn As String

Set cnn1 = New ADODB.Connection
Set rstSADJ = New ADODB.Recordset
Set rstOUT = New ADODB.Recordset

'Use date and time to create a unique table name.
tn = "SADJ_" & ImportSymbol & Format(Now
(), "mmddyyyy_hhnnss")

'Create the new output table
CurrentDb.Execute "CREATE TABLE " & tn _
& "(FieldName CHAR (40), Sample CHAR (100));"

'Open connection to DB
cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & Application.CurrentDb.Name

'Open recordsets and move to first record for sample
data
rstOUT.Open tn, cnn1, adOpenDynamic, adLockOptimistic,
adCmdTable
rstSADJ.Open ImportFile, cnn1, , , adCmdTable
rstSADJ.MoveFirst

'Write the field name and a sample from the first
record to the output table
For Each fldLoop In rstSADJ.Fields
rstOUT.AddNew
rstOUT![FieldName] = fldLoop.Name
rstOUT![Sample] = fldLoop.Value
rstOUT.Update
Next fldLoop

'Close recordsets, import form and connection.
rstSADJ.Close
rstOUT.Close
cnn1.Close
Set rstSADJ = Nothing
Set rstOUT = Nothing
DoCmd.Close acForm, "ImportFile", acSaveNo

'Set Recordsource for Mapper Sub form.
Call SetMapSub2Source(tn)
Application.RefreshDatabaseWindow
DoEvents

'open the File Mapper form.

DoCmd.OpenForm "FileMapper", acNormal, "", "", , acHidden

End Sub

Public Sub SetMapSub2Source(tablename As String)

DoCmd.OpenForm "MapSub2", acDesign, , , , acHidden
Forms!MapSub2.RecordSource = tablename
DoCmd.Close acForm, "MapSub2", acSaveYes
End Sub

Thanks
 
A couple of questions here.

If you stop the code after you assign the recordsource to
the subform, but before you open the FileMapper form, what
do you see as the recordsource of the subform if you
manually open it in design view? Is this correct at this
point?

If you open it by itself at this point, is it giving the
correct data?

If you only have FieldName and sample in the new
recordsource, what is the Master/Child link to the
FileMapper form? I don't see what the common key would be.

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
Sure, below is the code.
The user chooses a file from their file system into the
Importfile selector form and the form then activates the
GetFieldList module.
The record source is created by the code and populated
with the data from the file the user provided.
The new data source will have only the field names and a
sample of the data from the first line.
The new dataset would look something like this:
FieldName Sample
F1 john
F2 andrew
F3 doe

Public Sub GetFieldList(ImportFile As Variant,
ImportSymbol As Variant)
Dim cnn1 As ADODB.Connection
Dim rstSADJ As ADODB.Recordset
Dim rstOUT As ADODB.Recordset
Dim fldLoop As Field
Dim strCnn As String, tn As String

Set cnn1 = New ADODB.Connection
Set rstSADJ = New ADODB.Recordset
Set rstOUT = New ADODB.Recordset

'Use date and time to create a unique table name.
tn = "SADJ_" & ImportSymbol & Format(Now
(), "mmddyyyy_hhnnss")

'Create the new output table
CurrentDb.Execute "CREATE TABLE " & tn _
& "(FieldName CHAR (40), Sample CHAR (100));"

'Open connection to DB
cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & Application.CurrentDb.Name

'Open recordsets and move to first record for sample
data
rstOUT.Open tn, cnn1, adOpenDynamic, adLockOptimistic,
adCmdTable
rstSADJ.Open ImportFile, cnn1, , , adCmdTable
rstSADJ.MoveFirst

'Write the field name and a sample from the first
record to the output table
For Each fldLoop In rstSADJ.Fields
rstOUT.AddNew
rstOUT![FieldName] = fldLoop.Name
rstOUT![Sample] = fldLoop.Value
rstOUT.Update
Next fldLoop

'Close recordsets, import form and connection.
rstSADJ.Close
rstOUT.Close
cnn1.Close
Set rstSADJ = Nothing
Set rstOUT = Nothing
DoCmd.Close acForm, "ImportFile", acSaveNo

'Set Recordsource for Mapper Sub form.
Call SetMapSub2Source(tn)
Application.RefreshDatabaseWindow
DoEvents

'open the File Mapper form.

DoCmd.OpenForm "FileMapper", acNormal, "", "", , acHidden

End Sub

Public Sub SetMapSub2Source(tablename As String)

DoCmd.OpenForm "MapSub2", acDesign, , , , acHidden
Forms!MapSub2.RecordSource = tablename
DoCmd.Close acForm, "MapSub2", acSaveYes
End Sub

Thanks
-----Original Message-----
John,

Can you share the code that you are using as well as the
original recordsource?

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________



.
Click to expand...
Click to expand...
 
Back
Top