How to not allow users alter the table Results

hi,

i have a raw data table built, may i know how to hide the table to not allow users to alter the raw data table?

I use the Tool > Startup option to hide everything but if the users are clever enough, they would able to make that available and try to alter the raw data table which i want to prevent and hope that's not gonna happen.

Hi All

I have posted this query before (last year) but was forced to abandon it due to a death in the family. Now that I am back on track I need some help.

I found this code on the forum which works to a degree.

If I run the code it adds the records to the database fine. but if run again it duplicates all the data. I tried indexing a field with no duplicates which works fine but sometimes the record is ammended and field data is changed but as the field with no duplicates does not allow it to be added.

Any Ideas.

Evan

	Code:
	Option Compare Database
'This code was originally written by Ken Getz.
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
' Code courtesy of:
' Microsoft Access 95 How-To
' Ken Getz and Paul Litwin
' Waite Group Press, 1996
 
Type tagOPENFILENAME
    lStructSize As Long
    hwndOwner As Long
    hInstance As Long
    strFilter As String
    strCustomFilter As String
    nMaxCustFilter As Long
    nFilterIndex As Long
    strFile As String
    nMaxFile As Long
    strFileTitle As String
    nMaxFileTitle As Long
    strInitialDir As String
    strTitle As String
    flags As Long
    nFileOffset As Integer
    nFileExtension As Integer
    strDefExt As String
    lCustData As Long
    lpfnHook As Long
    lpTemplateName As String
End Type
 
Declare Function aht_apiGetOpenFileName Lib "comdlg32.dll" _
                                        () Alias "GetOpenFileNameA" (OFN As tagOPENFILENAME) As Boolean
Declare Function aht_apiGetSaveFileName Lib "comdlg32.dll" _
                                        ()
Alias "GetSaveFileNameA" (OFN As tagOPENFILENAME) As Boolean
Declare Function CommDlgExtendedError Lib "comdlg32.dll" () As Long
 
Global Const ahtOFN_READONLY = &H1
Global Const ahtOFN_OVERWRITEPROMPT = &H2
Global Const ahtOFN_HIDEREADONLY = &H4
Global Const ahtOFN_NOCHANGEDIR = &H8
Global Const ahtOFN_SHOWHELP = &H10
' You won't use these.
'Global Const ahtOFN_ENABLEHOOK = &H20
'Global Const ahtOFN_ENABLETEMPLATE = &H40
'Global Const ahtOFN_ENABLETEMPLATEHANDLE = &H80
Global Const ahtOFN_NOVALIDATE = &H100
Global Const ahtOFN_ALLOWMULTISELECT = &H200
Global Const ahtOFN_EXTENSIONDIFFERENT = &H400
Global Const ahtOFN_PATHMUSTEXIST = &H800
Global Const ahtOFN_FILEMUSTEXIST = &H1000
Global Const ahtOFN_CREATEPROMPT = &H2000
Global Const ahtOFN_SHAREAWARE = &H4000
Global Const ahtOFN_NOREADONLYRETURN = &H8000
Global Const ahtOFN_NOTESTFILECREATE = &H10000
Global Const ahtOFN_NONETWORKBUTTON = &H20000
Global Const ahtOFN_NOLONGNAMES = &H40000
' New for Windows 95
Global Const ahtOFN_EXPLORER = &H80000
Global Const ahtOFN_NODEREFERENCELINKS = &H100000
Global Const ahtOFN_LONGNAMES = &H200000
 
Function TestIt()
    Dim strFilter As String
    Dim lngFlags As Long
 
    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 (*.*)", "*.*")
 
    MsgBox "You selected: " & ahtCommonFileOpenSave(InitialDir:="C:", _
                                                    Filter:=strFilter, FilterIndex:=3, flags:=lngFlags, _
                                                    DialogTitle:="Hello! Open Me!")
 
    ' Since you passed in a variable for lngFlags,
    ' the function places the output flags value in the variable.
    Debug.Print Hex(lngFlags)
 
End Function
 
 
 
Function GetOpenFile(Optional varDirectory As Variant, _
                     Optional varTitleForDialog As Variant) As Variant
' Here's an example that gets an Access database name.
    Dim strFilter As String
    Dim lngFlags As Long
    Dim varFileName As Variant
 
    ' Specify that the chosen file must already exist,
    ' don't change directories when you're done
    ' Also, don't bother displaying
    ' the read-only box. It'll only confuse people.
    lngFlags = ahtOFN_FILEMUSTEXIST Or _
               ahtOFN_HIDEREADONLY Or ahtOFN_NOCHANGEDIR
 
    If IsMissing(varDirectory) Then
        varDirectory = ""
    End If
 
    If IsMissing(varTitleForDialog) Then
        varTitleForDialog = ""
    End If
 
    ' Define the filter string and allocate space in the "c"
    ' string Duplicate this line with changes as necessary for
    ' more file templates.
    strFilter = ahtAddFilterItem(strFilter, _
                                 "Access (*.mdb)", "*.MDB;*.MDA")
    ' Now actually call to get the file name.
 
    varFileName = ahtCommonFileOpenSave( _
                  OpenFile:=True, _
                  InitialDir:=varDirectory, _
                  Filter:=strFilter, _
                  flags:=lngFlags, _
                  DialogTitle:=varTitleForDialog)
 
    If Not IsNull(varFileName) Then
        varFileName = TrimNull(varFileName)
    End If
 
    GetOpenFile = varFileName
 
End Function
 
 
Function ahtCommonFileOpenSave( _
         Optional ByRef flags As Variant, _
         Optional ByVal InitialDir As Variant, _
         Optional ByVal Filter As Variant, _
         Optional ByVal FilterIndex As Variant, _
         Optional ByVal DefaultExt As Variant, _
         Optional ByVal FileName As Variant, _
         Optional ByVal DialogTitle As Variant, _
         Optional ByVal hwnd As Variant, _
         Optional ByVal OpenFile As Variant) As Variant
