Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Search box question (again) (2003)

    I'm trying to apply the advice I got from HansV in his reply to an earlier question (<post#=440,227>post 440,227</post#>). In my efforts, I'm just mucking things up. Here's the story:

    I found some code in Helen Feddema's book, Access 2002 Inside Out, that I think will do the trick. However, I've hit a bit of a snag. I'm getting an error, "Sub or Function not defined." I have no idea what this error message means.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Search box question (again) (2003)

    That error means precisely what it says: your code tries to call a sub or function that does not exist. When you get the error, the Visual Basic Editor even highlights the name of the sub or function that does not exist: IsLoaded.
    In the past, this was a much-used custom function, whose code you had to add to each database. But in recent versions of Access, you can use the IsLoaded property of AccessObject instead: replace the line

    If IsLoaded("frmGlossary1996") = False Then

    with

    If CurrentProject.AllForms("frmGlossary1996").IsLoade d = False Then

    Another problem is that your cmdFinished_Click procedure has 2 End Subs. Remove everything after the first End Sub.

    Thirdly, your query qryGlossary1996 refers to several non-existing fields.

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Search box question (again) (2003)

    Many thanks, Hans! Your comments have been very helpful, and now I've uncovered some more trouble. (A quick comment before I continue, re:
    <hr>your query qryGlossary1996 refers to several non-existing fields.<hr>
    I think I just went too far in paring down my database before posting it. The fields you refer to actually do exist. I've placed the modified code into my working database, where I have a few more issues.

    1. When I click the button cmdFinished, I get a data-type mismatch error ("Error #3464...Data type mismatch in criteria expression"). I'm wondering if this might have anything to do with the fact that one record in the Acronym field contains a piece of numerical data (FSR2S").
    2. I think I'm getting close: After I click OK on the error message and close the frmNewTerm form, I can put the cursor into one of the lookup combo boxes and press F9. Now the new item appears in the drop-down list, I can click on it, and the item will appear on the form.
    3. Pressing F9 is not a user-friendly approach, of course, and furthermore the application has not yet re-ordered the list. New records appear at the bottom of the drop-down list.

    My code, as modified, reads as follows:

    <font face="Georgia">Private Sub cmdFinished_Click()
    On Error GoTo ErrorHandler

    Dim frm As Access.Form
    Dim strSearch As String

    If CurrentProject.AllForms("frmGlossary1996").IsLoade d = False Then
    DoCmd.OpenForm "frmGlossary1996"
    End If

    Set frm = Forms![frmGlossary1996]
    If Me.Dirty = True Then
    DoCmd.RunCommand acCmdSaveRecord
    End If

    ' Find the Glossary record that matches the control.
    strSearch = "[ID] = " & Chr$(39) & _
    Me![ID] & Chr$(39)
    Debug.Print "Search string: " & strSearch

    frm.Requery
    frm.RecordsetClone.FindFirst strSearch
    If frm.RecordsetClone.NoMatch = True Then
    Debug.Print "No match found"
    End If
    frm.Bookmark = frm.RecordsetClone.Bookmark
    frm![cboFindAcronym].Requery

    'Close data entry form
    DoCmd.Close

    ErrorHandlerExit:
    Exit Sub

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

    End Sub</font face=georgia>

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Search box question (again) (2003)

    1. the cmdFinished_Click procedure contains

    strSearch = "[ID] = " & Chr$(39) & _
    Me![ID] & Chr$(39)

    ID is a number field, so you shouldn't use quotes around the value:

    strSearch = "[ID] = " & Me![ID]

    2. & 3. I would change the line that opens frmNewTerm and frmEditTerm to

    DoCmd.OpenForm stDocName, , , stLinkCriteria, , acDialog

    The acDialog argument means that the user has to close the form being opened before continuing to work in frmGlossary1996. You can then add code below this line to requery the combo boxes and the form:

    Me.cboLookupTerm.Requery
    Me.cboFindAcronym.Requery
    Me.Requery

    In other words, the requery code is in frmGlossary1996 instead of in the two other forms.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Search box question (again) (2003)

    The code belongs in the module for frmGlossary1996. The top combo box on this form in the database you attached yesterday is named cboLookupTerm.

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Search box question (again) (2003)

    Hans:

    Well, I think I've done as you recommended, and now I'm getting "Compile error: Method or data member not found." The yellow highlighting appears over "<font face="Georgia">Private Sub cmdAddNewTerm_Click()</font face=georgia>"; shading appears over "<font face="Georgia">cboLookupTerm</font face=georgia>". Where'd I mess up? <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29> Here's the latest code for this command button:
    <font face="Georgia"> <span style="background-color: #FFFF00; color: #000000; font-weight: bold">
    Private Sub cmdAddNewTerm_Click()</span hi>
    On Error GoTo Err_cmdAddNewTerm_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmNewTerm"
    DoCmd.OpenForm stDocName, , , stLinkCriteria, , acDialog

    Me.cboLookupTerm.Requery
    Me.cboFindAcronym.Requery
    Me.Requery

    Exit_cmdAddNewTerm_Click:
    Exit Sub

    Err_cmdAddNewTerm_Click:
    MsgBox Err.Description
    Resume Exit_cmdAddNewTerm_Click

    End Sub</font face=georgia>

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Search box question (again) (2003)

    Hans, I know I've done a very poor job of explaining this project, and for that I apologize. I'm even starting to confuse myself! Perhaps it's best to just start over. I'm posting another copy of my database, and following are the major challenges I'm trying to meet.

    1. The main form, frmGlossary1996, displays data from the existing glossary; the data source is a query, qryGlossary1996. The controls that appear as "Term," "Acronym," and "Meaning" are disabled to keep users from making careless mistakes. I plan to split the database later and give each user a copy of the front end tailored to his or her abilities.
    2. I've added separate forms for adding new glossary items (frmNewTerm) and for editing existing terms (frmEditTerm). My plan is that the user will launch one of those forms from a command button on the main form. Launching either of these forms should cause the main form to minimize. Closing either form will restore the main form.
    3. When the user comes back to the main form after entering a new record or editing an existing record, the search boxes in the form header will (when dropped open) display the list of available values in proper order (A-Z) and without displaying any null values. There are many records where the Term, Acronym, or Meaning fields are blank.

    I hope this better explains the project. I'll be grateful for your recommendations.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Search box question (again) (2003)

    The combo box formerly known as cboLookupTerm had been renamed to the hardly meaningful Combo13. I have renamed it back - you should use meaningful names wherever possible. If you're going to review or revise this database in a year's time, Combo13 will not give you the slightest indication what it's for, but cboLookupTerm will, making it much easier to understand code etc.

    I have changed the Row Source of cboLookupTerm and cboFindAcronym to be sorted alphabetically and to exclude null values. Open the form in design view and select a combo box to view its Row Source.
    I left the requery code in frmEditTerm since you hadn't removed it. This makes the requery code in the main form superfluous.

    I added a WhereCondition for opening frmEditTerm, so that the appropriate record is displayed.

    I added a relationship between the two tables.

  9. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Search box question (again) (2003)

    Hans, again you're my hero! Thank you very much!

    If you can indulge me for a couple more things: What can I do to make the main form minimize while either of the other forms (frmEditTerm frmNewTerm) is open? And then restore when the edit or new form closes?

    Re: adding the relationship

  10. #10
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Search box question (again) (2003)

    One more question, Hans: The Save record button on the frmEditTerm form appears to be rather useless. However, the Close form button lacks any code to save the new record. Nonetheless, that is exactly what happens when I click the Close form button. What's happening in the background to save the new record?

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Search box question (again) (2003)

    1) In the On Click code for both command buttons, you could add a line

    Me.Visible = False

    before the DoCmd.OpenForm line, and

    Me.Visible = True

    after it. This will hide the main form temporarily.

    2) By creating a relationship in the relationship window and by setting referential integrity for the relationship (double click the line to see its properties), you ensure that the user cannot enter an Owner in tblGlossary1996 that does not exist in tblOwners, and that the user cannot delete a record in tblOwners that has glossary items associated with it. (You can also set Cascading Deletes. When you delete an owner record, all associated glossary records would be deleted with it. Depending on what you want, that can be extremely useful or extremely dangerous)

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Search box question (again) (2003)

    Access automatically saves a record when
    - You move to another (or a new) record in the table, query or form in which you were entering or editing the record.
    - You close the table, query or form in which you were entering/editing the record.
    So a Save Record button is not really necessary.

  13. #13
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Search box question (again) (2003)

    I see. Thanks again, Hans!

  14. #14
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Search box question (again) (2003)

    Hans: I'm trying the code (Me.Visible = False), and I've hit a snag. For now, I've put it into the code for cmdAddNewTerm only. The line, Me.Visible = False, works fine. The second line, Me.Visible = True, however, gives me a "Error No: 3077; Description: Syntax error (missing operator) in expression" when I click the Finished button on the frmNewTerm form. Here's how I used the code you gave me (I'm sure I misunderstood something):

    <font face="Georgia">
    Private Sub cmdAddNewTerm_Click()
    On Error GoTo Err_cmdAddNewTerm_Click

    Dim stDocName As String

    Me.Visible = False

    stDocName = "frmNewTerm"
    DoCmd.OpenForm stDocName, , , , , acDialog

    Exit_cmdAddNewTerm_Click:
    Exit Sub

    Me.Visible = True

    Err_cmdAddNewTerm_Click:
    MsgBox Err.Description
    Resume Exit_cmdAddNewTerm_Click
    End Sub</font face=georgia>

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Search box question (again) (2003)

    That has nothing to do with the main form being visible or not. You don't test in the code for the Finished button on frmNewTerm whether the user has actually entered a new term. If not, you should not try to locate the new record, since it isn't there.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •