Replies

Are you using Access security? I had similar problem with secured databases after installing Jet 4.0 Service Pack 5 (SP5). MS Knowledge Base Article Q301474 describes problem and possible fix IF you are using WIN 2K. Resolution for those NOT using WIN 2K is not provided. Since I’m still using WIN 98 had to resort to usual “fix”: importing everything into a new, unsecured database & then reapplying security. Note the article references A2K not 2002 (I’m using A2K) but this is only explanation I’ve seen for this error.
HTH

I had this problem (Error 3020) constantly when upgraded fm ACC 97 to A2K whenever using RecordsetClone methods to navigate to new record on form, typically using combobox AfterUpdate events. The error would not occur when using built-in navigation buttons or GoToRecord commands. It did not have to be a memo field, error would occur when updating any field on form and not explicitly saving record before using combobox to move to a new record. So I quickly got in habit of adding “If Me.dirty then Me.Refresh” statements whenever using RecordsetClone for navigational purposes. I have not been successful in finding an explanation for this behavior in the MSKB or elsewhere.

If you are using the inches as unit of measurement the ColumnWidths setting should read like this:

Me.Combo2.ColumnWidths = "0 in;2 in;3 in" 

HTH

If you are using the Shell function to run Powerpoint, try adding the “/s” switch to the command line. Example:

Dim stAppName As String

stAppName = “C:Program FilesOfficePOWERPNT.EXE /s C:SlideShowJan02.pps”
Call Shell(stAppName, vbNormalFocus)

HTH

For anyone really interested in this type of trick you can also download a sample database illustrating similar technique from Roger’s Access Library.

I tried this in ACC 97 (which I rarely use) and you’re right, the hidden table was deleted after compacting. I did check out the article but it did not specify which version(s) of Access were applicable.

I use this type of setup on a personnel data form. The e-mail address field is a text field, not hyperlink. I’ve tried using hyperlink fields but always have problems using them. On the form, there’s an “E-Mail” button next to the e-mail textbox. When you click the button, the following code is executed:

    If Not IsNull(Me.EMAIL_txt) Then
        Me.Email_btn.Hyperlink.Address = "mailto:" & Me.EMAIL_txt
        Else
        Me.Email_btn.Hyperlink.Address = ""
        Beep
        MsgBox "No e-mail address is entered.", vbCritical, "NO ADDRESS"
    End If
 

A new, blank e-mail message pops up with e-mail address entered. This works with Outlook 2K and Outlook Express.
HTH

Out of curiousity (I’ve never heard of this alleged bug before) I tried this (repeatedly) and the table hidden using dbHiddenObject attribute was NOT deleted after compacting the database. After compacting, made hidden table visible again by using

CurrentDB.TableDefs("HIDDEN").Attributes = 0

Of course you then have to enter F5 to refresh the database window display to see the table. I have no interest in hiding tables since I normally use user-level security to prevent users from accessing tables directly, but it was useful to note that the dbHiddenObject attribute really hides the object, while the UI flag method can be defeated by anyone with the sense to check the “Hidden Objects” box in the Options/View dialog. (I’m using A2K SR-1A.)

Declare variable and use in the SQL expression:

    Dim strInterval as string
    strInterval = "m"
    "WHERE dtmDateCreate Between DateAdd(strInterval,-1,Date()) And Date()"

And I think you were also missing a closing parentheses for the DateAdd function. It might be easier to read SQL expression if you use date variable:

    Dim dtFirstDate as Date
    dtFirstDate = DateAdd("m", -1, Date())
   "WHERE dtmDateCreate Between dtFirstDate And Date()"

HTH

Thanx, I tried your suggestion. I was still having problems opening form in hidden mode, whether declaring variable as generic Form object or as specific form — the form would not stay hidden. After some testing found the problem was the form’s On Load event, which included a DoCmd.Maximize statement. A window that is maximized obviously doesn’t stay hidden very long (obvious to me now, anyway!). So for anyone opening a form in stealth mode, recommend avoiding any Maximize or similar statements till you make form visible.

You could use syntax like this:

        Dim frm As Form_frm_TOC_BP_ACD
        DoCmd.OpenForm "frm_TOC_BP_ACD", , , , , acHidden
        Set frm = Forms![frm_TOC_BP_ACD]
        
        With frm
            .RecordSource = strSQL
            .Visible = True
        End With
        
        Set frm = Nothing 

You use an object variable (frm), specify the form to reference (Form_NameOfForm), open form, then set object variable to instance of form you just opened. You can then use frm & dot operator to reference the form’s properties and methods. Functions/procedures declared as Public will be listed as form methods when you type the dot after “frm”. Public variables will be listed as properties. NOTE: I’ve never had much luck using WindowMode = acHidden when opening form; it still opens momentarily, resulting in an annoying flash on screen….
HTH

I had no problem referencing a module-level Public variable declared in a form module from a subform. As example, in main form declared Public mstrLNAME As String. From subform, referenced this variable as follows:

Private Sub Form_Current()
    Dim strLNAME As String
    strLNAME = Forms![MAIN_frm].mstrLNAME
    Me.txtMainLNAME = strLNAME
(or)
    Me.txtMainLNAME = Forms![MAIN_frm].mstrModLNAME
End Sub 

When running main form the subform control displays the value of the main form variable. Had no problem in assigning main form variable either to a variable in declared in subform, or directly to a subform control. This worked with string and integer, did not try with fixed-length string. As Charlotte noted a public variable acts as a public property of the module which you can reference using the dot operator.
HTH

It looks like you have 1 comma too many which makes “Quote Form” the 4th argument, which for InputBox function is xpos, a numerical value for horizontal screen position. That’s why you’re getting a type mismatch error. I think what you want is something like:

jobprice = InputBox("Enter the Job Cost - $" & [QPrice], "Quote Form", QCOST)

I switched the 2nd two arguments because it looks like “Quote Form” should be the title which is 2nd argument, while QCOST is supposed to be the default response (I think) which is 3rd argument. If you declared your variables as specific data types it’d be easier to keep track of what’s going on & avoid type mismatch errors.

Assuming you are using User-Level security, you can use something like this on Form_Load event:

    If IsGroupMember(CurrentUser, "Admins") Then
        Me.SSN.Visible = True
    Else
        Me.SSN.Visible = False
    End If

The IsGroupMember function tests to see if a user (normally CurrentUser) is a member of a specified group:

Function IsGroupMember(strUserName As String, strGroupName As String) As Boolean
On Error GoTo Err_Handler

   'Determine if current user is member of specified group account:
    Dim ws As DAO.Workspace
    Dim usr As DAO.User
    Dim grp As DAO.Group
    Dim strErrMsg As String
    Dim i As Integer
    
    Set ws = DBEngine.Workspaces(0)
    Set grp = ws.Groups(strGroupName)
    Set usr = ws.Users(strUserName)
    
    For i = 0 To usr.Groups.Count - 1
         If usr.Groups(i).Name = strGroupName Then
             IsGroupMember = True
             Exit For
         Else
             IsGroupMember = False
         End If
    Next i

Exit_Function:
    Set ws = Nothing
    Set grp = Nothing
    Set usr = Nothing
    Exit Function

Err_Handler:
        strErrMsg = "Error No " & Err.Number & ": " & Err.Description
        Beep
        MsgBox strErrMsg, vbExclamation, "IS GROUP MEMBER FUNCTION ERROR"
    Resume Exit_Function
   
End Function

This function should be saved in a standard module so it can be used for any form/report where needed. This will work with report On Open event too. The SSN textbox visible property should be set to false in design mode. Note: Ensure that the group accounts that are restricted from viewing SSN’s do not have Read permission on the table(s) with SSN data, or these users may be able to open table directly & read data. The forms/reports should be based on RWOP (Run With Owner’s Permission) queries only.
HTH.

I tried adding dummy records with null values in the numeric field and combo still worked OK. The only way I was able to duplicate error was by applying a number format to the numeric field in the underlying table, which resulted in Not in List error when typing number in combobox rather than selecting with mouse. As Mark L suggested, get rid of any input masks that may be defined, and also any formatting applied to this number field in table, query, or form. (I assume the Envelope No is a simple integer or long integer & requires no special formatting.)