' This is the entry point you'll use to call the common
' file open/save dialog. The parameters are listed
' below, and all are optional.
'
' In:
' Flags: one or more of the ahtOFN_* constants, OR'd together.
' InitialDir: the directory in which to first look
' Filter: a set of file filters, set up by calling
' AddFilterItem. See examples.
' FilterIndex: 1-based integer indicating which filter
' set to use, by default (1 if unspecified)
' DefaultExt: Extension to use if the user doesn't enter one.
' Only useful on file saves.
' FileName: Default value for the file name text box.
' DialogTitle: Title for the dialog.
' hWnd: parent window handle
' OpenFile: Boolean(True=Open File/False=Save As)
' Out:
' Return Value: Either Null or the selected filename
    Dim OFN As tagOPENFILENAME
    Dim strFileName As String
    Dim strFileTitle As String
    Dim fResult As Boolean
 
    ' Give the dialog a caption title.
    If IsMissing(InitialDir) Then InitialDir = CurDir
    If IsMissing(Filter) Then Filter = ""
    If IsMissing(FilterIndex) Then FilterIndex = 1
    If IsMissing(flags) Then flags = 0&
    If IsMissing(DefaultExt) Then DefaultExt = ""
    If IsMissing(FileName) Then FileName = ""
    If IsMissing(DialogTitle) Then DialogTitle = ""
    If IsMissing(hwnd) Then hwnd = Application.hWndAccessApp
    If IsMissing(OpenFile) Then OpenFile = True
 
    ' Allocate string space for the returned strings.
    strFileName = Left(FileName & String(256, 0), 256)
 
    strFileTitle = String(256, 0)
 
    ' Set up the data structure before you call the function
    With OFN
        .lStructSize = Len(OFN)
        .hwndOwner = hwnd
        .strFilter = Filter
        .nFilterIndex = FilterIndex
        .strFile = strFileName
        .nMaxFile = Len(strFileName)
        .strFileTitle = strFileTitle
        .nMaxFileTitle = Len(strFileTitle)
        .strTitle = DialogTitle
        .flags = flags
        .strDefExt = DefaultExt
        .strInitialDir = InitialDir
        ' Didn't think most people would want to deal with
        ' these options.
        .hInstance = 0
        '.strCustomFilter = ""
        '.nMaxCustFilter = 0
        .lpfnHook = 0
        'New for NT 4.0
        .strCustomFilter = String(255, 0)
        .nMaxCustFilter = 255
    End With
 
    ' This will pass the desired data structure to the
    ' Windows API, which will in turn it uses to display
    ' the Open/Save As Dialog.
    If OpenFile Then
        fResult = aht_apiGetOpenFileName(OFN)
    Else
        fResult = aht_apiGetSaveFileName(OFN)
    End If
 
    ' The function call filled in the strFileTitle member
    ' of the structure. You'll have to write special code
    ' to retrieve that if you're interested.
    If fResult Then
        ' You might care to check the Flags member of the
        ' structure to get information about the chosen file.
        ' In this example, if you bothered to pass in a
        ' value for Flags, we'll fill it in with the outgoing
        ' Flags value.
        If Not IsMissing(flags) Then flags = OFN.flags
        ahtCommonFileOpenSave = TrimNull(OFN.strFile)
    Else
        ahtCommonFileOpenSave = vbNullString
    End If
 
End Function
 
 
Function ahtAddFilterItem(strFilter As String, _
                          strDescription As String, Optional varItem As Variant) As String
' Tack a new chunk onto the file filter.
' That is, take the old value, stick onto it the description,
' (like "Databases"), a null character, the skeleton
' (like "*.mdb;*.mda") and a final null character.
    If IsMissing(varItem) Then varItem = "*.*"
    ahtAddFilterItem = strFilter & _
                       strDescription & vbNullChar & _
                       varItem & vbNullChar
End Function
 
 
Private Function TrimNull(ByVal strItem As String) As String
    Dim intPos As Integer
 
    intPos = InStr(strItem, vbNullChar)
 
    If intPos > 0 Then
        TrimNull = Left(strItem, intPos - 1)
    Else
        TrimNull = strItem
    End If
 
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Bookings", strItem, True    ' the True is "has field Names"
End Function
 
 
Private Sub cmdImport_Click()
' Declare local variables.
    Dim strImportFile As String
 
    ' Assign the value of the Import File Textbox to the local variable.
    'strImportFile = Nz(Me.txtImportFile, "")
 
    ' Turns the display of system messages off.
    DoCmd.SetWarnings False
 
    ' Query that empties the "temp" import table (tblBookingsTEMP).
    'DoCmd.OpenQuery "qryFleetAccountsPayableTEMPDELETE"
 
    'Call TestFile
    ' Use the TransferSpreadsheet action to import data between the current Microsoft Access
    ' database (.mdb) or Access project (.adp) and a spreadsheet file (.xls).
    strItem = strImportFile
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Bookings", strImportFile, True    ' the True is "has field
Names"
 
    ' Query that cleans up the imported data by removing all rows that contain "/" or "" or "#"
    ' or "A" or "E" or "I" or "O" or "U" or "Y". Also removes rows where the Last 5 is greater
    ' or less than 5 digits from the tblFleetAccountsPayableTEMP table.
    DoCmd.OpenQuery "qryBookingsTEMPCLEANUPDELETE"
 
    ' Move from "temp" table (tblFleetBookingsTEMP) to final table (tblFleetAccountsPayable).
    DoCmd.OpenQuery "qryBookingsAPPEND"
 
    ' Turns the display of system messages on.
    DoCmd.SetWarnings True
 
    MsgBox "The Import Bookings Process is complete! " & vbCrLf & _
           "Please verify that the Data in the Bookings table is Correct. ", vbInformation, "Import Bookings Data"
 
    ' Close the dlgImportFleetAccountsPayableData dialog.
    DoCmd.Close acForm, "dlgImportBookingsData"
 
    ' Closes the background panel if loaded.
    'If IsLoaded("frmBackground") Then
    'DoCmd.Close acForm, "frmBackground"
    'End If
End Sub
 
 
Function TestFile()
    Dim strFilter As String
    Dim strInputFileName As String
 
    strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.XLS)", "*.XLS")
 
    strInputFileName = ahtCommonFileOpenSave( _
                       Filter:=strFilter, OpenFile:=True, _
                       DialogTitle:="Please select an input file...", _
                       flags:=ahtOFN_HIDEREADONLY)
 
    'Call cmdImport_Click
End Function



I have been making a wizard for setting parameters order of appearance in my application.

The table structure is as follows:

tblPool
PoolID
Type
Construction
Sanitiser
pH Control

trelPool
RelPoolID
PoolID
TestID

tblTreatOrder
RelPoolID
Parameter
TestTreat
Order

There are quite a few supporting tables but they dont add clarity so we can omit them.

In the wizard on form one we set the attributes of the pool relating to the pool table, that is PoolType (Spa or Pool), Construction, Sanitiser and pH Control. I have added an option of ALL, ANY, ANY and ANY in respective order. ALL means either pool types in this case spa or pool, and ANY means that the parameter we are testing for should be present for all constructions and all sanitisers and all pH Controls.

So far so good. This works well. I feed this data back to a class to process as we move through the wizard.

The problem I am thinking about and wondering aloud how to approach is this;

In the case the user selects a specific pool with a specific pH and specific sanitiser and a specific construction they may want to alter the order of parameters.

