Radio Buttons, ComboBoxes, Security

[davulf]

Hi There,

I am currently trying to refine a form that I have in Access 2000. I currently have a ComboBox that lists all of the terms in a field in my table. When a term is selected, other fields are populated. I have been requested to make this more dynamic, in the sense that you should be able to search by any metric.

I think the best way to do this is to have a radio button (or a set of radio buttons) that you can select one, and it will populate the ComboBox with the proper field. The problem that I am seeing is that I need it to be mutually exclusive. When you click one radio button, it should blank all of the others.

How can I go about doing this?

Another problem that I am having is that there is an AutoComplete function on the ComboBoxes, when you start typing terms, they are filtered for the letters you have typed. I like this functionality. The problem is that if you continue to type letters to a point where the list is filtered to 0 rows, it displays a runtime error. From a user experience perspective, I was wondering if it was possible to supress that error from being shown, without entering debug mode and closing functionality.

The third problem that I am looking to solve is version control and end user management. I don't want to have my users accessing the backend, as they should only be able to use the forms that are displayed on screen. So I need to block all access to anything except the forms.

When the program starts, it should pop up the form I have, which contains buttons to access the other forms that the user would need. How can I disable everything else? (bear in mind that I want the shift key option to remain, as that is how I will have the administrator open the database for modification)

Thanks for all of the help,
DaVuLf

 

[SydneyGeek]

I am currently trying to refine a form that I have in Access 2000. I currently have a ComboBox that lists all of the terms in a field in my table. When a term is selected, other fields are populated. I have been requested to make this more dynamic, in the sense that you should be able to search by any metric.

Check out this link

Another problem that I am having is that there is an AutoComplete function on the ComboBoxes, when you start typing terms, they are filtered for the letters you have typed. I like this functionality. The problem is that if you continue to type letters to a point where the list is filtered to 0 rows, it displays a runtime error. From a user experience perspective, I was wondering if it was possible to supress that error from being shown, without entering debug mode and closing functionality.

This kind of problem is handled by using the NotInList event

The third problem that I am looking to solve is version control and end user management. I don't want to have my users accessing the backend, as they should only be able to use the forms that are displayed on screen. So I need to block all access to anything except the forms.

When the program starts, it should pop up the form I have, which contains buttons to access the other forms that the user would need. How can I disable everything else? (bear in mind that I want the shift key option to remain, as that is how I will have the administrator open the database for modification)

To pop up the desired form go to Tools > Startup, and select your form from the Startup Form list.
Also in the startup area, deselect Show Database Container and Allow Bypass Keys.
That stops people from viewing the tables etc, as you said: now you need a back door so you can see the container when you want. do this:
1. In the top left corner of the form create a Command Button. Make these settings:
Special Effect --- Flat
Border --- Transparent
BackColor -- match the backcolor of the form
Caption -- don't have a caption.

In the DoubleClick event of the button put this one-liner:

Docmd.SelectObject acTable, , True

To get to the event code, view the control's properties, go to the Events tab, double-click the blank line next to the event so you see Event Procedure, then click the ... at the end of the line.

OK. When the DB opens you will see the form. Users will be unable to see the container. When you double-click the top left corner of the form, the container will be visible.

Denis

 

[davulf]

Thanks Sydney. That helped.

I managed to get the searching thing fixed. Now I am having issues with this query:

strSQL = "
SELECT DISTINCT tblBusinessTerm.DataDictionaryPIN, tblBusinessTerm.sap_name 
FROM tblBusinessTerm 
WHERE tblBusinessTerm.sap_name IN (
     SELECT tblBusinessTerm.sap_name 
     FROM tblBusinessTerm 
     WHERE tblBusinessTerm.sap_name IS NOT NULL) 
ORDER BY tblBusinessTerm.sap_name;"

I am setting this strSQL to the rowsource for my comboBox. The problem is that it is returning multiple blank rows. What I want to do it eliminate the blanks. I have tried to do that with a nested SELECT, but it doesn't want to work for me.

Also, this code seems to significantly delay the time it takes for the dropdown window to open and work. Is there a way to optimize it?

Thanks,
DaVuLF

 

[SydneyGeek]

You don't need a subselect for that. The approach that you took is called a correlated subquery, and it will slow you down because it evaluates each record.

Try:

strSQL = " 
SELECT DISTINCT tblBusinessTerm.DataDictionaryPIN, tblBusinessTerm.sap_name 
FROM tblBusinessTerm 
WHERE tblBusinessTerm.sap_name IS NOT NULL
ORDER BY tblBusinessTerm.sap_name;"

Denis

 

[davulf]

Hi Denis,

Thanks for the reply. That was how I had the query written initially. After it was still returning several blanks (and non-distinct rows; duplicates) I decided that maybe another approach was in order. This was why I went the route of the sub-query.

The problem is that hasn't really solved the problems either, just created other ones.

Is there any other possible reason why this would be happening? I suspected it was because the PIN (the first column the SELECT was pulling up) were never going to be the same, as that is the index. This would mean it would be impossible to group the terms.

 

[SydneyGeek]

By the sound if it you have sap_names with different PIN numbers. If you create the query like I suggested you will get duplicates (and multiples) in the list. I think there's a structural issue that you need to deal with here. If I understand correctly, you have a table with unique PIN and (among other things) the sap_name, which occurs varying numbers of times in the table. Try this:

1. Create a lookup table with sap_id (Autonumber) and sap_name (Text). Populate this table by
SELECT DISTINCT sap_Name FROM tblBusinessTerm ORDER BY sap_Name;
and then turning that into an Append query that loads the names into tlkSAP_Names

2. Use this table as the source for the combo box. The control source will need to be a number, not text. (This can be done using an Update query)

Now, there is no need to run any fancy filters. The lookup table contains no gaps or duplicates. Hope that helps.

Denis

 

[davulf]

Hi Sydney,

Thanks for the advice. That was the one solution I was hoping to avoid, as it creates more of a headache for maintenance when the administrator wants to add terms.

The sub-query I was trying to use I presumed would group all of the sap_name terms together that were not duplicated. The WHERE IN clause would then only return the results from the select distinct where this was the case.

I will keep looking into how I can modify the query, as I think that is going to be the easiest way to work this. The biggest problem with the other solution is that sap_name is not the only rowsource, which is the reason I'm using SQL. The user can look terms up by about 5 different ways. This would mean 5 lookup tables that have to be maintained.

This is why I am trying to do it on the fly.

I will post again if I think of anything. If anyone else can offer help with this, that would be greatly appreciated.

 

[SydneyGeek]

No need to worry about the admin adding terms. You can make that happen on the fly, using the NotInList property of the combo box. That way, if the combo encounters a new item the user is prompted to add it.

Something like... (this is a working example I dug out of a database)

Private Sub txtBatch_NotInList(strNewData As String, _
    intResponse As Integer)
'Set Limit To List property to Yes.

On Error GoTo ErrorHandler

    Dim intResult As Integer
    Dim strTitle As String
    Dim intMsgDialog As Integer
    Dim strMsg1 As String
    Dim strMsg2 As String
    Dim strMsg As String
    Dim cbo As Access.ComboBox
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strTable As String
    Dim strEntry As String
    Dim strFieldName As String
  
    'The name of the lookup table
    strTable = "tlkBatch"
  
    'The type of item to add to the table
    strEntry = "Batch number"
  
    'The field in the lookup table in which the new entry is stored
    strFieldName = "BatchNo"
  
    'The add-to combo box
    Set cbo = Me![txtBatch]

    'Display a message box asking whether the user wants to add
    'a new entry.
    strTitle = strEntry & " not in list"
    intMsgDialog = vbYesNo + vbExclamation + vbDefaultButton1
    strMsg1 = "Do you want to add "
    strMsg2 = " as a new " & strEntry & " entry?"
    strMsg = strMsg1 + strNewData + strMsg2
    intResult = MsgBox(strMsg, intMsgDialog, strTitle)

    If intResult = vbNo Then
        'Cancel adding the new entry to the lookup table.
        intResponse = acDataErrContinue
        cbo.Undo
        Exit Sub
    ElseIf intResult = vbYes Then
        'Add a new record to the lookup table.
        Set dbs = CurrentDb
        Set rst = dbs.OpenRecordset(strTable)
        rst.AddNew
        rst.Update
        rst.Close
   
        'Continue without displaying default error message.
        intResponse = acDataErrAdded
    End If
    
    
ErrorHandlerExit:
    Exit Sub

ErrorHandler:
    MsgBox "Error No: " & Err.Number & "; Description: " & _
        Err.Description
    Resume ErrorHandlerExit

End Sub

Denis