Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2004
    Posts
    5

    Unanswered: help codeing in one of the things.. ^^;;

    ok working on a project for school and i want a box on the forum when i enter something (a number) and press enter, i want it to look though the records and see if it already is there, if so bring it up, if not ask if you want to make a new record. i have no clue, help? thanks

  2. #2
    Join Date
    Jan 2004
    Posts
    5
    bump

  3. #3
    Join Date
    Oct 2003
    Location
    Chicago, IL
    Posts
    154
    you could do this:

    On the form, create a blank text box. Name it "lookup"

    Then, create a query, call it "lookup query" Use the wizard and have it select all records from the table you want to reference against. For the criteria of the field you want to check for the number, put [FormName]![lookup]

    then, create a form that displays the criteria discovered using that query as the source. If records are found, they will be displayed. If no records are found, you should be able to enter a new record.

    Maybe? Give it a shot. I'll be out for the rest of the evening, catch up tomorrow.

  4. #4
    Join Date
    Jan 2004
    Posts
    5
    good idea, almost works, but when i change the data in the 'lookup' text box it doesn't display the new queryed data, just changes it to be a new record.

  5. #5
    Join Date
    Oct 2003
    Location
    Chicago, IL
    Posts
    154
    I forgot to mention that you should put a button next to the [lookup] field that will kick off the form. Jus create a button that after you enter the value you want to look for, you click it and it launches the form from a macro or whatnot. Make sure that the new form uses the lookup query as the source. because of the criteria in the query, you should either have records that meet the criteria that will show up in the form or you will have a blank form, indicating there are not any records that meet the criteria...

    This won't address the messagebox popping up asking if you want to enter a new record, I don't know how to do that if there are no records, maybe someone else does?

    I hope this helps, sorry if it doesn't!

  6. #6
    Join Date
    Jan 2004
    Posts
    5
    yea it does, thanks for the help

  7. #7
    Join Date
    Nov 2003
    Posts
    1,487
    Another Way...

    Create a textbox and name it SearchItem.

    In the OnKeyDown event of the SearchItem text box copy & paste the following code (changing any names as required):

    Code:
    If KeyCode = vbKeyReturn Then   'If The ENTER key is hit.
       Dim FndRecord
       FndRecord = DLookUp("[myTableRecordID]", "myTableName", "[myTableFieldToSearch] = " & Me.SearchItem) 
       If Not IsNull(FndRecord) Then
          DoCmd.GoToRecord acDataForm, "myFormName", acGoTo, CLng(FndRecord)
       Else
          Dim x As Integer
          x = MsgBox("Record Not Found:@@" & "Do you want to create a new Record?", vbQuestion + vbYesNo, "Record Not Found")
          If x = vbYes Then
             DoCmd.GoToRecord acDataForm, "myFormName", acNewRec
          End If
       End If
       KeyCode = 0
    End If
    There ya go

  8. #8
    Join Date
    Jan 2004
    Posts
    5
    now i know that should work ^^;; but i can make it work, thanks though

Posting Permissions

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