This form as it stands allows this.

However, if you then revist the all and any options as laid out above the parameters are reordered when we return to the specific example above. To try and make this clearer.

Attributes
Pool
Concrete
Liquid Chlorine
HCL Acid

Order Parameter
1 FAC
2 pH
3 TA

Attributes
ALL
ANY
ANY
ANY

Order Parameter
1 pH
2 FAC
3 TA

Attributes
Pool
Concrete
Liquid Chlorine
HCL Acid

Order Parameter
1 pH
2 FAC
3 TA


Which is not what we want to happen.

I think this error is caused by the way I save the order from the parameters. Following is the code I am currently using.


	Code:
	'Messages Sub
'Related Tables:
'tblTreatOrder
'trelPoolTest
'tlkupParameter
'tlkupTestTreat
'trelOutcome
'tblPoolTarget
'SubTables:
'trelOutcome -tblMessages, tblChemName, tlkupDirection
'trelPoolTest -tblPool, tlkupTestGroup
'tblTreatOrder -tlkupTestTreat, trelOutcome
'tblPoolTarget -tblTreatOrder, tlkupMath, trelOutcome
'Comments:
'The messages are added to tblMessages by the form in the wizard form itself, no action is required from this sub to add the
messages.  Rather this sub grabs the msgID from the combo box and this is all the information required.
'Likewise this sub grabs the information from the math combo box, the target value and the parameter selected.
'Fields:
'cboP - Column 0 is the ID of the parameter
'cboM - Column 0 is the ID of the math to apply
'txtT - is the target value at which this message fires
'txtM-Column 0 is the ID of the message
'Order is dictated by the order of appearance on the form this is available from intI
'Comment:
'There are several interrelated tables involved in this sub.  The core tables are trelPoolTest
'First action in the sub is to check we have an entry in trelPoolTest, that is that the pool and selected test group are
available, if they aren't we need to add them.
'CODE____________________________________________________________
Dim rst As ADODB.Recordset
Dim strSQL As String
Dim lngID As Long

    Set rst = New ADODB.Recordset
    
    strSQL = "SELECT * FROM trelOutcome WHERE Msg = " & Me.prpTMsg & ";"
    
    rst.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockPessimistic
    
    With rst
       
    If Me.prpTParam = 0 Then
    
       If .EOF Then
    
        .AddNew
        
        End If
        
        .Fields("Chemical") = 0
        .Fields("msg") = Me.prpTMsg
        
        .Update
        
    Else
    
        If .EOF Then
        
        MsgBox "You cannot add a new message for this chemical here.  Please go back and add the " & _
                       "message at the message form. ", vbOKOnly + vbInformation, "No Message on file"
                       
         End If
         
    End If
                       
    'End If
    
    .Close
    
    End With
        
    
    strSQL = "SELECT * FROM trelPoolTest WHERE Pool = " & Me.prpPoolID & " AND Test = " & Me.prpPoolTestGroup & ";"
    
    'Call myCreateQuery(strSQL, "qzDebugAddMsg" & Me.prpPoolID & "-" & Me.prpPoolTestGroup)
    
    rst.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockPessimistic
    
    With rst
    
        If .EOF Then
        
            'We dont have a pool test set up.  Set one up now
            
            .AddNew
            
            .Fields("Pool") = Me.prpPoolID
            .Fields("Test") = Me.prpPoolTestGroup
            
            .Update
            
        End If
        
            lngID = .Fields("RelPoolID")
            
        .Close
        
       
        
    End With
    
'Once verified that the pool and test group are entered into
'trelPoolTest we can then add the parameter to tblTreatOrder.
'Required ID from trelPoolTest, ParameterID, TestType and  Order
'Once this information is added to tblTreatOrder,
'we then need the ID value from tblTreatOrder
'CODE________________________________________________________________

    strSQL = "SELECT * FROM tblTreatOrder WHERE PoolTest =" & lngID & " AND Parameter = " & Me.prpTParam & _
                    " AND Type = " & Me.prpTType & ";"
    
    rst.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockPessimistic
    
    With rst
    
        If .EOF Then
        
        .AddNew
        
        .Fields("PoolTest") = lngID
        
        End If
        
        .Fields("Parameter") = Me.prpTParam
        .Fields("Type") = Me.prpTType
        .Fields("Order") = Me.prpTOrder
        
        .Update
        
        lngID = .Fields("ID")
        
        .Close
        
    End With
    
'The ID value from tblTreatOrder is required for the tblPoolTarget
'as well as Math, TargetValue and Outcome
'With these values added we are Complete, and the messages are
'correctly added to the tables to enable searching by the automated pool report.
'CODE________________________________________________________________
 
    strSQL = "SELECT * FROM tblPoolTarget WHERE PoolParam = " & lngID & ";"
    
    rst.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockPessimistic
    
    With rst
    
        If .EOF Then
        
            .AddNew
            
            .Fields("PoolParam") = lngID
            
        End If
        
            .Fields("Math") = Me.prpTmath
            .Fields("Tvalue") = Me.prpT
            .Fields("Outcome") = Me.prpTMsg
            
            .Update
            
            .Close
            
        End With
        
    Set rst = Nothing



Hello experts and beginners alike,

I'm planning for the creation of a button in a form. It will display browse window, when clicked, and allows you to select an Excel workbook. In the same window, a "Save" button allows you to import the Excel into new table in Access.

----

