Replies

i Don’T KNOw anY moRe. alL the Old ruLeS see TO hAve BeeN turNEd ∩dsıpǝ poʍu.

NULL is not a value. Rather it is the absence of a value.
So you can’t say: If txtpad.Value = Null Then

rather you need to use
If isNull(me.txtpad) Then

I agree that use a File Picker is a good idea. This is the sort of form I use for importing from Excel.

30409-ImportForm

I separate the file selection from the import using a Browse button, and have a Check Data in Excel button so the user can double check the file before importing.

I find importing from Excel much more problematic than you would expect. The column headings need to be just right, and people seem to always mess with the templates you give them etc.

I don’t have access to 2003 at the moment, but in 2007 (and 2010) there is an option that controls this:

30403-PasteDefault

You many need to change

And also perhaps add an x to the end of this.

zXLFPath = “G:ExcelArchives” & zXLFName & “.xls”

and also check that the file exists:

If dir(zXLFPath) “” then

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, “Student_Table_Import”, zXLFPath, True
else
msgbox “The file cannot be found.”
end if

I attach a little working demo.

I don’t know anything about MT103 files, but I attach a little demo that opens a recordset based on a query, then outputs each record as a separate text file.
This might get you started.

then put a combo on a form with a RowSource of qryTables. …….
using the combo box wizard I get ” no valid fields can be found in qryTables, please select a new source”

I get the same thing. I had not tested this as it did not occur to me that there would be a problem with this.
Here is a workaround. Change the Row Source Type of your combo to Value List then use the following code to fill the values from the query. The code goes in the On Load event of the form. Don’t repeat the Private Sub Form_Load() and the End sub.

Code:

Private Sub Form_Load()
    Dim strTableList As String
    Dim db As DAO.Database
    Dim rs As DAO.Recordset

    Set db = CurrentDb
    Set rs = db.OpenRecordset(“qryTables”, dbOpenSnapshot)
    If Not rs.EOF Then
        rs.MoveFirst

        Do While Not rs.EOF
            strTableList = strTableList & rs(“Name”) & “;”
            rs.MoveNext
        Loop
        strTableList = Left(strTableList, Len(strTableList) – 1)
        ‘ Substitute the name of your combo box
        Me.Combotables.RowSource = strTableList
    End If
    ‘ Clean up
    rs.Close
    Set rs = Nothing
    Set db = Nothing
End Sub

Also, the first Record is showing f_4F9F14BD3CC24FC9BD1EEA8B81596AC8_Data” instead of a actual table

Don’t know what to make of that. Get your database to show you Hidden and System objects, so you can see the full list of Tables.

Right Click on the top of the Navigation Bar and choose Navigation Options.
30397-NavOptions
Then tick the boxes for Show Hidden Objects and Show System Objects. See what you can find.
Look in MySysObjects (That is the table the query uses.)

The downside is without reducing the height on the picture control when there is no picture every detail line is at least the height of the picture control even when the text requires less than the 2 inch setting.

Hmm. That is not happening for me. Is the Can Shrink property of both the Detail Section and the Memo field set to yes?
30396-IllustratedCatalogue

A closing quote should be followed by a space, and an opening one should not.
So you might be able to use Find and Replace to replace an Opening Quote followed by a space with a Closing Quote followed by a space.

To actually get them to appear in the Find and Replace Dialog find a correct Closong Quote and copy it. Then find and incorrect one and select it.
Then open Find and Replace, and paste the Closing Quote into Replace with box.

I suspect you did not paste all the SQL I gave you. It looks like you left out the last bracket. The SQL I posted works for me in 2010.

Actually I do have another suggestion.
Instead of changing the Height of the picture in code:
Me!UM_Pic.Height = 0
Me!UM_Pic.Height = 2 * 1440

Just change its visibility
Me!UM_Pic.visible = False
Me!UM_Pic.Visible = True

and just leave the Height permanently at whatever you want it to be.

I don’t know why this should work, but I did make this change in my code.
Initially it did not help, but the report then worked properly when I added the FormatCount code.

Sorry, it seems that no one has an answer for you.

I believe that Access just does not expose the contents of these groups to VBA (but I would be happy to be proved wrong about this.)

I don’t know whether this helps you (or me)?
The Seek method is very fast because it uses an Index. The FindFirst method just works through the Recordset until it finds something.

So the first line of your Seek code specified the Index to use:

rst.Index = “PrimaryKey”

The Index Property of a Recordset is only available for Table Type Recordsets.

30387-Index

30388-Recordsettype

Whenever you create a query, Access stores the query as a piece of SQL. In normal use you don’t see it, but you can look at it ( and edit if you want) by switching to SQL view of a query. You don’t say what version of Access you use…the method of viewing the SQL is slightly different in different versions. In 2007 and 2010 it is under View on the Home tab.

30386-ViewSQL

So start to create a new query the normal way. The Show Table dialog pops up for you to add tables to the query. Close it without adding any tables, then immediately switch to SQL view of this query.
You will just see Select; Delete this and paste in the SQL I posted.
Test the query works by switching to datasheet view. Save it as (say) qryTables.