Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    May 2002
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Any Suggestions (97)

    I have several input Forms to make data entry easy. They come in handy when a user wants to add a new record, but what if they want to update an old record. The user will have the ID of the record. Does anyone have any suggestion on how to enter the ID number to get all the data for the record. I don't want to use a combo box because there are a lot of records. I know that I could put I "FIND" button on the form, but I wanted a better way. All tool bars are hidden to users.
    thanks
    dgw

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

    Re: Any Suggestions (97)

    A combo box is still a good idea because of the AutoCompletion feature. The user doesn't have to use the dropdown list, just the text box part of the combo box. As soon as the user has typed enough info, the complete ID will be shown.

    Instead of a combo box, you can also use an unbound text box. If you want the form to display a record as soon as the user types a valid ID, you can put code in the OnChange event. However, this can be annoying: if 1, 12, 123 and 1234 are all valid ID's, the records for all of these will be displayed in turn as the user types 1234. As an alternative, you can put code in the AterUpdate event of the combo box.

    There is an important point to keep in mind: if you write code for the OnChange event, you must refer to the text the user is entering as [TextBoxName].Text. In the AfterUpdate event, you can refer to the text in the text box as [TextBoxName].

    In both cases, code could look like this:

    Dim rst As RecordSet
    Set rst = Me.RecordsetClone ' for Access 97
    rst.FindFirst "ID=" & [TextBoxName] ' or [TextBoxName].Text, see above
    If Not rst.NoMatch Then
    If Me.Dirty Then RunCommand acCmdSaveRecord
    Me.Bookmark = rst.Bookmark
    End If
    Set rst = Nothing

    Notes:

    If the ID is text, surround the value you're looking for with quotes:
    rst.FindFirst "ID=" & Chr$(34) & [TextBoxName] & Chr$(34)

    If you want to find the first record whose ID starts with the text entered, use
    rst.FindFirst "ID Like " & [TextBoxName] & "*"
    or
    rst.FindFirst "ID Like " & Chr$(34)[TextBoxName] & "*" & Chr$(34)

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Any Suggestions (97)

    Put an unboud textbox in the footer or header of the form.
    In the after update of this txtbox enter the following code :
    <pre>Private Sub MyTextBox_AfterUpdate()
    Dim strSQL As String
    strSQL = "Select * From MyTable Where nameid = " & Me.MyTextBox
    Me.RecordSource = strSQL
    If Me.Recordset.RecordCount = 0 Then
    MsgBox "There is no record with this ID"
    strSQL = "Select * From MyTable"
    Me.RecordSource = strSQL
    End If
    End Sub
    </pre>

    Replace MyTable with the Record Source of the form and MyTextBox With the name of the unbound textbox.

    edited for use of access 2000 function
    Sorry, Me.Recordset.RecordCount is a Access 2000 Function replace the line :
    If Me.Recordset.RecordCount = 0 Then
    by
    If Me.NewRecord = True Then
    Francois

  4. #4
    Lounger
    Join Date
    May 2002
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Any Suggestions (97)

    Francois ,
    your code works fine when I use it on one form, but when I try to use it on another form it gives me an error!
    Runtime error 2001
    You canceled the previous operation.

    Any Ideas why

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Any Suggestions (97)

    No idea, can you attache the database or send it by email (see my profile)
    Francois

  6. #6
    Lounger
    Join Date
    May 2002
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Any Suggestions (97)

    Francois,
    I found out why the error is occuring. My Id Field is set to a text field. If I change it to a number it works fine. Is there anyway that this will work if the Field is a text field?
    thanks for all the help
    DGW

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Any Suggestions (97)

    If you ID field is a text field you had to add quotes around the data :
    <pre>strSQL = "Select * From MyTable Where nameid ='" & Me.MyTextBox & "'"</pre>

    Francois

Posting Permissions

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