(Here's my progress. I still need your help!)

I put the following code inside the button's VBA editor in Access:

Code: Private Sub cmdFileOpenBrowse_Click() ' Browse for open file Dim myOpenFileName As Variant myOpenFileName = GetOpenFileEnd Sub Then I put the following code found into a new module known as Module1.

Code: Option Compare Database 'This code was originally written by Ken Getz. 'It is not to be altered or distributed, 'except as part of an application. 'You are free to use it in any application, 'provided the copyright notice is left unchanged. ' ' Code courtesy of: ' Microsoft Access 95 How-To ' Ken Getz and Paul Litwin ' Waite Group Press, 1996 Type tagOPENFILENAME lStructSize As Long hwndOwner As Long hInstance As Long strFilter As String strCustomFilter As String nMaxCustFilter As Long nFilterIndex As Long strFile As String nMaxFile As Long strFileTitle As String nMaxFileTitle As Long strInitialDir As String strTitle As String Flags As Long nFileOffset As Integer nFileExtension As Integer strDefExt As String lCustData As Long lpfnHook As Long lpTemplateName As String End Type Declare Function aht_apiGetOpenFileName Lib "comdlg32.dll" _ Alias "GetOpenFileNameA" (OFN As tagOPENFILENAME) As Boolean Declare Function aht_apiGetSaveFileName Lib "comdlg32.dll" _ Alias "GetSaveFileNameA" (OFN As tagOPENFILENAME) As Boolean Declare Function CommDlgExtendedError Lib "comdlg32.dll" () As Long Global Const ahtOFN_READONLY = &H1 Global Const ahtOFN_OVERWRITEPROMPT = &H2 Global Const ahtOFN_HIDEREADONLY = &H4 Global Const ahtOFN_NOCHANGEDIR = &H8 Global Const ahtOFN_SHOWHELP = &H10 ' You won't use these. 'Global Const ahtOFN_ENABLEHOOK = &H20 'Global Const ahtOFN_ENABLETEMPLATE = &H40 'Global Const ahtOFN_ENABLETEMPLATEHANDLE = &H80 Global Const ahtOFN_NOVALIDATE = &H100 Global Const ahtOFN_ALLOWMULTISELECT = &H200 Global Const ahtOFN_EXTENSIONDIFFERENT = &H400 Global Const ahtOFN_PATHMUSTEXIST = &H800 Global Const ahtOFN_FILEMUSTEXIST = &H1000 Global Const ahtOFN_CREATEPROMPT = &H2000 Global Const ahtOFN_SHAREAWARE = &H4000 Global Const ahtOFN_NOREADONLYRETURN = &H8000 Global Const ahtOFN_NOTESTFILECREATE = &H10000 Global Const ahtOFN_NONETWORKBUTTON = &H20000 Global Const ahtOFN_NOLONGNAMES = &H40000 ' New for Windows 95 Global Const ahtOFN_EXPLORER = &H80000 Global Const ahtOFN_NODEREFERENCELINKS = &H100000 Global Const ahtOFN_LONGNAMES = &H200000 Function RecallFileLocation() As String Dim strFilter As String, strLoc As String Dim lngFlags As Long, MyDefault As String 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, "Excel Files (*.xls)", "*.XLS") strFilter = ahtAddFilterItem(strFilter, "All Files (*.*)", "*.*") MyDefault = FindDefaults("DefaultOpenLocation") ' MsgBox ValidateLocations(MyDefault) Returns results of test to see if exists RecallFileLocation = ahtCommonFileOpenSave(InitialDir:=MyDefault, _ Filter:=strFilter, FilterIndex:=3, Flags:=lngFlags, _ DialogTitle:="Find File to Open!") ' MsgBox "You selected: " & ahtCommonFileOpenSave(InitialDir:="S:Assignment List", _ ' Filter:=strFilter, FilterIndex:=3, Flags:=lngFlags, _ ' DialogTitle:="Find File to Open!") ' Since you passed in a variable for lngFlags, ' the function places the output flags value in the variable. Debug.Print Hex(lngFlags) End Function Function GetOpenFile(Optional varDirectory As Variant, _ Optional varTitleForDialog As Variant) As Variant ' Here's an example that gets an Access database name. Dim strFilter As String Dim lngFlags As Long Dim varFileName As Variant ' Specify that the chosen file must already exist, ' don't change directories when you're done ' Also, don't bother displaying ' the read-only box. It'll only confuse people. lngFlags = ahtOFN_FILEMUSTEXIST Or _ ahtOFN_HIDEREADONLY Or ahtOFN_NOCHANGEDIR If IsMissing(varDirectory) Then varDirectory = "" End If If IsMissing(varTitleForDialog) Then varTitleForDialog = "" End If ' Define the filter string and allocate space in the "c" ' string Duplicate this line with changes as necessary for ' more file templates. strFilter = ahtAddFilterItem(strFilter, _ "Access (*.mdb)", "*.MDB;*.MDA") ' Now actually call to get the file name. varFileName = ahtCommonFileOpenSave( _ OpenFile:=False, _ InitialDir:=varDirectory, _ Filter:=strFilter, _ Flags:=lngFlags, _ DialogTitle:=varTitleForDialog) MsgBox varFileName If Not IsNull(varFileName) Then varFileName = TrimNull(varFileName) End If GetOpenFile = varFileName End Function Function ahtCommonFileOpenSave( _ Optional ByRef Flags As Variant, _ Optional ByVal InitialDir As Variant, _ Optional ByVal Filter As Variant, _ Optional ByVal FilterIndex As Variant, _ Optional ByVal DefaultExt As Variant, _ Optional ByVal FileName As Variant, _ Optional ByVal DialogTitle As Variant, _ Optional ByVal hwnd As Variant, _ Optional ByVal OpenFile As Variant) As Variant ' This is the entry point you'll use to call the common ' file open/save dialog. The parameters are listed ' below, and all are optional. ' ' In: ' Flags: one or more of the ahtOFN_* constants, OR'd together. ' InitialDir: the directory in which to first look ' Filter: a set of file filters, set up by calling ' AddFilterItem. See examples. ' FilterIndex: 1-based integer indicating which filter ' set to use, by default (1 if unspecified) ' DefaultExt: Extension to use if the user doesn't enter one. ' Only useful on file saves. ' FileName: Default value for the file name text box. ' DialogTitle: Title for the dialog. ' hWnd: parent window handle ' OpenFile: Boolean(True=Open File/False=Save As) ' Out: ' Return Value: Either Null or the selected filename Dim OFN As tagOPENFILENAME Dim strFileName As String Dim strFileTitle As String Dim fResult As Boolean ' Give the dialog a caption title. If IsMissing(InitialDir) Then InitialDir = CurDir If IsMissing(Filter) Then Filter = "" If IsMissing(FilterIndex) Then FilterIndex = 1 If IsMissing(Flags) Then Flags = 0& If IsMissing(DefaultExt) Then DefaultExt = "" If IsMissing(FileName) Then FileName = "" If IsMissing(DialogTitle) Then DialogTitle = "" If IsMissing(hwnd) Then hwnd = Application.hWndAccessApp If IsMissing(OpenFile) Then OpenFile = True ' Allocate string space for the returned strings. strFileName = Left(FileName & String(256, 0), 256) strFileTitle = String(256, 0) ' Set up the data structure before you call the function With OFN .lStructSize = Len(OFN) .hwndOwner = hwnd .strFilter = Filter .nFilterIndex = FilterIndex .strFile = strFileName .nMaxFile = Len(strFileName) .strFileTitle = strFileTitle .nMaxFileTitle = Len(strFileTitle) .strTitle = DialogTitle .Flags = Flags .strDefExt = DefaultExt .strInitialDir = InitialDir ' Didn't think most people would want to deal with ' these options. .hInstance = 0 '.strCustomFilter = "" '.nMaxCustFilter = 0 .lpfnHook = 0 'New for NT 4.0 .strCustomFilter = String(255, 0) .nMaxCustFilter = 255 End With ' This will pass the desired data structure to the ' Windows API, which will in turn it uses to display ' the Open/Save As Dialog. If OpenFile Then fResult = aht_apiGetOpenFileName(OFN) Else fResult = aht_apiGetSaveFileName(OFN) End If ' The function call filled in the strFileTitle member ' of the structure. You'll have to write special code ' to retrieve that if you're interested. If fResult Then ' You might care to check the Flags member of the ' structure to get information about the chosen file. ' In this example, if you bothered to pass in a ' value for Flags, we'll fill it in with the outgoing ' Flags value. If Not IsMissing(Flags) Then Flags = OFN.Flags ahtCommonFileOpenSave = TrimNull(OFN.strFile) Else ahtCommonFileOpenSave = vbNullString End If End Function Function ahtAddFilterItem(strFilter As String, _ strDescription As String, Optional varItem As Variant) As String ' Tack a new chunk onto the file filter. ' That is, take the old value, stick onto it the description, ' (like "Databases"), a null character, the skeleton ' (like "*.mdb;*.mda") and a final null character. If IsMissing(varItem) Then varItem = "*.*" ahtAddFilterItem = strFilter & _ strDescription & vbNullChar & _ varItem & vbNullChar End Function Private Function TrimNull(ByVal strItem As String) As String Dim intPos As Integer intPos = InStr(strItem, vbNullChar) If intPos > 0 Then TrimNull = Left(strItem, intPos - 1) Else TrimNull = strItem End If End Function
The button worked. The browse menu appeared. I was able to select an Excel file in any directory. However, the import operation did not happen for some reason after clicking "Save". Nothing was imported into Access.

