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
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.
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?
In the OnKeyDown event of the SearchItem text box copy & paste the following code (changing any names as required):
If KeyCode = vbKeyReturn Then 'If The ENTER key is hit.
FndRecord = DLookUp("[myTableRecordID]", "myTableName", "[myTableFieldToSearch] = " & Me.SearchItem)
If Not IsNull(FndRecord) Then
DoCmd.GoToRecord acDataForm, "myFormName", acGoTo, CLng(FndRecord)
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
KeyCode = 0