Is there fault in the code? How to provoke Access to import the Excel after selecting it in this particular browse window? I also appreciate thread suggestions with similar context and solution. Thanks.

Note:-
Code shown found at http://www.mrexcel.com/forum/showthread.php?t=82469

Thank you for brief help on the following :

In attempting to get my Runtime Split Database opening and functioning on a computer isn’t on a network and doesn’t have Microsoft Access (or any Microsoft programs, for that matter), I copied the following 2 modules exactly as they are into Module 1 and Module 2, and it opens my Startup form, then goes into the form I created in place of a switchboard, but as soon as I try to select a form or a report, it comes up with a message to say that it can’t find the location (it names the one on the source computer, not the target computer).

I don’t know VBA and can’t understand the following code, and so I don’t know the answers to the following questions.

Firstly, do I need to replace certain bits of the code with names of objects that I have named or not?

Secondly, IF I do need to do that, WHICH bits of the code do I need to replace?

Thirdly, is there some sort of link I must make between the modules and the other code? If so, what and how?

Fourthly, was I wrong to have have copied in the following?
'***************** Code Start ***************
and
'***************** Code End ***************


I would be very grateful if somebody could please just answer the above questions briefly and if applicable, cite the code I need to replace with my own designated names, I can probably work out what I need to put in. If I can’t, I will ask further questions. I really would appreciate the above info, as I can only get to a computer without Microsoft Access to test my Runtime Split Database on now and then.

Module 1:
'***************** Code Start ***************
' This code was originally written by Dev Ashish.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Dev Ashish
'
Function fRefreshLinks() As Boolean
Dim strMsg As String, collTbls As Collection
Dim i As Integer, strDBPath As String, strTbl As String
Dim dbCurr As Database, dbLink As Database
Dim tdfLocal As TableDef
Dim varRet As Variant
Dim strNewPath As String

Const cERR_USERCANCEL = vbObjectError + 1000
Const cERR_NOREMOTETABLE = vbObjectError + 2000

On Local Error GoTo fRefreshLinks_Err

If MsgBox("Are you sure you want to reconnect all Access tables?", _
vbQuestion + vbYesNo, "Please confirm...") = vbNo Then Err.Raise cERR_USERCANCEL

'First get all linked tables in a collection
Set collTbls = fGetLinkedTables

'now link all of them
Set dbCurr = CurrentDb

strMsg = "Do you wish to specify a different path for the Access Tables?"

If MsgBox(strMsg, vbQuestion + vbYesNo, "Alternate data source...") = vbYes Then
strNewPath = fGetMDBName("Please select a new datasource")
Else
strNewPath = vbNullString
End If

For i = collTbls.Count To 1 Step -1
strDBPath = fParsePath(collTbls(i))
strTbl = fParseTable(collTbls(i))
varRet = SysCmd(acSysCmdSetStatus, "Now linking '" & strTbl & "'....")
If Left$(strDBPath, 4) = "ODBC" Then
'ODBC Tables
'ODBC Tables handled separately
' Set tdfLocal = dbCurr.TableDefs(strTbl)
' With tdfLocal
' .Connect = pcCONNECT
' .RefreshLink
' collTbls.Remove (strTbl)
' End With
Else
If strNewPath vbNullString Then
'Try this first
strDBPath = strNewPath
Else
If Len(Dir(strDBPath)) = 0 Then
'File Doesn't Exist, call GetOpenFileName
strDBPath = fGetMDBName("'" & strDBPath & "' not found.")
If strDBPath = vbNullString Then
'user pressed cancel
Err.Raise cERR_USERCANCEL
End If
End If
End If

'backend database exists
'putting it here since we could have
'tables from multiple sources
Set dbLink = DBEngine(0).OpenDatabase(strDBPath)

'check to see if the table is present in dbLink
strTbl = fParseTable(collTbls(i))
If fIsRemoteTable(dbLink, strTbl) Then
'everything's ok, reconnect
Set tdfLocal = dbCurr.TableDefs(strTbl)
With tdfLocal
.Connect = ";Database=" & strDBPath
.RefreshLink
collTbls.Remove (.Name)
End With
Else
Err.Raise cERR_NOREMOTETABLE
End If
End If
Next
fRefreshLinks = True
varRet = SysCmd(acSysCmdClearStatus)
MsgBox "All Access tables were successfully reconnected.", _
vbInformation + vbOKOnly, _
"Success"

fRefreshLinks_End:
Set collTbls = Nothing
Set tdfLocal = Nothing
Set dbLink = Nothing
Set dbCurr = Nothing
Exit Function
fRefreshLinks_Err:
fRefreshLinks = False
Select Case Err
Case 3059:

Case cERR_USERCANCEL:
MsgBox "No Database was specified, couldn't link tables.", _
vbCritical + vbOKOnly, _
"Error in refreshing links."
Resume fRefreshLinks_End
Case cERR_NOREMOTETABLE:
MsgBox "Table '" & strTbl & "' was not found in the database" & _
vbCrLf & dbLink.Name & ". Couldn't refresh links", _
vbCritical + vbOKOnly, _
"Error in refreshing links."
Resume fRefreshLinks_End
Case Else:
strMsg = "Error Information..." & vbCrLf & vbCrLf
strMsg = strMsg & "Function: fRefreshLinks" & vbCrLf
strMsg = strMsg & "Description: " & Err.Description & vbCrLf
strMsg = strMsg & "Error #: " & Format$(Err.Number) & vbCrLf
MsgBox strMsg, vbOKOnly + vbCritical, "Error"
Resume fRefreshLinks_End
End Select
End Function

Function fIsRemoteTable(dbRemote As Database, strTbl As String) As Boolean
Dim tdf As TableDef
On Error Resume Next
Set tdf = dbRemote.TableDefs(strTbl)
fIsRemoteTable = (Err = 0)
Set tdf = Nothing
End Function

Function fGetMDBName(strIn As String) As String
'Calls GetOpenFileName dialog
Dim strFilter As String

strFilter = ahtAddFilterItem(strFilter, _
"Access Database(*.mdb;*.mda;*.mde;*.mdw) ", _
"*.mdb; *.mda; *.mde; *.mdw")
strFilter = ahtAddFilterItem(strFilter, _
"All Files (*.*)", _
"*.*")

fGetMDBName = ahtCommonFileOpenSave(Filter:=strFilter, _
OpenFile:=True, _
DialogTitle:=strIn, _
Flags:=ahtOFN_HIDEREADONLY)
End Function

Function fGetLinkedTables() As Collection
'Returns all linked tables
Dim collTables As New Collection
Dim tdf As TableDef, db As Database
Set db = CurrentDb
db.TableDefs.Refresh
For Each tdf In db.TableDefs
With tdf
If Len(.Connect) > 0 Then
If Left$(.Connect, 4) = "ODBC" Then
' collTables.Add Item:=.Name & ";" & .Connect, KEY:=.Name
'ODBC Reconnect handled separately
Else
collTables.Add Item:=.Name & .Connect, Key:=.Name
End If
End If
End With
Next
Set fGetLinkedTables = collTables
Set collTables = Nothing
Set tdf = Nothing
Set db = Nothing
End Function

Function fParsePath(strIn As String) As String
If Left$(strIn, 4) "ODBC" Then
fParsePath = Right(strIn, Len(strIn) _
- (InStr(1, strIn, "DATABASE=") + 8))
Else
fParsePath = strIn
End If
End Function

Function fParseTable(strIn As String) As String
fParseTable = Left$(strIn, InStr(1, strIn, ";") - 1)
End Function
'***************** Code End ***************

Module 2:
'***************** Code Start **************
' This code was originally written by Ken Getz.
' It is not to be altered or distributed, 'except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code originally courtesy of:
' Microsoft Access 95 How-To
' Ken Getz and Paul Litwin
' Waite Group Press, 1996
' Revised to support multiple files:
' 28 December 2007

Type tagOPENFILENAME
lStructSize As Long
hwndOwner As Long
hInstance As Long
strFilter As String
strCustomFilter As String
nMaxCustFilter As Long
nFilterIndex As Long
strFile As String
nMaxFile As Long
strFileTitle As String
nMaxFileTitle As Long
strInitialDir As String
strTitle As String
Flags As Long
nFileOffset As Integer
nFileExtension As Integer
strDefExt As String
lCustData As Long
lpfnHook As Long
lpTemplateName As String
End Type

Declare Function aht_apiGetOpenFileName Lib "comdlg32.dll" _
Alias "GetOpenFileNameA" (OFN As tagOPENFILENAME) As Boolean

Declare Function aht_apiGetSaveFileName Lib "comdlg32.dll" _
Alias "GetSaveFileNameA" (OFN As tagOPENFILENAME) As Boolean
Declare Function CommDlgExtendedError Lib "comdlg32.dll" () As Long

Global Const ahtOFN_READONLY = &H1
Global Const ahtOFN_OVERWRITEPROMPT = &H2
Global Const ahtOFN_HIDEREADONLY = &H4
Global Const ahtOFN_NOCHANGEDIR = &H8
Global Const ahtOFN_SHOWHELP = &H10
' You won't use these.
'Global Const ahtOFN_ENABLEHOOK = &H20
'Global Const ahtOFN_ENABLETEMPLATE = &H40
'Global Const ahtOFN_ENABLETEMPLATEHANDLE = &H80
Global Const ahtOFN_NOVALIDATE = &H100
Global Const ahtOFN_ALLOWMULTISELECT = &H200
Global Const ahtOFN_EXTENSIONDIFFERENT = &H400
Global Const ahtOFN_PATHMUSTEXIST = &H800
Global Const ahtOFN_FILEMUSTEXIST = &H1000
Global Const ahtOFN_CREATEPROMPT = &H2000
Global Const ahtOFN_SHAREAWARE = &H4000
Global Const ahtOFN_NOREADONLYRETURN = &H8000
Global Const ahtOFN_NOTESTFILECREATE = &H10000
Global Const ahtOFN_NONETWORKBUTTON = &H20000
Global Const ahtOFN_NOLONGNAMES = &H40000
' New for Windows 95
Global Const ahtOFN_EXPLORER = &H80000
Global Const ahtOFN_NODEREFERENCELINKS = &H100000
Global Const ahtOFN_LONGNAMES = &H200000

Function TestIt()
Dim strFilter As String
Dim lngFlags As Long
strFilter = ahtAddFilterItem(strFilter, "Access Files (*.mda, *.mdb, *.accdb)", _
"*.MDA;*.MDB; *.ACCDB;")
strFilter = ahtAddFilterItem(strFilter, "dBASE Files (*.dbf)", "*.DBF")
strFilter = ahtAddFilterItem(strFilter, "Text Files (*.txt)", "*.TXT")
strFilter = ahtAddFilterItem(strFilter, "All Files (*.*)", "*.*")

' Uncomment this line to try the example
' allowing multiple file names:
' lngFlags = ahtOFN_ALLOWMULTISELECT Or ahtOFN_EXPLORER

Dim result As Variant

result = ahtCommonFileOpenSave(InitialDir:="C:", _
Filter:=strFilter, FilterIndex:=3, Flags:=lngFlags, _
DialogTitle:="Hello! Open Me!")

If lngFlags And ahtOFN_ALLOWMULTISELECT Then
If IsArray(result) Then
Dim i As Integer
For i = 0 To UBound(result)
MsgBox result(i)
Next i
Else
MsgBox result
End If
Else
MsgBox result
End If

' Since you passed in a variable for lngFlags,
' the function places the output flags value in the variable.
Debug.Print Hex(lngFlags)
End Function

Function GetOpenFile(Optional varDirectory As Variant, _
Optional varTitleForDialog As Variant) As Variant

' Here's an example that gets an Access database name.
Dim strFilter As String
Dim lngFlags As Long
Dim varFileName As Variant

' Specify that the chosen file must already exist,
' don't change directories when you're done
' Also, don't bother displaying
' the read-only box. It'll only confuse people.
lngFlags = ahtOFN_FILEMUSTEXIST Or _
ahtOFN_HIDEREADONLY Or ahtOFN_NOCHANGEDIR
If IsMissing(varDirectory) Then
varDirectory = ""
End If
If IsMissing(varTitleForDialog) Then
varTitleForDialog = ""
End If

' Define the filter string and allocate space in the "c"
' string Duplicate this line with changes as necessary for
' more file templates.
strFilter = ahtAddFilterItem(strFilter, _
"Access (*.mdb)", "*.MDB;*.MDA")

' Now actually call to get the file name.
varFileName = ahtCommonFileOpenSave( _
OpenFile:=True, _
InitialDir:=varDirectory, _
Filter:=strFilter, _
Flags:=lngFlags, _
DialogTitle:=varTitleForDialog)
If Not IsNull(varFileName) Then
varFileName = TrimNull(varFileName)
End If
GetOpenFile = varFileName
End Function

Function ahtCommonFileOpenSave( _
Optional ByRef Flags As Variant, _
Optional ByVal InitialDir As Variant, _
Optional ByVal Filter As Variant, _
Optional ByVal FilterIndex As Variant, _
Optional ByVal DefaultExt As Variant, _
Optional ByVal FileName As Variant, _
Optional ByVal DialogTitle As Variant, _
Optional ByVal hwnd As Variant, _
Optional ByVal OpenFile As Variant) As Variant

' This is the entry point you'll use to call the common
' file open/save dialog. The parameters are listed
' below, and all are optional.
'
' In:
' Flags: one or more of the ahtOFN_* constants, OR'd together.
' InitialDir: the directory in which to first look
' Filter: a set of file filters, set up by calling
' AddFilterItem. See examples.
' FilterIndex: 1-based integer indicating which filter
' set to use, by default (1 if unspecified)
' DefaultExt: Extension to use if the user doesn't enter one.
' Only useful on file saves.
' FileName: Default value for the file name text box.
' DialogTitle: Title for the dialog.
' hWnd: parent window handle
' OpenFile: Boolean(True=Open File/False=Save As)
' Out:
' Return Value: Either Null or the selected filename
Dim OFN As tagOPENFILENAME
Dim strFileName As String
Dim strFileTitle As String
Dim fResult As Boolean

' Give the dialog a caption title.
If IsMissing(InitialDir) Then InitialDir = CurDir
If IsMissing(Filter) Then Filter = ""
If IsMissing(FilterIndex) Then FilterIndex = 1
If IsMissing(Flags) Then Flags = 0&
If IsMissing(DefaultExt) Then DefaultExt = ""
If IsMissing(FileName) Then FileName = ""
If IsMissing(DialogTitle) Then DialogTitle = ""
If IsMissing(hwnd) Then hwnd = Application.hWndAccessApp
If IsMissing(OpenFile) Then OpenFile = True
' Allocate string space for the returned strings.
strFileName = Left(FileName & String(256, 0), 256)
strFileTitle = String(256, 0)
' Set up the data structure before you call the function
With OFN
.lStructSize = Len(OFN)
.hwndOwner = hwnd
.strFilter = Filter
.nFilterIndex = FilterIndex
.strFile = strFileName
.nMaxFile = Len(strFileName)
.strFileTitle = strFileTitle
.nMaxFileTitle = Len(strFileTitle)
.strTitle = DialogTitle
.Flags = Flags
.strDefExt = DefaultExt
.strInitialDir = InitialDir
' Didn't think most people would want to deal with
' these options.
.hInstance = 0
'.strCustomFilter = ""
'.nMaxCustFilter = 0
.lpfnHook = 0
'New for NT 4.0
.strCustomFilter = String(255, 0)
.nMaxCustFilter = 255
End With
' This will pass the desired data structure to the
' Windows API, which will in turn it uses to display
' the Open/Save As Dialog.
If OpenFile Then
fResult = aht_apiGetOpenFileName(OFN)
Else
fResult = aht_apiGetSaveFileName(OFN)
End If

' The function call filled in the strFileTitle member
' of the structure. You'll have to write special code
' to retrieve that if you're interested.
If fResult Then
' You might care to check the Flags member of the
' structure to get information about the chosen file.
' In this example, if you bothered to pass in a
' value for Flags, we'll fill it in with the outgoing
' Flags value.
If Not IsMissing(Flags) Then Flags = OFN.Flags
If Flags And ahtOFN_ALLOWMULTISELECT Then
' Return the full array.
Dim items As Variant
Dim value As String
value = OFN.strFile
' Get rid of empty items:
Dim i As Integer
For i = Len(value) To 1 Step -1
If Mid$(value, i, 1) Chr$(0) Then
Exit For
End If
Next i
value = Mid(value, 1, i)

' Break the list up at null characters:
items = Split(value, Chr(0))

' Loop through the items in the "array",
' and build full file names:
Dim numItems As Integer
Dim result() As String

numItems = UBound(items) + 1
If numItems > 1 Then
ReDim result(0 To numItems - 2)
For i = 1 To numItems - 1
result(i - 1) = FixPath(items(0)) & items(i)
Next i
ahtCommonFileOpenSave = result
Else
' If you only select a single item,
' Windows just places it in item 0.
ahtCommonFileOpenSave = items(0)
End If
Else
ahtCommonFileOpenSave = TrimNull(OFN.strFile)
End If
Else
ahtCommonFileOpenSave = vbNullString
End If
End Function

Function ahtAddFilterItem(strFilter As String, _
strDescription As String, Optional varItem As Variant) As String

' Tack a new chunk onto the file filter.
' That is, take the old value, stick onto it the description,
' (like "Databases"), a null character, the skeleton
' (like "*.mdb;*.mda") and a final null character.

If IsMissing(varItem) Then varItem = "*.*"
ahtAddFilterItem = strFilter & _
strDescription & vbNullChar & _
varItem & vbNullChar
End Function

Private Function TrimNull(ByVal strItem As String) As String
Dim intPos As Integer

intPos = InStr(strItem, vbNullChar)
If intPos > 0 Then
TrimNull = Left(strItem, intPos - 1)
Else
TrimNull = strItem
End If
End Function

Private Function FixPath(ByVal path As String) As String
If Right$(path, 1) "" Then
FixPath = path & ""
Else
FixPath = path
End If
End Function

'************** Code End *****************

A few months back I inherited a Time Recording System database. Actually there wasn't much there in terms of doing anything useful but it meant there was alot of mess to sift through and sort out. I have little experience with Access and have little time to get this system up and running. I would really appreciate it if I could get a bit of help and a few tips. Maybe I could even e-mail the db to someone to have a look at?


Basically there are three main tables recording the times a user has spent on a particular project and then displaying reports based on where people have spent there time on which projects, as well as general reports on listings of which projects are part of which section/department.


Table 1 - USER

Fields - Initials (primary key) (only 8 users so initials are all unique)

- Name

- Job Title

- Telephone No.



Table 2 - PROJECT

Fields - Project Ref No. (primary key)

- Project Title

- Project Type

- Budgeted Days for Project

- Report needed?

- etc.. etc..



Table 3 - PROJECT PROGRESS

- Date (primary key)

- Initials (primary key)

I am trying to make a database that is flly modifiable on H: and read-only on G:. It does not seem to be working correctly and I came across this code: Table Constraint
ALTER TABLE Test ADD
CONSTRAINT look_dont_touch
CHECK
(
-1 =
(
SELECT COUNT(*)
FROM Test AS T1
)
);
It does not allow a user accessing the database to add or delete. I am wondering how to implement it and will it affect both databases? I created the database on H: and placed a shortcut to it on G:, this way when H: is updated so is G: Thank you.

Hi, doing my project at college, decided to do a EPOS simulator for my sisters business based around barcodes etc and tied to a database. i've just knocked up something after normalising my data to prototype it to see if it is viable. I'm having problems with duplicate field entries. My db consists of essentially 4 tables at this point:

User : user ID, User Details

Product: Product ID, Barcode, Description, Cost Price, Sell Price

Transaction: Transaction Number, Date, User ID, Transaction Total

Transaction/Product - a compound key which is to manage the many to many that exists between Product and Transaction tables.

I am wanting to allow multiple entries for the same item, i.e. multiple scans, not a single sacn followed by a quantity. I keep getting an error message though that syas I am duplicating fields. I have tried altering the Index property for the fields in the Product/Transaction table but htis has had little effect.

Any thoughts please as to how to do this? I have attached the db for examination.

many thanks, Lol

Hi everyone...

I'm a high school student working on an Access project for a summer internship. I needed your assistance in writing a criteria for a select query.

Table1 has the following fields:
ID, First_Name, Last_Name, Org, Email, Status

Only "Email" is mandatory, ID is autonumber, the rest are optional.

I have to create a query that will allow users to search the table with any of the fields above. A user may search with only one field, e.g. all users where "org" = "YMCA"

Presently, I am using the similar criteria for all the fields:

Like "*" & [Forms]![Search]![txt_FirstName] & "*"


The problem occurs when, for example a record exists with the following -
First_Name = Null or Blank
Last_Name = "Smith"

If you search for "Smith" in Last_Name, then the record does not show up, because First_Name in the record is blank.

How can I alter the criteria for it do search correctly?

I already tried:
Like "*" & [Forms]![Search]![txt_FirstName] & "*" & ""


Thanks,
Gautam

Hopefully this won't be as complex as I imagine it!

I tried searching but am not sure what keywords to use properly so if this was addressed before, I appologise.

Here's my problem:

I have a table with multiple fields in it. Amongst the fields I have Agent_Name, Call_Date, talk_time, wrap_time, preview_time (there are more but i'm keeping my example simple).

Basically this will record all the calls we receive, the table records the name of the agent who took the call, the date and 3 different time references we use later.

Here's where I'm stuck. I want to build a form that would allow me to search that table. I need to be able for instance to find only the wrap_time for a single agent in the month of may.

The way I imagine this is with a drop down list that shows all available fields in the table. Then another drop down list that shows the agent's names and finaly a list box with 12 months that would allow the user to chose either a single month or multiple months (I know that's a problem in itself).

Right now my main concern is the first combo box, chosing which field to search from that list.

How should I approach this?

N.b. unfortunately I can't alter the orignal table's format as it comes from another system.

thank you in advance!

Hola,

I have a small question. I have a form which is based on a query that is built up through VBA code as an SQL text and the query is then saved. In case the query already exists, then the existing version of the query is first deleted (db.QueryDefs.Delete "QueryForReport") after which the new query under the new name is saved.

Access to the database is restricted through a workgroup information file. In case the database administrator is logged onto the database, there is no problem in opening the form that is based on this query. However, in case any other user opens the database, an error occurs: the user is not authorised to carry out this operation (delete the query). It does not matter which authorisations I specify for all users, even if I give all users administrator rights on the "QueryForReport", that only allows a user to delete the query once. Thereafter, a new query is created of which the current user is the owner and any other user (except for the administrator) is not authorised to delete that query. So, if thereafter another user logs into the database, then the query can again not be deleted.

A potential solution seemed to be: not to base the form on a query, but through VBA set the recordsource of the form (in the on-open event the following code is included: “me.recordsource = …… “. Now, this works fine with reports (that have subreports) but it does not seem to work with a form that has subforms. Upon opening the form, Access asks for the field through which the main form/table and the subform/table are linked. However, that field is definitely included in the sql code that is used to set the recordsource of the form (if i take the SQL text, put it in a query, save the query, and base the form on that query, then it works without any error, so the SQL code is correct). If you try to alter the field through which the main form and subforms are linked through the properties window then the message is given that the mainform and subforms are not linked. Strangely enough, if you just try to open the form and click “ok” when you are prompted for the link field, then the form opens and the correct link between main and subform seems to be in place. Even if you create a new record and fill in data in the subform, then in the linked field of the subform, automatically the correct code (of the main form) is filled in. So, some sort of link does seem to have been estaliblised.

Does anyone know how to properly set the recordsource in the open event, for a form with a subform without getting above problem?

Or maybe the solution is the following. Is it possible to change the owner (through VBA code) of the query "QueryForReport" to the CurrentUser before that query is to be deleted? That might be another solution.


Many thanks in advance for the help.

Best regards,

Okay. I have 2 databases. One is password protected and is saved .mdb for my use only. The other is for all to use saved as .mde. There are forms .mde linked to tables in .mdb. I want it so that someone goes into .mde enters form info close database. That info flows to .mdb and stays. Next user opens .mde and form is blank they enter info, close out and again that new info flows to .mdb with 2 records now. So on and so on. Only time records are taken out of .mdb is when I do it with my password. So far i am linked but .mde shows each record previously entered and current user of .mbe could alter prior records. If I make a form that only allows a single entry (and does not show the rest) how do I design a form so that when the db is closed the infor flows out. I am a newbie with some classes. Thanks.


Not finding an answer? Try a Google search.